You are considering an investment into Company XYZ and need to determine the company's value and the
appropriate investment amount. You have been provided with historical financial statements for the past
three years in order to build a forecast model. Key assumptions to use include:
1. Future sales revenue is assumed to increase at 2.5% annually.
2. Gross margin for 2021E is assumed to be equal to the average gross margin % for 2019 and 2020,
but will decrease by 2.5% (i.e. 250 bps) each year thereafter.
3. SG&A expense is assumed to be a percentage of revenue for the forecast period. That percentage is
equal to the 2018-2020 average. SG&A Ratio = SG&A / Total Revenue
4. Depreciation expense is assumed to be a percentage of revenue for the forecast period. That
percentage is equal to the 2018-2020 average
5. The tax rate for the forecast period is assumed to be equal to the effective tax rate for 2018
6. Capital expenditures for any given year in the forecast period is assumed to be 3x the prior year's
depreciation expense. For example, 2021 capital expenditures is equal to 3x 2020 depreciation
expense.
7. No new debt or equity is assumed to be issued
8. For an individual: ETR = Total Tax ÷ Taxable Income. For a corporation: ETR = Total Tax ÷ Earnings
Before Taxes.
1
1. What is Gross Profit in 2028E using the assumptions listed above and on the Control Panel?
Review Later
$17,545
$27,780
,$30,704
$40,938
2
2. What is EBITDA in 2022E using the assumptions listed above and on the Control Panel?
Review Later
$14,625
$20,172
$18,911
$17,404
3
3. What is Cash Generated From Operating Activities in 2025E using the assumptions listed above and
on the Control Panel?
Review Later
$12,074
$12,379
$12,640
$13,523
4
4. What is the PP&E balance in 2030E using the assumptions listed above and on the Control Panel?
Review Later
$122,220
$158,604
$136,122
$148,922
5
, 5. What is the cash ratio in 2025E using the assumptions listed above and on the Control Panel?
Review Later
4.8x
4.9x
5.5x
5.4x
6
6. What is the margin impact ratio in 2026E using the assumptions listed above and on the Control
Panel?
Review Later
0.35x
0.28x
0.19x
0.14x
7
7.What is the cash turnover ratio in 2029E using the assumptions listed above and on the Control Panel? Is
it higher or lower than the same ratio in 2020?
Review Later
0.30x; lower
1.79x; lower
0.56x; lower
1.68x; higher
8
8
8.What is the risk-free rate?
, Review Later
3.0%
2.0%
3.5%
2.5%
9
9.Based on a discounted cash flow analysis and using the WACC as the discount rate, what is the implied
equity value of Company XYZ on January 1, 2021?
Review Later
$77,210
$79,292
$73,712
$79,946
Questions:
1. Based on a discounted cash flow analysis and using the WACC as the discount rate, what is the
implied equity value of Company XYZ on January 1, 2021? [the right answer is in green = $73,712; I got as
close to that number as possible]
Cash from which period would I have to use to calculate Implied Equity Value? Is it from the terminal year or
from 2021? Right now, it's using cash from 2021.
I calculated PV of FCF and PV of TV by using NPV formula --->
=NPV(WACC!N25,G29:P29)*(1+WACC!$N$25)^0.5 (for mid-year discounting); xNPV returns a similar result
but it doesn't match the right answer.
10