Using the balance sheet, income statement, and the three variables provided in green (sales growth rate, discount rate or "W.A.C.C., and long-term growth rate), complete the following:

1. Unlevered free cash flows of the firm ("FCFF") for the first five years
2. Present values of the FCFF for the first five years
3. Present value of the terminal value
4. Firm's overall Present Value as of today

Please show your work (Excel screenshot of calculations is accepted)

#VALUE! 0 0 REMEMBER: APV METHOD USES UNLEVERED CASH FLOWS FOR BASE CASE + PV OF I.T.S. Sales Growth (YOU MUST USE YOUR RATE) 3.00% Firm Value with EOP cash flows at Year 0: Current Assets/ Sales 15.00% TOTAL PV(ITS) at Year with Miller Theory: Accounts Payable / Sales 8.00% = Adjusted Present Value at Year 0: Net Fixed Assets / Sales 77.00% Cost of Goods Sold / Sales 50.00%) - Debt on EOP at Year : Depreciation Exp / Sales 10.00% = Present Value of the Equity at Year 0: Interest rate on debt 10.00% Interest earned on cash balances 8.00% Debt Policy Corporate Tax Rate 35.00% Debt/Total Assets: 32.00% Dividend Payout Ratio 80.00% 0 0 Year o Year 1 Year 2 Year 3 Year 4 Year 5 Year Income Statement Sales Costs of Goods Sold Depreciation Expense Interest Expense on Debt & Revolver Interest Income on Cash & Mkt Sec Profit before tax Taxes Profit after tax Dividends Retained Earnings 10,000,000 10,300,000 10,609,000 10,927,270 11,255,088 11,592,741 (5,000,000) (5,150,000) (5,304,500) (5,463,635) (5,627,544) (5,796,370) (1,000,000) (1,030,000) (1,060,900) (1,092,727) (1,125,509) (1,159,274) (320,000) (332,407) (357,664) (383,819) (410,901) (438,939) 64,000 83,978 124,708 167,013 210,942 256,548 3,744,000 3,871,571 4,010,645 4,154,102 4,302,076 4,454,705 (1,310,400) (1,355,050) (1,403,726) (1,453,936) (1,505,727) (1,559,147) 2,433,600 2,516,521 2,606,919 2,700,166 2,796,350 2,895,558 (1,946,880) (2,013,217) (2,085,535) (2,160,133) (2,237,080) (2,316,447) 486,720 503,304 521,384 540,033 559,270 579,112 800,000 1,500,000 1,299,447 1,545,000 1,818,261 1,591,350 2,357,063 1,639,091 2,916,493 1,688,263 3,497,211 1,738,911 Balance Sheet Cash Current Assets (A/R & Inventory) Other Assets Fixed Assets: At Cost Accumulated Depreciation Net Fixed Assets Total Assets 10,700,000 11,961,000 13,259,830 14,597,625 15,975,554 17,394,820 -3,000,000 4,030,000 -5,090,900 -6,183,627 -7,309,136 -8,468,410 7,700,000 7,931,000 8,168,930 8,413,998 8,666,418 8,926,410 10,000,000 10,775,447 11,578,541 12,410,151 13,271,174 14,162,532 Current Liabilities (Revolver & A/P) Revolving Line of Credit ("Revolver") Accounts Payable ("A/P") Debt Stock Accumulated Retained Earnings Total Liabilities and Equity (Internal Check A = L +E?) 0 0 0 0 0 800,000 824,000 848,720 874,182 900,407 927,419 3,200,000 3,448,143 3,705,133 3,971,248 4,246,776 4,532,010 4,500,000 4,500,000 4,500,000 4,500,000 4,500,000 4,500,000 1,500,000 2,003,304 2,524,688 3,064,721 3,623,991 4,203,103 10,000,000 10,775,447 11,578,541 12,410,151 13,271,174 14,162,532 0 0 0 0 0 0 Free cash flow calculation (YOU MUST INSERT YOUR CELLS FOR YEARS 1, 2, 3, 4, & 5. DO NOT INSERT FOR YEAR O.) Year Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Profit after tax Add: Depreciation Expense Less: Increase in Current Assets Add: Increase in Current Liabilities (Revolver +A/P) Less: Capital Expenditures on Fixed Assets Add: After-tax Interest on Debt Less: After-tax Interest on Cash Free Cash Flow to the Firm (FCFF) 0 0 0 0 0 Derived growth rate of FCFFs Years 1-5: #DIV/0! (NOTE TO STUDENT: YOU MUST HAVE FCFF VALUES; COMPLETE THE CELLS!) Valuing the Firm Unlevered Wgt avg cost of capital, WACC Long-term growth rate of FCFFs, g 9.00%I