x H 6 BAM Ch 12 - Excel Tahoma INSERT PAGE LAYOUT FILE HOME FORMULAS DATA REVIEW VIEW X Cut ? T1 - OX A AUBREY HENSHAW A AutoSum - A Fill Sort & Find & Clear- Filter Select Editing HE Insert Delete Format Cells K L M N Tahoma - A A Wrap Text General Copy - Paste Format Painter BIU.E. A Merge & Center - $ -% Conditional Format as Formatting Table Styles Clipboard Font Alignment Numbe Styles Comment 65 - X fo A B E G H 34 Forecast the preliminary balance sheets and income statements in Column H. Don't include any line of credit or 35 special dividend in the preliminary forecast. 36 37 After completing the preliminary forecast of the balance sheets and income statement, go to the area below the 38 preliminary forecast and identify the financing deficit or surplus. Then use Excel's IF statements to specify the 39 amount of any new line of credit OR special dividend (you should not have a new line of credit AND a special 40 dividend, only one or the other). 41 42 After specifying the amounts of the special dividend or line of credit, create a second column (1) for the final forecast 43 next to the column for the preliminary forecast (H). In this final forecast, be sure to include the effect of the special 44 dividend or line of credit. 45 46 47 Income Statements: 2020 Preliminary 48 (December 31, in thousands of dollars) 2019 forecast (doesn't 2020 Final forecast 49 Historical 2020 Input include special (includes special 50 2019 ratios Forecasting basis ratios dividend or LOC) dividend or-LOC) 51 Sales $455,150 Growth 52 Expenses (excluding depr. & amort.) $386,878 % of sales 53 Depreciation and Amortization $14,565 % of fixed assets 54 EBIT $53,708 55 Interest expense on long-term debt $11,880 Interest rate x average debt during year 56 Interest expense on line of credit $0 57 EBT $41,828 58 Taxes (25%) $10,457 59 Net Income $25,097 60 61 Common dividends (regular dividends) $12,554 Growth 62 Special dividends Zero in preliminary forecast Parrard winrarastarfin Percent of Fornaxide Estimated es based on it mihrain The special dividend in the final forecast should be equal to the amount specified in the area below the balance sheets. Build a Model Cell G104 commented by Mike Ehrhardt + 90% 14 3:36 PM 2/20/2021 Adobe X Cut x H 6 Tahoma BAM Ch 12 - Excel ? T1 - OX FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW A AUBREY HENSHAW A AutoSum - A Tahoma - A A Wrap Text General HE Copy - Fill Paste BIU-E. A - % 8.8 Conditional Format as Cell Merge & Center Insert Delete Format Sort & Find & Format Painter Clear - Formatting Table Styles Filter - Select Clipboard Font Alignment Number Styles Cells Editing Comment 65 X fx A B c D E G H M N 62 Special dividends Zero in preliminary forecast Derow ule valane seers. 63 Addition to retained earnings $12.543 64 The addition to retained 65 Balance Sheets 2020 Preliminary earnings is equal to the net 66 (December 31, In thousands of dollars) 2019 forecast doesn't 2020 Final forecast Income minus any regular and 67 Historical 2020 Input include special (includes special special dividends. 68 2019 ratios Forecasting basis ratios dividend or LOC) dividend or LOC) 69 Assets: Special dividend in 70 Cash $18.206 Divine Arngring w RX % of sales preliminary forecast 71 Accounts Receivable $100,133 % of sales is zero. 72 Inventories $45,515 % of sales 73 Total current assets $163,854 Percent of forecasted Percent of forecasted 74 Fixed assets $364,120 % of sales Sales Percent of forecasted Percent of forecasted 75 Total assets $527,974 76 sales. Percent of forecasted 77 Liabilities and equity Percent of forecasted 78 Accounts payable $31,861 % of sales sales. 79 Accruals $27,309 % of sales line of credit is zero in preliminary 80 Line of credit In final forecast, the line of $0 Zero in preliminary forecast forecast. credit is equal to the 81 Total current ilabilities $59,170 amount specified in the 82 Long-term debt $120,000 Previous section below the balance 83 Total liabilities $179,170 sheets 84 Common stock $60,000 Previous The amount shown in retained earnings in the final 85 Retained Earnings $106,745 Previous + Addition to retained earnings forecast is equal to the amount from the previous 86 Total common equity $166,745 year's balance sheet (not the amount from the 87 Total liabilities and equity $345,914 preliminary forecast) plus the addition to retained earnings on the final forecasted income statement. 88 89 Identify Financing Deficit or Surplus 90 The amount shown in retained Aarninac In the nreliminary Build a Model + Cell G104 commented by Mike Ehrhardt 0 - + 90% 3:37 PM Adobe 2/20/2021 BE 14 )1 1 X Cut x H 6 Tahoma BAM Ch 12 - Excel ? T - SX FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW A AUBREY HENSHAW A AutoSum - A Tahoma - A A Wrap Text General AY Copy - HE Fill Paste BIU - A A- Merge & Center - % 8.8 Conditional Format as Cell Insert Delete Format Format Painter Sort & Find & Formatting Table Styles Clear Filter - Select Clipboard Font Alignment Number Styles Cells Editing Comment 65 - X fx B D E F G H K L 1 M N 86 Total common equity $166,745 year's balance sheet (not the amount from the 87 Total liabilities and equity $345,914 preliminary forecast) plus the addition to retained 88 earnings on the final forecasted income statement. 89 Identify Financing Deficit or Surplus 90 The amount shown in retained 91 Increase in spontaneous liabilities (accounts payable and accruals) earnings in the preliminary forecast is equal to the amount 92 + Increase in long-term bonds, preferred stock and common stock from the previous year's balance 93 + Net income (in preliminary forecast) minus regular common dividends sheet plus the addition to retained 94 Increase in financing earnings on the preliminary 95 forecasted Income statement. 96 - Increase in total assets 97 Amount of financing deficit or surplus: 98 In final forecast, the line of 99 If deficit in financing (negative), show the amount for the line of credit credit is equal to the 100 If surplus in financing (positive), show the amount of the special dividend amount specified in the 101 section below the balance 102 a. What are the forecasted levels of the line of credit and special dividends? Isheets 103 104 Required ine of credit Note: we copied values from H99:H100) when sales growth in G51 = 8%. 105 Special dividends 106 107 b. Now assume that the growth in sales is only 3% (do this by changing the growth rate in Cell G51). What are the 108 forecasted levels of line of credit and special dividends? 109 110 Required ine of credit Note: We copied values from H99:H100) when sales growth in G51 = 3%. 111 Special dividends 112 113 114 Build a Model + Cell G104 commented by Mike Ehrhardt + 90% 14 3:37 PM Adobe 2/20/2021 BE | | x H 6 BAM Ch 12 - Excel Tahoma INSERT PAGE LAYOUT FILE HOME FORMULAS DATA REVIEW VIEW X Cut ? T1 - OX A AUBREY HENSHAW A AutoSum - A Fill Sort & Find & Clear- Filter Select Editing HE Insert Delete Format Cells K L M N Tahoma - A A Wrap Text General Copy - Paste Format Painter BIU.E. A Merge & Center - $ -% Conditional Format as Formatting Table Styles Clipboard Font Alignment Numbe Styles Comment 65 - X fo A B E G H 34 Forecast the preliminary balance sheets and income statements in Column H. Don't include any line of credit or 35 special dividend in the preliminary forecast. 36 37 After completing the preliminary forecast of the balance sheets and income statement, go to the area below the 38 preliminary forecast and identify the financing deficit or surplus. Then use Excel's IF statements to specify the 39 amount of any new line of credit OR special dividend (you should not have a new line of credit AND a special 40 dividend, only one or the other). 41 42 After specifying the amounts of the special dividend or line of credit, create a second column (1) for the final forecast 43 next to the column for the preliminary forecast (H). In this final forecast, be sure to include the effect of the special 44 dividend or line of credit. 45 46 47 Income Statements: 2020 Preliminary 48 (December 31, in thousands of dollars) 2019 forecast (doesn't 2020 Final forecast 49 Historical 2020 Input include special (includes special 50 2019 ratios Forecasting basis ratios dividend or LOC) dividend or-LOC) 51 Sales $455,150 Growth 52 Expenses (excluding depr. & amort.) $386,878 % of sales 53 Depreciation and Amortization $14,565 % of fixed assets 54 EBIT $53,708 55 Interest expense on long-term debt $11,880 Interest rate x average debt during year 56 Interest expense on line of credit $0 57 EBT $41,828 58 Taxes (25%) $10,457 59 Net Income $25,097 60 61 Common dividends (regular dividends) $12,554 Growth 62 Special dividends Zero in preliminary forecast Parrard winrarastarfin Percent of Fornaxide Estimated es based on it mihrain The special dividend in the final forecast should be equal to the amount specified in the area below the balance sheets. Build a Model Cell G104 commented by Mike Ehrhardt + 90% 14 3:36 PM 2/20/2021 Adobe X Cut x H 6 Tahoma BAM Ch 12 - Excel ? T1 - OX FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW A AUBREY HENSHAW A AutoSum - A Tahoma - A A Wrap Text General HE Copy - Fill Paste BIU-E. A - % 8.8 Conditional Format as Cell Merge & Center Insert Delete Format Sort & Find & Format Painter Clear - Formatting Table Styles Filter - Select Clipboard Font Alignment Number Styles Cells Editing Comment 65 X fx A B c D E G H M N 62 Special dividends Zero in preliminary forecast Derow ule valane seers. 63 Addition to retained earnings $12.543 64 The addition to retained 65 Balance Sheets 2020 Preliminary earnings is equal to the net 66 (December 31, In thousands of dollars) 2019 forecast doesn't 2020 Final forecast Income minus any regular and 67 Historical 2020 Input include special (includes special special dividends. 68 2019 ratios Forecasting basis ratios dividend or LOC) dividend or LOC) 69 Assets: Special dividend in 70 Cash $18.206 Divine Arngring w RX % of sales preliminary forecast 71 Accounts Receivable $100,133 % of sales is zero. 72 Inventories $45,515 % of sales 73 Total current assets $163,854 Percent of forecasted Percent of forecasted 74 Fixed assets $364,120 % of sales Sales Percent of forecasted Percent of forecasted 75 Total assets $527,974 76 sales. Percent of forecasted 77 Liabilities and equity Percent of forecasted 78 Accounts payable $31,861 % of sales sales. 79 Accruals $27,309 % of sales line of credit is zero in preliminary 80 Line of credit In final forecast, the line of $0 Zero in preliminary forecast forecast. credit is equal to the 81 Total current ilabilities $59,170 amount specified in the 82 Long-term debt $120,000 Previous section below the balance 83 Total liabilities $179,170 sheets 84 Common stock $60,000 Previous The amount shown in retained earnings in the final 85 Retained Earnings $106,745 Previous + Addition to retained earnings forecast is equal to the amount from the previous 86 Total common equity $166,745 year's balance sheet (not the amount from the 87 Total liabilities and equity $345,914 preliminary forecast) plus the addition to retained earnings on the final forecasted income statement. 88 89 Identify Financing Deficit or Surplus 90 The amount shown in retained Aarninac In the nreliminary Build a Model + Cell G104 commented by Mike Ehrhardt 0 - + 90% 3:37 PM Adobe 2/20/2021 BE 14 )1 1 X Cut x H 6 Tahoma BAM Ch 12 - Excel ? T - SX FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW A AUBREY HENSHAW A AutoSum - A Tahoma - A A Wrap Text General AY Copy - HE Fill Paste BIU - A A- Merge & Center - % 8.8 Conditional Format as Cell Insert Delete Format Format Painter Sort & Find & Formatting Table Styles Clear Filter - Select Clipboard Font Alignment Number Styles Cells Editing Comment 65 - X fx B D E F G H K L 1 M N 86 Total common equity $166,745 year's balance sheet (not the amount from the 87 Total liabilities and equity $345,914 preliminary forecast) plus the addition to retained 88 earnings on the final forecasted income statement. 89 Identify Financing Deficit or Surplus 90 The amount shown in retained 91 Increase in spontaneous liabilities (accounts payable and accruals) earnings in the preliminary forecast is equal to the amount 92 + Increase in long-term bonds, preferred stock and common stock from the previous year's balance 93 + Net income (in preliminary forecast) minus regular common dividends sheet plus the addition to retained 94 Increase in financing earnings on the preliminary 95 forecasted Income statement. 96 - Increase in total assets 97 Amount of financing deficit or surplus: 98 In final forecast, the line of 99 If deficit in financing (negative), show the amount for the line of credit credit is equal to the 100 If surplus in financing (positive), show the amount of the special dividend amount specified in the 101 section below the balance 102 a. What are the forecasted levels of the line of credit and special dividends? Isheets 103 104 Required ine of credit Note: we copied values from H99:H100) when sales growth in G51 = 8%. 105 Special dividends 106 107 b. Now assume that the growth in sales is only 3% (do this by changing the growth rate in Cell G51). What are the 108 forecasted levels of line of credit and special dividends? 109 110 Required ine of credit Note: We copied values from H99:H100) when sales growth in G51 = 3%. 111 Special dividends 112 113 114 Build a Model + Cell G104 commented by Mike Ehrhardt + 90% 14 3:37 PM Adobe 2/20/2021 BE | |
