excel cash flow analysis

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.

Share your thoughts below :)