Tag Archives: creating a pivot table cross-tab

Excel Cash Flow Analysis

In-Year Cumulative Cash Flow is Key

For about 12 years now I have tracked my current account bank data in Excel, tweaking my process to look for better ways of managing my budget. In-year cumulative cash flow is the best way to gauge personal finances (from a basic budgeting perspective). The chart below shows in-year cumulative cash flow for 2.5yrs and you can see that net gains in 2012, 2013, and 2014 to date were respectively £6k, £3k, and £5k.

Excel Cash Management

You can also see that large deficits in some months were compensated by other months’ gains in all three years.

Benefits of Excel Cash Flow Analysis

Cumulative cash flow analysis can really help you manage your personal in-year finances and help you in the following ways:

  • It gives you the ability to manage your cash flow effectively
  • Better financial discipline
  • Ability to understand cash flow movements over time, get perspective, and set goals
  • Understand (and not get phased by) dips/peaks in monthly cash flows

The basics of this technique include setting an annual budget split by expense/income category and by month, then setting shorter term budgets one month ahead throughout the year. The trick is to become good at predicting what will happen. So you need to track what you expected vs. what happened… both over the course of the year, and also in smaller increments (monthly, or quarterly).

This video shows you how to extract transaction data from your online bank account and set it up in Excel so you can monitor budget vs. actual cash flows. Summary steps for this process are outlined below. Click here to get the final file from the video.

8-Step Summary for Excel Cash Flow Analysis

  1. Pull your online banking transaction data into Excel (e.g. Bank of Scotland, or Wells Fargo)
  2. Categorize by Account Type (choose 6-12 categories)
  3. Set an annual budget, split by category and month
  4. Use a dynamic matrix (drawing on SUMIF) to summarize your actual income/expense data
  5. Aggregate monthly budgeted vs. actual cash flows
  6. Get the cumulative in-year sum of your monthly aggregated cash flows using the form: =SUM(A$1:A1) and filled down
  7. Chart the cumulative in-year budgeted and actual cash flows to visualize and understand the trend
  8. If you have multi-year data, create a pivot table to analyze trends and use them to inform your current budget decisions

Excel Personal Finance Musings

As I alluded to above, Excel personal finance is one of the first ways I learned to use Excel, and it is something I actually get a kick out of. But over the years I have sometimes found the process of gathering bank data cumbersome (e.g. I used to do it weekly) or somewhat fruitless (when I wasn’t really gleaning the insight I needed from the analysis).

As I mentioned earlier, the trick with this is to be able to accurately anticipate and forecast cash flow. Once you can do this with your bank account, you can start setting meaningful goals and getting better discipline around where your money is going. The idea of forecasting then base lining is something that subconsciously dawned on me after working closely with a finance team for a couple of years and with their LE balancing, Month-end, accrual adjustments, and 3yr-Plan reconciliation processes.

From a personal finance perspective, I used to measure average expenditure based on rolling 3 and 6 month periods–this was good for gauging whether or not our outgoings were sustainable. But the cumulative in-year cash flow really felt like a breakthrough when I started actively monitoring that instead. The benefit of the ‘cumulative’ element is that some big expenses may not occur in the month you budget for–so you do really well in month x, but go massively in the red for month x+1–simply because your budget and actuals aren’t perfectly aligned.

Anyway, hopefully this is useful to someone. Let me know below if you have anything to add or share.

Pivot Tables Starter Guide

This tutorial shows you some basic techniques for how to use pivot tables with a simple bank account data example. It includes a video demo and an Excel download. We start with some bank account data for one month (and your online banking service will provide similar e.g. Halifax) and use pivot table techniques to view it by category and week as shown:

How to use pivot tables: current account data for one month
How to use pivot tables: current account data for one month

How to use Pivot Tables Tutorial

Watch this video to see how it is done:

How to use Pivot Tables Example: Tutorial 5-Step Summary

  1. Get your data into Excel and make sure the data is contiguous (i.e. no completely blank rows or columns), also make sure your data has column headers in one row (i.e. not merged headers over two rows). My example (Excel file HERE) has four columns of data (date, transaction, CR, and DB).
  2. Press Alt+D+P to open the New Pivot Table dialogue and enter your source range as well as destination (top-left) pivot table cell. Then drag the CR and DB fields into the VALUES section of the pivot table
  3. Add AcType column and create a category for each transaction, also add a Cashflow column with the to show the net cash flow (i.e. CR-DB) for each transaction, and finally add a Week Commencing column to get the Week Commencing (Monday) date for each transaction
  4. Change the pivot table datasource to include your additional columns
  5. Drag the Week_Commencing field into the Columns section, drag the AcType field into the Rows section, and drag the Cashflow field into the Values section

Applying these Basic Pivot Table Skills

I have been using Excel for data analysis professionally for ca. 10yrs across 5 industries and pivot tables are by far the best Excel tool for ad-hoc data analysis. This article is designed as a primer or starter guide to pivot tables showing their utility by using data we are pretty familiar with.

The pivot table basics taught in this lesson are applicable to any dataset where you want to aggregate and cross-tabulate calculations–e.g. survey results analysis, analyzing sales trend data, operational or production data analysis, splitting marketing data by channel, etc. Look out for other articles on Analyze6.com for more advanced pivot table technique tutorials.

Techniques Learned in this Tutorial

changing pivot table data source, modifying underlying pivot table data, create a pivot table, add a calculation to pivot table, pivot table value formats, basic pivot table skills, changing pivot table settings, creating a pivot table cross-tab