Bonus Assignment_DCF Valuation_Piravin
.xlsm
keyboard_arrow_up
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
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
calc
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
Please help me to analyze this Financials
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
a. Perform a Du Pont analysis on Green Valley. Assume that the
industry average ratios are as follows:
Total margin
3.5%
Total asset turnover
1.5
Equity multiplier
2.5
Return on equity (ROE)
13.1%
b. Calculate and interpret the following ratios:
Industry Average
Return on assets (ROA)
Current ratio
5.2%
2.0
Days cash on hand
22 days
Average collection period
19 days
Debt ratio
71%
Debt-to-equity ratio
2.5
Times interest earned (TIE) ratio
2.6
Fixed asset turnover ratio
1.4
c. Assume that there are 10,000 shares of Green Valley's stock
outstanding and that some recently sold for $45 per share.
• What is the firm's price/earnings ratio?
What is its market/book ratio?
(Hint: These ratios are discussed in the supplement to this
chapter.)
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
I need help
arrow_forward
Solve this accounting question
arrow_forward
Provide solution for this question
arrow_forward
Question:
Selected financial information
Corporation is reproduced below:
from Syntex
1. NOPAT turnover (average NOA equals ending NOA)
is two.
2. NOPAT turnover margin equals 5%.
3. Leverage ratio (average NFO/average common
equity) is 1.786m and the Spread is 4.4%.
Required:
a. Compute return on net operating assets (RNOA).
b. Compute return on common equity using its three
major components.
c. Analyze the disaggregation of return on common
equity. What is the "leverage advantage (in percent
return) accruing to common equity"?
arrow_forward
Need help
arrow_forward
Suppose the HomeNet's Cost of Capital is12%, use NPV, IRR, MIRR, PI, PP and DPPinvestment appraisal methods to analyse thisforecasted FCFs. Interpret your investment decisions madeaccording to the rules mentioned
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
SEE MORE QUESTIONS
Recommended textbooks for you
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_forwardNeed answer the financial accounting questionarrow_forwardAssume 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
- Please help me to analyze this Financialsarrow_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
- a. Perform a Du Pont analysis on Green Valley. Assume that the industry average ratios are as follows: Total margin 3.5% Total asset turnover 1.5 Equity multiplier 2.5 Return on equity (ROE) 13.1% b. Calculate and interpret the following ratios: Industry Average Return on assets (ROA) Current ratio 5.2% 2.0 Days cash on hand 22 days Average collection period 19 days Debt ratio 71% Debt-to-equity ratio 2.5 Times interest earned (TIE) ratio 2.6 Fixed asset turnover ratio 1.4 c. Assume that there are 10,000 shares of Green Valley's stock outstanding and that some recently sold for $45 per share. • What is the firm's price/earnings ratio? What is its market/book ratio? (Hint: These ratios are discussed in the supplement to this chapter.)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_forwardI need helparrow_forward
- Solve this accounting questionarrow_forwardProvide solution for this questionarrow_forwardQuestion: Selected financial information Corporation is reproduced below: from Syntex 1. NOPAT turnover (average NOA equals ending NOA) is two. 2. NOPAT turnover margin equals 5%. 3. Leverage ratio (average NFO/average common equity) is 1.786m and the Spread is 4.4%. Required: a. Compute return on net operating assets (RNOA). b. Compute return on common equity using its three major components. c. Analyze the disaggregation of return on common equity. What is the "leverage advantage (in percent return) accruing to common equity"?arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- Survey of Accounting (Accounting I)AccountingISBN:9781305961883Author:Carl WarrenPublisher:Cengage Learning
Survey of Accounting (Accounting I)
Accounting
ISBN:9781305961883
Author:Carl Warren
Publisher:Cengage Learning