Monthly reporting consolidation template
By the Analistable team · Updated · 2 min read
The workbook has a Data sheet where each unit's monthly return becomes one row, and a Summary that totals revenue, costs, profit, margin and headcount per month. A submissions check compares rows received with the number of units expected, so a missing or duplicated return is visible before the numbers go out.
Part of our guide: How to consolidate data in Excel
What the template does
Many organisations collect the same few numbers from every branch, department or subsidiary each month. The usual workbook has one tab per unit and a summary built from cell references, which breaks when a unit is added or a tab is renamed. This template stores every return as a row in one table instead and summarises it with SUMIFS, the pattern recommended in SUMIFS across multiple sheets and the consolidation guide.
Sheet-by-sheet guide
| Sheet | You fill in | Formulas add |
|---|---|---|
| Instructions | Nothing | — |
| Data | Unit, Month, Revenue, Costs, Headcount | — |
| Summary | Months in column A, Expected units in column H | Totals, Profit, Margin, Submissions, Status |
How to fill it in
- When a unit sends its return, add one row to Data: unit name, month as YYYY-MM text, revenue, costs and month-end headcount.
- Add each reporting month to Summary column A and the number of units you expect in column H.
- Fill the formulas down. The Total row sums revenue, costs and profit and recalculates margin from the totals.
- Check the Status column before circulating: Missing means a return has not arrived; Duplicate? means a unit reported twice.
How the formulas work
Each total is a SUMIFS on the month; margin is calculated from the totals, never averaged:
=SUMIFS(Data!$C$2:$C$100,Data!$B$2:$B$100,A2)
=IFERROR(ROUND(D2/B2,3),0)The submission check counts rows for the month and compares with the expected number:
=COUNTIFS(Data!$B$2:$B$100,A2)
=IF(G2=H2,"Complete",IF(G2>H2,"Duplicate?","Missing"))Worked example
Three units (North, South, East) reporting July to September. All nine returns arrived, so every month shows Complete:
| Month | Revenue | Costs | Profit | Margin | Headcount | Status |
|---|---|---|---|---|---|---|
| 2026-07 | 129000 | 100000 | 29000 | 0.225 | 34 | Complete |
| 2026-08 | 132000 | 103000 | 29000 | 0.22 | 35 | Complete |
| 2026-09 | 137000 | 104000 | 33000 | 0.241 | 37 | Complete |
| Total | 398000 | 307000 | 91000 | 0.229 | Complete |
Headcount is a point-in-time figure, so the Total row leaves it blank rather than adding three month-ends together.
Limits and when to move on
- Rows must be typed or pasted in. If each unit sends a file, stack them with Power Query or follow appending monthly files without duplicates.
- Five metrics fit comfortably. For a full P&L per unit, use the multi-entity P&L consolidation template below, or store returns in long format (Unit, Month, Line, Amount).
- For questions the summary does not answer — which unit's costs grew fastest, say — load the Data sheet into Analistable and ask in plain English.
Frequently asked questions
- Why store months as text?
- Text like 2026-09 matches exactly in SUMIFS and sorts correctly. Real dates work too, but every return must use the same day of the month, usually the first.
- How do I add a new unit?
- Just add its rows to Data and raise the Expected number in Summary. No formulas change.
- Does it work in Google Sheets?
- Yes. The workbook uses only SUMIFS, COUNTIFS, IF, IFERROR, ROUND and SUM.