Three numbers per category
In short: plan, actual and variance. The plan is your budget for the category, the actual the sum of the month's entries, the variance actual minus plan – positive means you spent more than planned. Add the variance in percent so large and small categories become comparable.
| Category | Plan | Actual | Variance |
|---|---|---|---|
| Groceries | CHF 1’100 | CHF 1287.40 | +CHF 187.40 (+17 %) |
The formula in Excel
SUMIFS does the actual: =SUMIFS(Amount,Category,"Groceries",Date,">="&MonthStart,Date,"<="&MonthEnd). If expenses are stored as negatives, put a minus in front. Variance is =Actual-Plan, in percent =IF(Plan=0,"",(Actual-Plan)/Plan).
Conditional formatting makes patterns visible: red from plus 10 percent AND more than CHF 50 variance. The double condition stops a category with a CHF 20 budget from lighting up at CHF 25 actual.
Which gaps matter
Not every gap is a problem. Annual and quarterly bills, holidays and December gifts create one-off spikes; they belong in a sinking fund, not in the monthly comparison. Real signals are recurring overruns in variable categories – groceries, restaurants, leisure, online shopping.
Next to the month, always look at the rolling three-month average. If it sits above plan, either the plan was too low or a habit has changed. Both deserve a decision.
When the plan is wrong
A budget you exceed every month is not a savings target but a misjudgement. Adjust it to reality, then decide deliberately where to cut. A realistic plan with a small buffer lasts longer than an ambitious one abandoned after three months.
Conversely, if a category stays below plan for good, money is freed up. Move it on purpose into savings goals – otherwise it vanishes into everyday spending.
Year view: cumulative instead of month by month
Next to the monthly view, use the cumulative one: plan and actual from January to the current month. A category over budget in March but under in January and February may be exactly on track for the year. The formula is the same SUMIFS with the start of the year instead of the month.
For volatile categories – clothes, gifts, health – the year view is the only sensible one. Give them an annual budget and read only the cumulative figure; you avoid false alarms in December and false comfort in February.
Plan and actual, ready-made
In the household budget for Excel plan and actual sit per category and month on one sheet, with every active category included; the dashboard shows spending by group against budget and «available until month end». CHF 49 once.
In the BudgetHub app you see the status of each budget during the month, not only afterwards.