Complete spreadsheet problem aroundsales forecasting.Formulas are given

Contain formulas and references to calculations. Click on the cells and see the calculati Answers Contain formulas and references to calculations. Click on the cells and see the calculati 12-7 2010 Sales Net Income M (Profit Margin) Dividends Paid Payout Ratio RR 350.00 10.50 $ 4.20 40.00% 60.00% 2011 Sales - Increase % Increase Sales M (Profit Margin) Net Income Payout Ratio Dividends Paid RR 70.00 AFN = (A*/S0)S - (L*/S0)S - MS1(RR) (A*/S0)S (L*/S0) S MS1(RR) AFN 12-b AFN = (A*/S0)S - (L*/S0)S - MS(RR) Step 1 Find S1 S1 = S0 times ((A*/S0) - (L*/S0))/((A*/S0) - (L*/S0)- MS(RR)) Step 2 Subtract S0 from S1 (A*/S0) = Calculate from the Current Year's Balance Sheet and (L*/S0) = (A*/S0) - (L*/S0) % of Assets that change with Sales minus % of Spontaneous Liabilities that S0 = S1 = M S1 = S0 times ((A*/S0) - (L*/S0))/((A*/S0) - (L*/S0)- M(RR)) From above M(RR) From above S1-S0 Change in Sales % Change in Sales W/O AFN 12-7c 2010 Assets: Cash and cash equivalents Short-term investments Accounts Receivable Inventories Total current assets Fixed assets A* A* A* A* A* A* 3.50 26.00 58.00 87.50 35.00 Total assets A* 122.50 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 L* L* 122.50 Specified sources of financing = Additional funds needed (AFN) = Required additional notes payable = Additional short-term investments = Sales Increase Tax rate Dividend Payout Ratio Interest Rate on Loans 2010 2011 basis Ratios Inputs % of sales Previous % of sales % of sales % of sales 9.00 % of sales 8.50 % of sales 18.00 Previous 35.50 6.00 Previous 41.50 15.00 Previous 66.00 Previous + Change in R/E 81.00 Required assets = 12-8 Key Input Data: Forecasting Used in the forecast 15% 40% 45% 10% (000) 2010 Forecasting 2010 2011 Actual basis Ratios Inputs $36,000 Growth $32,440 % of sales $3,560 $460 Interest rate x beginning of year debt $3,100 $1,240 $1,860 $837 Payout Ratio Sales Expenses (excluding depr. & amort.) EBIT Net Interest Expense EBT Taxes (40%) Net Income Common dividends Addition to retained earnings (DRE) $1,023 (000) 2010 Forecasting 2010 2011 Actual basis Ratios Inputs Assets: Cash and cash equivalents Short-term investments Accounts Receivable Inventories Total current assets Fixed assets A* A* A* A* A* A* 1,080.00 6,480.00 9,000.00 16,560.00 12,600.00 Total assets A* 29,160.00 L* L* 4,320.00 % of sales 2,880.00 % of sales 2,100.00 Previous 9,300.00 3,500.00 Previous 12,800.00 3,500.00 Previous 12,860.00 Previous + Change in R/E 16,360.00 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 Required assets = Specified sources of financing = Additional funds needed (AFN) = Required additional notes payable = Additional short-term investments = 29,160.00 % of sales Previous % of sales % of sales % of sales 12-9 Key Input Data: Used in the forecast 10% 40% 112,000.00 3.70% 13% Sales Increase Tax rate Dividends Dividends Growth Interest Rate on Loans (000) 2010 Forecasting 2010 2011 Actual basis Ratios Inputs $3,600,000 Growth $3,279,720 % of sales $320,280 $18,280 Interest rate x beginning of year debt $302,000 $120,800 $181,200 $108,000 Sales Expenses (excluding depr. & amort.) EBIT Net Interest Expense EBT Taxes (40%) Net Income Common dividends Addition to retained earnings (DRE) $73,200 (000) 2010 Forecasting 2010 2011 Actual basis Ratios Inputs Assets: Cash and cash equivalents Short-term investments Accounts Receivable Inventories Total current assets Fixed assets A* A* A* A* A* A* 180,000.00 360,000.00 720,000.00 1,260,000.00 1,440,000.00 Total assets A* 2,700,000.00 Liabilities and equity Accounts payable Accruals Notes payable Total current liabilities Long-term debt Total liabilities L* L* % of sales Previous % of sales % of sales % of sales 360,000.00 % of sales 180,000.00 % of sales 156,000.00 Previous 696,000.00 Previous 696,000.00 0.000% Common stock Retained Earnings Total common equity 1,800,000.00 Previous 204,000.00 Previous + Change in R/E 2,004,000.00 Total liabilities and equity 2,700,000.00 Required assets = Specified sources of financing = Additional funds needed (AFN) = Required additional notes payable = Additional short-term investments = e cells and see the calculations or references e cells and see the calculations or references pontaneous Liabilities that change with Sales Without AFN AFN With AFN - 2011 Forecast $36,000 (000) Without AFN AFN With AFN 2011 Forecast Without AFN AFN With AFN