Build a month's budget from your own data

You will be able to make a one-page monthly summary that shows your spending in each category next to a target.

A monthly budget summary is one page listing each spending category with what you actually spent, what you meant to spend, and the difference between the two. Every number on it comes from a formula, never a total you typed in, so when a figure surprises you, you can trace it back to the transaction rows that produced it.

Priya (an example learner) has March in a spreadsheet: 87 rows, each with a date, a description, an amount and a category she has already checked. She still doesn't know where the money went, or whether that is where she wanted it to go. The summary answers both questions, and building it takes 4 steps.

Setting up the file with three tabs

Use one file with 3 tabs: Rows, Rules and Summary.

The Rows tab holds the transactions: date in column A, description in column B, amount in column C and category in column D. This is the labelled output you pasted back from the assistant in lesson 2.2, "Ask for categories, not totals", with the corrections from lesson 2.3, "Check the labels and fix the rules", applied. The Rules tab holds the merchant rules from lesson 2.3, one rule per line. The Summary tab is the page you are building.

The tabs stay separate so that next month you can paste a new month into Rows and the Summary updates on its own, with nothing to retype.

Totalling each category with SUMIF

On the Summary tab, list your categories down column A starting at cell A2, and type the heading Actual in B1.

Before writing any formulas, name two columns on the Rows tab so formulas on other tabs can refer to them cleanly. Name column D Categories and column C Amounts. In Excel, select the column and type the name into the box to the left of the formula bar. In Google Sheets, use named ranges from the Data menu.

Then in B2 enter =SUMIF(Categories, A2, Amounts) and copy it down. SUMIF adds every amount whose category matches a given label, and the formula is the same in Excel and Google Sheets. Read it as: look down Categories, find every cell equal to the category in A2, and add up the matching amounts. If a category shows zero when you know you spent money there, the usual cause is a spelling difference between tabs, such as "Eating out" on one tab and "eating-out" on the other.

A pivot table does the same job, and some people find it easier. Select the data on Rows, insert a pivot table, and put category in rows and amount in values as a sum. Choose whichever you'll still understand next month. Either way, the total comes from the rows.

The transfer category you set up in lesson 2.3 goes on the summary so you can see it, but it stays out of the spending total. If your total sits in a cell at the bottom, have it add the category rows above it and skip the transfer row.

Adding a target and a difference

Put the heading Target in column C and Difference in column D. Type a monthly target for each category into column C. If you set a spending plan in The Singapore personal finance system, lesson 2.2, "Set a spending plan from your real numbers", take the targets from there. If you don't have one, use this first month's actuals rounded to a sensible number and adjust them later.

In D2 enter =C2-B2 and copy it down. The difference is the target minus the actual. A positive figure means you spent less than the target, and a negative figure means you went over.

Priya's summary shows Groceries at S$642.80 against a target of S$600, a difference of minus S$42.80. Eating out is S$418.35 against S$350, minus S$68.35. Transport is S$186.20 against S$200, plus S$13.80. She can see at once that food overall was over by about S$111 (S$42.80 + S$68.35 = S$111.15) and that transport was fine.

Checking the formulas against hand-added sums

A formula can be wrong, and you won't know until you test it. Pick a category with only a few rows, filter Rows to show just that category, add the amounts with a calculator or in your head, and compare your answer with the SUMIF cell. If they match, the formula is reading the right column and the right label.

Priya picks a short category with three rows: S$12.50, S$8.90 and S$64.30. By hand that comes to S$85.70, and her SUMIF cell also shows S$85.70.

If your figures disagree, look for:

a row whose category is spelled differently from the label on the Summary tab an amount stored as text, which often sits on the left of the cell instead of the right a refund entered with the wrong sign

Test at least 2 categories. It takes a couple of minutes, and it is what lets you trust every other number on the page.

If you get stuck on a formula, ask the assistant. Describe the layout, not the data: which tab, which columns, what each column holds and what you want the cell to show. Asking for a formula gets a working result far more often than asking the assistant for a working total. Treat any formula it writes as a suggestion until it matches a sum you checked by hand, and test it the same way.

When you're done you have one page listing every category with its actual, target and difference, plus a total that leaves out transfers, with the rows and rules sitting behind it. You can point at any figure and show which transactions produced it.

Your own month goes through the same four steps now, and it will take longer the first time than it ever will again.

Build the budget summary for your month with category totals, targets and differences. Add up two of the category totals by hand to check them.

Course

Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).