WORKED EXAMPLE · ACCOUNTANTS AND AUDITORS

Clean a council’s spend data, summarise it and test it for duplicates

An internal auditor downloaded Telford & Wrekin Council’s published spend over £100 for December 2025: 3,161 payments in one sheet, a total row at the bottom and stray spaces in supplier names. tidied the sheet without losing a row, added a Summary sheet of formulas and a chart that match the council’s total to the penny, and flagged six possible duplicate payments. The Summary sheet took a second ask, and the Excel comments on the flagged rows were not made.

  • Not a row lost3,161 payments kept in their order, credit notes included. The amounts still add up to the council’s own total, £20,034,704.27.
  • Summaries as formulas, not pasted numbersSpend by area and the top ten suppliers are SUMIF and COUNTIF over the data sheet, with a real Excel chart. Click a cell and trace it back.
  • Audit tests on the same workbookThe duplicate test is a column on the data sheet. The amounts, the Summary sheet and the chart stay as they were.

01 · REQUEST 1

tidied the sheet, and put the summary in the wrong place

The auditor

This is Telford & Wrekin Council's spend data for December 2025 (sheet 'Transparency Report'). Please tidy it for analysis and add a summary, in this workbook: 1. Delete the unlabelled total row at the bottom (row 3163, a SUBTOTAL formula) so only the transactions remain. Keep the credit notes (negative amounts). 2. Trim leading/trailing spaces in Supplier Name - some suppliers show up twice because of a trailing space, e.g. 'British Gas Trading Ltd '. 3. Add a 'Date' column next to Trans Date holding just the date (Trans Date also carries a time). 4. Add a 'Summary' sheet with: a. Total spend by Service Delivery Area, largest first b. Top 10 suppliers by total spend, with their number of transactions c. A bar chart of spend by Service Delivery Area Use formulas on the Summary sheet where you can, and keep the original row order.

  1. Read the sheet and found its three traps

    Row 3163 is an unlabelled SUBTOTAL of the whole column, so a naive sum counts the month twice. Twenty supplier names end in a space, so “British Gas Trading Ltd” shows up as two suppliers. And the date column carries a time of day.

    Telford_Wrekin_transparency_report_December_2025.xlsxbefore
    A1Service Delivery Area
    AEFG
    1Service Delivery AreaSupplier NameTrans DateAmount
    2Adult Social CareAFC Telford Holdings Limited17/12/2025 12:27400.00
    3Adult Social CareAFC Telford Holdings Limited17/12/2025 12:27675.00
    …
    17Adult Social CareBritish Gas Trading Ltd 08/12/2025 17:05174.11
    18Adult Social CareBritish Gas Trading Ltd 08/12/2025 17:05375.93
    …
    3161Prosperity & InvestmentSteel Street Ltd12/12/2025 12:091,520.00
    3162Prosperity & InvestmentPAVE-AWAYS LTD18/12/2025 10:25160,055.42
    3163=SUBTOTAL(9,G2:G3162)
    Transparency Report
    Area, supplier, date and amount for a few rows (other columns hidden). The last row, in yellow, is the unlabelled total.

  2. Deleted the total row, trimmed the names and added a Date column

    All 3,161 payments stay in their download order, the 33 credit notes included. Trimming brings 616 supplier names down to 610. Column G holds =INT(F2) and so on down the sheet: the date without the time.

    Telford_Wrekin_transparency_report_December_2025.xlsxsame file
    A1Service Delivery Area
    AEFGH
    1Service Delivery AreaSupplier NameTrans DateDateAmount
    2Adult Social CareAFC Telford Holdings Limited17/12/2025 12:2717/12/2025400.00
    3Adult Social CareAFC Telford Holdings Limited17/12/2025 12:2717/12/2025675.00
    …
    17Adult Social CareBritish Gas Trading Ltd08/12/2025 17:0508/12/2025174.11
    18Adult Social CareBritish Gas Trading Ltd08/12/2025 17:0508/12/2025375.93
    …
    3161Prosperity & InvestmentSteel Street Ltd12/12/2025 12:0912/12/20251,520.00
    3162Prosperity & InvestmentPAVE-AWAYS LTD18/12/2025 10:2518/12/2025160,055.42
    Transparency Report
    The tidied sheet: the new Date column G in green, and the data now ends at row 3162.

  3. Worked out spend by area, but delivered it as a CSV instead of a Summary sheet

    The totals were right. They came back as a separate file next to the workbook, not as the sheet the request asked for, and the reply said the Summary sheet was not done.

    • Transparency Report sheettidied: total row gone, names trimmed, Date column added
    • Summary sheetnot made; a CSV of spend by area came instead

  4. Checked against the download

    We compared the delivered sheet with the council’s file row by row.

    3,161 rows in the same order as the download, no supplier name with a stray space, 33 negative amounts.

    The amounts add up to £20,034,704.27, the council’s own subtotal.

XLSXDelivered: the tidied workbook3,161 payments with a Date column; no Summary sheet yet

02 · FOLLOW-UP

Told the Summary sheet was missing, added it as formulas and a chart

The auditor

The Summary sheet is missing. Please add it to this workbook as I asked: total spend by Service Delivery Area (largest first), the top 10 suppliers with their number of transactions, and a bar chart of spend by area. Use formulas where you can.

  1. Added a Summary sheet with the 11 areas, largest first

    Each total is a SUMIF over the data sheet, with a grand total under them. Below that, the top ten suppliers by spend, each with a SUMIF total and a COUNTIF transaction count. Green numbers are formulas; click one to see it.

    Telford_Wrekin_transparency_report_December_2025.xlsxnew sheet
    A1Telford & Wrekin Council — December 2025 spend: Summary
    ABCD
    1Telford & Wrekin Council — December 2025 spend: Summary
    2Covering 3161 transactions, 1-31 December 2025
    3
    4Total spend by Service Delivery Area (largest first)
    5AreaTotal spend
    6Prosperity & Investment5,744,335.15
    7Neighbourhood & Enforcement Services4,948,876.56
    8Education & Skills4,609,122.15
    9Corporate Items1,734,016.14
    10Housing, Commercial & Customer Services1,254,821.61
    11Health & Wellbeing971,198.15
    12Finance, People & IDT263,360.71
    13Children's Safeguarding & Family Support258,890.99
    14Adult Social Care208,305.53
    15Policy & Governance40,802.28
    16Corporate Communications975.00
    17Grand total20,034,704.27
    18
    19Top 10 suppliers by total spend
    20RankSupplierTotal spendTransactions
    211Bowmer & Kirkland Ltd1,884,772.102
    222PAVE-AWAYS LTD1,661,965.989
    233Veolia ES T&W Ltd1,482,145.551
    244PYRAMID SCHOOLS (HADLEY) LTD1,462,179.115
    255Balfour Beatty Civils Limited1,376,066.911
    266Id verde1,067,695.396
    277SHROPSHIRE COMMUNITY HEALTH NHS TRUST838,577.003
    288WORCESTERSHIRE COUNTY COUNCIL756,693.521
    299Hawk Incentives Ltd (Crown Commercial Services)631,235.426
    3010SHROPSHIRE COUNCIL538,607.763
    Transparency ReportSummarynew
    The Summary sheet as delivered.

  2. Drew a real Excel bar chart of spend by area

    The chart reads the Summary table, so it moves with the formulas. It is drawn again here from the data inside the delivered file.

    Telford_Wrekin_transparency_report_December_2025.xlsxSummary sheet

    Spend by Service Delivery Area — December 2025

    The chart on the Summary sheet.

  3. Checked against the download

    We recomputed the area totals and the top ten from the council’s file and compared.

    All eleven areas and all ten suppliers match to the penny; the grand total is £20,034,704.27.

    The transaction counts are right: Bowmer & Kirkland 2, PAVE-AWAYS 9, Veolia 1.

XLSXDelivered: the workbook with its Summary sheetSUMIF and COUNTIF formulas, and the chart

03 · REQUEST 2

Asked for a duplicate-payment test, flagged six payments and skipped the comments

The auditor

Now run a duplicate-payment test on the same workbook: 1. Add a 'Possible duplicate' column to the data sheet that flags rows where Supplier Name, Amount and Date all match another row and the amount is £5,000 or more. 2. Add an Excel comment on each flagged row: 'Query AP: possible duplicate - obtain invoice nos.' 3. Virgin Media Business also has a credit of -13,500.00 on 23/12/2025. Add a comment on that row: 'Credit same month as flagged Virgin Media duplicates - check if it reverses one of them.' 4. List the flagged rows in a small table on the Summary sheet. Don't change any amounts, and keep the Summary tables and chart from before.

  1. Added a Possible duplicate column and flagged three pairs

    A-STAR TRANSPORT MIDLANDS on 22 December, Acorn Education & Care on 16 December (rows 405 and 420, not next to each other) and Virgin Media Business on 9 December: same supplier, same amount, same date, £5,000 or more.

    Telford_Wrekin_transparency_report_December_2025.xlsxafter
    E1Supplier Name
    EGHI
    1Supplier NameDateAmountPossible duplicate
    …
    190A-STAR TRANSPORT MIDLANDS LTD22/12/20255,100.00
    191A-STAR TRANSPORT MIDLANDS LTD22/12/20255,600.00Yes
    192A-STAR TRANSPORT MIDLANDS LTD22/12/20255,600.00Yes
    …
    405Acorn Education & Care Ltd16/12/202532,547.95Yes
    …
    420Acorn Education & Care Ltd16/12/202532,547.95Yes
    …
    838VIRGIN MEDIA BUSINESS09/12/202512,894.84Yes
    …
    840VIRGIN MEDIA BUSINESS09/12/202512,894.84Yes
    …
    853VIRGIN MEDIA BUSINESS23/12/2025-13,500.00
    Transparency ReportSummary
    Supplier, date, amount and the new column I for the six flagged rows (yellow), with row 190 and the Virgin Media credit note in row 853 for contrast.

  2. Changed no amount, and left two parts of the request undone

    The column still adds up to £20,034,704.27 and the Summary sheet is as it was. The seven Excel comments and the table of flagged rows on the Summary sheet were not made, and the reply said so.

    • Possible duplicate columnsix rows flagged
    • Amounts, Summary sheet, chartunchanged
    • Seven Excel commentsnot made
    • Flagged rows on the Summary sheetnot made

  3. Checked by running the same test ourselves

    Same supplier, amount and date, £5,000 or more, over the council’s file.

    Our test finds exactly rows 191, 192, 405, 420, 838 and 840, the six that are flagged.

    No amount changed. The file has no comments, and the Summary sheet still ends at row 30.

XLSXDelivered: the workbook with the duplicate flagsthe Possible duplicate column; Summary sheet and chart as before

The files

XLSXBefore: December 2025 spend3,161 payments · on telford.gov.ukXLSXAfter request 1tidied data, no summary yetXLSXAfter the follow-upSummary sheet and chartXLSXAfter request 2the Possible duplicate column

Where the file comes from: Telford & Wrekin Council, “Expenditure over £100”, December 2025, published under the Local Government Transparency Code. Contains public sector information licensed under the Open Government Licence v3.0. The council has not reviewed or endorsed this example.

Bring your own spreadsheet

Most AI tools give you numbers to paste back. works inside your workbook: it cleans the data without losing a row, builds summaries as formulas you can trace to the payments, and flags the transactions worth a query.

Try it on your own spreadsheetDownload the finished workbook

KEEP READING

Read another worked example

Accountants & auditors

50 pages of scanned invoices into a checked AP ledger

Bankers & Consultants

Earnings release PDF to Excel, with YoY formulas and a chart

Bankers & Consultants

US solar O&M market sized from EIA data, in the same workbook

See all 19 worked examples All examples for Accountants & auditors