Question 1
The “Question 1” tab of the spreadsheet contains the most recent (“Year 0”) income statement for
Flitwick Corporation. You’ll need to forecast the next 5 years of the income statement (including the
Free Cash Flow) using the following assumptions:
•
Sales will grow by 13% each year.
•
COGS and SG&A will be forecast using the percent of sales technique.
•
Depreciation will grow by 9% each year.
•
Interest expense will grow by 11% each year.
•
Flitwick’s tax rate is 31%.
•
CapEx and Change in NWC will grow by 12% each year.
•
At the end of year 5, you will sell the company for $40,000,000 (note: this number should be
added to the year 5 free cash flow).
•
The appropriate discount rate is 16%.
Once you’ve estimated the free cash flows, find the value of the firm (i.e. the present value of the free
cash flows.)
Question 2
You’re considering purchasing a house. You’ve estimated that the maximum monthly payment you can
afford is $3,500. Your local bank is currently charging 0.4% monthly interest for a 30-year (360 month)
mortgage. What is the most you can borrow?
(Hint: set up the “Question 2” tab as we did in the mortgage example in the lecture video. Then use
solver/goalseek to find the mortgage amount that makes the final ending balance equal to zero).
Question 3
You’re trying to estimate the value of a stock. They don’t currently pay a dividend, but you think they
will begin to pay a dividend of $0.25 per share in 6 years. You predict that the dividends will double
every year for the first 5 dividend payments, then afterwards will grow at a stable 4% growth rate forever.
The appropriate discount rate for this stock is 12%. What is the value of a share?