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 December 31 Balance Sheets NOTE: Cell Reference the Ratios from 2019 to use as the 2020 inputs 2020 Inputs Without AFN 2019 Ratios Forecasting basis 2020 AFN 2019 With AFN Assets: Cash Accounts Receivable Inventories Total current 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 Liabilities and equity 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 Previous +DRE $204,745 $715,850 REMEMBER. Payment of Dividends will Total assets Planned liabilities and equity Additional funds needed (AFN) Required additional notes payable Special dividends $0 $0 REMEMBER - You CANNOT have both at the same timelt What are the forecasted levels of notes payable and special dividende? Required additional notes payable Special dividendo Note: TYPE IN the values from G76:G77 when sales growth in C24 b. Now assume that the growth in sales is only 8% CHANGE THE GROWTH RATE FOR THE INCOME STATEMENT AND RECALCULATE THE FORECAST What are the forecasted levels of notes payable and special dividends? Note: TYPE IN the values from G76:677 when sales growth in C24 Required add lonal notes payable Special dividends
