How do you make an MIS report in Excel?

1. 1. Close the month first.

   Bank reconciled, sales and purchases posted, accruals and depreciation in. An MIS built on an open month is rebuilt the week after.

2. 2. Export the trial balance with four columns.

   Opening balance, debits, credits and closing balance for every ledger, not just the closing figure. The movement columns are what the month's profit and loss is made of.

3. 3. Map every ledger to a report head, once.

   Revenue, direct costs, employee costs, other expenses, receivables, payables and so on. This is the slow part the first month and the reusable part every month after.

4. 4. Build the statements from the mapping, never by typing figures.

   Each line of the profit and loss and the balance sheet is a formula that sums the ledgers mapped to it, such as SUMIFS over the trial balance by head. A typed number is the one that goes stale.

5. 5. Add the comparisons.

   This month against last month, against the same month last year, and the year to date. A figure on its own is not management information.

6. 6. Check it before anyone reads it.

   The trial balance nets to zero, the balance sheet balances, and profit in the balance sheet equals profit in the profit and loss.

7. 7. Write one line on each movement that matters.

   What moved, by how much and why. The commentary is what gets read; the tables are what gets checked.

What each section of the finished report should contain is on the MIS report format page; how to map ledgers is in trial balance to management report.

The layout of an MIS changes when a director asks for a new view. The mapping does not: once “Warehouse Rent” is an other expense and “Sales — Hardware” is revenue, they stay that way every month. Keep the mapping as its own sheet of two columns, ledger and head, and let every statement read from it. Next month you paste in a new trial balance and only the new ledgers need a head.

In a trial balance exported for a month, the closing balance of every income and expense ledger runs from the start of the financial year. The month’s sales are this month’s closing less last month’s. Report the closing balance as the month and every figure after the first month of the year is wrong, and it still adds up.

The other common breaks are a new ledger that appears in the export and is mapped to nothing, and credit balances added with the wrong sign. The balance check in step six catches the second; only a check for unmapped ledgers catches the first.

Yes, and you should: an MIS for accounts made of formulas can be checked, and one made of typed numbers cannot. Step by step, three kinds of formula do almost all the work. SUMIFS adds the trial balance up by report head, reading the head from the mapping sheet. A lookup such as XLOOKUP or INDEX and MATCH pulls last month’s and last year’s figures beside this month’s. And plain division gives the ratios — gross margin, debtor days, the current ratio — from cells that are already formulas.

Add one check cell that counts trial balance ledgers with no head in the mapping, and another that shows the balance sheet difference. If both read zero, the workbook is complete and balances. The workbook Magic MIS produces is made of live formulas too, with no macros, so it can be opened and followed in Excel.

Magic MIS does these seven steps from the raw trial balance: it maps the ledgers, builds a checked Excel workbook with live formulas, and opens every figure to the ledgers behind it. The mapping is kept for the company, so next month is a refresh rather than a rebuild, and a month on unchanged books uses no AI at all.