Tag Archives: SUMIF function

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.

Analyzing Your Cumulative Net Calories

Calorie Data Analysis

I started tracking my cumulative net calories–and just by doing this I have started getting healthier. If you are overweight and like crunching data this might work for you!

My formula is: NC=M-E  (where M=meal calories consumed and E=exercise calories burned)

Here is how I am doing (as at 11th March), looking at cumulative net calories over time.

calorie data analysis
calorie data analysis

Method for Calorie Data Analysis

Watch the video below to see the method I used to track my daily net calorie flow (intake less burned), or go straight to the summary steps below. Get the file from the video here.

5-Step Method

  1. Create a dataset with four headings: date, item, calories_in, calories_out, net_calories
  2. Look online to estimate your daily calorie burn (based on age, height, and weight). For example here.
  3. Update your dataset each day for items consumed (e.g. meals + drinks) or calorie-burning activities done (like playing sport, cleaning your house, cycling to shops, etc.). Just google ‘[food] calories’ (e.g. ‘avocado calories’ leads here). You will quickly get good at guessing. Same thing goes for exercise.
  4. Add an input range for your chart and use the SUMIF function to get the total net calories per day. Then use a cumulative SUM function to get the cumulative total. To do this just lock the first cell in your range and drag down (in my example ‘=SUM(P$11:P14)’ see video!)
  5. Create your chart and you’re done!

A Genuine Learning Experience

For me doing this has been quite illuminating. I got the idea from my cousin who lost a lot of weight several years ago and when asked how he said it was just because he started measuring what he was eating. A true testament to the value of data analysis for improving performance. As Thomas Monson has said ‘When performance is measured, performance improves’.

In my case, I think this works partly because I enjoy analyzing data. In recent months I have made attempts to regulate my diet with rules like:

  • No eating after 7pm
  • Small meals Thursdays
  • Half-fasting once a week

And so on. But these have not worked. What has been astounding to me (since I have literally NEVER measured calorie intake in my whole life) is that I could consistently come in under my base burn each weekday, no problem. But then on takeaway Friday or Sunday dinner I could quite easily blow out by +1000kcal for the day! Notably, eating 2/3 of a 200g bar of chocolate will clock about 700kcal (about 1/2 what I had eaten all day on a normal weekday!)

Anyway, all this has helped me to regulate my diet without really exercising too much will power. So it is worth a try.

Do you like this post? Why not subscribe to Analyze 6?

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!