Turn transaction data into a cost report that shows where the money is going

01

Decide what the report must answer

This guide builds a report that answers four questions most owners can’t answer from the profit and loss:

1. Where does the money actually go: serving customers, winning them, keeping them, or running the business?
2. Which of those is growing faster than the business?
3. Who are we paying the most?
4. What renews automatically, and has its price crept up?

Keep these four questions in front of you. They decide every mapping choice in steps 04 and 05.

02

Export the transaction listing

Every accounting system names this report differently. Look for a report that lists one row per transaction, with the account it was posted to, often called “Account Transactions”, “General Ledger Detail”, “Transaction Detail by Account” or “Detailed General Ledger”. A profit-and-loss summary is not enough.

1. Choose the last 12 complete months. Leave out the current, unfinished month.
2. Include only expense accounts: cost of sales, operating expenses and interest. Leave out income, bank, debtors, creditors and VAT control accounts.
3. Make sure the report includes: date, account name, supplier (or contact), description, reference and the amount excluding VAT.
4. Include supplier credits. They reduce costs and belong in the total.
5. Export as CSV if you can; Excel also works.
6. From the profit and loss for the same months, add up cost of sales, expenses and interest, and write down the total. You will check against it in step 06.

03 · Free route

Set up the spreadsheet and import the export

1. In Google Drive, create a folder named Cost report – live and a new Google Sheet inside it named Cost report.
2. Rename the first tab Data. Add four more tabs named Mapping, Classified, Checks and Report.
3. With the Data tab open, select File → Import → Upload and choose your export.
4. For Import location, choose Replace current sheet. Leave the separator on Detect automatically. Select Import data.
5. Select View → Freeze → 1 row so the headings stay visible.

This guide assumes the sample file’s column order: A Date, B Account code, C Account name, D Supplier, E Description, F Reference, G Source, H Amount excl VAT, I VAT, J Amount incl VAT. If yours is different, step 05 shows how to adapt the formulas.

04 · Free route

Build the mapping table

This is the heart of the guide. Your accounts say what kind of cost something is. The mapping says what it is for. It uses six management groups:

Delivering the service

What it costs to serve paying customers: materials, stock resold, tools used on client work, site travel.

Winning new customers

What it costs to get a first sale: advertising, lead generation, sales commission, sales tools.

Keeping customers

What it costs to hold on to the customers you have: renewal discounts, review meetings, newsletters, client events.

Running the business

Central costs: rent, admin, accounting, insurance, office software.

Owner and non-operating

Spending that is not part of running the business: loan interest, personal purchases on the business card.

Not yet classified

Anything your mapping can’t place. This must be close to zero before you trust the report (step 08).

1. In the Mapping tab, type these headings in row 1: A Account, B Group, C Renews automatically, E Keyword, F Group, G Renews automatically, I Group, J Share of salaries.
2. In cell A2, list every account in your export: =SORT(UNIQUE(FILTER(Data!C2:C, Data!C2:C<>"")))
3. Select column A, copy it, and paste it back with Edit → Paste special → Values only. The list now stays put when next month’s data arrives, so your groups stay next to the right accounts.
4. For each account, type a group in column B, exactly as written above, and Yes or No in column C for whether it renews without anyone deciding. For salaries, type Split by role (step 05 explains). For an account that mixes purposes, pick its most common purpose for now; step 05 handles the exceptions.

The sample file’s accounts are mapped like this:

Want a second opinion? Ask ChatGPT. It only needs your account names, not your transactions:

05 · Free route

Add rules for mixed accounts and split salaries

Keyword rules

A rule says: when this word appears in the supplier or description, use this group instead of the account’s. To find the mixed accounts, select Data → Create a filter on the Data tab, filter one account at a time, and read down the suppliers. Anywhere you think “that one is really for something else”, add a rule in columns E to G of Mapping.

• Keywords are not case sensitive, and part of a word is enough: Signal Leads or Signal.
• The first matching rule wins, so put specific keywords above general ones.
• Leave Renews automatically blank to keep the account’s setting.
• Remove the filter when you are done (Data → Remove filter).

The sample needs these rules:

Salary split

Salaries are usually the biggest cost, and they arrive as one journal a month. Leaving them in one lump hides most of what it costs to win and keep customers. In Mapping columns I and J, enter the four operating groups and the share of payroll each one takes. The shares must add up to 100%.

Work the shares out on paper, not in the sheet. List each role, its monthly cost from your payroll report, and roughly how that person’s time divides. A technician is all delivery; a salesperson might be 80% winning and 20% keeping. Add the rand amounts per group and divide by total payroll. Only the four percentages go into the workbook, never names or individual pay.

If your columns are different

Using the sample file? Skip to step 06. Otherwise, give ChatGPT your column headings and two or three rows you invent by hand, never the export itself:

06 · Free route

Classify every line and check the data

In the Classified tab, type these headings in row 1: A Date, B Month, C Account, D Supplier, E Description, F Amount, G Group, H Renews. Then enter three formulas. Each one fills its columns all the way down and grows with next month’s data, so there is nothing to drag.

Select column A and choose Format → Number → Date so dates don’t show as numbers.

Now, in the Checks tab, enter these labels in column A and formulas in column B. The last column shows what the sample listing should give. (The report-total check shows an error until step 07 is done.)

Then work through these before going further:

• Total excl VAT must match the profit-and-loss total you wrote down in step 02. If it doesn’t, the export is missing an account, includes VAT, or covers the wrong months.
• Months in the data must be 12. The comparison in step 07 relies on it.
• Accounts missing from Mapping must be 0. If not, a new account has appeared: add it to the bottom of column A in Mapping and give it a group.
• Salary split adds up to must be 100%.
• Lines that appear more than once counts both copies. In the sample, one bookkeeping bill was captured twice, so the books themselves are overstated by R6 500. Ask your bookkeeper to reverse the duplicate, then export again.

To see the unclassified lines, enter this in cell D1 of the Checks tab: =FILTER(Classified!A2:F, Classified!G2:G="Not yet classified"). The sample shows 12 card purchases with descriptions like “Card purchase - online store”. Step 08 deals with them.

07 · Free route

Build the report

The Report tab has five parts. Build them in this order.

1. Where the money went (A1:C7)

1. Type Group, Last 12 months and Share in A1:C1.
2. Type the six management groups in A2:A7, in the order listed in step 04.
3. In B2: =SUMIF(Classified!$G$2:$G, A2, Classified!$F$2:$F) + SUMIF(Classified!$G$2:$G, "Split by role", Classified!$F$2:$F) * IFERROR(VLOOKUP(A2, Mapping!$I$2:$J, 2, FALSE), 0)
4. In C2: =B2/SUM($B$2:$B$7). Copy B2:C2 down to row 7.

2. Month by month (A11 onwards)

1. Type Month in A11. In B11: =TRANSPOSE(A2:A7)
2. In A12: =SORT(UNIQUE(FILTER(Classified!B2:B, Classified!B2:B<>"")))
3. In B12, then copy it across to G12: =MAP($A12:$A, LAMBDA(m, IF(m="", "", SUMIFS(Classified!$F$2:$F, Classified!$B$2:$B, m, Classified!$G$2:$G, B$11) + SUMIFS(Classified!$F$2:$F, Classified!$B$2:$B, m, Classified!$G$2:$G, "Split by role") * IFERROR(VLOOKUP(B$11, Mapping!$I$2:$J, 2, FALSE), 0))))
4. Select A11:G23, choose Insert → Chart, and set the chart type to Stacked column chart. Drag the chart below the table, to row 26 or lower.

3. First three months against the last three (E1:H7)

1. Type Group, First 3 months, Last 3 months and Change in E1:H1.
2. In E2: =A2
3. In F2: =SUM(INDEX($B$12:$G$14, 0, MATCH(E2, $B$11:$G$11, 0)))
4. In G2: =SUM(INDEX($B$21:$G$23, 0, MATCH(E2, $B$11:$G$11, 0)))
5. In H2: =IF(F2=0, "", G2/F2-1). Copy E2:H2 down to row 7.

4. Top suppliers, excluding payroll (J11)

In J11: =QUERY(Data!A1:J, "select D, sum(H) where D is not null and D <> 'Payroll' group by D order by sum(H) desc limit 10 label sum(H) 'Last 12 months'", 1)

5. Contracts that renew on their own (M11 onwards)

1. Type Supplier, First charge, Latest month and Change in M11:P11.
2. In M12: =SORT(UNIQUE(FILTER(Classified!D2:D, Classified!H2:H="Yes")))
3. In N12: =MAP(M12:M, LAMBDA(s, IF(s="", "", INDEX(FILTER(Classified!F2:F, Classified!D2:D=s), 1))))
4. In O12: =MAP(M12:M, LAMBDA(s, IF(s="", "", SUMIFS(Classified!F2:F, Classified!D2:D, s, Classified!B2:B, $A$23))))
5. In P12: =MAP(N12:N, O12:O, LAMBDA(first, latest, IF(first="", "", latest/first-1)))

Format the Share, Change and Last 12 months columns as percentages or currency with Format → Number. When you are done, Report total minus data total on the Checks tab must show 0.

08 · Free route

Read the report and close the gaps

Close the gaps first

Unclassified lines are a bookkeeping gap, not a spreadsheet problem. Send the list from step 06 to whoever holds the card or does the books, and ask them to recode each one to the right account or add the supplier and a description in the accounting system. Then export again. Don’t fix them in the sheet: the books would stay vague and next month the gap comes back.

You can use the report while Unclassified share of total is under 1%. Above that, close the gaps before drawing conclusions. The sample is at 0.25%, so it is usable.

What the sample shows

• Total costs grew 4.8%, but the cost of winning customers grew 43.6%. On the profit and loss, Advertising looks flat: R14 950 in November, R14 611 in October. Inside it, Google Ads went from R5 850 to R14 061 a month while client events faded away. And a lead-generation retainer that started in February sits in Professional fees, where nobody would look for it.
• The cost of keeping customers fell 13%. Fine if it was a choice. Worth asking about if nobody decided it, especially while more is being spent winning new ones.
• Three of 20 renewing contracts changed price: NetWatch rose 18% and OfficeOne 12%, and the Signal Leads retainer more than doubled, from R5 000 to R11 000.
• Three pairs of software tools do the same job (remote access, passwords, file sharing). The renewing list puts them side by side; ask which one in each pair you need.

03 · AI workspace route

Make a safe upload copy and turn off training

1. Open your export and delete every column the report doesn’t use. Keep only: date, account name, supplier, description, reference and amount excluding VAT.
2. Check that payroll appears as one line a month, with no employee names or individual pay (see step 02). If it doesn’t, sort that out with your bookkeeper before uploading anything.
3. Where a supplier is a private person, such as a freelancer or a landlord, replace their name with a fixed label such as Contractor 1. Use the same label every month so the mapping still recognises them.
4. On your computer, create a folder named Cost report and save the copy there as cost-listing-upload.csv. Practising? Do the same to the sample file: delete its Account code, Source, VAT and Amount incl VAT columns.
5. In Claude: select your name, then Settings → Privacy, and turn off Help improve our AI models.
6. In ChatGPT: open your account menu, then Settings → Data controls, and turn off Improve the model for everyone.

04 · AI workspace route

Open your AI workspace

Follow the instructions for the tool you pay for. Both lead to the same place.

Both apps change often. If a label here doesn’t match what you see, look for the option to give the app access to a folder on your computer.

05 · AI workspace route

Let the AI propose the mapping, then decide it yourself

Read the six management groups and the note on why correct totals can hide the problem in step 04. Then work out your salary split on paper, as described under “Salary split” in step 05. Paste this prompt into Claude or ChatGPT. It is the same for both.

1. Open Mapping.xlsx and go through every row. The AI knows accounting conventions, but only you know that the client lunch was about keeping a customer or that a tool is used on client work. Change anything that’s wrong, directly in the file.
2. Check that the salary split adds up to 100%.
3. Save the file. From now on, this mapping is yours: the AI uses it but doesn’t change it.

Practising with the sample? Use this prompt instead, so your results match the ones in this guide:

06 · AI workspace route

Ask for the whole workbook in one go

Start a new task in the same folder and paste this prompt. It is the same for both tools.

07 · AI workspace route

Check its work

1. Open the workbook. Select three numbers on the Report tab and confirm the formula bar shows a formula, not a typed number.
2. Compare the total on the Checks tab with the profit-and-loss total you wrote down in step 02, and confirm Report total minus Data total is 0. Practising with the sample? Every Checks value should match the “Sample result” column in step 06.
3. Open the Mapping tab and confirm it is exactly your Mapping.xlsx, with nothing added or changed.
4. Pick five lines on the Classified tab, including one from a mixed account, and check each landed in the group you would choose.
5. Compare the Report with step 08’s tables. With the sample, Winning new customers is R695 082 over the year and rises 43.6% from the first three months to the last three; NetWatch, OfficeOne and Signal Leads are the only renewing suppliers whose price changed.
6. If anything differs, tell the AI exactly which number is wrong and what it should be, and ask it to show the formula it used. Check again after every fix.

Then read the report and close the gaps exactly as in step 08.

08 · AI workspace route

Save the monthly refresh and know what you’re trusting

Keep this prompt. You will use it every month in step 09.

• The workbook is yours. It keeps working without the AI tool; only the refresh needs it.
• Keep last month’s workbook. If a refresh goes wrong, you can compare the two or go back.
• The AI’s summary is a convenience. The Checks tab is the evidence. Read the checks, not only the summary.
• The mapping is a decision. Change it yourself, and only after thinking about it.

09

Refresh it next month

1. Export the latest 12 complete months the same way as in step 02.
2. Free route: open the Data tab, select File → Import → Upload, and choose Replace current sheet. AI workspace route: make the upload copy as in step 03, then run your saved refresh from step 08.
3. Look at the Checks tab. Fix anything that isn’t as expected, especially new accounts, new unclassified lines and duplicates.
4. Read the report against the four questions from step 01. Write down one action.

10

Keep it useful

• Monthly: refresh, check, and add rules for any new supplier that belongs somewhere other than its account’s group.
• Every six months: redo the salary split. Roles change, and so does how people spend their time.
• Before any renewal: look at the renewing list and decide, rather than let it happen.
• Backups: before changing the mapping, select File → Make a copy (AI route: copy Cost report.xlsx in your folder).

Common fixes

The total doesn’t match the profit and loss

Look for a missing account (often cost of sales or interest), VAT-inclusive amounts, an extra month, or income accounts included by mistake.

Report total minus data total isn’t 0

A group in Mapping column B or F is spelt differently from the six in the report. Copy and paste the group names rather than retyping them.

A supplier lands in the wrong group

A rule higher up the list is matching first. A short keyword like Coffee can catch more than you meant. Make the keyword more specific or move the rule down.

Dates show as text (Dates not recognised is above 0)

Check File → Settings → Locale matches the date format in your export, then import again. Exporting dates as year-month-day avoids the problem altogether.

A formula shows #ERROR! or #VALUE!

If your spreadsheet uses semicolons, replace the commas between formula parts with semicolons. Otherwise paste the formula and error into ChatGPT and ask what is wrong, without any data.