Building a Monthly MIS Report in Excel A Structure That Survives Month Two
Most MIS packs are built once, work beautifully, and become unmaintainable by the third month. The difference between a pack that survives and one that does not is decided by its structure, not by the formulas in it.
Short answer
What the pack is for
An MIS pack is not an accounting report. A trial balance tells you the books balance; an MIS pack tells somebody which decision to make. That distinction decides what goes in it.
| Section | The question it answers | Usually built from |
|---|---|---|
| Revenue and margin by segment | Where are we making money and where are we not? | Sales register by product or state, with the direct cost of each |
| Profit and loss summary — month and YTD | How did the period go? | Trial balance or ledger extract, mapped to MIS lines |
| Variance against budget | Is the difference from plan explained? | The same summary, with the budget in parallel columns |
| Variance against prior year | Is this a trend or a one-off? | The same summary for the same period last year |
| Receivables ageing | How much is overdue, and from whom? | Open sales invoices with due dates |
| Payables and cash position | What is going out, and can we cover it? | Vendor balances and bank balances |
| Working capital and key ratios | Is the business getting better or worse at this? | The figures above, combined |
Cut anything nobody acts on
The three-layer rule
This is the whole of the structure advice, and everything else in the article follows from it.
| Layer | Contains | Changed when |
|---|---|---|
| 1 — Data | One sheet, one row per ledger entry or transaction. No subtotals, no formatting, no blank rows, no merged cells | Every month, by replacing the extract |
| 2 — Calculation | Summaries by account and period, the mapping lookup, the variance figures | Almost never — this is where the logic lives |
| 3 — Presentation | The layout the reader sees: headings, number formats, the order of the lines, the notes | Rarely, and never to change a number |
The failure mode this prevents is the one that kills most packs. Without separated layers, next month involves editing formulas inside the report layout — a SUMIFS range extended here, an account code changed there — and each edit is an opportunity to break something that nobody will notice until the figures are questioned.
The test for whether you have done this correctly
Step 1: fix the period and the comparison
| Decision | Options | Note |
|---|---|---|
| Reporting period | Calendar month, or a 4-4-5 period | Whatever the business is managed on — but it must be the same every month |
| Comparisons | Budget, prior year, prior month, forecast | Two comparisons is usually the limit before a table becomes unreadable |
| Year-to-date basis | From 1 April, from 1 January | For most Indian businesses this is the financial year from April |
| Currency and units | Rupees, thousands, lakhs | State it once in the header of the pack, not on each row |
Write those four decisions into the header of the pack itself. Half of the arguments about a monthly report are really arguments about what it is comparing, and having it in writing ends them.
Step 2: the data layer
Get the extract in the right shape
Store it as a Table
Add the period column explicitly
Load it with Power Query if it arrives the same way each month
Keep the budget in the same shape
Step 3: the mapping table
This is the piece most packs are missing, and it is the one that makes the difference between a report you maintain and a report you rebuild.
| Account code | Account name | MIS line | Line type |
|---|---|---|---|
| 4001 | Domestic sales — goods | Revenue — Domestic | Income |
| 4002 | Export sales — goods | Revenue — Export | Income |
| 4005 | Sales returns | Revenue — Domestic | Income |
| 5101 | Opening stock | Cost of goods sold | Expense |
| 5104 | Purchases — traded goods | Cost of goods sold | Expense |
| 5110 | Freight inward | Cost of goods sold | Expense |
| 5201 | Salaries and wages | Employee benefit expense | Expense |
| 5901 | Interest on term loan | Finance cost | Expense |
| 5905 | Depreciation | Depreciation and amortisation | Expense |
Two things are happening here. The MIS line column lets several ledger accounts roll into one reporting line — which is how a pack groups, say, three separate travel accounts into "Travel and conveyance". The Line type column is what lets the variance formula know whether spending more than the budget is good news.
A new ledger account is a new row, not a formula edit
Step 4: the calculation layer
| Calculation | Approach |
|---|---|
| Actual by MIS line and period | SUMIFS over the data table, with the account codes coming from the mapping table |
| Budget by MIS line and period | SUMIFS over the budget table, matched on line and period |
| Year to date | The same SUMIFS with the period criterion replaced by a set of periods up to the current one |
| Prior year | The same again, with the prior year's periods — which is why the period column must be in the data |
| Variance | A sign-corrected subtraction, using the Line type column |
| Variance percentage | The variance divided by the budget, with the result handled when the budget is nil |
Using SUMIFS with a cell reference as the criterion — the MIS line name in the presentation sheet — rather than hard-coding the account codes is what lets the pack be copied forward month after month without editing.
Step 5: variance that reads correctly
Here is the detail that separates a pack people understand from one they have to translate. Spending ₹40,000 more than budget is unfavourable on a cost line and favourable on a revenue line. A single Actual minus Budget column cannot express both, so the reader ends up doing the sign arithmetic in their head, row by row — and eventually getting one wrong in a meeting.
| MIS line | Type | Budget (₹) | Actual (₹) | Variance (₹) | Fav / (Unfav) |
|---|---|---|---|---|---|
| Revenue — Domestic | Income | 42,00,000 | 44,80,000 | 2,80,000 | Favourable |
| Revenue — Export | Income | 8,50,000 | 7,10,000 | (1,40,000) | Unfavourable |
| Cost of goods sold | Expense | 29,40,000 | 31,20,000 | 1,80,000 | Unfavourable |
| Employee benefit expense | Expense | 6,20,000 | 6,05,000 | (15,000) | Favourable |
| Finance cost | Expense | 1,10,000 | 1,34,000 | 24,000 | Unfavourable |
| Depreciation | Expense | 82,000 | 82,000 | 0 | — |
The formula is a single IF on the line type: for an income line the favourable direction is actual above budget, and for an expense line it is the reverse. Once that is in the calculation layer, the reader never has to think about the sign again.
Do not hide an unfavourable variance behind a formatted minus
Step 6: the presentation layer
| Rule | Why |
|---|---|
| One sheet, the figures linked from the calculation layer | Nobody has to hunt for which sheet holds the real number |
| Column headers carry the period, not just “Actual” | A pack emailed around gets separated from its context |
| Fixed number formats, set once | ₹ in lakhs on one row and units on the next is a reading error waiting to happen |
| No merged cells in the data area | They break sorting, and they break the moment somebody wants to pivot the pack |
| Cost centres and segments as columns, not as separate blocks | Blocks have to be rebuilt when a segment is added; columns do not |
| Print setup done once — fit to width, headers repeated | The pack will be printed, and it should not be re-set-up each month |
The monthly routine
Lock the period
Replace the data sheet
Refresh everything
Reconcile the pack to the trial balance
Read the variance before you send it
Save a copy with values only
That sequence is the reporting stage of the month-end routine — the full sequence, including the reconciliations that have to finish before the pack can be built, is in the month-end Excel routine.
Common mistakes
- Building the report first and the data later. The layout feels like the deliverable, so it gets built first, and every month after that is spent feeding it by hand.
- Typing account codes into formulas. The pack becomes wrong the first time the chart of accounts changes, silently.
- Storing actuals as a report rather than as data. A formatted twelve-column summary cannot be joined to next month's extract or to a budget.
- Using the previous pack as this month's template. Old figures get typed over, and one gets missed. Replace the data; do not overwrite the output.
- No reconciliation between the pack and the ledger. The gap is found by the auditor rather than by the person who produced it.
- A variance column that does not distinguish favourable from unfavourable. Readers misread it, and the report gets a reputation for being hard to interpret.
- Sending the live workbook. The recipient refreshes it, breaks a link, and can no longer reproduce the figures they read last month.
Where the pack stops being a spreadsheet problem
Once the three layers are separated and the mapping table is in place, most of the monthly effort has gone. What remains is the data assembly — and where the figures come out of a comparison rather than a single ledger, that comparison has to be done first.
A payables number built on an unreconciled vendor ledger, or an input credit figure built on an unreconciled 2B comparison, is a precise number of the wrong thing. Where the pack depends on comparisons, Piloteq Automate is the step that applies the same matching rules every month and records a reason against each difference — so the number that reaches the pack has already been examined once. If the pack is assembled from several files each month, combining multiple Excel files removes that step too.
If you would rather not build this by hand
Getting the numbers settled before they are reported
A pack is only as reliable as the figures that feed it. Where those figures come from a comparison — books against a statement, a return against a register — Piloteq Automate does the matching with the rules you set and records the reason for every difference.
- ✓Match rules saved and reapplied each period
- ✓Tolerance, group and duplicate handling
- ✓Exceptions listed with the reason recorded
- ✓A consistent comparison, month after month
Frequently asked questions
What should a monthly MIS report contain?+
The sections management actually makes decisions from: revenue and margin by segment, the profit and loss summary with the month and year-to-date figures, variance against budget and against the same period last year, receivables ageing, payables and cash position, and any operational measure the business is run on. The test is not whether a section is standard — it is whether somebody acts on it.
How do I build an MIS report in Excel so it works every month?+
Separate the workbook into three layers: a data layer holding the ledger extract with one row per entry and nothing else, a calculation layer that turns that into figures by account and period, and a presentation layer that lays the figures out for reading. The month's work then happens in the data layer only, and nothing in the presentation layer is ever retyped.
How should variance be presented when income and expense differ?+
Report variance as favourable or unfavourable rather than as a raw subtraction. Spending more than budget is unfavourable for a cost line and favourable for a revenue line, so a single Actual minus Budget column forces the reader to remember which is which for every row. Add a line-type flag to your chart-of-accounts mapping and let the formula choose the sign.
What is a chart-of-accounts mapping table?+
A two-column table listing every account code in the ledger against the MIS line it belongs to. Every figure in the pack is then produced by looking up the accounts that map to a line, rather than by listing account codes by hand. It is the single change that makes a monthly pack maintainable, because a new ledger account is a new row in the mapping rather than a formula edit in nine places.
Should the MIS pack be one workbook or several?+
One workbook with clearly separated sheets — data, calculation, presentation. Splitting it across files introduces links that break when a file is moved or renamed, and it makes the pack harder to refresh than it needs to be. If the pack must be distributed, distribute a copy with the figures pasted as values and the working file kept separately.
How do I handle last month's figures being restated?+
Keep the reporting period as a column in the data, and note the restatement on the pack rather than editing the previous pack. Editing a pack that has already gone to management means two versions exist with the same title, which is the situation that makes people stop trusting the numbers.