Learning goals

Run the five-step monthly close without improvising
Reconcile every headline figure before the report leaves your desk
Explain the boundary rows and the cleaning decisions to a skeptical reader
Share the report without sharing the machinery

Concepts

The five-step close

September's close, in full: 1) export the three CSVs from Fieldlight, Ledgerly, and Clockwork into C:\Reports\Exports using the usual file names; 2) open the reporting workbook in desktop Excel and press Refresh All; 3) update the parameters and Settings for the new month (ReportMonth 9, and the period cells on Settings); 4) walk the fifteen metrics against this checklist; 5) save a values-only copy or PDF for distribution.

Four hours of copy-paste became about ten minutes, and -- more important than the time -- every step is now a named, repeatable act. The routine is the deliverable. The queries only have to work; the routine is what makes them trustworthy month after month.

=IFERROR($C$14/(Settings!$C$10*$C$15),0)

Team utilization: August timesheet hours (C14, 892) divided by capacity -- the 150-hour monthly setting times the consultant count in C15. The result, 0.8495, displays as 84.95% and IFERROR keeps the cell at zero rather than #DIV/0! if a refresh ever returns an empty table.

The reconciliation checklist

Before the report leaves your desk, all fifteen metrics must tie to something you can point at. Cash and receivables: 16,820.00 invoiced in August (the eight invoices INV-2405 through INV-2412); 10,500.00 collected (payments dated Aug 5, 14, 21, and 28: 920.00 + 3,300.00 + 2,320.00 + 3,960.00); 10,415.00 open across seven invoices; 930.00 overdue, INV-2404 alone.

Pipeline: ten opportunities worth 123,000.00, of which 66,950.00 sits in Proposal stage -- 7.3 months of coverage against August invoicing. Delivery: 110 hours on August invoices, 892 timesheet hours, 84.95 percent utilization of 1,050 available. Controls: 12 raw CRM rows staged, 2 removed by cleaning, 13 invoices tracked, average invoice 1,908.08.

💡 Tips:

The two boundary rows are the ones to rehearse out loud: INV-2408 (paid Sep 3, 2026) is Paid in the ledger but excluded from August collections; INV-2413 (dated Sep 2, 2026) exists but is excluded from every August metric. If you can explain those two, you can defend the whole sheet.

Amounts exclude sales tax. When the accountant asks, the answer is the Read Me row and your state Department of Revenue, not a guess.

Share values, not machinery

The reporting workbook is a single-user desktop file by design: it holds live queries, a Settings sheet, and a formula layer that only makes sense with the raw sheets behind it. Distribute a values-only copy (Paste Special > Values onto a clean sheet) or a PDF. Nobody downstream needs to refresh anything, and nobody can break a query they cannot reach.

What you keep is the workbook, the source folder, and the Query Log. What the owners get is one page that answers their five questions with numbers that tie. When they ask for one more cut -- revenue by service line, say -- you already know the answer: the Service Line column is in Invoice Facts, the merge is documented in the log, and one more SUMIF on the report is a ten-minute change, not a rebuild.

=SUMIF('Invoice Facts'!$F$5:$F$1004,"Managed Support",'Invoice Facts'!$I$5:$I$1004)

The next report's first new metric, already within reach: sum invoice amounts where the merged Service Line column equals Managed Support. The heavy lifting was the merge in lesson 6; every future question over service lines is now one SUMIF away.

Practice

The final exercise opens on the Monthly Report with cell C16 blank -- the utilization metric that closes the delivery block. Walk the full reconciliation while you are there. Nothing is saved in the browser.

Retype the utilization metric — Cell C16 is empty. Type =IFERROR($C$14/(Settings!$C$10*$C$15),0) and press Enter: 84.95% -- 892 hours against 1,050 capacity. Cross-check against the Utilization tab: 148 + 146 + 136 + 130 + 120 + 112 + 100 = 892.
Walk the cash block — C5 16,820.00, C6 10,500.00, C7 10,415.00, C8 930.00. Open Invoice Facts and prove each one: the eight August-dated invoices; the four August-dated payments; the seven rows with an open balance; the single row past its NET 30 due date at the Aug 31, 2026 close.
Walk the pipeline and delivery blocks — C9 10 opportunities, C10 123,000.00 pipeline value, C11 66,950.00 in Proposal, C12 7.3 months of coverage. Then C13 110 billed hours, C14 892 timesheet hours, C15 7 consultants, C16 84.95% utilization.
Check the controls and download — C17 average invoice 1,908.08 across 13 invoices, C18 12 raw CRM rows staged, C19 2 removed by cleaning. Then download the course workbook below: raw sheets, query outputs, the report, the twelve M scripts, and the query log -- everything this course built.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Practice files: en-power-query-reporting-case.xlsx, en-power-query-reporting-template.xlsx (see the attachments section at the bottom of this page).

Checklist

Work through each item; when every box passes, this lesson is done:

☐ Retyped =IFERROR($C$14/(Settings!$C$10*$C$15),0) into C16 and got 84.95% utilization (892 of 1,050 hours)
☐ Reconciled the headline figures: 16,820.00 invoiced, 10,500.00 collected, 10,415.00 open, 930.00 overdue, 123,000.00 pipeline
☐ Can explain the two boundary rows (INV-2408 paid Sep 3, 2026; INV-2413 dated Sep 2, 2026) and the two cleaned-away CRM rows without notes
☐ Downloaded the macro-free workbook and know the five steps of next month's close

⚡ Lesson 12 · The monthly close routine