In this article
- A good client MIS is about eight short pages in the same order every month, with a one-page summary on top.
- Build the workbook in layers: data sheets, a ledger map, a calculation sheet and the report pages. Never type numbers into the report.
- Agree ten KPIs and their definitions with the owner once, and never change them quietly.
- A fixed monthly routine by working day keeps the pack on time and checked.
- Label every page as unaudited management information, not a certificate.
An MIS report format in Excel that a business owner actually reads is short, the same every month, and starts with the answer. Thirty pages of schedules stay unopened on the desk. One page of ratios is not trusted. What works for most small and mid-sized businesses is about eight short pages with a summary on top and an action list at the end. This guide is for any tax and accounts office that prepares monthly numbers for clients. It gives the MIS layout below, ten KPIs with plain definitions, the workbook structure behind it, and a monthly routine your team can run.
MIS report format in Excel: the eight-page layout
Keep the pages in this order every month. The owner learns where to look, and your team learns what to prepare.
| Page | Contents | Question it answers |
|---|---|---|
| 1. Summary | Five lines in plain words, and the KPI table with a signal for each (on track, watch, act) | Where do I stand, in one minute? |
| 2. Profit | Month and year to date: sales, gross margin, expenses, profit, against budget and last year | Am I making money? |
| 3. Margins | Gross margin by product, branch, channel or top 10 customers | Which sales actually pay? |
| 4. Debtors | Ageing in buckets (0-30, 31-60, 61-90, over 90 days), top overdue customers, debtor days | Who owes me, and for how long? |
| 5. Creditors and stock | Creditor ageing, stock value and stock days, slow-moving items if tracked | What do I owe, and how much is stuck on the shelf? |
| 6. Cash and bank | Bank balances, limit used and available, loan repayments due, a short cash forecast | Will I have enough cash? |
| 7. Compliance and bank calendar | One status line from your compliance team (filed, pending, notice open) and bank dates such as stock statement and limit renewal | Is anything about to go wrong? |
| 8. Actions | Last month's actions with status, and space for this month's | What are we doing about it? |
A smaller client can start with pages 1, 2, 4 and 6. Add the rest as the business grows or the owner asks.
Page 7 is a status line only. The MIS does not show tax workings; those stay in your compliance files under their own engagement.
The ten KPIs and their definitions
Agree these with the owner once, write them on a definitions sheet inside the workbook, and never change a definition quietly. A KPI that means something different every month is worse than none.
| KPI | Plain definition |
|---|---|
| Sales vs budget | This month's sales as a % of the budgeted figure |
| Gross margin % | (Sales minus direct costs) divided by sales |
| Net profit % | Profit before tax divided by sales, from management accounts |
| Debtor days | Debtors divided by the last 3 months' sales, times the days in those months |
| Overdue over 90 days | Rupees outstanding for more than 90 days |
| Creditor days | Creditors divided by the last 3 months' purchases, times the days |
| Stock days | Stock divided by the last 3 months' cost of sales, times the days |
| Cash runway | Lowest closing cash in the forecast, and the week it falls |
| Limit headroom | Sanctioned bank limit minus the amount used |
| Actions closed | Last month's actions done, divided by the total |
Show amounts in Rs. lakh with one or two decimals on the summary page. Owners read "Rs. 52.48 lakh" faster than "52,48,190".
Power BI for Tax & Accounts Offices builds a client owner page from sales, receipts, expenses and a trial balance export, with practice files, a ledger-to-MIS-head map and a monthly refresh and check routine.
The workbook behind the report
The most common mistake with any MIS report format in Excel is typing numbers straight into the report pages. Next month nobody remembers where they came from. Build the workbook in four layers instead.
| Sheet | What goes in | Who touches it |
|---|---|---|
| Data: TB | The month's trial balance export, pasted as it comes, one row per ledger | Staff, each month |
| Data: Sales, Receipts, Purchases | Register exports with date, party code, amount | Staff, each month |
| Data: Ageing | Debtors and creditors ageing as on month end | Staff, each month |
| Map | Each ledger name and the MIS head it belongs to (Sales, Direct cost, Salaries, Rent, Finance cost) | Senior, only when a new ledger appears |
| Calc | All totals by MIS head and month, built with formulas from the data and map sheets | Senior, built once |
| Budget | Monthly budget by MIS head, agreed with the owner | Senior, once a year |
| Report pages 1 to 8 | Only links to Calc, charts and the written summary | Senior writes the summary |
| Checks | Tie-outs that must show zero (see the routine below) | Reviewer, each month |
Excel features that make it work
- Excel Tables (Ctrl+T) on every data sheet, so formulas grow automatically when next month's rows are pasted.
- SUMIFS on the Calc sheet to total by MIS head and month. One formula pattern, copied across, is easier to check than many pivots.
- XLOOKUP or VLOOKUP to bring each ledger's MIS head from the Map sheet. Any ledger that returns blank is a new ledger to map.
- PivotTables and slicers for the margin and debtors pages, where the owner wants to filter by branch or customer.
- Simple charts: a line for sales against budget and last year, a column chart for debtors ageing. No 3D, no pie with more than five slices.
If the same exports arrive every month in the same layout, Power Query can load and clean them in one Refresh instead of copy and paste. Power Query and Macros for Accountants: Automate Excel shows how that works, and Excel Automation for Tax & Accounts Offices teaches it step by step. If lookups, SUMIFS and pivots are new to your staff, Excel for Tax Practice covers them on real office files; Excel for Chartered Accountants: 15 Formulas You Need lists the formulas.
The monthly MIS routine
A layout without a routine falls apart by the third month. Fix the days, using working days after month end (WD1 is the first working day of the new month).
| When | Step | Who |
|---|---|---|
| WD1 to WD3 | Client's accountant closes the month and sends the TB, registers and ageing in the agreed layout | Client team |
| WD4 | Paste data, refresh, clear the "unmapped ledgers" list, fill the Checks sheet | Staff |
| WD5 | Review numbers, write the five-line summary and the KPI signals, update the action list | Senior |
| WD6 | Partner review, then send the PDF to the person named in the engagement letter | Partner |
| By WD10 | Monthly meeting with the owner, actions agreed and noted on page 8 | Partner or senior |
Stagger clients so all packs do not fall on the same days, and protect the busy compliance season by agreeing a slightly later date for those months in advance. The same check-and-release routine carries over unchanged if you later move the pack into a Power BI client dashboard.
If your firm is also the client's auditor, complete and document the independence check that applies before you take on or continue MIS work.
- TB total debits equal credits, and the TB sales ledgers equal the sales register for the month.
- Unmapped ledgers on the Map check = 0.
- Debtors on page 4 equal the debtors control balance in the TB, or the difference is explained.
- Year to date figures equal the sum of the months.
- Correct client name, month and "as on" date on every page; footer present.
- Last month's file saved as a separate version, never overwritten.
Five mistakes that kill a client MIS
- Too long. If the owner asks a question answered on page 14, the pack is too long. Move it to the first four pages or cut it.
- Changing definitions. Debtor days on a 3-month basis one month and 12 months the next makes the trend meaningless.
- No written summary. Charts without five plain sentences leave the owner to guess. Start with the one thing that matters, for example "Cash is tight in week 7 because three large debtors are past 90 days."
- Late every month. A pack that arrives on the 25th is history, not information. Agree the data date with the client in writing.
- Client data in the wrong place. Keep the workbook in the client's permissioned folder, send by an approved channel, and never use one client's file as a "sample" for another. Data Security for CA Firms: DPDP-Ready 12-Point Checklist has the office checklist.
When to move the MIS from Excel to Power BI
Excel is the right home for the first months: everyone can open it, and the layout settles. Move to a dashboard when three things are true: the data arrives in the same layout every month, the owner wants to click and filter, and you are comparing more than a year of months. Power BI for Chartered Accountants: Office and Client MIS explains the office and client dashboards, what is free and how to share them safely.
Power BI for Tax & Accounts Offices builds exactly this client view from sales, receipts, expenses and a trial balance export, with practice files, a ledger-to-MIS-head map and a monthly refresh routine with check figures. If the MIS is part of a paid monthly advisory service, Launch a Virtual CFO Line covers packages, the scope letter, the independence check and the monthly meeting, and its tools include an Excel KPI dashboard sheet and an MIS pack checklist. You can read the first lesson of the Power BI course free to see whether your clients are ready for a dashboard yet.
Questions people ask
What should an MIS report contain for a small business?
A one-page summary, profit against budget and last year, margins, debtors ageing, creditors and stock, cash and bank, a short status line from the compliance team, and an action list. Small clients can start with four of these pages.
What is the difference between MIS and financial statements?
Financial statements are prepared under accounting standards for owners, lenders and regulators, usually yearly. An MIS is an internal monthly report for decisions, in the format management wants, and it is not audited.
How often should an MIS report be prepared?
Monthly suits most businesses, sent within an agreed number of working days after month end. Some clients also want a short weekly cash and collections update.
Which Excel features are best for an MIS report?
Excel Tables for the data, SUMIFS or PivotTables for the totals, slicers for filtering, and simple charts. Power Query helps when the same exports arrive every month.
Can I give my client an MIS in Power BI instead of Excel?
Yes, once the Excel version is stable and the data arrives in the same layout every month. Power BI then refreshes the whole pack in one click.
Sources
- Microsoft Support, Overview of Excel tables
- Microsoft Support, SUMIFS function
- Microsoft Support, Create a PivotTable to analyze worksheet data
- Microsoft Support, Use slicers to filter data
- Microsoft Learn, Power Query Folder connector
Launch a Virtual CFO Line includes an Excel KPI dashboard sheet and an MIS pack checklist; Power BI for Tax & Accounts Offices turns the same MIS into a dashboard. The Office plan gives your whole team every course.
