I need help on the screen shot provided on the Costs Volume Profit Analysis, Profit volume forecast:
This is the criteria for Costs Volume Profit Analysis:
This is the criteria for Profit volume forecast:
These are the Questions i needed answered:
Fixed Costs % of Total Fixed Cost Items Rent Utilities Admin Costs Insurance $ $ $ $ Quarter 1 29,200.00 $ 3,580.00 $ 6,745.00$ 2,760.00 $ Quarter 2 29,200.00 $ 3,580.00 $ 6,880.00 $ 2,850.00 $ Quarter 3 30,163.60 $ 3,580.00 $ 6,750.00 $ 2,760.00 $ Quarter 4 Tarterly Avera 30,163.60 3,580.00 $3,580.00 6,810.00 $6,796.25 2,850.00 $2,805.00 69% 8% 16% 7% Quarterly Totals $ 42,285.00 $ 42,510.00 $ 43,253.60 $ 43,403.60 Total Fixed Costs $ 171,452.20 Highest Quarterly Admin Costs: Average Monthly Rent: Rent % Increase: $ $ 6,880.00 29,681.80 3.20% 19 Section D D1 1 D2 1 D3 2 D4 1 The following are to be completed in the CVP Analysis Worksheet B6 and C6 contain the number of units sold for the two different product types respectively. In B7 and C7 calculate the % sales mix (percentage of total) for each product type. You can assume for the CVP analysis that this will stay constant. In F4 pull through the total fixed costs from the Fixed Costs sheet (if the value in the fixed costs sheet changes, this should update.) In B11 and C11, use the information in the BOM to calculate the Direct material costs for one unit of each product. In B12 and C12 use the values in the Labour Costs sheet to calculate the cost of labour to produce one unit of each product. In B17 and C17 calculate the contribution margin per unit for each product (ensure you include vary with unit and vary with revenue costs). In F9 calculate the Weighted Average Contribution Margin using the sales mix percentages calculated previously. In F10 calculate the break-even units (the combined number of units from the two products that need to be sold to not make a loss). As we cannot sell part of a product, apply an appropriate rounding function to get the answer to 0 decimal places. (This answer is very important so double check it.) D5 2 D6 1 D7 2 Question Instruction Marks Done D8 2 In B19 and C19 use the previous result and the sales mix to work out the break-even point in units per product. In B20 and C20 calculate the break even point in dollars for each product. D9 1 D10 In F13 to F16 calculate the Break-Even Variable Costs for the two products. 4 D11 1 D12 In F19 calculate the Break-Even Contribution Margin (this is the contribution margin for the total number of units required to break even.) In F20 calculate the Break-Even Profit Before Tax. (Break even profit would normally be 0, but because we rounded up our break-even units, this should be slightly more than 0.) 1 Section E 19 E1 1 E2 1 E3 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 3 E6 2 E7 1 E8 2 E9 4 xed Costs Total Costs Total $ Sales Value $1,400,000 $1,200,000 $1,000,000 $800,000 S600,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). $400.000 $200.000 1000 1500 2000 2500 3000 3500 Cost Volume Profit Analysis Fixed Costs Product Information Unit Sales Price Units Manufactured and Sold Sales Mix (% of total units sold) WB500 $320.00 5060 WB660 $475.00 3340 Vary-with-Revenue Costs Sales Commissions 3.5% Weighted Average Contribution Margin Break Even Units (weighted) WB500 WB660 Vary with Unit Costs Direct Material Direct Labor Other Vary-with-Unit Costs Total Vary with Unit Costs $14.80 $14.80 $17.45 $17.45 Break Even Variable Costs Direct Material Direct Labor Other Vary-with-Unit Costs Sales Commissions Total Variable Costs WB500 WB660 Break Even Analysis Contribution Margin Per Unit $0.00 Break Even Point in Units/Product Break Even in Sales Dollars Break Even Contribution Margin Break Even Profit Before Tax Profit Volume Forecast Tax Rates WB500 Sales Mix WB660 Sales Mix Unit Increments 60% 40% 1,000 10% State Tax Federal Tax 5% 1000 Sales Volume in Units Total $ Sales Value Variable Costs Direct Material Direct Labor Other Vary-with-Unit Costs Sales Commissions Total Variable Costs $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $ Profit Before Tax (Contribution Margin - Fixed Costs) Income Tax State Tax Federal Tax Total Income Tax $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $ Profit after Tax % Profit (of Sales) Break-Even Analysis Line Chart Data Fixed Costs Total Costs Total $ Sales Value Fixed Costs % of Total Fixed Cost Items Rent Utilities Admin Costs Insurance $ $ $ $ Quarter 1 29,200.00 $ 3,580.00 $ 6,745.00$ 2,760.00 $ Quarter 2 29,200.00 $ 3,580.00 $ 6,880.00 $ 2,850.00 $ Quarter 3 30,163.60 $ 3,580.00 $ 6,750.00 $ 2,760.00 $ Quarter 4 Tarterly Avera 30,163.60 3,580.00 $3,580.00 6,810.00 $6,796.25 2,850.00 $2,805.00 69% 8% 16% 7% Quarterly Totals $ 42,285.00 $ 42,510.00 $ 43,253.60 $ 43,403.60 Total Fixed Costs $ 171,452.20 Highest Quarterly Admin Costs: Average Monthly Rent: Rent % Increase: $ $ 6,880.00 29,681.80 3.20% 19 Section D D1 1 D2 1 D3 2 D4 1 The following are to be completed in the CVP Analysis Worksheet B6 and C6 contain the number of units sold for the two different product types respectively. In B7 and C7 calculate the % sales mix (percentage of total) for each product type. You can assume for the CVP analysis that this will stay constant. In F4 pull through the total fixed costs from the Fixed Costs sheet (if the value in the fixed costs sheet changes, this should update.) In B11 and C11, use the information in the BOM to calculate the Direct material costs for one unit of each product. In B12 and C12 use the values in the Labour Costs sheet to calculate the cost of labour to produce one unit of each product. In B17 and C17 calculate the contribution margin per unit for each product (ensure you include vary with unit and vary with revenue costs). In F9 calculate the Weighted Average Contribution Margin using the sales mix percentages calculated previously. In F10 calculate the break-even units (the combined number of units from the two products that need to be sold to not make a loss). As we cannot sell part of a product, apply an appropriate rounding function to get the answer to 0 decimal places. (This answer is very important so double check it.) D5 2 D6 1 D7 2 Question Instruction Marks Done D8 2 In B19 and C19 use the previous result and the sales mix to work out the break-even point in units per product. In B20 and C20 calculate the break even point in dollars for each product. D9 1 D10 In F13 to F16 calculate the Break-Even Variable Costs for the two products. 4 D11 1 D12 In F19 calculate the Break-Even Contribution Margin (this is the contribution margin for the total number of units required to break even.) In F20 calculate the Break-Even Profit Before Tax. (Break even profit would normally be 0, but because we rounded up our break-even units, this should be slightly more than 0.) 1 Section E 19 E1 1 E2 1 E3 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 3 E6 2 E7 1 E8 2 E9 4 xed Costs Total Costs Total $ Sales Value $1,400,000 $1,200,000 $1,000,000 $800,000 S600,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). $400.000 $200.000 1000 1500 2000 2500 3000 3500 Cost Volume Profit Analysis Fixed Costs Product Information Unit Sales Price Units Manufactured and Sold Sales Mix (% of total units sold) WB500 $320.00 5060 WB660 $475.00 3340 Vary-with-Revenue Costs Sales Commissions 3.5% Weighted Average Contribution Margin Break Even Units (weighted) WB500 WB660 Vary with Unit Costs Direct Material Direct Labor Other Vary-with-Unit Costs Total Vary with Unit Costs $14.80 $14.80 $17.45 $17.45 Break Even Variable Costs Direct Material Direct Labor Other Vary-with-Unit Costs Sales Commissions Total Variable Costs WB500 WB660 Break Even Analysis Contribution Margin Per Unit $0.00 Break Even Point in Units/Product Break Even in Sales Dollars Break Even Contribution Margin Break Even Profit Before Tax Profit Volume Forecast Tax Rates WB500 Sales Mix WB660 Sales Mix Unit Increments 60% 40% 1,000 10% State Tax Federal Tax 5% 1000 Sales Volume in Units Total $ Sales Value Variable Costs Direct Material Direct Labor Other Vary-with-Unit Costs Sales Commissions Total Variable Costs $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $ Profit Before Tax (Contribution Margin - Fixed Costs) Income Tax State Tax Federal Tax Total Income Tax $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $ Profit after Tax % Profit (of Sales) Break-Even Analysis Line Chart Data Fixed Costs Total Costs Total $ Sales Value
