Bonus Assignment_DCF Valuation_Piravin

.xlsm

School

Toronto Metropolitan University *

*We aren’t endorsed by this school

Course

514

Subject

Finance

Date

Jan 9, 2024

Type

xlsm

Pages

20

Uploaded by LieutenantCaribou430

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 % 16.4% % 47.8% % 138.8% % 10.9% % 27.8% % 11.2% $mm 0.0 $mm 830.5 $mm (830.5) mm 1,600.0 $ (0.5)
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.0% 23.0% 25.0% Output Scenario 1 Base Case + 10% 16.4% 47.8% 138.8% 2 Base Case 14.2% 48.3% 135.7% 3 Base Case - 10% 12.1% 48.6% 134.1% 4 Sales Volumn Growth (Flat) - Low 14.2% 48.1% 135.0% 5 Sales Volumn Growth (Rolled) - Low 14.2% 48.2% 135.6% 6 Gross Profit Margin - Low 12.1% 48.1% 140.1% 7 Dividend Payout Ratio - Low 14.2% 48.2% 132.6% 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)
8 Days Sales Outstanding - Low 14.2% 48.7% 134.7% 9 Inventory Turns - Low 14.2% 47.9% 136.9% 10 Days Payables Outstanding - Low 14.2% 48.3% 135.7% 11 Capital expenditures as % of sales - Low 14.2% 49.1% 133.5% 12 WACC (stable period) - Low 14.2% 48.3% 135.7% 13 ROIC (stable period) - Low 14.2% 48.3% 135.7% 14 Growth Rate (stable period) - Low 14.2% 48.3% 135.7% 15 Sales Volumn Growth (Flat) - High 14.2% 48.5% 136.5% 16 Sales Volumn Growth (Rolled) - High 14.2% 48.3% 135.8% 17 Gross Profit Margin - High 16.4% 48.3% 132.2% Incremental Downside and Upside Sensitivity Equity Value per Share Parameter Low-Base High-Base Total Sales Growth Rate -100.00% -10.17% -110.17% Gross Profit Margin -100.00% -11.98% -111.98% Dividend Payout Ratio -100.00% -11.62% 111.62% Capital expenditures as % of sales #VALUE! -9.66% #VALUE! WACC (stable period) -2.82% 0.00% 2.82% ROIC (stable period) -11.05% -14.18% 25.23% Growth Rate (stable period) -20.11% -11.05% 31.16% #VALUE! Err:502 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 0.00% 0.00% 0.00% 0.00% 0.0 Chart Ti Low-Base H
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.0% 7.5% 7.5% 2.0% Reinvestment Rate (K) Equity Value 10.9% 27.8% 11.2% 1,626.8 1,716.5 171.6 9.3% 27.8% 9.7% 1,481.4 1,571.1 157.1 7.9% 27.8% 8.3% 1,321.5 1,411.2 141.1 9.2% 27.8% 9.7% 1,469.8 1,559.5 156.0 9.3% 27.8% 9.7% 1,480.1 1,569.8 157.0 8.1% 27.8% 8.3% 1,209.7 1,299.4 129.9 9.1% 27.8% 9.7% 1,480.0 1,569.7 157.0 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
9.3% 27.8% 9.9% 1,496.9 1,586.6 158.7 9.3% 27.8% 9.6% 1,465.0 1,554.7 155.5 9.3% 27.8% 9.7% 1,471.5 1,561.2 156.1 9.3% 27.8% 9.9% 1,506.0 1,595.7 159.6 9.3% 27.8% 9.7% 1,676.6 1,766.3 176.6 9.3% 30.9% 9.7% 1,426.2 1,515.9 151.6 9.3% 25.0% 9.7% 1,481.4 1,571.1 157.1 9.4% 27.8% 9.8% 1,493.2 1,582.9 158.3 9.3% 27.8% 9.8% 1,482.8 1,572.5 157.2 10.5% 27.8% 11.2% 1,752.5 1,842.2 184.2 Small Parameter Low-Base High-Base Err:502 #VALUE! 0.00% 0.00% Err:502 16 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 15 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 14 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 13 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 12 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 00% 0.00% 0.00% 0.00% 0.00% itle High-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