Macroeconomic Scenario Model
Public Finance Financial Model (Free Excel Download)
Translate rates, inflation, growth, commodities, and market assumptions into forecast scenarios that reveal exposure across revenue, costs, cash flow, and valuation.
professionals from Deloitte
Used by professionals from
About this model
A Macroeconomic Scenario Model stress-tests a mid-market manufacturer across four economic environments - Recession, Base, Boom, and Stagflation - each with distinct GDP, inflation, FX, and wage impacts. The model captures how domestic revenue (GDP-driven) and export revenue (foreign GDP and FX-driven) diverge from COGS escalation, creating margin compression or expansion. In Recession, domestic revenue declines 2.5% while materials cost escalates only 1.0% inflation - seemingly favorable, but weak volumes dominate. In Stagflation, nominal revenue grows 6.5% but material costs inflate 7.0% and interest expense soars to 11.5%, compressing net margins by 400+ bps relative to Base.
The Assumptions sheet houses a scenario toggle (user selects 1=Recession, 2=Base, 3=Boom, or 4=Stagflation in cell C5); CHOOSE formulas in column G pull the active scenario's assumption values. Revenue projections compound from prior year at scenario-specific rates (GDP + CPI + company alpha). COGS applies dual escalation: materials per unit escalates at Commodity_Inflation (independent of selling price inflation), and labor per unit at Wage_Inflation - this divergence is the core macro sensitivity. Fixed OpEx (SG&A, R&D) escalates at CPI. Working capital days vary by scenario - DSO extends in Recession (customers pay slower), compresses in Boom. The Macro_Dashboard sheet displays all four scenario KPIs side-by-side (Year 5 revenues, EBITDA, cash, debt levels) without requiring the user to toggle, enabling visual comparison.
This model suits corporate CFOs, board risk committees, lenders, and investors modeling downside resilience or upside optionality. Key metrics include revenue CAGR, EBITDA margin (range 12-22% depending on scenario), net margin (5-12%), interest coverage (1.5-3.5×), and cash position. A typical mid-cap manufacturer's Stagflation case reveals whether current debt levels are sustainable if rates spike and input costs soar - critical for investment-grade rating maintenance.
What every model includes
Live formulas, no hardcoded values
Outputs are driven by live formulas, so the workbook updates from its assumptions instead of relying on hardcoded results.
All assumptions in one tab
Inputs are clearly marked in the Assumptions tab and separated from calculations, making it clear what to change and what to leave intact.
Statements always balancing
For integrated-statement models, the balance sheet, cash flow, and supporting schedules tie through properly.
Distinct schedules for clarity
Debt, working capital, taxes, and cash flow can get messy quickly. We group calculations in clear schedules, not across disconnected tabs.
No hidden macros or external links
There are no unexplained external workbook links or macros to undermine auditability or portability.
Changes flow through the model
Update a key driver and see the impact carry through the forecast, financing, and return outputs. We never use hardcoded numbers in formulas.
What's inside the Macroeconomic Scenario Model
- Macroeconomic variables including GDP, inflation, unemployment, and interest rates
- Industry-specific economic indicators and sensitivities
- Scenario definitions for base, upside, and downside cases with key assumptions
- Company P&L linkages to economic variables
- Valuation impact across scenarios with probability-weighted expected value
- Risk factor analysis and scenario probability weighting
Inside the Macro Scenario Model: How Economic Regimes Drive Manufacturing Financials
This template builds a three-statement forecast for a mid-market manufacturer under four macro regimes. The macro scenario model links GDP, inflation, FX, rates and working capital to revenue, costs and cash flow.
It shows how base, boom, recession and stagflation assumptions flow through to enterprise value and equity returns.
Mapping Macro Drivers to Revenue Streams
The model separates revenue into domestic and export streams, each compounding from the prior year rather than from the base. Domestic growth combines GDP growth, CPI inflation and a company-specific alpha that captures market share or pricing power.
- Export growth adds foreign GDP and CPI but subtracts home-currency appreciation, since a stronger currency reduces home-currency export receipts. Each scenario supplies a distinct set of these drivers, so the same structure produces contrasting top-line paths.
- Stagflation, for example, can show positive nominal growth because high inflation inflates selling prices even as real volume growth stays weak.
Cost Escalation and Margin Sensitivity
Materials and labour costs are expressed as a percentage of revenue multiplied by a cumulative escalation factor. This dual-escalation design captures the divergence between selling-price inflation, which drives revenue, and input-cost inflation from commodities and wages, which drives costs.
- Because the two rates differ across scenarios, gross margin expands or compresses rather than holding constant. Fixed operating expenses such as SG&A, R&D and other overheads escalate with the active scenario CPI.
- Interest expense uses an effective rate equal to the policy rate plus a credit spread, applied to the opening debt balance, so financing costs rise most sharply in stagflation.
Working Capital, Cash Flow and Balance Sheet Integrity
Working capital days are scenario-dependent: receivables and inventory days typically lengthen in a recession while payables compress, and the reverse occurs in a boom.
- AR, inventory and AP are calculated from revenue or COGS using those days.
- The cash flow statement follows the indirect method, adding back depreciation and adjusting for working capital movements, then deducting maintenance capex and scheduled debt repayment.
- A Day 0 balance sheet reconstruction derives opening retained earnings from base-case assumptions so the historical position balances and Year 1 working capital movements are measured from a coherent starting point.
Scenario Selection, Dashboard Comparison and Practical Use
A toggle selects one of four scenarios, and the active column feeds the named ranges that drive the statements.
- The macro dashboard compares Year 5 key metrics across all four scenarios simultaneously using algebraic formulas built directly from the scenario inputs, so every regime remains visible regardless of the toggle.
- Depreciation follows a straight-line path with no terminal-year dump, and checks validate balance sheet balancing, cash reconciliation and depreciation limits.
- The template is suited to stress-testing an existing mid-market manufacturer's financials under alternative economic environments.
Formatted to IB standards
Named theme colors repaint the whole workbook in one click, on top of an investment-banking structure with clear input, output, and cross-sheet reference styling - brand-ready, institutional-grade, and fully auditable.
Created by ex-finance professionals
Hey, I’m Alex and I created Finamodel.
Over my years in the finance industry I kept building the same models over and over again. Same structure, same assumptions, different logo. So I started building frameworks to turn them into clean, reusable templates.
Every model here is one I’d actually use for a client, and I personally vet each one before it goes up.
I’m not an expert in every industry, but I’ve built enough models to know what belongs in one. And when something is completely foreign to me, I reach out to my network for experts to work on our models with us.
Having a template library on hand cuts a first build from hours to minutes.
Need help finding your model? You’ll find me in the Finamodel app!
Frequently asked
How many scenarios should I model?+
Start with three: base (most likely), upside (strong growth, low inflation), and downside (recession, high inflation). Add a severe stress scenario if needed, but too many scenarios can dilute focus and communication.
How do I calibrate scenario assumptions?+
Use consensus economist forecasts for the base case. For upside and downside, move key variables such as GDP and unemployment by one to two standard deviations from historical norms.
How do I assign scenario probabilities?+
Use market-implied probabilities from bond spreads and volatility indices where available. A common starting point is 50% base, 25% upside, and 25% downside, adjusted for current conditions.
Who uses macroeconomic scenario models?+
Economic analysts, risk managers, strategy teams, and investors use these models for strategic planning, risk budgeting, and valuation sensitivity analysis across economic cycles.
How do I link macro variables to company P&L?+
Identify the key economic drivers of each revenue and cost line, for example linking consumer spending growth to retail revenue or unemployment to credit losses, and build regression-based or judgment-based sensitivity coefficients.
Have more financial modelling questions? Contact us
Related templates
Credit Stress Testing Framework
Stress test credit portfolios across default probabilities, loss severities, and adverse macroeconomic scenarios.
Risk Parity Portfolio Model
Allocation framework that weights assets by inverse volatility to achieve equal risk contribution across equities, bonds, and alternatives.
Multi-Asset Portfolio Allocation
Asset allocation model optimizing portfolio construction across equities, bonds, alternatives, and real assets.
Public Housing Authority Operating Model
Unit mix, rent roll, operating expenses, and asset replacement reserve for public housing portfolios.
