Bonus Assignment - DCF Valuation - Start
.xlsm
keyboard_arrow_up
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
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
Related Questions
General Accounting Question please answer do fast
arrow_forward
Financial Accounting
arrow_forward
Assume the following ratios are constant please provide this question solution accounting
arrow_forward
Assume the following ratios areconstant: Total asset turnover 2.8Profit margin 6.8 % Equitymultiplier 2 Payout ratio 30 %What is the sustainable growthrate? (Do not round intermediate
Training
calc
arrow_forward
Determining PB Ratio for Companies with Different Returns and Growth
Assume that the present value of expected ROPI follows a perpetuity with growth g (Value = Amount/ [r - g]). Determine the theoretically correct PB ratio for each of the
following companies A and B. Note: NOPAT = NOA » RNOA.
Company Net Operating Assets Equity RNOA ROE Weighted Avg. Cost of Capital Growth Rate in ROPI
$100
$100 19% 19%
10%
2%
$100
$100
12% 12%
10%
4%
A
B
Round answers to two decimal places.
PB Ratio
Company A
Company B
arrow_forward
Need answer the financial accounting question
arrow_forward
Assume the following ratios are constant.
Total asset turnover
1.49
Profit margin
8.7%
Equity multiplier
1.6
Payout ratio
55%
What is the sustainable growth rate?
arrow_forward
Need help with this question solution general accounting
arrow_forward
Consider the table given below to answer the following question.
Year
Asset value
Earnings
Net investment
Free cash flow (FCF)
Return on equity (ROE)
Asset growth rate
Earnings growth rate
10.00
Present value
1.20
1.29
0.00
0.12
0.12
2
3
4
5
6
7
8
9
10
1.84
11.20 12.54 14.05 15.31 16.69 18.19 19.29 20.44 21.67
1.34 1.51 1.69
2.00 2.18 2.31 2.45 2.60
1.34 1.51 1.26
1.38 1.50 1.09 1.16 1.23 1.30
0.42
0.46 0.50 1.09 1.16 1.23 1.30
0.12
0.12 0.12 0.12 0.12 0.12
0.12 0.12 0.09
0.09 0.06 0.06 0.06 0.06
0.00 0.00
0.12. 0.12
0.12
0.09
0.12 0.12 0.12 0.09 0.09 0.09 0.06 0.06 0.06
Assuming that competition drives down profitability (on existing assets as well as new investment) to 11.5% in year 6, 11% in year 7.
10.5% in year 8, and 8% in year 9 and all later years. What is the value of the concatenator business? Assume 10% cost of capital. (Do
not round intermediate calculations. Enter your answer in millions rounded to 2 decimal places.)
D
$ 14.46 million
arrow_forward
Assume the following ratios are constant:
Total asset
turnover
Profit margin
Equity multiplier
Payout ratio
2.5
6.5%
1.6
20%
What is the sustainable growth rate? (Do not round intermediate calculations and enter
your answer as a percent rounded to 2 decimal places, e.g., 32.16.)
Sustainable growth rate
%
arrow_forward
Consider the table given below to answer the following question.
Year
Asset value
Earnings
Net investment
Free cash flow
Return on equity
Asset growth rate
Earnings growth rate
1
9.00
1.44
1.44
0.00
0.16
0.16
Present value
2
10.44
1.67
1.67
0.00
0.16
0.16
0.16
million
3
4
5
12.11 14.05 15.87
1.94
2.54
2.25
1.94
1.83
2.06
0.00
0.42
0.48
0.16
0.16
0.16
0.16
0.13
0.13
0.16
0.16
0.13
6
7
17.94 20.27
2.78
2.33
0.45
0.155
0.13
0.09
3.04
2.03
1.01
0.15
0.10
0.09
8
9
22.30 24.53
3.23
2.94
2.23
2.45
1.00
0.49
0.145
0.12
0.10
0.10
0.06 -0.09
Assuming that competition drives down profitability (on existing assets as well as new investment) to 15.5% in year 6, 15% in
year 7, 14.5% in year 8, and 12% in year 9 and all later years. What is the value of the concatenator business? Assume 13% cost
of capital.
Note: Do not round intermediate calculations. Enter your answer in millions rounded to 2 decimal places.
10
26.98
3.24
2.70
0.54
0.12
0.10
0.10
arrow_forward
What is the sustainable growth rate on this financial accounting question?
arrow_forward
Please provide answer the accounting question
arrow_forward
Assume the following ratios are constant.
Total asset turnover
Profit margin
Equity multiplier
Payout ration
2.29
Sustainable growth rate
5.7%
1.76
36%
What is the sustainable growth rate?
Note: Do not round intermediate calculations and enter your answer as a percent rounded to 2 decimal places, e.g., 32.16.
arrow_forward
Consider the table given below to answer the following question.
Asset value
Earnings
Net investment
Free cash flow (FCF)
Return on equity (ROE)
Asset growth rate
Earnings growth rate
Present value
Year
1
4
5
6
7
8
9
10
11.00
3.07 3.35
2.51
2.74
2
3
12.65 14.55 16.73 18.74 20.99 23.50 25.62 27.93 30.44
1.65 1.90 2.18 2.51 2.81 3.04 3.29 3.46
1.65 1.90 2.18 2.01 2.25 2.52 2.12 2.31
0.50
0.56 0.52
1.18
0.61
1.15
0.15 0.15 0.15 0.15 0.15 0.145 0.14 0.135
0.11 0.11
0.15 0.15 0.15 0.12 0.12 0.12 0.09 0.09 0.09 0.09
0.15 0.15 0.15 0.12 0.08 0.08 0.05 -0.11 0.09
0.56
Assuming that competition drives down profitability (on existing assets as well as new investment) to 14.5% in year 6, 14% in year 7,
13.5% in year 8, and 11% in year 9 and all later years. What is the value of the concatenator business? Assume 14% cost of capital. (Do
not round intermediate calculations. Enter your answer in millions rounded to 2 decimal places.)
million
arrow_forward
Use the information from this table to find the covariance for each asset.
State of Economy
Probability of State
Return on
Asset J
Return on
Asset K
Boom
30%
5%
24%
Growth
40%
5%
12%
Stagnant
20%
5%
4%
Recession
10%
5%
-10%
arrow_forward
SEE MORE QUESTIONS
Recommended textbooks for you
Principles of Accounting Volume 2
Accounting
ISBN:9781947172609
Author:OpenStax
Publisher:OpenStax College
Intermediate Financial Management (MindTap Course...
Finance
ISBN:9781337395083
Author:Eugene F. Brigham, Phillip R. Daves
Publisher:Cengage Learning
Survey of Accounting (Accounting I)
Accounting
ISBN:9781305961883
Author:Carl Warren
Publisher:Cengage Learning
Related Questions
- Assume the following ratios areconstant: Total asset turnover 2.8Profit margin 6.8 % Equitymultiplier 2 Payout ratio 30 %What is the sustainable growthrate? (Do not round intermediate Training calcarrow_forwardDetermining PB Ratio for Companies with Different Returns and Growth Assume that the present value of expected ROPI follows a perpetuity with growth g (Value = Amount/ [r - g]). Determine the theoretically correct PB ratio for each of the following companies A and B. Note: NOPAT = NOA » RNOA. Company Net Operating Assets Equity RNOA ROE Weighted Avg. Cost of Capital Growth Rate in ROPI $100 $100 19% 19% 10% 2% $100 $100 12% 12% 10% 4% A B Round answers to two decimal places. PB Ratio Company A Company Barrow_forwardNeed answer the financial accounting questionarrow_forward
- Assume the following ratios are constant. Total asset turnover 1.49 Profit margin 8.7% Equity multiplier 1.6 Payout ratio 55% What is the sustainable growth rate?arrow_forwardNeed help with this question solution general accountingarrow_forwardConsider the table given below to answer the following question. Year Asset value Earnings Net investment Free cash flow (FCF) Return on equity (ROE) Asset growth rate Earnings growth rate 10.00 Present value 1.20 1.29 0.00 0.12 0.12 2 3 4 5 6 7 8 9 10 1.84 11.20 12.54 14.05 15.31 16.69 18.19 19.29 20.44 21.67 1.34 1.51 1.69 2.00 2.18 2.31 2.45 2.60 1.34 1.51 1.26 1.38 1.50 1.09 1.16 1.23 1.30 0.42 0.46 0.50 1.09 1.16 1.23 1.30 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.12 0.09 0.09 0.06 0.06 0.06 0.06 0.00 0.00 0.12. 0.12 0.12 0.09 0.12 0.12 0.12 0.09 0.09 0.09 0.06 0.06 0.06 Assuming that competition drives down profitability (on existing assets as well as new investment) to 11.5% in year 6, 11% in year 7. 10.5% in year 8, and 8% in year 9 and all later years. What is the value of the concatenator business? Assume 10% cost of capital. (Do not round intermediate calculations. Enter your answer in millions rounded to 2 decimal places.) D $ 14.46 millionarrow_forward
- Assume the following ratios are constant: Total asset turnover Profit margin Equity multiplier Payout ratio 2.5 6.5% 1.6 20% What is the sustainable growth rate? (Do not round intermediate calculations and enter your answer as a percent rounded to 2 decimal places, e.g., 32.16.) Sustainable growth rate %arrow_forwardConsider the table given below to answer the following question. Year Asset value Earnings Net investment Free cash flow Return on equity Asset growth rate Earnings growth rate 1 9.00 1.44 1.44 0.00 0.16 0.16 Present value 2 10.44 1.67 1.67 0.00 0.16 0.16 0.16 million 3 4 5 12.11 14.05 15.87 1.94 2.54 2.25 1.94 1.83 2.06 0.00 0.42 0.48 0.16 0.16 0.16 0.16 0.13 0.13 0.16 0.16 0.13 6 7 17.94 20.27 2.78 2.33 0.45 0.155 0.13 0.09 3.04 2.03 1.01 0.15 0.10 0.09 8 9 22.30 24.53 3.23 2.94 2.23 2.45 1.00 0.49 0.145 0.12 0.10 0.10 0.06 -0.09 Assuming that competition drives down profitability (on existing assets as well as new investment) to 15.5% in year 6, 15% in year 7, 14.5% in year 8, and 12% in year 9 and all later years. What is the value of the concatenator business? Assume 13% cost of capital. Note: Do not round intermediate calculations. Enter your answer in millions rounded to 2 decimal places. 10 26.98 3.24 2.70 0.54 0.12 0.10 0.10arrow_forwardWhat is the sustainable growth rate on this financial accounting question?arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- Principles of Accounting Volume 2AccountingISBN:9781947172609Author:OpenStaxPublisher:OpenStax College
- Intermediate Financial Management (MindTap Course...FinanceISBN:9781337395083Author:Eugene F. Brigham, Phillip R. DavesPublisher:Cengage LearningSurvey of Accounting (Accounting I)AccountingISBN:9781305961883Author:Carl WarrenPublisher:Cengage Learning
Principles of Accounting Volume 2
Accounting
ISBN:9781947172609
Author:OpenStax
Publisher:OpenStax College
Intermediate Financial Management (MindTap Course...
Finance
ISBN:9781337395083
Author:Eugene F. Brigham, Phillip R. Daves
Publisher:Cengage Learning
Survey of Accounting (Accounting I)
Accounting
ISBN:9781305961883
Author:Carl Warren
Publisher:Cengage Learning