Concept
1. Data (resources/finance) — tidy, long format
| File | Rows | Columns |
|---|---|---|
finance_pl.csv |
264 | Month (1st of month), Scenario (Actual/Budget), Line, Amount |
finance_lines.csv |
11 | Line, Group (Revenue/COGS/Opex/Depreciation/Interest/Tax), Type (Income/Cost), Order |
finance_balance.csv |
8 | Item, Value (year-end balances and cash-flow items) |
One row per Month × Scenario × Line (11 base lines × 12 months × 2 scenarios). Derived lines (Gross Profit, EBITDA, PBT, PAT) are calculated in CALC, never stored — so they can never disagree with their parts.
Load as tblPL, tblLines, tblBS. Add a Group column to tblPL with =XLOOKUP([@Line], tblLines[Line], tblLines[Group]) (or merge in Power Query).
2. Inputs
| Name | Content |
|---|---|
inp_Month |
dropdown of month starts (Apr-2025 … Mar-2026) → Mar-2026 |
inp_Mode |
option buttons: 1 = Month, 2 = YTD |
k_From |
=IF(inp_Mode=1, inp_Month, DATE(YEAR(EDATE(inp_Month,-3)),4,1)) (FY start = 1 April) |
k_To |
=inp_Month |
3. One amount function for everything
A helper LAMBDA keeps CALC readable (Name Manager → AMT):
=LAMBDA(scenario, grp,
SUMIFS(tblPL[Amount], tblPL[Scenario], scenario, tblPL[Group], grp,
tblPL[Month], ">="&k_From, tblPL[Month], "<="&k_To))
=AMT("Actual","Revenue"), =AMT("Budget","Opex"). (Without LAMBDA, write the SUMIFS each time.)
4. P&L summary table (CALC → DASH)
| Line | Actual | Budget | Variance | Var % | Fav? |
|---|---|---|---|---|---|
| Revenue | =AMT("Actual","Revenue") |
=AMT("Budget","Revenue") |
=B-C |
=D/C |
income: D ≥ 0 |
| COGS | … | … | cost: D ≤ 0 | ||
| Gross Profit | =Revenue − COGS |
||||
| Opex | AMT(…,"Opex") |
||||
| EBITDA | =GP − Opex |
||||
| Depreciation, Interest | … | ||||
| PBT · Tax · PAT | … |
Favourable logic: for income and profit lines, Actual > Budget is good; for cost lines, Actual < Budget is good. A helper column Sign (+1 income/profit, −1 cost) lets one rule colour everything: =D*Sign >= 0 → green, else red.
FY2025-26 full year (YTD at Mar-2026), ₹ lakh:
| Line | Actual | Budget | Variance | Var % |
|---|---|---|---|---|
| Revenue | 2,950.0 | 3,055.0 | −105.0 | −3.4% |
| COGS | 2,462.0 | 2,535.7 | −73.7 | −2.9% (fav) |
| Gross Profit | 488.0 | 519.3 | −31.3 | −6.0% |
| Opex | 286.5 | 279.2 | +7.3 | +2.6% (adverse) |
| EBITDA | 201.6 | 240.1 | −38.6 | −16.1% |
| Depreciation + Interest | 32.4 | 32.4 | 0 | 0% |
| PBT | 169.2 | 207.7 | −38.6 | −18.6% |
| PAT | 126.9 | 155.8 | −28.9 | −18.6% |
Message: a 3.4% revenue miss became an 18.6% profit miss — lower gross margin (16.5% vs 17.0%) plus opex 2.6% over budget. That's operating leverage, and the title should say so.
Filter scope: Month/YTD controls apply to P&L, budget variances, expense breakdown and period margins. The supplied balance file is annual only: keep the cash waterfall fixed to FY2025-26 using full-year PAT and depreciation, and label Current ratio, Debt/Equity and ROE as 31-Mar-2026 / full FY. They must not mix a selected month’s PAT with annual cash movements. ROE here uses year-end equity; label that convention. Ratio target bands are illustrative, not universal health thresholds.
5. Cash-flow waterfall
From tblBS and the P&L (₹ lakh):
| Step | Value |
|---|---|
| Opening cash | 60.0 |
| + PAT | +126.9 |
| + Depreciation (non-cash) | +18.0 |
| − Increase in working capital | −45.0 |
| − Capex | −40.0 |
| − Loan repayment | −24.0 |
| Closing cash | 95.9 |
Insert → Waterfall; set Opening and Closing as totals; increases green, decreases red (Format Data Point). Subtitle: "Cash up ₹35.9 L despite ₹40 L capex".
6. Budget variance chart
Horizontal bars of Variance (₹ lakh) for Revenue, Gross Profit, Opex lines, EBITDA, PAT — coloured by Fav? (not by sign!). Opex +7.3 is a positive number but red (adverse). Sorted by impact. Use the "Invert if negative" trick only if you first multiply cost variances by −1 into a "Profit impact" column — then every bar's sign = good/bad.
7. Expense breakdown
Opex by line (YTD Mar-2026, ₹ lakh): Salaries 125.6 (43.9%) · Rent 66.6 (23.3%) · Marketing 43.5 (15.2%) · Logistics 24.8 (8.7%) · Utilities 13.6 (4.7%) · Other 12.4 (4.3%). Sorted bar with % labels — not a pie (six similar-ish slices). Optional: add Budget as a thin marker to see which line overran.
8. Ratio cards
| Card | Formula | Value | Note |
|---|---|---|---|
| Gross margin | GP / Revenue | 16.5% | budget 17.0% ▼ |
| EBITDA margin | EBITDA / Revenue | 6.8% | budget 7.9% ▼ |
| Net margin | PAT / Revenue | 4.3% | budget 5.1% ▼ |
| Current ratio | Current assets / Current liabilities | 1.75 | example target > 1.5 |
| Debt / Equity | Total debt / Equity | 0.34 | low leverage |
| ROE | PAT / Equity | 36.3% | year-end equity |
Margins compare with budget (points, ▲▼); balance-sheet ratios compare with a target band. Define each ratio on the README so nobody argues about the formula.
9. Month view insight
Switch to Month mode: October 2025 revenue is high (₹360.1 L) but its EBITDA margin is only 3.1% (festive discounts + extra marketing and salaries), while December earns 9.5%. November has higher revenue (₹364.8 L). A good dashboard makes this visible with a small EBITDA-margin-by-month line under the KPI cards.
10. Layout
┌ Finance Dashboard — YTD Mar-2026 (dynamic) (●Month ○YTD) [Month ▼] ┐
│ [Revenue] [Gross margin] [EBITDA margin] [PAT] [Current ratio] [D/E] │
│ [P&L summary table with variance & ▲▼] │ [Cash-flow waterfall] │
│ [Budget variance bars (profit impact)] │ [Expense breakdown bar] │
└──────────────────────────────────────────────────────────────────────────────────┘
Common mistakes
Storing Gross Profit/EBITDA as data (they drift from their parts). Colouring cost overruns green because the number is positive. Pie chart for six expense lines. YTD that starts in January instead of April.