Question

show thw cell refference.

Chapter 12. Financial Statement Forecasting Belize Diving Company's 2019 financial statements are shown below. Forecast Cozumel Diving's 2020 income statement and balance sheets. Use the following assumptions: (1) Sales grow by 15%. (2) The ratios of expenses to sales, depreciation to fixed assets, cash to sales, accounts receivable to sales, and inventories to sales will be the same in 2019 as in 2020. (3) Belize Diving will not issue any new stock or new long-term bonds. (4) The interest rate is 4.5% for short-term debt and 6.25% for long-term debt. (5) No interest is earned on cash. (6) Dividends grow at a 11% rate. (7) Calculate the additional funds needed (AFN). If new financing is required, assume it will be raised as notes payable. Assume that any new notes payable will be borrowed on the last day of the year, so there will be no additional interest expense for the new notes payable. If surplus funds are available, pay a special dividend. NAME: SECTION: FALL 2020 EXTRA CREDIT a. What are the forecasted levels of notes payable and special dividends? Key Input Data: Tax rate Sales growth rate Dividend grow Notes Payable rd Long-Term Debt rd Used in the forecast 35.00% 15.00% 11.00% 4.50% 6.25% NOTE: Cell Reference the Ratios from 2019 to use as the 2020 inputs December 31 Income Statements: 2020 2020 Inputs Forecast Sales Expenses (excluding depr. & amort.) EBITDA Depreciation and Amortization EBIT Net Interest E EBT Taxes Net Income Common dividends (regular dividends) Addition to retained earnings (DRE) Forecasting 2019 2019 basis Ratios $855,900 Growth $680,400 % of sales $175,500 $22,680% of fixed assets $152,820 $12,550 interest rate x beginning of year debt $140,270 $49,095 $91,176 $15,400 Growth $75,776

Answer 1

December 31 Income statement 2020 Inputs $128,385 $102,060 0.79 0.10 $3,402 Sales Expenses (excluding depr & amort) EBIDTA Depreciation and amortisation EBIT Net interest expense EBT Taxes Net income Common dividend (regular dividends) Addition to retained earnings (DRE) Forecasting 2019 2019 basis Ratios $855,900 Growth $680,400 % of sales $175,500 $22,680 % of fixed assets $152,820 $12,550 Interest rate beginning of year debt $140,270 $49,095 $91,175 $15,400 Growth $75,775 2020 Forecast $984,285 $782,460 $ 201,825 $26,082 $175,743 $23,945 $151,798 $53,129 $98,669 $17,094 $81,575 Amount Rate of interest Notes payable $46,000 4.50% $2,070 Long-term debt ######## 6.25% $21,875 $23,945 $1,694

2019 Ratios 2020 Inputs Without AFN 2020 AFN 2020 With AFN December 31 Balance sheets Forecasting 2019 basis 4ses: Cash $28,400 % of sales Accounts receivable $176,650 % of sales Inventories $275,800 % of sales Total current assets $480,850 Fixed assets $235,000 % of sales Total assets $715,850 0.03 0.21 0.32 $4,260 $26,498 $41,370 $32,660 $203,148 $317,170 $552,978 $270,250 $823,228 $32,660 $203,148 $317,170 $552 978 $270,250 $823,228 0.27 $35,250 0.09 0.05 $11,461 $5,805 Liabwandey Accounts payable Accruals Notes payable Total current liabilities Long-term debt Total liabilities Common stock Retained earnings Total common equity Total liabilities and equity $76,405 % of sales $38,700 % of sales $46,000 Previous $161,105 $350,000 Previous $511,105 $98,000 Previous $106,745 revious+DRE $204,745 $715,850 $87,866 $44,505 $46,000 $178,371 $350,000 $528,371 $98,000 $188,320 $286,320 $814,690 $87,866 $44,505 $46,000 $178,371 $350,000 $528,371 $98,000 $179,783 $277,783 $806,153 ($8,537) Total assets= Planned liabilities and equity= Additional funds needed (AFN)= $823,228 $814,690 ($8,537) Required Additional Notes payable= Special dividends $0 $8,537

a. What are the forecasted levels of notes payable and special dividends?
Required Additional Notes payable=				$0
Special dividends					$8,537

working

December 31 Income statement Sales Expenses lexoluding depr & amort) EBIDTA Depreciation and amortisation EBIT Net interest expense EBT Taxes Net income Common dividend (regular dividends) Addition to retained earnings (DRE) Forecasting 2019 2020 2020 2019 basis Ratios Inputs Forecast $855,900 Growth 855900'15% 128385+855900 $680,400 % of sales 680400/855900 680400/85590091283851 102060+680400 $175,500 984285-782460 $22,680 % of fixed assets 22680/235000 22680/235000'35,250 22680+3402 $152,820 201825-26082 $12,550 Interest rate beginning of year debt $23,945 $140,270 175743-23945 $49,095 151798'0.35 $91,175 151798-53129 $15,400 Growth 15400'11% 15400+1694 $75,775 98669-17094)

December 31 Balance sheets Forecasting 2019 basis 2019 Ratios 2020 Inputs 2020 AFN 2020 With AFN Without AFN 28400/855900 176650/855900 275800/855900 28400/855900'128385 176650/855900 128385 275800/855900'128385 Cash Accounts receivable Inventories Total ourrent assets Fixed assets Total assets $28,400 % of sales $176,650 % of sales $275,800 % of sales $480,850 $235,000 % of sales $715,850 28400+4260 176650+26498 275800+41370 32660+203148+317170 235000+35250 270250+552978 28400+4260 176650+26498 275800+41370 32660+203148+317170 235000+35250 270250+552978 235000/855900 235000/855900'128385 764051855900 38700/855900 764051855900 128385 38700/855900 128385 $0 Liables and Accounts payable Accruals Notes payable Total current liabilities Long-term debt Total liabilities Common stock Retained earnings Total common equity $76,405 % of sales $38,700 % of sales $46,000 Previous $161,105 $350,000 Previous $511,105 $98,000 Previous $106,745 revious+DRE $204,745 $715,850 76405+11461 38700+5805 $46,000 46000+44505+87866 $350,000 178371+350000 $98,000 106745+81575 188,320+98000 286,320+528,371 76405+11461 38700+5805 $46,000 46000+44505+87866 $350,000 178371+350000 $98,000 188,320-8,537 179,783+98000 277,783+528,371 ($8,537) Total assets Planned liabilities and equity= Additional funds needed (AFN) 270250+552978 286,320+528,371 823228-814,690

When sales is growing at 8%

December 31 Income statement 2020 Inputs $68,472 $54,432 $1,814 Sales Expenses lexoluding depr & amort) EBIDTA Depreciation and amortisation EBIT Net interest expense EBT Taxes Net income Common dividend (regular dividends) Addition to retained earnings (DRE) Forecasting 2019 2019 basis Ratios $855,900 Growth $680,400 % of sales 0.79 $175,500 $22,680 % of fixed assets 0.10 $152,820 $12,550 Interest rate beginning of year debt $140,270 $49,095 $91,175 $15,400 Growth $75,775 2020 Forecast $924,372 $734,832 $189,540 $24.494 $165,046 $23,945 $141,101 $49,385 $91,715 $17,094 $74,621 $1,694

December 31 Balance sheets Forecasting 2019 basis 2019 Ratios 2020 Inputs 2020 AFN 2020 With AFN Without AFN 0.03 0.21 0.32 $2,272 $14,132 $22,064 Cash Accounts receivable Inventories Total current assets Fixed assets Total assets $28,400 % of sales $176,650 % of sales $275,800 Z of sales $480,850 $235,000 % of sales $715,850 $30,672 $190,782 $297,864 $519,318 $253,800 $773,118 $30,672 $190,782 $297,864 $519,318 $253,800 $773,118 0.27 $18,800 0.09 0.05 $6,112 $3,096 $26,562 Liabwand'em Accounts payable Accruals Notes payable Total current liabilities Long-term debt Total liabilities Common stook Retained earnings Totalcommon equity Total liabilities and equity $76,405 % of sales $38,700 % of sales $46,000 Previous $161,105 $350,000 Previous $511,105 $98,000 Previous $106,745 revious+DRE $204,745 $715,850 $82,517 $41,796 $46,000 $170,313 $350,000 $520,313 $98,000 $181,366 $279,366 $799,680 $82,517 $41,796 $72,562 $196,875 $350,000 $546,875 $98,000 $181,366 $279,366 $826,242 $0 Total assets Planned liabilities and equity= Additional funds needed (AFN)= $773, 118 $799,680 $26,562 Required Additional Notes payable= Special dividends $26,562 $0

Required Additional Notes payable=				$26,562
Special dividends					$0

Working

December 31 Income statement Sales Expenses lexoluding depr & amort) EBIDTA Depreciation and amortisation EBIT Net interest expense EBT Taxes Net income Common dividend (regular dividends) Addition to retained earnings (DRE) Forecasting 2019 2020 2019 basis Ratios Inputs $855,900 Growth 855900'8%. $680,400 % of sales 680400/855900 680400/855900'68472 $175,500 $22,680 % of fixed assets 22680/235000 22680/235000'18800 $152,820 $12,550 Interest rate beginning of year debt $140,270 $49,095 $91,175 $15,400 Growth 15400'11% $75,775 2020 Forecast 855900+68472 680400+54432 924372-734832 22680+1814 189540-24494 $23,945 165046-23945 1411010.35) 141101-49385 15400+1694 91715-17094