Build a goodwill impairment early warning model: cash-generating units, triggers,…
- User
Build a goodwill impairment early warning model: cash-generating units, triggers, sensitivity, and board updates.
- Assistant
Goodwill Impairment Early Warning Model
As Sherlock, I'll build a comprehensive, actionable early warning model for goodwill impairment testing under IAS 36 (IFRS). This model focuses on cash-generating units (CGUs), impairment triggers, sensitivity analysis, and board reporting. It's designed for quarterly monitoring, with annual full tests, to flag risks early and support proactive decisions.
The model uses:
- Value in Use (VIU) via Discounted Cash Flow (DCF) as the primary recoverable amount metric (easier to control internally than Fair Value Less Costs of Disposal - FVLCD).
- Excel/Google Sheets template logic (with formulas; I can provide a downloadable link if needed, but here's the blueprint).
- Automation potential: Integrate with ERP/financial systems for real-time triggers.
1. Cash-Generating Units (CGUs) Setup
CGUs are the smallest identifiable groups expected to generate independent cash inflows. Allocate goodwill to CGUs based on acquisition synergies.
Step 1.1: CGU Mapping Template
CGU Name Business Segment Carrying Value of Goodwill (£m) Total CGU Carrying Amount (£m) Allocation % of Total Goodwill CGU-A (Retail) Europe Retail 150 500 30% CGU-B (Online) E-commerce 200 700 40% CGU-C (Wholesale) B2B Supply 100 300 20% CGU-D (Tech) Software 50 150 10% Total 500 1,650 100% - Formula for Allocation %:
=Goodwill_CGU / Total_Goodwill - Annual Review: Reassess if restructurings occur (e.g., merge CGU-A and CGU-B if synergies overlap).
2. Impairment Triggers
Monitor qualitative and quantitative indicators quarterly. If any trigger hits, run full VIU test + sensitivity.
Trigger Dashboard (Quarterly Monitoring)
Category Trigger Threshold Monitoring Metric Status (Green/Yellow/Red) Last Checked External Significant decline in market cap >20% drop in 12m Market Cap vs. Book Value Green Q1 2024 Adverse economic changes GDP growth <1% or inflation >5% Macro indices (IMF/OECD data) Green Q1 2024 Increase in discount rate WACC +2% YoY Bond yields + equity risk premium Yellow Q1 2024 Internal Actual EBITDA < Budget >10% shortfall for 2 quarters Budget vs. Actual Report Red Q1 2024 Negative cash flow trends Operating CF <0 for 2 quarters Cash Flow Statement Green Q1 2024 Loss of key customers/contracts >15% revenue from top client lost Customer Concentration Report Green Q1 2024 Tech/Regulatory obsolescence New regulation impacting >10% revenue Risk Register Green Q1 2024 Other Carrying amount > recoverable amount (headroom check) Headroom <20% VIU Model Output Yellow (CGU-B) Q1 2024 - Early Warning Rule:
- Red: Immediate full impairment test.
- Yellow: Run sensitivity analysis.
- Auto-Alert: Use
=IF(AND(Metric<Threshold, Quarters>=2),"RED","GREEN")in Excel.
- Data Sources: ERP (e.g., SAP), Bloomberg for market data.
3. Core Impairment Model (VIU via DCF)
For each CGU, calculate Recoverable Amount (RA) = VIU (assume FVLCD unavailable internally).
DCF Template per CGU (5-Year Explicit + Terminal Value)
Assumptions (base case; sensitivity below):
- Revenue Growth: 3-5% (historical + forecast).
- EBITDA Margin: 15-20%.
- Capex: 5% of revenue.
- WACC: 8% (risk-free + premium).
- Terminal Growth: 2%.
Year Revenue (£m) EBITDA (£m) FCF (£m) Discount Factor PV of FCF (£m) 1 300 60 45 0.926 41.7 2 315 63 47.3 0.857 40.5 3 331 66.2 49.6 0.794 39.4 4 347 69.5 52.1 0.735 38.3 5 365 73.0 54.7 0.681 37.2 Terminal Value (Yr5) 1,085 (Perpetuity) 0.681 739 Total VIU 936 CGU Carrying Amount 500 Headroom (VIU - Carrying) 436 (87%) -
Key Formulas:
- FCF:
=EBITDA * (1-Tax) - Capex - ΔNWC(Tax=25%, ΔNWC=2% Rev). - Discount Factor:
=1/(1+WACC)^Year - Terminal:
=FCF5*(1+g)/(WACC-g) - Headroom %:
=(VIU - Carrying)/Carrying
- FCF:
-
Run Quarterly: Update forecasts from budget/actuals.
4. Sensitivity Analysis
Test "what-if" scenarios to quantify risk. Flag if headroom drops <20%.
Sensitivity Table (for CGU-B Example, Base VIU=£700m, Carrying=£700m, Headroom=0%)
Scenario WACC Change Growth Change EBITDA Margin Change VIU (£m) Headroom % Risk Level Base 0% 0% 0% 700 0% Monitor Bear 1 +1% -1% -2% 550 -21% Impair £150m Bear 2 +2% -2% -3% 450 -36% Impair £250m Bull -0.5% +1% +1% 850 +21% Safe Stress (Recession) +3% -3% -5% 350 -50% Critical - Excel Tornado Chart: Use Data Table for 1-way sensitivity (e.g., vary WACC ±2% in 0.5% steps).
- Monte Carlo (Advanced): Use @RISK or Python (below snippet) for probabilistic headroom.
import numpy as np def viu_monte_carlo(wacc_mean=0.08, growth_mean=0.03, n_sims=1000): wacc = np.random.normal(wacc_mean, 0.01, n_sims) growth = np.random.normal(growth_mean, 0.02, n_sims) # DCF logic here... return np.mean(viu_sims) # Prob(headroom <0) = X% - Thresholds:
Headroom % Action >50% Low risk 20-50% Medium (board note) 0-20% High (sensitivity deep-dive) <0% Impair immediately
5. Board Updates & Dashboard
Quarterly one-page dashboard for board packs. Automate in Power BI/Tableau or Excel.
Sample Board Summary (Q1 2024)
GOODWILL IMPAIRMENT EARLY WARNING - Total Goodwill: £500m [Traffic Light Summary] - CGU-A: 🟢 Headroom 87% | No triggers - CGU-B: 🟡 Headroom 0% | EBITDA miss (Yellow trigger) | Bear scenario: -21% - CGU-C: 🟢 Headroom 45% - CGU-D: 🟢 Headroom 60% Key Risks: Rising WACC (Yellow). Stress VIU implies £150m potential impairment (CGU-B). Recommendations: 1. Run full test on CGU-B. 2. Hedge via cost cuts (target +2% margin). Next Update: Q2 2024- Frequency: Quarterly board paper + ad-hoc if Red trigger.
- Escalation: Auto-email if headroom <20%.
- Audit Trail: Log all assumptions/changes.
Implementation Guide
- Build in Excel: Copy tables above; use INDIRECT for multi-CGU.
- Integrate Data: Link to financial model (e.g., via Power Query).
- Test: Backtest on historical data (e.g., 2020 COVID scenario).
- Compliance: Document assumptions per IAS 36 (para 134); get board approval on WACC/growth.
This model provides 80% of impairment foresight with minimal effort. If you share specific CGU data/financials, I can customize with live calcs or Python code. Questions?
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.