📎 This article includes 1 downloadable practice file ↓

📘 Excel for Finance Course · Lesson 4 of 8

Management wants the P&L by the 5th. If you rebuild it by hand every month, a formula-driven layout on top of the GL extract will give you days back.

Start from a tidy extract

One row per month per ledger head: Month, Head, Type (Income / COGS / Opex / Finance), Amount. If your extract has only ledger names, add a mapping table (Ledger → Type → P&L line) and bring the type in with XLOOKUP.

The P&L layout

Months across (Jul, Aug, Sep, Quarter), lines down:

Revenue:      =SUMIFS(GL[Amount], GL[Month], C$1, GL[Type], "Income")
COGS:         =SUMIFS(GL[Amount], GL[Month], C$1, GL[Type], "COGS")
Gross profit: =C2 - C3
GP %:         =C4 / C2
Opex:         =SUMIFS(GL[Amount], GL[Month], C$1, GL[Type], "Opex")
EBITDA:       =C4 - C6 + SUMIFS(GL[Amount], GL[Month], C$1, GL[Head], "Depreciation")
Finance cost: =SUMIFS(GL[Amount], GL[Month], C$1, GL[Type], "Finance")
PBT:          =C4 - C6 - C8

The mixed reference C$1 (month header) lets one formula copy across all months (see absolute and relative references).

Analysis rows

• Month-on-month change: =D2/C2-1 with conditional formatting for big swings.
• Expense as % of revenue for each opex head.
• Largest opex head: =INDEX(SORTBY(heads, totals, -1), 1).

Where people go wrong

More: monthly MIS report complete guide.

Practice

Download the workbook below. Build each figure in the yellow column of the Practice sheet; the Check column turns green when it matches, and the Answers sheet has a working formula for every task.