Bonus Assignment - DCF Valuation - Start

.xlsm

School

Toronto Metropolitan University *

*We aren’t endorsed by this school

Course

631

Subject

Finance

Date

Feb 20, 2024

Type

xlsm

Pages

21

Uploaded by ElderWater15776

Report
IGM Control Panel Key Model Drivers: Key Performance Indicators: Year of Interest: Sales Growth Rate 7.0% Gross Profit Margin 23.0% Net Profit Margin (NI/S) Dividend Payout Ratio 25.0% Asset Turnover (S/A) Capital expenditures as % of sales 5.0% Equity Multiplier (A/E) Terminal Value Parameters Return on Equity WACC (stable period) 7.50% Reinvestment Rate (K) ROIC (stable period) 7.50% Return on Invested Capital Growth Rate (stable period) 2.00% Valuation as of: 9/30/2015 Enterprise Value Less Net Debt Equity Value Shares Outstanding Equity Value per Share
2016P % 7.7% % 103.4% % 259.2% % 20.7% % 49.2% % 12.5% $mm 390,217.8 $mm 50,110.3 $mm 340,107.5 mm 1,600.0 $ 212.57
Scenario Analysis 2 Scenario 1 Base Case + 10% 7.7% 25.3% 27.5% 2 Base Case 7.0% 23.0% 25.0% 3 Base Case - 10% 6.3% 20.7% 22.5% 4 Sales Growth Rate - Low 6.3% 23.0% 25.0% 5 Gross Profit Margin - Low 7.0% 20.7% 25.0% 6 Dividend Payout Ratio - Low 7.0% 23.0% 22.5% 7 Capital expenditures as % of sales - Low 7.0% 23.0% 25.0% 8 WACC (stable period) - Low 7.0% 23.0% 25.0% 9 ROIC (stable period) - Low 7.0% 23.0% 25.0% 10 Growth Rate (stable period) - Low 7.0% 23.0% 25.0% 11 Sales Growth Rate - High 7.7% 23.0% 25.0% 12 Gross Profit Margin - High 7.0% 25.3% 25.0% 13 Dividend Payout Ratio - High 7.0% 23.0% 27.5% 14 Capital expenditures as % of sales - High 7.0% 23.0% 25.0% 15 WACC (stable period) - High 7.0% 23.0% 25.0% 16 ROIC (stable period) - High 7.0% 23.0% 25.0% 17 Growth Rate (stable period) - High 7.0% 23.0% 25.0% 2 Base Case 7.00% 23.00% 25.00% Output Scenario 1 Base Case + 10% 0.079121 1.04096786 2.5939834 2 Base Case 0.077276 1.03440041 2.5919679 3 Base Case - 10% 0.075405 1.02795613 2.5904584 4 Sales Growth Rate - Low 0.078002 1.02871407 2.5881212 5 Gross Profit Margin - Low 0.074696 1.03564389 2.6023538 6 Dividend Payout Ratio - Low 0.077276 1.03233744 2.5837646 7 Capital expenditures as % of sales - Low 0.077276 1.03440041 2.5919679 8 WACC (stable period) - Low 0.077276 1.03440041 2.5919679 Case Number: Case Number Sales Growth Rate Gross Profit Margin Dividend Payout Ratio Case Number Net Profit Margin (NI/S) Asset Turnover (S/A) Equity Multiplier (A/E)
9 ROIC (stable period) - Low 0.077276 1.03440041 2.5919679 10 Growth Rate (stable period) - Low 0.077276 1.03440041 2.5919679 11 Sales Growth Rate - High 0.076559 1.04007482 2.5958179 12 Gross Profit Margin - High 0.079855 1.03315992 2.5816891 13 Dividend Payout Ratio - High 0.077276 1.03647164 2.6002567 14 Capital expenditures as % of sales - High 0.077276 1.03440041 2.5919679 15 WACC (stable period) - High 0.077276 1.03440041 2.5919679 16 ROIC (stable period) - High 0.077276 1.03440041 2.5919679 17 Growth Rate (stable period) - High 0.077276 1.03440041 2.5919679 Incremental Downside and Upside Sensitivity Equity Value per Share Parameter Low-Base High-Base Total Sales Growth Rate 0.011915 -0.0122157 0.0241308 Gross Profit Margin -0.128467 0.13776024 0.2662275 Dividend Payout Ratio -0.000529 0.0005291 0.0010581 Capital expenditures as % of sales 0.01076 -0.0107935 0.0215533 WACC (stable period) 0.152662 -0.1160233 0.2686856 ROIC (stable period) -0.039065 0.03196235 0.0710274 Growth Rate (stable period) -0.001896 0.00189581 0.0037916 Dividend Payout Ratio Growth Rate Capital Expediture as % of Sales Sales Growth Rate ROIC Gross Profit Margin WACC -0.15 -0.1 -0.05 Tornado Diagram: Impact on Share Low-Base Hi
WACC (stable period) 5.5% 8.3% 8.3% 2.2% 5.0% 7.5% 7.5% 2.0% 4.5% 6.8% 6.8% 1.8% 5.0% 7.5% 7.5% 2.0% 5.0% 7.5% 7.5% 2.0% 5.0% 7.5% 7.5% 2.0% 4.5% 7.5% 7.5% 2.0% 5.0% 6.8% 7.5% 2.0% 5.0% 7.5% 6.8% 2.0% 5.0% 7.5% 7.5% 1.8% 5.0% 7.5% 7.5% 2.0% 5.0% 7.5% 7.5% 2.0% 5.0% 7.5% 7.5% 2.0% 5.5% 7.5% 7.5% 2.0% 5.0% 8.3% 7.5% 2.0% 5.0% 7.5% 8.3% 2.0% 5.0% 7.5% 7.5% 2.2% 5.00% 7.50% 7.50% 2.00% Reinvestment Rate (K) Equity Value 0.2136479669 0.553611269916539 0.127197775 396834.6981 346724.41006 216.70276 0.2071859182 0.492413781048809 0.125145386 390217.7862 340107.49816 212.56719 0.2007950145 0.42868631623826 0.123042596 386165.4666 336055.17863 210.03449 0.2076745869 0.468257795149899 0.125805566 394270.2042 344159.91619 215.09995 0.2013142892 0.509725456218964 0.121548998 346525.1215 296414.83352 185.25927 0.2061182979 0.492413781048809 0.125145386 390037.8647 339927.5767 212.45474 0.2071859182 0.437377450902024 0.126013308 393877.292 343767.00398 214.85438 0.2071859182 0.492413781048809 0.125145386 442139.3638 392029.07578 245.01817 Capital expenditures as % of sales ROIC (stable period) Growth Rate (stable period) Return on Equity Return on Invested Capital Enterprise Value Equity Value per Share Run All Scenarios Set up Model for Given Case Reset Model with Base Case Parameters
0.2071859182 0.492413781048809 0.125145386 376931.4565 326821.16854 204.26323 0.2071859182 0.492413781048809 0.125145386 389573.0084 339462.7204 212.1642 0.206696825 0.516684845843356 0.124489003 386063.142 335952.85399 209.97053 0.2129969507 0.476058011301776 0.128744331 437071.0778 386960.78982 241.85049 0.2082646558 0.492413781048809 0.125145386 390397.738 340287.44997 212.67966 0.2071859182 0.547450111195594 0.124289338 386546.8377 336436.54975 210.27284 0.2071859182 0.492413781048809 0.125145386 350757.3872 300647.09918 187.90444 0.2071859182 0.492413781048809 0.125145386 401088.4195 350978.13149 219.36133 0.2071859182 0.492413781048809 0.125145386 390862.5639 340752.27592 212.97017 Small Parameter Low-Base High-Base Match Description 0.0010581162 1 -0.00052901 0.000529103 3 Dividend Pa 0.0037916116 2 -0.00189581 0.001895806 7 Growth Rate 0.0215533449 3 0.01075985 -0.010793495 4 Capital Exped 0.0241307888 4 0.011915109 -0.012215679 1 Sales Growt 0.0710274342 5 -0.03906509 0.031962345 6 ROIC 0.2662274628 6 -0.12846722 0.137760243 2 Gross Profit 0.2686855688 7 0.152662255 -0.116023314 5 WACC 0 0.05 0.1 0.15 0.2 e Price (Base Case: $212.57) igh-Base
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help