MGMT 648 Applied Finance – Week 2 Spreadsheet Assignment

Please display your primary formulas using FORMULATEXT. Some of these have been pre-populated in the homework template. You should upload a single Excel spreadsheet. Please do not insert or delete rows/columns and be sure answers are in the indicated shaded regions. Please be sure to include your name and student ID on the first sheet.

Part A: Free Cash Flow and Valuation

Please calculate free cash flows to the firm using the forecasted financial statements provided. Calculate the terminal value using the growing perpetuity formula. What is the enterprise value of the firm’s operations?

Part B: Pro-forma with Debt Plug

Forecast the income statement and balance sheet for the next five periods. The spreadsheet contains assumptions about how you expect key financial ratios to behave over this period. Please use debt as the plug in your pro-forma.

Notes and Assumptions

The firm does not pay dividends.
Interest income and expense are calculated based on average balance sheet items and will maintain the same rates.
Depreciation expense is based on average gross fixed assets and this rate will continue.
The firm does not intend to issue or repurchase equity.
The firm plans to use retire debt using excess cash or issue new debt to satisfy any cash needs.
Assume the firm will achieve its average sales growth over the last 4 periods going forward.
Margins, working capital (including cash), and net fixed assets will continue at their current year ratio to sales.
The firm’s tax rate is 21%.

Solution

Part A: Free Cash Flow and Valuation

Step 1: Net Income

The net income values for each year are given directly from the income statement, we take this using formula in excel. In this case you should have the following in their respective years;

Net Income(t=1)=187.88
Net Income(t=2)=250.56
Net Income(t=3)=263.29
Net Income(t=4)=325.23

Step 2: Interest Expense

The interest expense values are also provided in the income statement and remain constant across all years:

Interest Expense(t=1)=−125
Interest Expense(t=2)=−125
Interest Expense(t=3)=−125
Interest Expense(t=4)=−125

Step 3: -Tax rate * Interest Expense

Calculate this by multiplying the tax rate by the interest expense in the respective year. This is the tax shield from using debt to finance operations.