You will produce a one-page monthly summary showing spending by category against a target.
Priya now has March in a spreadsheet: 87 rows, each with a date, a description, an amount and a category she has checked. What she doesn't have yet is the answer to the question she started with, which is where the money went and whether that's where she wanted it to go.
This exercise builds the one-page summary that answers it. Every number on the page will come from a formula you can trace back to rows, so when a total surprises you, you can see exactly why.
Put the work on three tabs in one file.
The first tab, Rows, holds the labelled transactions: date in column A, description in B, amount in C, category in D. This is what 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.
The second tab, Rules, holds your merchant rules from lesson 2.3, one per line.
The third tab, Summary, is the page you're about to build. Keeping the three apart is what makes next month quick. You'll paste a new month into Rows, and the Summary updates on its own.
On the Summary tab, list your categories down column A, one per row, starting at A2. Put a heading in B1 called Actual.
First, give the two columns on the Rows tab names, so formulas on other tabs can refer to them cleanly. Select column D on Rows and name it Categories, then select column C and name it Amounts. In Excel you type the name into the box to the left of the formula bar; in Google Sheets you use named ranges from the Data menu.
In B2, enter a formula that adds up every amount whose category matches A2. In both Excel and Google Sheets that is:
=SUMIF(Categories, A2, Amounts)
Read it from left to right: look down the Categories column, find every cell equal to the category in A2, and add up the matching amounts. Copy the formula down for every category. If a category shows zero when you know you spent money there, the usual cause is a spelling difference, 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 Rows data, insert a pivot table, put category in rows and amount in values as a sum. Use whichever you'll understand next month. Either way the total comes from the rows, and nobody types it in.
Remember the transfer category from lesson 2.3. List it on the summary so you can see it, but leave it out of your spending total. If your total sits in a cell at the bottom, add up the categories above it and skip the transfer row.
Put Target as the heading of column C and Difference in column D. In column C, type the amount you want to spend in each category in a month. If you set a spending plan in The Singapore personal finance system, lesson 2.2, Set a spending plan from your real numbers, use those figures. If not, use your first month's actuals rounded to a sensible number and adjust later.
In D2, enter =C2-B2 and copy it down. A positive difference means you spent less than the target. A negative one means you went over.
Here is part of Priya's summary, with figures that are an example. Groceries came to S$642.80 against a target of S$600, a difference of minus S$42.80. Eating out was S$418.35 against S$350, minus S$68.35. Transport was S$186.20 against S$200, plus S$13.80. She saw at once that food as a whole, both kinds together, was over by about S$111, and that transport was fine.
Formulas can be wrong too, and you won't know until you test them. Pick a category with only a few rows, filter Rows to show just that category, and add the amounts on a calculator or in your head.
Priya picked a short category and added three rows by hand: S$12.50, S$8.90 and S$64.30. That's S$85.70. Her SUMIF cell showed S$85.70, so the formula was picking up the right column and the right label. If the figures disagree, look for a row with the category spelled differently, an amount stored as text (it often sits on the left side of the cell instead of the right), or a refund with the wrong sign.
Do this for at least two categories. It takes a couple of minutes and it's the reason you can trust every other number on the page.
If you get stuck on a formula at any point, that's a good moment to use the assistant for what it's good at. Describe your layout, not your data: "My transactions are on a tab called Rows, with amounts in column C and categories in column D. On a Summary tab I have category names in column A. Write a formula for column B that totals each category." You'll get a working formula far more often than a working total.
Then test it the same way, on rows you can add by hand. A formula the assistant wrote is still just a suggestion until it matches a sum you checked.
A finished budget is one page: every category, its actual, its target, the difference, and a total that leaves transfers out. Behind it sit the rows and the rules. You should be able to point at any figure and show which transactions made 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, and check two category totals by hand.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).