Question

Subject 3 (30%) An equity research analyst of “CS Asset Management" issued a report on the internet company "ABC". She implemented a Discounted Cash Flow (DCF) model in order to derive her target price for "ABC". The following Table presents a summary of some of the analyst's key forecasts. Table: Forecasts for ABC (CS) 2024E Forecasts of Financial Ratios 2020E 2021 Sales Growth Rate 54.1% 30.2% EBIT Margin (EBIT / Sales) 46.3% 48.9% Effective Tax Rate 29.9% 27.5% Depreciation & Amortization / 11.6% 11.3% Sales Increase in Net Working Capital / 3.2% 2.6% Sales | Capital Expenditures / Sales | 17.2% 14.7% 2022 20.5% 49.9% 25.0% 11.0% 2023 15.2% 50.1% 23.0% 11.0% 51.0% 23.0% 11.0% 2.1% 2.2% 2.2% 12.6% 12.0% 12.0% The analyst used a weighted average cost of capital of 10% and assumed a perpetual annual free cash flow growth rate of 4% for the period beyond FY 2024. For FY 2019, the analyst expected that ABC will have a net cash position of €13,804 (cash and short-term investments greater than total debt obligations), sales equal to €15,896, and number of common shares outstanding equal to 2.733. All the amounts are expressed in millions Required: A. Estimate the Discounted Cash Flow (DCF)-based target price at the end of FY 2019. (20 points) B. ABC's stock price at the report issue date was €76.45. Please indicate the recommendation type (Buy, Hold or Sell) that is implied by the expected upside (downside) potential. Explain the rationale of your recommendation (150 words) (5 points) C. Perform a sensitivity analysis using two alternative scenarios for the terminal free cash flow growth rate (-/+ 0.5 percentage points from the base scenario) and another two alternative scenarios for the weighted average cost of capital (-/+ 0.5 percentage points from the base scenario), resulting in a matrix with a total of nine scenarios including the base scenario. (5 points)

Answer 1

SOLUTION:-

First step is to calculate free cash flows to firm (FCFF) for all the projected years by using all data given and calculate present value of FCFF for all these years as shown in the table below: LA H 2019 Actual 15896 vouw Sales Sales Growth EBIT EBIT Margin Effective Tax Rate (ETR) EBIT (1 - ETR) Depreciation & Amortization / Sales Depreciation & Amortization (D&A) Increase in Net Working Capital / Sales Increase in Net Working Capital Capital Expenditure / Sales Capital Expenditure FCFF (EBIT*(1-ETR)+D&A Increase in Working Capital - Capital Expenditure PV of FCFF 2020 Expected -C4°(1+D5) 0.541 -D4°D7 0.463 0.299 -D6*(1-DS) 0.116 =D4°D10 0.032 -D12D4 0.172 -D14 D4 2021 Expected =D4*(1+ES) 0.302 -E4%E7 0.489 0.275 -E6*(1-ES) 0.113 =E4°E10 0.026 -E12E4 0.147 -E14E4 2022 Expected -E4*(1+F5) 0.205 -F4°F7 0.499 0.25 -F6*(1-F8) 0.11 =F4°F10 0.021 -F12 F4 0.126 =F14°F4 2023 Expected =F4*(1+G5) 0.152 -G4G7 0.501 0.23 -G6*(1-G8) 0.11 G4°G10 0.022 -G12G4 0.12 -G14G4 2024 Expected G4*(1+H5) 0.073 -H4 H7 0.51 0.23 -H6*(1-HS) 0.11 =H4 H10 0.022 -H12 H4 0.12 =H14H4 13 14 -D9+D11 D13-D15 -E9+E11-E13 E15 -F9+F11 F13-F15 -G9+G11-G13 G15 =D16/(1+SCS20) (D2-SCS2) =E16/(1+SCS20) (E2-SCS2) =F16/(1+SCS20)(F2-SCS2) G16/(1+SCS20) (G2-SCS2) -H9 H11 H13-H15 H16/(1+SCS20) (H2-SCS2)

The result will be as below: C D E F G H 2019 2020 2021 2022 2023 2024 Actual Expected Expected Expected Expected Expected € 15,896.00 € 24,495.74 € 31,893.45 € 38,431.61 € 44,273.21 € 47,505.15 54.1% 30.2% 20.5% 15.2% 7.3% € 11,341.53 € 15,595.90 € 19,177.37 € 22,180.88 € 24,227.63 46.3% 48.9% 49.9% 50.1% 51.0% 29.9% 27.5% 25.0% 23.0% 23.0% € 7,950.41 € 11,307.02 € 14.383.03 € 17,079.28 € 18,655.27 11.6% 11.3% 11.0% 11.0% 11.0% € 2,841.51 € 3,603.96 € 4,227.48 € 4,870.05 € 5,225.57 3.2% 2.6% 2.1% 2.2% 2.2% € 783.86 € 829.23 € 807.06 € 974.01 € 1,045.11 17.2% 14.7% 12.6% 12.0% 12.0% € 4,213.27 € 4,688.34 € 4,842.38 € 5.312.79 € 5,700.62 10 Sales Sales Growth EBIT EBIT Margin Effective Tax Rate (ETR) EBIT * (1 - ETR) Depreciation & Amortization / Sales Depreciation & Amortization (D&A) Increase in Net Working Capital / Sales Increase in Net Working Capital Capital Expenditure / Sales Capital Expenditure FCFF (EBIT*(1-ETR)+D&A-Increase in Working Capital - Capital Expenditure PV of FCFF 11 12 13 14 15 € 5,794.78 € 9,393.42 € 12,961.06 € 15,662.53 € 17,135.11 17 € 5,267.99 € 7,763.16 € 9,737.84 € 10,697.72 € 10,639.55 10

Then next step is to calculate the terminal value of FCFF and discount it back to present value by using WACC. Terminal value is calculated by the formula = FCFF for the last projected year * (1 + terminal growth rate) / (WACC – terminal growth rate). This is done as shown in the table below: B Calculation of Target Price Weighted Average Cost of Capital (WACC) Terminal Growth Rate (TGR) 0.1 0.04 Terminal Value (TV=FCFF during 2024 *(1+ TGR)/(WACC - TGR)) Sum of PV of FCFF till 2024 PV of TV (TV/(1+ WACC) 45) =H16*(1+C21)/(C20-C21) =SUM(D17:H17) =C22/(1+C20 (SHS2-C2) 24 The result will be as follows: Calculation of Target Price Weighted Average Cost of Capital (WACC) 10% Terminal Growth Rate (TGR) 4% Terminal Value (TV = FCFF during 2024 * € 297,008.55 (1+ TGR)/(WACC - TGR)) Sum of PV of FCFF till 2024 € 44,106.25 PV of TV (TV/ (1 + WACC) ^5) € 184.418.94

Afterwards, we will calculate the equity value. However for this, we first need to calculate enterprise value which would be present value of FCFF for all the projected years + present value of terminal value. Then we will add net cash position in enterprise value to arrive at equity value. Target share price would be calculated by dividing equity value with common share outstanding. This will be arrived at as follows: A B Calculation of Target Price Weighted Average Cost of Capital (WACC) Terminal Growth Rate (TGR) 0.1 0.04 Terminal Value (TV=FCFF during 2024 * (1+ TGR)/(WACC - TGR) Sum of PV of FCFF till 2024 PV of TV (TV/(1 + WACC) 5) Enterprise value Add: Net cash Equity value Common shares outstanding Target price Stock price Upside / (downside) % =H16*(1+C21)(C20-C21) =SUM(D17:H17) =C22/(1+C20) (SHS2-C2) =SUM(C23:C24) 13804 =SUM(C25:C26) 2733 =C27/C28 76.45 =(C29-C30/C30 31

Thus, the result would be as follows and target price would be €88.67 4% Calculation of Target Price Weighted Average Cost of Capital (WACC) 10% Terminal Growth Rate (TGR) Terminal Value (TV=FCFF during 2024 * € 297,008.55 (1+TGR)/(WACC - TGR)) Sum of PV of FCFF till 2024 € 44,106.25 PV of TV (TV/(1+ WACC)^5) € 184,418.94 Enterprise value € 228,525.20 Add: Net cash € 13,804.00 Equity value € 242,329.20 Common shares outstanding 2,733 Target price € 88.67 Stock price € 76.45 Upside / (downside) % 16% ABC's target share price using DCF methodology is €88.67 whereas the stock price is €76.45. Hence the potential upside is 16% calculated as (88.67-76.45)/76.45. Hence one should buy ABC stock at €76.45 as the stock is worth €88.67 signifying availability of stock at a bargain price. Another way to look at it is from the principal of margin-of- safety. Since DCF valuation is highly sensitive to assumptions about growth projections, reinvestment needs (in terms of capital expenditure as well as working capital), terminal growth rate and cost of capital, a rational investor should seek comfort against the unmet assumptions. So even if some of these assumptions are wrong, still the current price offers some cushion (as reflected in difference between price and estimated value) against the dilution in value. Hence this discount reflects margin-of-safety. Hence investor should buy stock at the current price of €76.45.

Sensitivity analysis can be done by using 'Data Tables' functionality in Excel. The sensitivity analysis can be conducted as per the table shown below. E F G H I =C29 0.095 0.1 0.105 0.035 -TABLE(C21,C20) =TABLE(C21,C20) =TABLE(C21,C20) Terminal Growth Rate 0.04 -TABLE(C21,C20) =TABLE(C21.C20) =TABLE(C21.C20) 0.045 =TABLE(C21.C20) =TABLE(C21.C20) =TABLE(C21.C20) WACC The Final result will be as follows: € 88.67 9.5% 10.0% 10.5% Terminal Growth Rate 3.5% 4.0% 4.5% € 90.14 € 96.74 € 104.67 € 83.18 € 88.67 € 95.16 € 77.22 € 81.84 € 87.23 WACC Happy Learning!

THANK YOU, if any queries please leave your valuable comment on comment box.........

If possible then rate the answer as well