Absolutely, I can help you structure your **step-by-step financial analysis in Excel** for the **Mercury Athletic Footwear** case using the Discounted Cash Flow (DCF) method. Below I’ll walk you through **each step you need to do in Excel** and provide explanations and formulas to use, so you can build your own financial analysis spreadsheet. --- ## Step 1: Gather the Required Data You will need the following inputs from the case: - **Projections** of Revenue, EBIT, Depreciation, Capital Expenditures, Net Working Capital for 2007-2011 (from the case) - **Tax Rate** (from the case) - **Debt, Equity, Beta, Risk-free Rate, Market Risk Premium** (to calculate WACC) - **Number of Shares Outstanding (if needed)** - **Terminal growth rate or exit multiple** for Terminal Value --- ## Step 2: Estimate the Weighted Average Cost of Capital (WACC) ### a) **Calculate Cost of Equity:** **Formula:** Cost of Equity = Risk-free rate + Beta × Market risk premium **Example in Excel:** If Risk-free rate is 5%, Beta is 1.2, and Market risk premium is 6%: `= 5% + 1.2 * 6%` → **12.2%** ### b) **Cost of Debt:** Use pre-tax cost of debt × (1 – tax rate). **Example in Excel:** If cost of debt is 7% and tax rate is 40%: `= 7% * (1 - 40%)` → **4.2%** ### c) **Weights:** - Weight of Equity = Equity / (Equity + Debt) - Weight of Debt = Debt / (Equity + Debt) **Example:** If Equity = $800M, Debt = $200M: Equity Weight = `= 800/(800+200)` → **0.8** Debt Weight = `= 200/(800+200)` → **0.2** ### d) **WACC Formula:** WACC = (Cost of Equity × Weight Equity) + (Cost of Debt × Weight Debt) **Example in Excel:** `= (12.2% * 0.8) + (4.2% * 0.2)` → **10.6%** **Should you use Mercury’s or AGI’s WACC?** Generally, use the **acquirer's WACC** if AGI intends to fully integrate Mercury and take on its risk profile. If Mercury will continue to operate separately, use Mercury’s. --- ## Step 3: Project Free Cash Flows (FCF) for 2007-2011 **FCF Formula:** FCF = EBIT × (1 – Tax Rate) + Depreciation – CapEx – Change in Net Working Capital **In Excel, for each year:** 1. **EBIT**: from projections 2. **Tax**: `= EBIT * Tax Rate` 3. **NOPAT**: `= EBIT - Tax` or `= EBIT * (1-Tax Rate)` 4. **Add Depreciation**: from projections 5. **Subtract CapEx**: from projections 6. **Subtract Change in NWC**: (NWC_t - NWC_t-1) **Example table:** | Year | EBIT | Tax Rate | Depreciation | CapEx | NWC | ΔNWC | FCF | |------|------|----------|--------------|-------|-----|------|-----| | 2007 | X | Y | Z | A | B | B-B0 | FCF | - ΔNWC = NWC (Current Year) – NWC (Previous Year) - FCF = EBIT*(1-Tax Rate) + Depreciation – CapEx – ΔNWC --- ## Step 4: Estimate Terminal Value (TV) in 2011 **Method 1: Perpetuity (Stable Growth)** - TV = (FCF_2011 × (1 + g)) / (WACC – g) - g = terminal growth rate (e.g., 3%) **Method 2: Exit Multiple** - TV = EBIT_2011 × Industry Exit Multiple (e.g., 8x) **Choose and explain your method.** - Stability: Use perpetuity if Mercury will continue as a viable business. - Acquisition: Use multiples if M&A market is active and data is available. --- ## Step 5: Discount FCFs and Terminal Value to Present Value - **For each year (2007-2011):** - Discount Factor = 1 / (1 + WACC) ^ n - n = number of years into future (1 for 2007, 2 for 2008, etc.) - PV of FCF = FCF × Discount Factor - **Terminal Value:** - Discount Terminal Value back to 2006 (base year): - PV_TV = TV / (1 + WACC)^N (N = number of years to TV) **Excel Table Example:** | Year | FCF | Discount Factor | PV of FCF | |------|-------|----------------|-----------| | 2007 | 12.0M | =1/(1+10.6%)^1 | 10.85M | | ... | ... | ... | ... | | 2011 | XX | =1/(1+10.6%)^5 | YY | | Terminal Value | Discount Factor | Present Value | |---------------|----------------|---------------| | $XXXM | =1/(1+WACC)^5 | $ZZZM | --- ## Step 6: Sum Present Values to Estimate Enterprise Value Enterprise Value = PV of FCFs (2007-2011) + PV of Terminal Value --- ## Step 7: Adjust for Debt/Surplus Cash (if needed) - If you need Equity Value: Equity Value = Enterprise Value – Debt + Cash --- ## Step 8: Recommendation & Maximum Price - The **maximum price AGI should pay** is the calculated Equity Value. - Compare this value to the seller’s asking price. --- ## Step 9: Sensitivity Analysis (Optional but Good Practice) - Try different WACCs, terminal growth rates, or multiples to show value ranges. --- ## How to Structure This in Excel ### **Sheet 1: Inputs** - List all assumptions: WACC, tax rate, projections ### **Sheet 2: FCF Calculations** - Table for each year: EBIT, Taxes, Depreciation, CapEx, ΔNWC, FCF ### **Sheet 3: Terminal Value** - Calculation using both methods ### **Sheet 4: DCF Summary** - Discount FCFs and TV back to present, sum them ### **Sheet 5: Sensitivity** - Vary WACC, terminal growth, etc. --- ## **Sample Excel Layout** ``` A | B | C | D | E | F | G | H Year | EBIT | Tax Rate | Deprec. | CapEx | NWC | ΔNWC | FCF --------------------------------------------------------------------------------------------- 2006 | | | | | $100 | | 2007 | $50 | 40% | $10 | $12 | $110 | $10 | =B2*(1-C2)+D2-E2-G2 2008 | $55 | 40% | $12 | $14 | $120 | $10 | =B3*(1-C3)+D3-E3-G3 ... ``` For Discounted Cash Flow: ``` A | B | C | D Year | FCF | Discount Fac | PV FCF ------------------------------------------------ 2007 | $XX | =1/(1+WACC)^1| =B2*C2 ... 2011 | $YY | =1/(1+WACC)^5| =B6*C6 TV | $ZZZ | =1/(1+WACC)^5| =B7*C7 ``` --- ## **Final Recommendation** - If the calculated value is **above the asking price** and you believe the projections/assumptions are reasonable, **recommend acquisition**. - Otherwise, **do not recommend** (or recommend at a lower price). --- ## **Summary Table of Steps** | Step | Action | |------|--------| | 1 | Gather data from case | | 2 | Calculate WACC | | 3 | Forecast FCFs (2007-2011) | | 4 | Estimate Terminal Value | | 5 | Discount cash flows and TV to present value | | 6 | Sum PVs for Enterprise Value | | 7 | Subtract debt, add cash for Equity Value | | 8 | Compare to asking price and make recommendation | | 9 | Do sensitivity analysis for key assumptions | --- ## **Key Excel Formulas to Use** - `=NPV(rate, values)` for discounting FCFs (but best to use manual discounting for clarity) - `=B2*(1-C2)+