(Hartfield Medical Supplies) The financial requirements are calculated using best estimates. However, you typically will also consider other contingencies in order to be better prepared. To illustrate this type of analysis, recalculate 2014 funds requirements using the percent of sales approach for all 9 feasible scenarios based on following variations: Sales growth = 5%, 10%, 15%; Total operating costs (excluding depreciation)/Sales = 80%, 90%, 100%

When I change the total operating costs, the AFN does NOT change and I need to construct a 3x3 table, with all NINE possible scenarios, however, nothing seems to be changing. Please show formulas in Excel if possible. Thank you.

1. Balance Sheets Most Recent 2013 Forecast 2014 Input Basis for 2014 Forecast 1.00% 14.00% 20.00% * 2014 Sales * 2014 Sales * 2014 Sales $20.0 280.0 400.0 $700.0 500.0 $1,200.0 $22.00 $308.00 $440.00 $770.00 $550.00 $1,320.00 25.00% * 2014 Sales Assets Cash Accts. rec. Inventories Total CA Net fixed assets Total assets Liabilities and equity Accts.pay. & accruals Line of credit Total CL Long-term debt Total liabilities Common stock Retained earnings Total common equity Total liabs. & equity 4.00% * 2014 Sales Draw on LOC if financing deficit Carry over from previous year $80.0 0.0 $80.0 500.0 $580.0 420.0 200.0 $620.0 $1,200.0 Carry over from previous year Old RE + Add. to RE Check: TA - Total Liab. & Eq. = 2. Income Statement Input 110% 90.00% 10.00% Basis for 2014 Forecast 2013 Sales 2014 Sales X 2014 Net PP&E $88.00 $0.00 $88.00 $500.00 $588.00 $420.00 $253 $673 $1,261 $59.00 Forecast 2014 $2,200.00 $1,980.00 $55.00 $165.00 $40.00 $0.00 $125.00 $50.00 $75.00 $22.00 $0.00 $53.00 Most Recent 2013 $2,000.0 1,800.0 50.0 $150.0 40.0 0.0 $110.0 44.0 $66.0 $20.0 10.00% 10.00% Sales Op.costs (excl. depr.) Depreciation EBIT Less: Interest on LTD Interest on LOC Pretax earnings Taxes (40%) Net income Regular common dividends Special dividends Addition to RE Avg bonds Beginning LOC 40.00% * Pretax earnings 100% $0.0 2013 Dividend Pay if financing surplus Net income - Dividends $46.0 1. Balance Sheets Most Recent 2013 Forecast 2014 Input Basis for 2014 Forecast 1.00% 14.00% 20.00% * 2014 Sales * 2014 Sales * 2014 Sales $20.0 280.0 400.0 $700.0 500.0 $1,200.0 $22.00 $308.00 $440.00 $770.00 $550.00 $1,320.00 25.00% * 2014 Sales Assets Cash Accts. rec. Inventories Total CA Net fixed assets Total assets Liabilities and equity Accts.pay. & accruals Line of credit Total CL Long-term debt Total liabilities Common stock Retained earnings Total common equity Total liabs. & equity 4.00% * 2014 Sales Draw on LOC if financing deficit Carry over from previous year $80.0 0.0 $80.0 500.0 $580.0 420.0 200.0 $620.0 $1,200.0 Carry over from previous year Old RE + Add. to RE Check: TA - Total Liab. & Eq. = 2. Income Statement Input 110% 90.00% 10.00% Basis for 2014 Forecast 2013 Sales 2014 Sales X 2014 Net PP&E $88.00 $0.00 $88.00 $500.00 $588.00 $420.00 $253 $673 $1,261 $59.00 Forecast 2014 $2,200.00 $1,980.00 $55.00 $165.00 $40.00 $0.00 $125.00 $50.00 $75.00 $22.00 $0.00 $53.00 Most Recent 2013 $2,000.0 1,800.0 50.0 $150.0 40.0 0.0 $110.0 44.0 $66.0 $20.0 10.00% 10.00% Sales Op.costs (excl. depr.) Depreciation EBIT Less: Interest on LTD Interest on LOC Pretax earnings Taxes (40%) Net income Regular common dividends Special dividends Addition to RE Avg bonds Beginning LOC 40.00% * Pretax earnings 100% $0.0 2013 Dividend Pay if financing surplus Net income - Dividends $46.0