Section E 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. In C4 create a calculation to add the Increment (the amount our forecast is increasing by) in B3 to the volume in B4. (Note we want the ability to change the values in B3 and B3 so do NOT just add 25, use a relative reference or a range name). Drag the formula across to G4. In row 5 calculate the total sales revenue for each of the different Sales Volumes. Complete the variable costs table for the different sales volumes (B8:G13). In row 16 calculate the Contribution Margin for each sales volume. 1 In row 17 calculate Contribution Margin less Fixed Costs for each sales volume. 000 DO 0 0 0 In row 20 calculate the state tax incurred for each sales volume, if the profit was zero or below, the calculation should return 0. (Do NOT just type 0 as the volumes can change.) In row 21 calculate the federal tax incurred for each sales volume, if the profit was zero or below the calculation should return 0. (Do NOT just type 0 as the volumes can change.) In row 24 calculate the profit after factoring in the income tax. In row 26 work out the profit for each sales volume as a percentage of sales. Format as a percentage with 2 decimal places (do not apply rounding formulas). Complete the Break-Even Analysis Line Chart Data in B29 to G32 for each of the Unit volumes. (Note Total Costs includes Variable Costs, Fixed Costs and Income Tax) Using the data in B29 to G23, create a 2D Line Chart that plots the Total Fixed Costs, Total Costs & Total Sales. Add the Volume Units as Horizontal Axis Labels and add the Chart Title: Break-Even Analysis (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. N.B. Only include one chart ensure it is on the Forecast Sheet and that it is a 2D Line Chart with no line markers (not a Scatter chart with smooth lines). E10 2 0 E11 0 D Profit Volume Forecast 25 3 4 5 Unit Volume Increments Sales Volume in Units Total Sales Value 25 100 125 150 7 Variable Costs 8 Direct Labor 9 Direct Material 10 Factory Overhead 11 Other Vary-with-Unit Costs 12 Sales Commissions 13 Other Vary-with-Revenue Costs 14 Total Variable Costs - $ - $ - $ - $ - $ 15 16 Contribution Margin 17 Contribution Margin - Fixed Costs 18 19 Income Tax 20 State Tax 21 Federal Tax 22 Total Income Tax 24 $ Profit 26 % Profit of Sales Value 27 28 Break-Even Analysis Line Chart Data 29 Volumes in Units 30 Fixed Costs 31 Total Costs 32 Total Sales