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...

1 answer below »
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 afterward 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?


Answered 1 days AfterAug 29, 2022

Answer To: The “Question 1” tab of the spreadsheet contains the most recent (“Year 0”) income statement for...

Rinki answered on Aug 31 2022
56 Votes
Question 1
    Flitwick Corporation
    PARTICULAR    Year 0    Year 1    Year 2    Year 3    Year 4    Year 5
    Sales    $ 51,000,000    $ 57,630,000    $ 65,121,900    $ 73,587,747    $ 83,154,154    $ 93,964,194
    Cost of G
oods Sold    $ 29,500,000    $ 33,335,000    $ 37,668,550    $ 42,565,461    $ 48,098,971    $ 54,351,838
    Gross Profit    $ 21,500,000    $ 24,295,000    $ 27,453,350    $ 31,022,285    $ 35,055,183    $ 39,612,356
    Selling, General and Administrative Costs    $ 7,800,000    $ 8,814,000    $ 9,959,820    $ 11,254,597    $ 12,717,694    $ 14,370,994
    Depreciation    $ 3,300,000    $ 3,597,000    $ 3,920,730    $ 4,273,596    $ 4,658,219    $ 5,077,459
    Earnings Before Interest and Tax (EBIT)    $ 10,400,000    $ 11,884,000    $ 13,572,800    $ 15,494,093    $ 17,679,269    $ 20,163,903
    Interest Expense    $ 2,100,000    $ 2,331,000    $ 2,587,410    $ 2,872,025    $ 3,187,948    $ 3,538,622
    Earnings Before Tax    $ 8,300,000    $ 9,553,000    $ 10,985,390    $ 12,622,068    $ 14,491,321    $ 16,625,281
    Taxes (31%)    $ 2,573,000    $ 2,961,430    $ 3,405,471    $ 3,912,841    $ 4,492,310    $ 5,153,837
    Net Income    $ 5,727,000    $ 6,591,570    $ 7,579,919    $ 8,709,227    $ 9,999,012    $ 11,471,444
    Operating Cash Flow
    CapEx    $ 3,250,000    $ 3,640,000    $ 4,076,800    $ 4,566,016    $ 5,113,938    $ 5,727,610
    Sale of Company                        $ 40,000,000
    Change in NWC    $ 2,200,000    $ 2,464,000    $ 2,759,680    $ 3,090,842    $ 3,461,743    $ 3,877,152
    Free Cash Flow    $ 3,577,000    $ 4,084,570    $ 4,664,169    $ 5,325,965    $ 6,081,550    $ 46,944,141
    Present Value @12%
    Present Value Factor    0.893    0.893    0.893    0.893    0.893    0.893
    Present Value     $ 3,193,750    $ 3,646,937    $ 4,164,437    $ 4,755,326    $ 5,429,956    $ 41,914,411
    Value of firm     $ 63,104,817
Question 2
            Mortgage Amount         $667,092.00    (using solver)
            Monthly Payment        $3,500.00
                                Calculation using formula
        Month    Beginning Balance    Interest @0.4% PM    Payment    Ending Balance
        1    $667,092    $2,668    $3,500    $666,260        P    =    EMI*...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here