- 11 - Clear Calibri (Body) B IUB A E Merge Paste C lest depreciation 8 9 Other operating expenses Cost of strateginitat 10K Notes payable 8% Bonds payable 12 Line of credit 15 16 Taxes Net Income before pref div 30% Pre-tax earnings 17 Perred divide 10Preferred stock 19 Regular common dvidends 20 Special dividends 40% Payout ratio Accounts recevable 200.0 600.0 600.0 SARARAAN RASA ST investments 1400.0 400.0 Notes payable 1000 No change Change at year end Total surrenti Long term bond 300.0 100.0 3000.0 No change 40 1 Common stock Retained earnings No change Previous change RE 43 Total es and out 45 Check it and etuity Normal View Ready Five year Planning Horn Inputs Sales growth COGS Exel Depreciation Sale Depec NFA Other per Esp Sales SO Acc puy Sales Accruals Sales Cost of regie initiatives Operating assets and operating i ts Depreciation Otherwing expenses NOPAT Free cash flows Net w orking capital Totalt peting capital Target WC OP R NOPAT Sales CR Ratio Tocal op capital Sales ROIC-NOPAT/Total op capital Asume Year is the first year of constant growth Vale of pers on of years Vile of per end of year Print value of the value Present value of FCF. yes 1-5 Value of operations and of year Estimated intrinses Vale of per price, end of year end of year Estimated total intrinsie van Less all dels Estimated intrinsic va of e quity Estimated intrinsic stock price, at end of year The attached spreadsheet gives the assumptions necessary to forecast the operations for a corporation. The spreadsheet includes: 1. The income statement and balance sheet for year 0 2. Explanation of the input used to forecast year 1 3. Explanation of the input used to forecast operations years 1-5 and to forecast operations once constant growth is attained in year 5. Complete the forecasted income statement and balance sheet for year 0. Complete all yellow cells in columns E and F. Do this in two steps first without any financing changes then with the necessary changes in the line of credit or payment of a special dividend to meet the firm's financing needs for year 1. Assume any changes to the line of credit or the payment of the special dividend happens at the end of the year so there is no interest payment on the line of credit. Complete the five-year forecast of operations. Complete all yellow cells in columns through O. Find the horizon value at the end of year 5 which is the beginning of year 6. Use the horizon value and forecast of operations for years 1-5 to find the value of operations and the estimated intrinsic stock price at the end of year 0. I left out the line for Cash in the Forecast of Operations. For Line 22, columns L-P, make that cash plus accounts receivable. Add the percentages for cash and A/R together, then forecast the amount for years 1-5. 10:47 AM 0 7 17% .. AT&T Notes Done PELLULU Answer everything in yellow and paper to qwertyuiop as a f g h i ko zxcvbnm 123 space return - 11 - Clear Calibri (Body) B IUB A E Merge Paste C lest depreciation 8 9 Other operating expenses Cost of strateginitat 10K Notes payable 8% Bonds payable 12 Line of credit 15 16 Taxes Net Income before pref div 30% Pre-tax earnings 17 Perred divide 10Preferred stock 19 Regular common dvidends 20 Special dividends 40% Payout ratio Accounts recevable 200.0 600.0 600.0 SARARAAN RASA ST investments 1400.0 400.0 Notes payable 1000 No change Change at year end Total surrenti Long term bond 300.0 100.0 3000.0 No change 40 1 Common stock Retained earnings No change Previous change RE 43 Total es and out 45 Check it and etuity Normal View Ready Five year Planning Horn Inputs Sales growth COGS Exel Depreciation Sale Depec NFA Other per Esp Sales SO Acc puy Sales Accruals Sales Cost of regie initiatives Operating assets and operating i ts Depreciation Otherwing expenses NOPAT Free cash flows Net w orking capital Totalt peting capital Target WC OP R NOPAT Sales CR Ratio Tocal op capital Sales ROIC-NOPAT/Total op capital Asume Year is the first year of constant growth Vale of pers on of years Vile of per end of year Print value of the value Present value of FCF. yes 1-5 Value of operations and of year Estimated intrinses Vale of per price, end of year end of year Estimated total intrinsie van Less all dels Estimated intrinsic va of e quity Estimated intrinsic stock price, at end of year The attached spreadsheet gives the assumptions necessary to forecast the operations for a corporation. The spreadsheet includes: 1. The income statement and balance sheet for year 0 2. Explanation of the input used to forecast year 1 3. Explanation of the input used to forecast operations years 1-5 and to forecast operations once constant growth is attained in year 5. Complete the forecasted income statement and balance sheet for year 0. Complete all yellow cells in columns E and F. Do this in two steps first without any financing changes then with the necessary changes in the line of credit or payment of a special dividend to meet the firm's financing needs for year 1. Assume any changes to the line of credit or the payment of the special dividend happens at the end of the year so there is no interest payment on the line of credit. Complete the five-year forecast of operations. Complete all yellow cells in columns through O. Find the horizon value at the end of year 5 which is the beginning of year 6. Use the horizon value and forecast of operations for years 1-5 to find the value of operations and the estimated intrinsic stock price at the end of year 0. I left out the line for Cash in the Forecast of Operations. For Line 22, columns L-P, make that cash plus accounts receivable. Add the percentages for cash and A/R together, then forecast the amount for years 1-5. 10:47 AM 0 7 17% .. AT&T Notes Done PELLULU Answer everything in yellow and paper to qwertyuiop as a f g h i ko zxcvbnm 123 space return