I need help creating a sensitivity analysis for my class. The growth in sales, COGS and operating expenses are the variables I would like to use for the analysis. The pro forma for the years is also posted.

inputs Growth in Sales COGS % of Sales Operating Expenses % Sales 13.0% 40.0% 52.0% Sensitivity of EFN and Excess Cash with changes to growth in sales % value. 2002 2003 2004 Excess Cash EFN Excess Cash EFN Excess Cash 0 12.11 0 14.41 0 EFN 15.02 0.10 0.11 0.12 0.13 0.14 0.15 0.16 0.17 Growth in Sales % 0.18 Sensitivity of EFN and Excess Cash with changes to COGS% of sales value. 2002 2003 2004 Excess Cash EFN Excess Cash EFN Excess Cash EFN COGS % of Sales 0.10 0.11 0.12 0.13 0.14 0.15 0.16 0.17 0.18 Sensitivity of EFN and Excess Cash with changes to Operating Expenses % of sales value. 2002 2003 2004 Excess Cash EFN Excess Cash EFN Excess Cash EFN Operating Expenses % of Sales 0.10 0.11 0.12 0.13 0.14 0.15 0.16 0.17 0.18 ssumptions Forecast 002-2004 1999 303.7 127.7 176.0 Fiscal Year Ended FelThis is the interest rate. Net Interest Expense = (Int Rate * (Long term 2000 Liabilities + EFN)) - (Int rate * Excess Cash) 330.1 374.1 Growth 130.9 39.7% 149.0 39.8% % sales 199.2 225.1 13.0% 40.0% 2002 422.7 169.1 253.6 2003 477.7 191.1 286.6 2004 539.8 215.9 323.9 42.0% Income Statement Turnover Cost of sales Gross profit Operating expenses -excluding exceptional costs -exceptional costs Restructuring costs Net interest expense Profit before tax (PBT) Tax expense Profit/(loss) after tax 49.9% 1.5% 5.5% 50.3% 0.0% 0.8% 52.3% 3.0% 0.3% 151.4 4.5 16.6 0.1 3.4 8.0 (4.6) 166.2 0.0 2.7 1.5 28.8 10.4 18.4 195.7 11.2 1.0 4.4 12.8 3.5 9.3 % sales Il% sales % sales Debt, EFN and Excess Cash 52.0% 0.0% 0.0% 6.0% 219.8 0.0 0.0 4.4 29.4 8.8 20.6 248.4 0.0 0.0 5.3 33.0 9.9 23.1 280.7 0.0 0.0 6.2 37.0 11.1 25.9 % of PBT 30.0% Constant Ordinary dividends Profit/(loss) retained 10.9 10.9 (15.5) 10.9 7.5 10.9 (1.6) Constant 10.9 9.7 10.9 12.2 10.9 15.0 34.0 11.2% 19.2 5.8% 13.7 3.7% 4.0% Balance Sheet Cash Excess Cash (Plug) Accounts receivable Inventories Other current assets Net fixed assets Other assets Total assets 27.8 38.6 12.5 87.8 0.0 200.7 9.2% 12.7% 4.1% 28.9% 0.0% 66.1% 30.3 44.7 15.6 104.7 6.0 220.5 9.2% 13.5% 4.7% 31.7% 1.8% 66.8% 30.3 51.3 17.5 110.6 6.7 230.1 8.1% 13.7% 4.7% 29.6% 1.8% 61.5% % sales Plug Il% sales % sales ll% sales % sales l% sales 8.0% 13.7% 4.7% 30.0% 1.8% 16.9 0.0 33.8 57.9 19.9 126.8 7.6 19.1 0.0 38.2 65.4 22.5 143.3 8.6 297.1 21.6 0.0 43.2 74.0 25.4 161.9 9.7 335.7 262.9 10.7 7.1 13.0 11.3 10.8 0.0 21.6 28.0 4.3% 3.7% 3.6% 0.0% 7.1% 20.5 11.7 15.6 0.3 13.3 36.7 6.2% 3.5% 4.7% 0.1% 4.0% Accounts payable Taxes payable Accruals loverdrafts Other current liabilities Long-term liabilities EFN (Plug) Other liabilities Shareholders' equity Total liabs & equity 2.9% 1.9% 3.1% 0.2% 4.5% 11.5 0.7 16.9 61.2 % sales % sales % sales % sales % sales Fixed Plug Il % sales 4.0% 2.0% 3.5% 0.2% 4.0% 16.9 8.5 14.8 0.8 16.9 61.2 12.1 0.4 131.3 262.9 19.1 9.6 16.7 1.0 19.1 73.3 14.4 0.5 143.5 297.1 21.6 10.8 18.9 1.1 21.6 87.7 15.0 0.5 158.5 335.7 335.7 320.7 15.0 0.6% 0.3% 0.1% 0.1% 1.7 114.3 200.7 1.0 121.4 220.5 0.4 121.6 230.1 Trial assets Trial liabs. & equity Trial plug 262.9 250.8 12.1 297.1 282.7 14.4 inputs Growth in Sales COGS % of Sales Operating Expenses % Sales 13.0% 40.0% 52.0% Sensitivity of EFN and Excess Cash with changes to growth in sales % value. 2002 2003 2004 Excess Cash EFN Excess Cash EFN Excess Cash 0 12.11 0 14.41 0 EFN 15.02 0.10 0.11 0.12 0.13 0.14 0.15 0.16 0.17 Growth in Sales % 0.18 Sensitivity of EFN and Excess Cash with changes to COGS% of sales value. 2002 2003 2004 Excess Cash EFN Excess Cash EFN Excess Cash EFN COGS % of Sales 0.10 0.11 0.12 0.13 0.14 0.15 0.16 0.17 0.18 Sensitivity of EFN and Excess Cash with changes to Operating Expenses % of sales value. 2002 2003 2004 Excess Cash EFN Excess Cash EFN Excess Cash EFN Operating Expenses % of Sales 0.10 0.11 0.12 0.13 0.14 0.15 0.16 0.17 0.18 ssumptions Forecast 002-2004 1999 303.7 127.7 176.0 Fiscal Year Ended FelThis is the interest rate. Net Interest Expense = (Int Rate * (Long term 2000 Liabilities + EFN)) - (Int rate * Excess Cash) 330.1 374.1 Growth 130.9 39.7% 149.0 39.8% % sales 199.2 225.1 13.0% 40.0% 2002 422.7 169.1 253.6 2003 477.7 191.1 286.6 2004 539.8 215.9 323.9 42.0% Income Statement Turnover Cost of sales Gross profit Operating expenses -excluding exceptional costs -exceptional costs Restructuring costs Net interest expense Profit before tax (PBT) Tax expense Profit/(loss) after tax 49.9% 1.5% 5.5% 50.3% 0.0% 0.8% 52.3% 3.0% 0.3% 151.4 4.5 16.6 0.1 3.4 8.0 (4.6) 166.2 0.0 2.7 1.5 28.8 10.4 18.4 195.7 11.2 1.0 4.4 12.8 3.5 9.3 % sales Il% sales % sales Debt, EFN and Excess Cash 52.0% 0.0% 0.0% 6.0% 219.8 0.0 0.0 4.4 29.4 8.8 20.6 248.4 0.0 0.0 5.3 33.0 9.9 23.1 280.7 0.0 0.0 6.2 37.0 11.1 25.9 % of PBT 30.0% Constant Ordinary dividends Profit/(loss) retained 10.9 10.9 (15.5) 10.9 7.5 10.9 (1.6) Constant 10.9 9.7 10.9 12.2 10.9 15.0 34.0 11.2% 19.2 5.8% 13.7 3.7% 4.0% Balance Sheet Cash Excess Cash (Plug) Accounts receivable Inventories Other current assets Net fixed assets Other assets Total assets 27.8 38.6 12.5 87.8 0.0 200.7 9.2% 12.7% 4.1% 28.9% 0.0% 66.1% 30.3 44.7 15.6 104.7 6.0 220.5 9.2% 13.5% 4.7% 31.7% 1.8% 66.8% 30.3 51.3 17.5 110.6 6.7 230.1 8.1% 13.7% 4.7% 29.6% 1.8% 61.5% % sales Plug Il% sales % sales ll% sales % sales l% sales 8.0% 13.7% 4.7% 30.0% 1.8% 16.9 0.0 33.8 57.9 19.9 126.8 7.6 19.1 0.0 38.2 65.4 22.5 143.3 8.6 297.1 21.6 0.0 43.2 74.0 25.4 161.9 9.7 335.7 262.9 10.7 7.1 13.0 11.3 10.8 0.0 21.6 28.0 4.3% 3.7% 3.6% 0.0% 7.1% 20.5 11.7 15.6 0.3 13.3 36.7 6.2% 3.5% 4.7% 0.1% 4.0% Accounts payable Taxes payable Accruals loverdrafts Other current liabilities Long-term liabilities EFN (Plug) Other liabilities Shareholders' equity Total liabs & equity 2.9% 1.9% 3.1% 0.2% 4.5% 11.5 0.7 16.9 61.2 % sales % sales % sales % sales % sales Fixed Plug Il % sales 4.0% 2.0% 3.5% 0.2% 4.0% 16.9 8.5 14.8 0.8 16.9 61.2 12.1 0.4 131.3 262.9 19.1 9.6 16.7 1.0 19.1 73.3 14.4 0.5 143.5 297.1 21.6 10.8 18.9 1.1 21.6 87.7 15.0 0.5 158.5 335.7 335.7 320.7 15.0 0.6% 0.3% 0.1% 0.1% 1.7 114.3 200.7 1.0 121.4 220.5 0.4 121.6 230.1 Trial assets Trial liabs. & equity Trial plug 262.9 250.8 12.1 297.1 282.7 14.4