Domain: Finance
Question:
ABC Company is evaluating a potential acquisition of MRC Global Inc. (MRC). As part of the diligence process, the company is reviewing details regarding the financial risks and opportunities associated with the deal. Develop a financial model that can be used to test how certain assumptions affect valuation and expected returns.

Assumptions:
Corporate Tax Rate is 25%
Depreciation = Capex % revenue
Steel is 25% of revenue
Logistics is 6% of revenue
SG&A is 10% of revenue

1)  Assess how changes in the steel input costs, logistics, and SG&A cost drivers could affect EBITDA margins and operating cash flow. Specifically, for each (steel input costs, logistics, and SG&A cost drivers), quantify the EBITDA margin impact of a +/- 10% change. Show the results as dollar impact on EBITDA and operating cash flow, assuming a baseline EBITDA of $300M and revenue of $3.5B.

2) Purchase price is $1.8B enterprise value (~ 6x EBITDA). Test synergy outcomes of $20M and $50M annually. For each case, calculate ROI as a percentage using a 10% discount and a 5-year timeframe. 

3) As the primary valuation tool, develop a discounted cash flow that encompasses two contrasting cases (with sensitivity testing where appropriate): a) A steady state scenario (stable EBITDA margins at 8.5%, CAPEX = 2% of revenue, revenue growth at 5% compound annual growth rate), b) An unfavorable margin compression (EBITDA margins at 6.5%, CAPEX = 3% of revenue, revenue growth at 2% compound annual growth rate) For both, use a 9% WACC and 2.5% terminal growth. Perform a sensitivity test with WACC +/- 100 bps and EBITDA margin measured in +/- bps. Explain how outcomes affect ABC's investment decision (expecting an ROI >10%).

The model should assist ABC in determining whether expected returns meet the hurdle test of ROI >10% under both scenarios. A good financial model should show assumptions, calculations, and outputs that include ROI calculations, sensitivity analysis, and DCF scenarios. A strong response will compare outputs across scenarios and explain the decision relevance. Recommend whether ABC should proceed, defer, or renegotiate terms based on quantified results.

All calculations should be rounded as follows: $M to nearest 0.1, $B to nearest 0.01, ROI to 1 decimal place, basis points to nearest 10 bps.

Expected points in a correct answer:
States the baseline Revenue is $3.5B.
States the baseline EBITDA is $300M.
Calculates the baseline EBITDA margin is 8.6%.
States the Corporate tax rate is 25%.
States the WACC is 9%.
States the terminal growth rate (g) is 2.5%.
States the assumption for Net Working Capital is 1% of revenue growth each year.
States the assumption that Depreciation equals Capex % of revenue.
States the purchase price Enterprise Value is $1.8B.
States the cost weight for Steel is 25% of revenue.
States the cost weight for Logistics is 6% of revenue.
States the cost weight for SG&A is 10% of revenue.
Calculates a 10% change in steel costs translates into an EBITDA swing of approximately +/- $87.5 million.
Calculates the after-tax operating cash flow impact from a 10% change in steel costs is +/- $65.6 million.
Calculates a 10% change in logistics costs results in a ±$21 million EBITDA variation.
Calculates the after-tax operating cash flow impact from a 10% change in logistics costs is +/- $15.8 million.
Calculates a 10% shift in SG&A equates to an EBITDA change of +/- $35 million.
Calculates the after-tax cash flow impact from a 10% shift in SG&A is +/-$26.3 million.
Draws a conclusion from sensitivity testing highlighting the dominant risk factor
Calculates that $20 million of annual synergies produce an NPV of about $75.8 million.
Calculates the return for $20 million of annual synergies is roughly a 4.2% return.
Calculates that increasing synergies to $50 million annually lifts the NPV to about $189.5 million.
Calculates the return for $50 million of annual synergies is a 10.5% return.
Concludes the higher synergy case sets the NPV at slightly over the 10% hurdle.
Calculates the DCF in the steady-state case yields an enterprise value of $2.98B.
Calculates the steady-state enterprise value is $1.18B above the $1.8B purchase price.
Calculates the steady-state case implies an ROI of 65% relative to price.
Calculates the DCF value in the margin-compression case falls to $1.41B.
Calculates the margin-compression DCF value is $0.39B below the purchase price.
Calculates the margin-compression case translates to an ROI of −22%.
Determines that to achieve an ROI greater or equal to 10%, EV must exceed $1.98B.
Concludes that Case A clears the hurdle because its EV is greater than $1.98B.
Concludes that Case B fails the hurdle because its EV is less than $1.98B.
Calculates that for Case B to meet the 10% hurdle, the price must drop to ≤ $1.28B.
Calculates the Scenario A enterprise value with an 8% WACC is $3.53B.
Calculates the Scenario A enterprise value with a 10% WACC is $2.58B.
Calculates the impact of a +/-100 bps WACC change in Scenario A is +$548M / −$402M (allow +/-5M)
Calculates the Scenario B enterprise value with an 8% WACC is $1.66B.
Calculates the Scenario B enterprise value with a 10% WACC is $1.22B.
Calculates the impact of a +/-100 bps WACC change in Scenario B is +$255M / −$186M (allow +/-5M)
Calculates the impact of a +/- 50 bps EBITDA margin change in Scenario A is an EV change of +/- 231M.
Calculates the impact of a +/- 50 bps EBITDA margin change in Scenario B is an EV change of +/- $203M.
Recommends to proceed if diligence supports steady state margins (8.5% + ).
Recommends to proceed if steel logistics exposure is hedged/contractually mitigated.
States that at $1.8B, the model indicates ROI 65% vs price.
Recommends to renegotiate if the compression case feels more probable.
Recommends a fair value to still meet a 10% hurdle is < $1.28B EV.
Recommends to defer if volatility in steel or unresolved audit/regulatory items makes margin durability uncertain.
Recommends to defer if uncertainty can't be contractually offset.
Calculates and reports the EBITDA margin impact in basis points (bps) from a +/- 10% change in steel, logistics, and SG&A costs.