Prob 2). Use Excel to perform the following tasks on the income statement model provided: a). Create a two-way data table for net income by varying: 1) Gross Profit (Low = 200,000, High = 800,000, increment = 50,000), and 2) Var.Cost% (Low = 0.10, High = 0.30, increment - 0.025). Use conditional formatting to identify negative values (Loss) of Net Income. b). Create a chart (surface chart) with sensitivity analysis that graphically represents the data table from (a). c). Create a histogram of the results of a). What are your conclusions? P R S 1 2 Typical Income Statement for Manufacturing Business 3 4 5 Revenue COGS 900000 360000 540000 COGS%= 40% 6 Gross Profit Var. Cost%= 30% 7 8 9 10 Variable Cost Fixed Cost Operating Expenses 270000 125000 395000 11 12 13 Net Operating Income 145000 14 Interest Expense 25000 Earnings Before Taxes 120000 15 16 17 18 Taxes 40800 Tax Rate= 34% 19 20 * units in $$ 21 22 23 Net Income 79200 Calculations COGS = Revenue * COGS% Variable Cost = Var. Cost% * Revenue Taxes = Taxes Rate * Earnings Before Taxes 24 25 Prob 2). Use Excel to perform the following tasks on the income statement model provided: a). Create a two-way data table for net income by varying: 1) Gross Profit (Low = 200,000, High = 800,000, increment = 50,000), and 2) Var.Cost% (Low = 0.10, High = 0.30, increment - 0.025). Use conditional formatting to identify negative values (Loss) of Net Income. b). Create a chart (surface chart) with sensitivity analysis that graphically represents the data table from (a). c). Create a histogram of the results of a). What are your conclusions? P R S 1 2 Typical Income Statement for Manufacturing Business 3 4 5 Revenue COGS 900000 360000 540000 COGS%= 40% 6 Gross Profit Var. Cost%= 30% 7 8 9 10 Variable Cost Fixed Cost Operating Expenses 270000 125000 395000 11 12 13 Net Operating Income 145000 14 Interest Expense 25000 Earnings Before Taxes 120000 15 16 17 18 Taxes 40800 Tax Rate= 34% 19 20 * units in $$ 21 22 23 Net Income 79200 Calculations COGS = Revenue * COGS% Variable Cost = Var. Cost% * Revenue Taxes = Taxes Rate * Earnings Before Taxes 24 25