Market Downturn Impact
Assesses how a market downturn of varying severity would affect revenue, cash flow, and business viability. Calculates a resilience score, the reserves needed to survive each scenario, and the projected recovery timeline.
Risk - Market Downturn Impact.xlsx
Excel (.xlsx) — No macros — Works in Excel, Google Sheets, LibreOffice
What This Spreadsheet Solves
- No pre-planned response for different downturn severity levels
- Business resilience to market shocks is untested and unquantified
- Reserve requirements for survival are unknown
- Recovery timeline estimation does not exist for planning purposes
- Stakeholder communications during downturns lack data-backed projections
Who This Is For
- Executive teams planning downturn contingencies
- CFOs sizing reserve funds against economic scenarios
- Board members evaluating organizational resilience
- Strategic planners modeling multi-year recovery paths
Inputs
- $Annual Revenue
- $Annual Fixed Costs
- $Current Cash Reserves
- %Downturn Severity (% Revenue Decline)
- #Expected Downturn Duration (Months)
Outputs
- Resilience score (0-100)
- Reserves needed to survive the downturn
- Cash shortfall or surplus under scenario
- Months to full revenue recovery post-downturn
- Break-even revenue during downturn
How Calculations Work
The model reduces revenue by the severity percentage for the downturn duration. Fixed costs remain constant; variable costs scale with revenue. Monthly cash flow is projected and compared against reserves. The resilience score weights current reserves, cost flexibility, and revenue diversification. Recovery timeline assumes revenue rebuilds at a configurable rate (default: 5% per month of the lost revenue). Break-even during the downturn is recalculated using the compressed contribution margin.
Example Use Case
Scenario: Annual revenue: $2.4M. Fixed costs: $1.1M/year. Cash reserves: $350,000. Downturn: 35% revenue decline for 9 months.
Result: Resilience score: 54/100. Revenue during downturn: $130,000/month (vs normal $200,000). Monthly cash burn during downturn: $21,700. Reserves needed: $195,300. Current reserves cover the scenario with $154,700 surplus. Full recovery to pre-downturn revenue: 7 months post-downturn.
What You Get — 5 Sheets
Technical Details
Frequently Asked Questions
What downturn severity should I plan for?
Model at least three scenarios: mild (15-20% decline), moderate (30-40%), and severe (50%+). The 2008 recession saw 20-40% revenue declines across most industries; COVID-19 hit some sectors 50-80%.
How is the resilience score calculated?
It combines three factors: reserve coverage (months of expenses covered), cost flexibility (percentage of costs that can be cut quickly), and revenue diversification (HHI of revenue sources). Each is weighted equally by default.
Does recovery happen linearly?
The default model uses a linear recovery rate. In CONFIG, you can switch to an S-curve recovery (slow start, rapid middle, slow finish) which is more realistic for most markets.
Should I include credit facilities in reserves?
Include available (undrawn) credit facilities, but note them separately. Credit may be restricted during actual downturns, so the model allows you to apply a haircut percentage to credit availability.
How do I improve my resilience score?
The three levers are: increase reserves, increase cost flexibility (shift fixed to variable), and diversify revenue sources. The model shows the score impact of each change.
Download Market Downturn Impact
Ready to use immediately. Enter your data in the INPUT sheet, see results in OUTPUT.