Tag Archives: Dynamic CrossTabs

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.

Create a Dynamic Crosstab in Excel

Create a Dynamic CrossTab Range

This tutorial is going to teach you how to create a dynamic crosstab that pulls a vertical dataset into a crosstab with two dimensions you choose. To see how it is done watch the video below or jump straight to the summary step guide below. The associated file from the video is here.

7-Step Summary Guide

  1. Get your dataset in place and identify the two variables you want to get a cross tab for
  2. Draw up the two dimensions of your crosstab with one variable in rows and the other in columns
  3. Go back to your dataset and create a key for each record/row using the CONCATENATE function or ‘&’ operator (see video). This will be the KEY that your cross tab vectors will use to get their values
  4. If you are going to use VLOOKUP to pull back the associated values into your crosstab make sure the value you want to pull in is to the right of your KEY column
  5. Go to your cross tab and in the values area write a VLOOKUP formula. Be sure to lock the column of your column dimension and the row part of your row dimension, and you will need to fully lock the reference table argument in your formula (e.g. =VLOOKUP($M5&N$4,$H$1:$I$51,2,FALSE)). If you’re doing this you might want to also wrap an IFERROR function around the VLOOKUP formula (see video!)
  6. If your dataset has only unique KEY records (i.e. they are not duplicated in the KEY column you created) you are done… if you want to sum or count and there are duplicate instances of your key, use SUMIF or COUNTIF functions (or array functions if you know how–see this article for more on array formulas)
  7. To use a sumif, just replace your VLOOKUP formula with a SUMIF formula in the same form. My example has this one: =SUMIF($H$2:$H$51,$M5&N$4,$I$2:$I$51)

When to Use this Technique

This is a truly dynamic technique ubiquitous among Excel modelers that I have seen in my career. It is also useful in my expert view. If you are doing quick analysis always go straight for the pivot table to do this kind of thing. If you want to create something that updates dynamically without using a pivot table–and you know the extent of the dimension ranges in advance (by this, I mean you know all the Weeks and AcTypes, or whatever else you are examining, in advance) then use this type of thing.

These can be great for scorecards or other data tracking tools that auto update using Excel formulas.

Got great ideas for applying this? Let me know in the comments below. Like this application?–then subscribe to my newsletter!