Budgets and spend to date: practice exercises
Running totals, variance, % spent, projections. 5 exercises on real-looking data, each marked the moment you press Check. This lesson is free, with no account needed.
In D2, work out the spend to date: the total of every month so far, up to and including that row. Your formula will be copied down to D13.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Budget | Spend | Spend to date |
| 2 | April | £1,400.00 | £1,102.00 | |
| 3 | May | £1,400.00 | £1,618.00 | |
| 4 | June | £1,400.00 | £1,819.00 | |
| 5 | July | £1,400.00 | £1,449.00 | |
| 6 | August | £1,400.00 | £1,662.00 | |
| 7 | September | £1,400.00 | £857.00 | |
| 8 | October | £1,400.00 | £1,704.00 | |
| 9 | November | £1,400.00 | £1,112.00 | |
| 10 | December | £1,400.00 | £980.00 | |
| 11 | January | £1,400.00 | £1,178.00 | |
| 12 | February | £1,400.00 | £1,492.00 | |
| 13 | March | £1,400.00 | £950.00 |
How it works
A budget sheet compares what was planned with what was spent. Variance is actual minus budget, so an overspend comes out positive.
A running total adds up everything so far: =SUM($C$2:C2) locks the start of the range and lets the end move as it's copied down. COUNT counts only the months that have figures in, which is useful for projecting a full year.
=SUM($B$2:B2) =C5-B5 =IF(C5>B5,"Over budget","") =AVERAGE(C2:C5)*12
Running totals and variances
A budget sheet usually asks three questions each month: how much have we spent so far, how far are we over or under, and which months went over?
- Spent to date is a running total:
=SUM($C$2:C2). The start is locked with $, the end isn't, so each row adds one more month. - Variance is spent minus budget. Positive means over budget, negative means under.
IFcan flag the months that went over, and leave the rest blank with"".
Worked example
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Budget | Spent | Spent to date | Variance | Flag |
| 2 | Apr | £500.00 | £420.00 | |||
| 3 | May | £500.00 | £530.00 | |||
| 4 | Jun | £500.00 | £610.00 | |||
| 5 | Jul | £500.00 | £380.00 |
=SUM($C$2:C2)in D2. Copied down, the range grows: D3 adds C2:C3, D4 adds C2:C4, and D5 shows the total spent so far, £1,940.=C2-B2in E2. Spent minus budget. May is £30 over and June £110 over; April and July are under, so they show minus figures.=IF(C2>B2,"Over","")in F2. IF flags the months where spending beat the budget, and leaves the others empty.
Percentages and forecasts
- Percentage spent is spent divided by budget,
=C2/B2, shown as a percentage. 110% means 10% over. - A simple forecast for the year takes the average month so far and multiplies by 12:
=SUM(C2:C5)/COUNT(C2:C5)*12. COUNT only counts months that have a figure, so blank months later in the year don't drag it down.
What you'll practise
- In D2, work out the spend to date: the total of every month so far, up to and including that row. Your formula will be copied down to D13.
- In D2, work out the variance for each budget line: actual minus budget, so an overspend shows as a positive number and an underspend as negative.
- In D2, work out what share of each budget has been spent so far. Column D is formatted as a percentage.
- In D2, show Over when the actual spend is more than the budget, and leave the cell blank otherwise. A line that's exactly on budget isn't over.
- Only some months have been reported so far. In H1, project the spend for the whole year: the average of the months reported so far, × 12.