A monthly MIS pack in Excel rests on a handful of formulas: SUMIFS to total by month and head, a lookup to map codes, a pivot for quick slices, variance percentage against budget, a rolling 12-month total, ageing buckets and DSO. All figures below are invented, for illustration.
Facts checked: 9 October 2026. XLOOKUP is available in Microsoft 365 and Excel 2021 and later; older versions need INDEX with MATCH. Formulas are standard Excel functions. This is a how-to, so no statute or rate is cited.
The core formulas
| Job | Formula | Note |
|---|---|---|
| Total by month and head | =SUMIFS(Amt, Month, "Sep-26", Head, "Salaries") |
Sum range first, then criteria pairs |
| Map code to name | =XLOOKUP(A2, Master[Code], Master[Name], "Not found") |
Microsoft 365 and Excel 2021+ |
| Same, older Excel | =INDEX(Master[Name], MATCH(A2, Master[Code], 0)) |
Works in all versions |
| Variance % | =(Actual-Budget)/Budget |
Format as percentage |
| Rolling 12 months | =SUMIFS(Amt, Month, ">"&EDATE(D1,-12), Month, "<="&D1) |
D1 holds the month end date |
| Ageing bucket | =LOOKUP(Days, {0,31,61,91}, {"0-30","31-60","61-90","90+"}) |
Days sorted ascending |
| DSO | =Receivables/CreditSales*Days |
Days in the period |
Worked examples
All numbers are illustrative.
SUMIFS. A ledger export has salaries of ₹6,40,000 in September and ₹6,10,000 in August. The formula above, with "Sep-26", returns ₹6,40,000. Use a real date column, not text, in your own file.
XLOOKUP. Your trial balance has ledger code 4102. The mapping sheet lists 4102 as "Software subscriptions". The lookup returns the name and, with the fourth argument, shows "Not found" instead of an error when a new ledger has not been mapped. That flag is a useful control in itself.
Variance %. Actual sales ₹48,50,000 against budget ₹52,00,000. Variance is (48,50,000 − 52,00,000) / 52,00,000 = −6.73%. For cost lines, say in the note whether a negative is favourable.
Rolling 12 months. With a month-end date of 30 September 2026, the formula sums October 2025 to September 2026. It smooths seasonality, so use it next to the single-month figure, not in place of it.
Ageing. An invoice 47 days overdue falls in the 31-60 bucket. The same LOOKUP applied to every invoice row gives a column you can pivot.
DSO. Receivables ₹1,20,00,000; credit sales for the quarter ₹3,60,00,000; 90 days. DSO = 1,20,00,000 / 3,60,00,000 × 90 = 30 days.
Pivot tables
A pivot over the ledger export, with month across and head down, gives the P&L view without formulas. Refresh it after pasting new data. Keep the raw export on its own sheet so the pivot can be rebuilt.
What the CFO should see
- Revenue, gross margin and operating profit against budget and last year, with variance percentage.
- Cash and bank position, with the next four weeks of expected receipts and payments.
- Receivables ageing and DSO, and payables due.
- Working capital movement and the main reasons.
- Two or three exceptions with owners, not a long list.
Keep the pack to one page of numbers and one of commentary. The MIS report: what a CFO should see post covers the layout. To skip the setup, try the MIS report format generator, the working capital calculator and the debtors ageing analyzer.
Frequently asked questions
Should I use XLOOKUP or INDEX-MATCH?
XLOOKUP is simpler, but it needs Microsoft 365 or Excel 2021 and later. If your team shares files with older Excel, INDEX with MATCH is safer.
Why does my SUMIFS return zero?
Usually the month column holds text, not dates, or the criteria do not match exactly, for example a trailing space in the head name.
How do I calculate variance when the budget is zero?
The percentage is undefined. Show the amount variance and leave the percentage blank, for example with =IF(Budget=0,"",(Actual-Budget)/Budget).
Is DSO calculated on total sales?
Use credit sales when you can separate them. On total sales, the figure understates DSO if cash sales are large.
Statutory facts on this page are checked against their sources, and the page says where it relied on secondary reporting. How we verify · Report an error