Spreadsheet Assignment
Complete the following textbook questions:
Start with the partial model in the file Ch21 P08 Build a Model.xlsx on the textbook’s Web site. Kasperov Corporation has an unlevered cost of equity of 12% and is taxed at a 40% rate. The 4-year forecasts of free cash flow and interest expenses are shown in the following table; free cash flow and interest expenses are expected to grow at a 5% rate after Year 4. Using the compressed APV model, answer the following questions.
INPUTS (In Millions) Projected
Year: 1 2 3 4
Free cash flow $200 $280 $320 $340
Interest expense $100 $120 $120 $140
a. Calculate the estimated horizon value of unlevered operations at Year 4 (i.e., immediately after the Year-4 free cash flow).
b. Calculate the current value of unlevered operations.
Current value of unlevered operations = Estimated horizon value of unlevered operations at Year 4 x (1
c. Calculate the estimated horizon value of the tax shield at Year 4 (i.e., immediately after the Year-4 free cash flow).
d. Calculate the current value of the tax shield.
e. Calculate the current total value.