O h Autove . 12 File Home Insert Draw Assignment 5 BUS FINANCE WEEK 6 Formulas Data Review View Page Layout Help maan Aerial 10 A == Curreng Copy Format Painter B Clipboard A A Wrap Test Merge & Center - O E $ % 9 a *-9 conditional For Formatting 16 Income Statements: 45 December 31, in thousands of dollars) 2017 Preliminary forecast doesn't include special dividend or LOC) $482,459 $410.090 48 Sales 2017 input ratios Forecasting basis $456,150 100.00% Growth 106.0% $386,878 5.0% % of sales 85.0% $14585 80 % of red assets sets 8.0% $53.709 $11.880 Interest rate average debt during year 19 Expenses (excluding depr. & amort) 50 Depreciation and Amortization 2017 Final forecast (includes special dividend or LOC) $482,459 $410.090 $15.439 $56,930) $13,200 $15.439 $66,930 52 Interest expense on long term debt 53 Interest expense onine of credit 54 EBT 55 Taxes (406) 56 Net Income $13,200 $16,731 $25,097 $43,730 $17,492 $26,238 $43,730 $17.492 $26 238 Sa Common dividende regular dividends) Special dividends 50 Addition to retained earnings $12,554 Growth Zero in preliminar 8.00% $13,558 $12,543 $13,558 so 6 Balance Sheets December 31, in thousands of dollars) 2016 Historical ratios 2017 Preliminary forecast doesn't 66 Assets Forecasting basis 2017 input ratios nclude bei dividend or LOC) 2017 Final forecast includes special dividend or LOC) ts Receivable $18,206 $100,133 220 % of sales % of sales 22.00 12 Totalasses Aumento 5163.854 $112.000 $345.914 40.0% $19,298 $100,141 $48.246 5173,685 $192.48. $366,669 40.00% $100, 141 $48.246 $173,685 $ 192,984 $366669 Type here to search AutoSave OD 12 A File Home Insert DrawPage Layout A XO Ariel 10 Copy - Format Painter B1 ssignment 5 BUS FINANCE WEEK 6. Search Formulas Data Review View Help AA == Wap Text Currency . Merge & Center - $ % & Conditional Forma Formatting - Table 59 Special dividends 80 Addition to retained earnings Zero in preliminary forecast $12,680 62 Balance Sheets December 31, in thousands of dollars) 2015 Historical 2017 Preliminary forecast doesn't include special dividend or LOC) ratios 2017 Input ratios Forecasting basis 2017 Final forecast (includes special dividend or LOC) 4.00% 22.0% 100% $10.200 $100,133 $45 515 $163,054 $12.000 $345 914 % of sales % of sales % of sales 70 Total current assets 22.00% 10.00% $19.2989 $106,1419 $48.246 $173.685 $192.984 $366,669 6 $19,298 $106,141 $43.246 $173,685 $192,984 $385.869 % of sales 40.00% T ande Accruals IT Line of credit Total current e s Long term det 80 Total st Common stock 29 R ed Earnings 8 Totalcommon equity $31,861 7.0% % of sales of sales 7.00% $27309 80% of sales Zero in preliminary forecast $59.170 $120,000 Previous $179,170 $60.000 Previous $100.745 Previous. Addition to retained earnings $33,772 $28.948 so $62.720 $120,000 $182,720 $60.000 $119,425 $179,425 $33,7721 $28,048 $4,524 $67244 $120.000 $187,244 $60.000 $119.4251 $179,425 $366.669 3 Identity Financing Deficit or Surplus Increase in spontaneous abies (ccounts payable and accruals) Increase in long-term bonds, preferred stock and common stock Type here to search Save L Data Review View Formulas Insert Draw Page Layout Currency Home 2x E Ariel BIU - 10 - AA E - A E 2 Wrap Text Merge & Center - $ 68-8 % 9 Number Copy Format Painter Cipboard - Font $0 Line of credit Total current liabilities Longterm debt Total liabilities Common stock Retained Earnings Total common equity Total liabilities and equity DE $0 Zero in preliminary forecast $59,170 $120,000 Previous $179.170 $60,000 Previous $106,745 Previous + Addition to retained earnings $166,745 $345,914 $62,720 $120,000 $182,720 $60,000 $119,425 $179,425 $362,145 5 Identify Financing Deficit or Surplus Increase in spontaneous liabilities (accounts payable and accruals) Increase in long-term bonds, preferred stock and common stock Net Income in preliminary forecast) minus regular common dividends Increase in financing 3 - Increase in total assets 4 Amount of financing deficit or surplus: 6 7 deficit in financing (negative), show the amount for the line of credit surplus in financing positive), show the amount of the special dividend What are the forecasted levels of the line of credit and special dividends? 1 Required ine of credit 12 Special dividends $4,525 Note: we copied values from H99:H1 4. Now that the growth in sales is only 3% (do this by changing the growth rate in Cell G51). What are the 5 forecasted levels of line of credit and special dividends? 50 of Required ine of credit Assignment 6 $0 Note: we copied values from H99:H10 Type here to search 5 O h Autove . 12 File Home Insert Draw Assignment 5 BUS FINANCE WEEK 6 Formulas Data Review View Page Layout Help maan Aerial 10 A == Curreng Copy Format Painter B Clipboard A A Wrap Test Merge & Center - O E $ % 9 a *-9 conditional For Formatting 16 Income Statements: 45 December 31, in thousands of dollars) 2017 Preliminary forecast doesn't include special dividend or LOC) $482,459 $410.090 48 Sales 2017 input ratios Forecasting basis $456,150 100.00% Growth 106.0% $386,878 5.0% % of sales 85.0% $14585 80 % of red assets sets 8.0% $53.709 $11.880 Interest rate average debt during year 19 Expenses (excluding depr. & amort) 50 Depreciation and Amortization 2017 Final forecast (includes special dividend or LOC) $482,459 $410.090 $15.439 $56,930) $13,200 $15.439 $66,930 52 Interest expense on long term debt 53 Interest expense onine of credit 54 EBT 55 Taxes (406) 56 Net Income $13,200 $16,731 $25,097 $43,730 $17,492 $26,238 $43,730 $17.492 $26 238 Sa Common dividende regular dividends) Special dividends 50 Addition to retained earnings $12,554 Growth Zero in preliminar 8.00% $13,558 $12,543 $13,558 so 6 Balance Sheets December 31, in thousands of dollars) 2016 Historical ratios 2017 Preliminary forecast doesn't 66 Assets Forecasting basis 2017 input ratios nclude bei dividend or LOC) 2017 Final forecast includes special dividend or LOC) ts Receivable $18,206 $100,133 220 % of sales % of sales 22.00 12 Totalasses Aumento 5163.854 $112.000 $345.914 40.0% $19,298 $100,141 $48.246 5173,685 $192.48. $366,669 40.00% $100, 141 $48.246 $173,685 $ 192,984 $366669 Type here to search AutoSave OD 12 A File Home Insert DrawPage Layout A XO Ariel 10 Copy - Format Painter B1 ssignment 5 BUS FINANCE WEEK 6. Search Formulas Data Review View Help AA == Wap Text Currency . Merge & Center - $ % & Conditional Forma Formatting - Table 59 Special dividends 80 Addition to retained earnings Zero in preliminary forecast $12,680 62 Balance Sheets December 31, in thousands of dollars) 2015 Historical 2017 Preliminary forecast doesn't include special dividend or LOC) ratios 2017 Input ratios Forecasting basis 2017 Final forecast (includes special dividend or LOC) 4.00% 22.0% 100% $10.200 $100,133 $45 515 $163,054 $12.000 $345 914 % of sales % of sales % of sales 70 Total current assets 22.00% 10.00% $19.2989 $106,1419 $48.246 $173.685 $192.984 $366,669 6 $19,298 $106,141 $43.246 $173,685 $192,984 $385.869 % of sales 40.00% T ande Accruals IT Line of credit Total current e s Long term det 80 Total st Common stock 29 R ed Earnings 8 Totalcommon equity $31,861 7.0% % of sales of sales 7.00% $27309 80% of sales Zero in preliminary forecast $59.170 $120,000 Previous $179,170 $60.000 Previous $100.745 Previous. Addition to retained earnings $33,772 $28.948 so $62.720 $120,000 $182,720 $60.000 $119,425 $179,425 $33,7721 $28,048 $4,524 $67244 $120.000 $187,244 $60.000 $119.4251 $179,425 $366.669 3 Identity Financing Deficit or Surplus Increase in spontaneous abies (ccounts payable and accruals) Increase in long-term bonds, preferred stock and common stock Type here to search Save L Data Review View Formulas Insert Draw Page Layout Currency Home 2x E Ariel BIU - 10 - AA E - A E 2 Wrap Text Merge & Center - $ 68-8 % 9 Number Copy Format Painter Cipboard - Font $0 Line of credit Total current liabilities Longterm debt Total liabilities Common stock Retained Earnings Total common equity Total liabilities and equity DE $0 Zero in preliminary forecast $59,170 $120,000 Previous $179.170 $60,000 Previous $106,745 Previous + Addition to retained earnings $166,745 $345,914 $62,720 $120,000 $182,720 $60,000 $119,425 $179,425 $362,145 5 Identify Financing Deficit or Surplus Increase in spontaneous liabilities (accounts payable and accruals) Increase in long-term bonds, preferred stock and common stock Net Income in preliminary forecast) minus regular common dividends Increase in financing 3 - Increase in total assets 4 Amount of financing deficit or surplus: 6 7 deficit in financing (negative), show the amount for the line of credit surplus in financing positive), show the amount of the special dividend What are the forecasted levels of the line of credit and special dividends? 1 Required ine of credit 12 Special dividends $4,525 Note: we copied values from H99:H1 4. Now that the growth in sales is only 3% (do this by changing the growth rate in Cell G51). What are the 5 forecasted levels of line of credit and special dividends? 50 of Required ine of credit Assignment 6 $0 Note: we copied values from H99:H10 Type here to search 5