MGFD10 Investments Winter 2023 Assignment #4
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
Assignment #4
MGFD10 Investments
Winter 2023
Due on March 15, 2023
In this assignment, we value a hypothetical firm using a spreadsheet. Go to Quercus for the sample spreadsheet, ps4_questions.xlsx. The sheet contains the excerpt from the historical income statements and balance sheet data from the fiscal year 2017 to 2021.
In this Assignment (‘only’in this Assignment), you can just submit the spreadsheet without separate WORD/PDF write-ups. Also, you should stick with Excel for this – there is little point in using Python or R for this type of question.
1. First, we calculate the free cash flow to the firm (FCFF) using historical data in Sheet‘DCF’, Cell E29 to I29. Report FCFF for each year from 2017 to 2021.
2. Next, we conduct a two-step valuation procedure. In particular, we project cash flows for the first five years. Then, we calculate the terminal value at the end of year five.
We make three cases for sales growth from 2022 to 2026. In sheet Revenue, input the following sales growth assumptions for Products A and B and calculate the resulting sales values in rows 17, 19, 26, 28, 35, and 37.
|
Case |
Base |
Bull |
Bear |
|
Product A |
3% |
4% |
2% |
|
Product B |
0% |
0.5% |
-0.5% |
Report your Consolidated Sales in 2022 under the Base scenario.
3. Go to sheet Operation. Calculate values in operating items forecast using assumptions in sheet Inputs.
• COGS, Operating Expenses, Depreciation & Amortization, CAPEX, and Net Working Capital are a fraction of sales in the year.
• Taxes are a fraction of EBIT.
• Let’s assume that the firm has a target ratio of debt to EBITDA. Thus, from the given year’s EBITDA, you can multiply the ratio in Inputs and back out Total Debt at the end of the year. Calculate Net Borrowing by taking the difference between Total Debt this year and the previous year.
• Total Interest Expense is a fraction of Total Debt at the year-end.
Report your NOPAT (EBIT-Taxes) and Net Borrowing in 2022 in the Base case.
4. Now, you are ready to calculate FCFF and FCFE. Go to sheet DCF and fill in cash flow information from Operation. In this sheet,
FCFF = NOPAT + D&A − Changes in WC − CAPEX.
Report your 2022 FCFF under the Base scenario.
5. Now, we consider FCFE, the cash flow to shareholders. To do this, we adjust FCFF using payout to debt holders. Use the formula below and calculate FCFE from 2022 to 2026. Report your forecast for FCFE in the Base case.
FCFE = FCFF − After Tax Interest Expense + Net Borrowing.
hint: Do not forget about the tax adjustment for interest expense! If you have more debt, you can save on taxes.
6. In this step, you discount the future FCFE using the discount rate in D41. Calculate the present value of cash flows and report them in row 34.
7. Now we compute the Terminal Value in 2026. Assume the constant cash flow growth rate of 2% and discount rate of 10.73%, and use the Gordon Growth Formula to obtain the terminal value (in 2026) and the present value of terminal value as of 2021.
8. There is an alternative method called the Exit Multiple approaches. This value (assuming the price-EBITDA ratio of a similar firm applies) is already calculated for you. In this study, we take the average between the two approaches to arrive at the present value of the terminal value (Cell D57).
9. Finally, add the cash and cash equivalent in the balance sheet to obtain the fair value of equity (Cell D65). You are done! Report the model-implied share price for the three cases (Base, Bull, and Bear).
2023-03-17