Follow the instructions in section E to fill each light grey cell with correct formula. Please refund me for this question since I can't provide further information and don't need further help either.

Section E 19 E1 1 0 E2 1 4 E4 1 The following are to be completed in the Profit Volume Forecast Worksheet In this sheet we want to be able to forecast what our profits will look like with different sales volumes and different sales mixes. Click on the drop down in B3 and change it to 50%, the value in B4 adjusts accordingly. Now change it back to 60%. Click in B5 and you will see you can also select the increment (the amount we go up in) we want to model, for example we may want to see profit for 500, 1000, 1500 units or we may want to see profit for 1000, 2000, 3000 units. Click in B7 and you will see you can select the minimum sales volume you want to model. In C7 create a formula to add the Increment in B5 to the volume in B7. Use appropriate cell referencing so that the formula can be dragged across to G7 and the total will increase by the value shown in B5 (and change if the value in B5 changes). Drag the formula across to G7. In row 8 calculate the total sales revenue for each of the different Sales Volumes using the sales mix options selected in B3 and B4 (do not use the sales mix from the CVP sheet.) Complete the variable costs table for the different sales volumes (B11:G14) using the sales mix options selected in B3 and B4. In row 17 calculate Profit Before Tax (Contribution Margin - Fixed Costs) for each sales volume (this will return a negative value where the unit volume is less than the break-even). In B20 calculate the state tax incurred for each sales volume using the rate at the top of the sheet. If the profit was zero or below, the calculation should return O. (Do NOT just type 0 as the volumes can change.) Apply appropriate cell referencing so that the formula can be dragged down and then across to complete the tax table. In row 24 calculate the profit after factoring in the income tax (this may return a negative value where the unit volume is less than or close to the break-even). In row 26 work out the profit for each sales volume as a percentage of sales. Complete the Break-Even Analysis Line Chart Data in B29 to G31 for each of the Unit volumes. (Note Total Costs includes Variable Costs, Fixed Costs and Income Tax) Use the data in A29:G31 to create a 2D Line Chart that plots the Total Fixed Costs, Total Costs & Total $ Sales. Add the Sales Volume in Units figures as Horizontal Axis Labels and add the Chart Title: Profit Volume Forecast (mark given only if this exact title is used). Use chart tools (not drag and drop) to position the legend at the top of the chart. The completed chart should look something like this (although values will vary). The point where the lines Profit Volume Forecast cross is the break even point. E5 E6 2 E7 1 aa E8 2 E9 4 Fixed Costs Total Costs Total S Sales Value $1,400,000 $1,200,000 $1,000,000 $800,000 N.B. Do NOT use Pivot Tables or Pivot Charts. Only include one chart, ensure it is on the Profit Volume Forecast Sheet and that it is a 2D Line Chart with no line markers (not a Scatter chart with smooth lines). $600,000 $400,000 $200,000 SO 1000 1500 2000 2500 3000 3500 B D E F G 1 Profit Volume Forecast 60% 40% 1,000 Tax Rates State Tax Federal Tax 10% 5% 1000 2,000 3,000 4,000 5,000 6,000 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 2 3 WB500 Sales Mix 4 WB660 Sales Mix 5 Unit Increments 6 7 Sales Volume in Units 8 8 Total $ Sales Value 9 9 10 Variable Costs 11 Direct Material 12 Direct Labor 13 Other Vary-with-Unit Costs 14 Sales Commissions 15 Total Variable costs 16 17 $ Profit Before Tax (Contribution Margin - Fixed Costs) 18 19 Income Tax 20 State Tax 21 Federal Tax 22 Total Income Tax 23 24 $ Profit after Tax 25 26 % Profit (of Sales) 27 28 Break-Even Analysis Line Chart Data 29 Fixed Costs 30 Total Costs 31 Total $ Sales Value 32 33 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Section E 19 E1 1 0 E2 1 4 E4 1 The following are to be completed in the Profit Volume Forecast Worksheet In this sheet we want to be able to forecast what our profits will look like with different sales volumes and different sales mixes. Click on the drop down in B3 and change it to 50%, the value in B4 adjusts accordingly. Now change it back to 60%. Click in B5 and you will see you can also select the increment (the amount we go up in) we want to model, for example we may want to see profit for 500, 1000, 1500 units or we may want to see profit for 1000, 2000, 3000 units. Click in B7 and you will see you can select the minimum sales volume you want to model. In C7 create a formula to add the Increment in B5 to the volume in B7. Use appropriate cell referencing so that the formula can be dragged across to G7 and the total will increase by the value shown in B5 (and change if the value in B5 changes). Drag the formula across to G7. In row 8 calculate the total sales revenue for each of the different Sales Volumes using the sales mix options selected in B3 and B4 (do not use the sales mix from the CVP sheet.) Complete the variable costs table for the different sales volumes (B11:G14) using the sales mix options selected in B3 and B4. In row 17 calculate Profit Before Tax (Contribution Margin - Fixed Costs) for each sales volume (this will return a negative value where the unit volume is less than the break-even). In B20 calculate the state tax incurred for each sales volume using the rate at the top of the sheet. If the profit was zero or below, the calculation should return O. (Do NOT just type 0 as the volumes can change.) Apply appropriate cell referencing so that the formula can be dragged down and then across to complete the tax table. In row 24 calculate the profit after factoring in the income tax (this may return a negative value where the unit volume is less than or close to the break-even). In row 26 work out the profit for each sales volume as a percentage of sales. Complete the Break-Even Analysis Line Chart Data in B29 to G31 for each of the Unit volumes. (Note Total Costs includes Variable Costs, Fixed Costs and Income Tax) Use the data in A29:G31 to create a 2D Line Chart that plots the Total Fixed Costs, Total Costs & Total $ Sales. Add the Sales Volume in Units figures as Horizontal Axis Labels and add the Chart Title: Profit Volume Forecast (mark given only if this exact title is used). Use chart tools (not drag and drop) to position the legend at the top of the chart. The completed chart should look something like this (although values will vary). The point where the lines Profit Volume Forecast cross is the break even point. E5 E6 2 E7 1 aa E8 2 E9 4 Fixed Costs Total Costs Total S Sales Value $1,400,000 $1,200,000 $1,000,000 $800,000 N.B. Do NOT use Pivot Tables or Pivot Charts. Only include one chart, ensure it is on the Profit Volume Forecast Sheet and that it is a 2D Line Chart with no line markers (not a Scatter chart with smooth lines). $600,000 $400,000 $200,000 SO 1000 1500 2000 2500 3000 3500 B D E F G 1 Profit Volume Forecast 60% 40% 1,000 Tax Rates State Tax Federal Tax 10% 5% 1000 2,000 3,000 4,000 5,000 6,000 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 2 3 WB500 Sales Mix 4 WB660 Sales Mix 5 Unit Increments 6 7 Sales Volume in Units 8 8 Total $ Sales Value 9 9 10 Variable Costs 11 Direct Material 12 Direct Labor 13 Other Vary-with-Unit Costs 14 Sales Commissions 15 Total Variable costs 16 17 $ Profit Before Tax (Contribution Margin - Fixed Costs) 18 19 Income Tax 20 State Tax 21 Federal Tax 22 Total Income Tax 23 24 $ Profit after Tax 25 26 % Profit (of Sales) 27 28 Break-Even Analysis Line Chart Data 29 Fixed Costs 30 Total Costs 31 Total $ Sales Value 32 33 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00