Every finance team produces a monthly MIS pack: revenue, costs, margins, variances and a few charts for management. Most are rebuilt by hand each month. This guide builds one that you fill with data and refresh — the layout, formulas and charts stay put. It works in Excel 2013 and later.

The structure (five sheets)

The golden rule: data sheets hold data, the report sheet holds formulas. Never type numbers into the report.

Step 1: raw data as Tables

Paste the GL export into Data_Actual with columns: Month (first day of month as a real date), Account, Account Name, Cost Centre, Amount. Press Ctrl+T and name the table Actual. Do the same for budget (Budget).

Step 2: the mapping table

On Map, list every account with its MIS line and a sign (+1 for income, −1 for expenses if your GL stores them as negatives):

Add a helper column in Actual that looks up the MIS line:

=IFERROR(INDEX(Map[MIS Line], MATCH([@Account], Map[Account], 0)), "UNMAPPED")

A quick check cell on the Control sheet catches new accounts every month:

=COUNTIF(Actual[MIS Line], "UNMAPPED")

If it shows anything but 0, add the new account to Map before sending the pack.

Step 3: the Control sheet

• SelMonth — a data-validation dropdown of month dates.
• FYStart — =DATE(YEAR(SelMonth)-(MONTH(SelMonth)<4),4,1) (Indian financial year starting April).
• Units — 100000 for ₹ lakh.

Name each cell (Formulas → Define Name) so formulas read like sentences.

Step 4: the P&L block

On MIS, list MIS lines down column B. The core formulas:

' Month actual
=SUMIFS(Actual[Amount], Actual[MIS Line], $B8, Actual[Month], SelMonth) / Units
' Year-to-date actual
=SUMIFS(Actual[Amount], Actual[MIS Line], $B8, Actual[Month], ">="&FYStart, Actual[Month], "<="&SelMonth) / Units
' Month budget
=SUMIFS(Budget[Amount], Budget[MIS Line], $B8, Budget[Month], SelMonth) / Units
' Same month last year
=SUMIFS(Actual[Amount], Actual[MIS Line], $B8, Actual[Month], EDATE(SelMonth,-12)) / Units

Add subtotal rows (Gross Margin = Revenue − Material Cost; EBITDA = Gross Margin − Opex) as simple references to the lines above, so the structure is visible to anyone auditing it.

Step 5: variances that read correctly

A cost that is below budget is good; revenue below budget is bad. Use a “favourable” flag per line (1 for income lines, −1 for cost lines) so the sign always means the same thing:

Variance:        =D8-E8
Variance %:      =IFERROR((D8-E8)/ABS(E8), "")
Favourable?:     =IF((D8-E8)*$A8>=0, "▲", "▼")

Conditional formatting: green for ▲, red for ▼. Management reads the arrows first.

Step 6: KPI tiles

Put each KPI in a large merged-looking cell (use Center Across Selection instead of Merge) with a small “vs budget” line beneath.

Step 7: charts that update themselves

Build a 12-month trend table next to the report using SUMIFS with EDATE(SelMonth, -11) … SelMonth as months. Chart from that table: revenue columns with a gross-margin % line on the secondary axis. Changing SelMonth slides the whole chart.

Step 8: the monthly routine (10 minutes)

1. Paste the new month’s GL export at the bottom of Actual — the table grows.
2. Check UNMAPPED = 0; map any new account.
3. Change SelMonth.
4. Tie the MIS revenue and profit to the trial balance (a check cell showing the difference).
5. Export the MIS sheet to PDF.

Going further

• Move the data steps to Power Query so monthly files combine automatically.
• For many companies or cost centres, use a Power Pivot data model instead of SUMIFS.
• Automate the PDF and email with a short VBA macro.