Confident Cells

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.

Your turn

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.

ABCD
1MonthBudgetSpendSpend to date
2April£1,400.00£1,102.00
3May£1,400.00£1,618.00
4June£1,400.00£1,819.00
5July£1,400.00£1,449.00
6August£1,400.00£1,662.00
7September£1,400.00£857.00
8October£1,400.00£1,704.00
9November£1,400.00£1,112.00
10December£1,400.00£980.00
11January£1,400.00£1,178.00
12February£1,400.00£1,492.00
13March£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.
  • IF can flag the months that went over, and leave the rest blank with "".

Worked example

ABCDEF
1MonthBudgetSpentSpent to dateVarianceFlag
2Apr£500.00£420.00
3May£500.00£530.00
4Jun£500.00£610.00
5Jul£500.00£380.00
  1. =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.
  2. =C2-B2 in E2. Spent minus budget. May is £30 over and June £110 over; April and July are under, so they show minus figures.
  3. =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

  1. 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.
  2. 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.
  3. In D2, work out what share of each budget has been spent so far. Column D is formatted as a percentage.
  4. 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.
  5. 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.