the sheet is provided below the questions

D2 Section D The following are to be completed in the CVP Analysis Worksheet D1 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.) D3 In B11 and C11, use the information in the BOM to calculate the Direct material costs for one unit of each product. D4 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. D5 In B17 and C17 calculate the contribution margin per unit for each product (ensure you include vary with unit and vary with revenue costs). D6 In F9 calculate the Weighted Average Contribution Margin using the sales mix percentages calculated previously. D7 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.) Question Instruction D8 In B19 and C19 use the previous result and the sales mix to work out the break-even point in units per product. D9 In B20 and C20 calculate the break even point in dollars for each product. D10 In F13 to F16 calculate the Break-Even Variable Costs for the two products. D11 In F19 calculate the Break-Even Contribution Margin (this is the contribution margin for the total number of units required to break even.) D12 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 B C D E F 1 Bill of Materials wN Quantity 1 4 1 1 1 8 4 1 1 4 1 8 2 4 1 2 4 4 3 Product 4 WB660 5 WB660 6 WB660 7 WB660 8 WB660 9 WB660 10 WB660 11 WB660 12 WB500 13 WB660 14 WB500 15 WB660 16 WB500 17 WB500 18 WB660 19 WB500 20 WB660 21 WB500 22 WB500 23 W1660 24 WB500 25 WB660 26 WB660 27 WB660 28 WB660 29 WB500 30 WB660 31 WB660 32 WB660 33 WB500 34 WB500 35 WB500 36 WB660 37 WB500 38 WB500 39 WB660 40 WB660 41 WB660 42 WB500 43 WB500 Part Code PX1004683 PX242979 PX373195 PX374089 PX374561 PX577199 PX723206 PX723541 PX723542 PX723557 PX723566 PX723593 PX723594 PX723596 PX723742 PX723743 PX723745 PX723820 PX725125 PX792653 PX796490 PX797516 PX807530 PX807531 PX807532 PX807539 PX807540 PX809486 PX820261 PX820262 PX820263 PX820266 PX820728 PX820728 PX820834 PX823385 PX823386 PX823978 4 8 4 Product Decsription Block Rear 14-X Spring 5b Latch Bracket 552 Axle Nut Super Large Foot Arm 320 Arm 35 Foot Pin 10 Bolt Large Bolt Small Bolt Socket Socket Leg Foot Sa Nut Rivet Use: 1004688-16 Nut Wing Spring 3a Grip Pin 12 Pin 33 Vice Screw Sa Vice Screw Sa Washer Special Block 12/A Block Front RH Nut Block Rear Knob Knob Single Grip Screw 2.4mm Screw Special Vice Jaw Front Vice Jaw Front Screw 1.2mm Price Each $14.29 $1.91 $3.11 $53.11 $2.61 $1.91 $2.16 $2.08 $1.99 $2.99 $2.19 $1.66 $2.19 $1.99 $2.16 $2.09 $2.61 $2.39 $1.99 $1.91 $2.19 $1.66 $2.99 $2.16 $1.74 $16.99 $3.83 $1.83 $15.59 $10.99 $2.49 $13.74 $2.09 $2.09 $2.19 $1.99 $1.66 $27.29 $27.29 $2.29 Total Price $14.29 $7.64 $3.11 $53.11 $2.61 $15.28 $8.64 $2.08 $1.99 $11.96 $2.19 $13.28 $4.38 $7.96 $2.16 $4.18 $10.44 $9.56 $7.96 $15.28 $8.76 $3.32 $2.99 $4.32 $3.48 $33.98 $7.66 $14.64 $15.59 $10.99 $4.98 $13.74 $4.18 $4.18 $2.19 $7.96 $13.28 $27.29 $27.29 $4.58 2 1 2 2 2 2 8 1 1 2 1 2 2 1 4 8 1 PX823978 PX860085 1 2 E F G H $ $ $ $ Quarter 1 31,700.00 $ 3,500.00 $ 7,155.00 $ 2,740.00 $ Quarter 2 31,700.00 $ 3,500.00 $ 7,300.00 $ 3,130.00 $ Quarter 3 32,936.30 $ 3,500.00 $ 7,300.00 $ 2,740.00 $ Quarter 4 Quarterly Average 32,936.30$ 32,318.15 3,500.00 $ 3,500.00 7,300.00 $ 7,263.75 3,130.00 $ 2,935.00 % of Total 70% 8% 16% 6% Fixed Costs 2 3 Fixed Cost Items 4 Rent 5 Utilities 6 Admin Costs 7 Insurance 8 9 Quarterly Totals 10 11 Total Fixed Costs 12 13 Highest Quarterly Admin Costs: 14 Average Monthly Rent: 15 Rent% Increase: 16 $ 45,095.00 $ 45,630.00 $ 46,476.30 $ 46,866.30 $ 184,067.60 $ $ 7,300.00 10,772.72 3.9% B C D E F Fixed Costs WB500 $310.00 5970 WB660 $465.00 3940 Vary-with-Revenue Costs Sales Commissions 3.5% Weighted Average Contribution Margin Break Even Units (weighted) WB500 WB660 Cost Volume Profit Analysis 2 3 4 Product Information 5 Unit Sales Price 6 Units Manufactured and sold 7 Sales Mix (% of total units sold) 8 9 10 vary with Unit Costs 11 Direct Material 12 Direct Labor 13 Other Vary-with-Unit Costs 14 Total Vary with Unit Costs 15 16 Break Even Analysis 17 Contribution Margin Per Unit 18 19 Break Even Point in Units/Product 20 Break Even in Sales Dollars 21 $15.82 $15.82 $19.12 $19.12 Break Even Variable costs Direct Material Direct Labor Other Vary-with-Unit Costs Sales Commissions Total Variable Costs WB500 WB660 $0.00 Break Even Contribution Margin Break Even Profit Before Tax D2 Section D The following are to be completed in the CVP Analysis Worksheet D1 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.) D3 In B11 and C11, use the information in the BOM to calculate the Direct material costs for one unit of each product. D4 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. D5 In B17 and C17 calculate the contribution margin per unit for each product (ensure you include vary with unit and vary with revenue costs). D6 In F9 calculate the Weighted Average Contribution Margin using the sales mix percentages calculated previously. D7 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.) Question Instruction D8 In B19 and C19 use the previous result and the sales mix to work out the break-even point in units per product. D9 In B20 and C20 calculate the break even point in dollars for each product. D10 In F13 to F16 calculate the Break-Even Variable Costs for the two products. D11 In F19 calculate the Break-Even Contribution Margin (this is the contribution margin for the total number of units required to break even.) D12 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 B C D E F 1 Bill of Materials wN Quantity 1 4 1 1 1 8 4 1 1 4 1 8 2 4 1 2 4 4 3 Product 4 WB660 5 WB660 6 WB660 7 WB660 8 WB660 9 WB660 10 WB660 11 WB660 12 WB500 13 WB660 14 WB500 15 WB660 16 WB500 17 WB500 18 WB660 19 WB500 20 WB660 21 WB500 22 WB500 23 W1660 24 WB500 25 WB660 26 WB660 27 WB660 28 WB660 29 WB500 30 WB660 31 WB660 32 WB660 33 WB500 34 WB500 35 WB500 36 WB660 37 WB500 38 WB500 39 WB660 40 WB660 41 WB660 42 WB500 43 WB500 Part Code PX1004683 PX242979 PX373195 PX374089 PX374561 PX577199 PX723206 PX723541 PX723542 PX723557 PX723566 PX723593 PX723594 PX723596 PX723742 PX723743 PX723745 PX723820 PX725125 PX792653 PX796490 PX797516 PX807530 PX807531 PX807532 PX807539 PX807540 PX809486 PX820261 PX820262 PX820263 PX820266 PX820728 PX820728 PX820834 PX823385 PX823386 PX823978 4 8 4 Product Decsription Block Rear 14-X Spring 5b Latch Bracket 552 Axle Nut Super Large Foot Arm 320 Arm 35 Foot Pin 10 Bolt Large Bolt Small Bolt Socket Socket Leg Foot Sa Nut Rivet Use: 1004688-16 Nut Wing Spring 3a Grip Pin 12 Pin 33 Vice Screw Sa Vice Screw Sa Washer Special Block 12/A Block Front RH Nut Block Rear Knob Knob Single Grip Screw 2.4mm Screw Special Vice Jaw Front Vice Jaw Front Screw 1.2mm Price Each $14.29 $1.91 $3.11 $53.11 $2.61 $1.91 $2.16 $2.08 $1.99 $2.99 $2.19 $1.66 $2.19 $1.99 $2.16 $2.09 $2.61 $2.39 $1.99 $1.91 $2.19 $1.66 $2.99 $2.16 $1.74 $16.99 $3.83 $1.83 $15.59 $10.99 $2.49 $13.74 $2.09 $2.09 $2.19 $1.99 $1.66 $27.29 $27.29 $2.29 Total Price $14.29 $7.64 $3.11 $53.11 $2.61 $15.28 $8.64 $2.08 $1.99 $11.96 $2.19 $13.28 $4.38 $7.96 $2.16 $4.18 $10.44 $9.56 $7.96 $15.28 $8.76 $3.32 $2.99 $4.32 $3.48 $33.98 $7.66 $14.64 $15.59 $10.99 $4.98 $13.74 $4.18 $4.18 $2.19 $7.96 $13.28 $27.29 $27.29 $4.58 2 1 2 2 2 2 8 1 1 2 1 2 2 1 4 8 1 PX823978 PX860085 1 2 E F G H $ $ $ $ Quarter 1 31,700.00 $ 3,500.00 $ 7,155.00 $ 2,740.00 $ Quarter 2 31,700.00 $ 3,500.00 $ 7,300.00 $ 3,130.00 $ Quarter 3 32,936.30 $ 3,500.00 $ 7,300.00 $ 2,740.00 $ Quarter 4 Quarterly Average 32,936.30$ 32,318.15 3,500.00 $ 3,500.00 7,300.00 $ 7,263.75 3,130.00 $ 2,935.00 % of Total 70% 8% 16% 6% Fixed Costs 2 3 Fixed Cost Items 4 Rent 5 Utilities 6 Admin Costs 7 Insurance 8 9 Quarterly Totals 10 11 Total Fixed Costs 12 13 Highest Quarterly Admin Costs: 14 Average Monthly Rent: 15 Rent% Increase: 16 $ 45,095.00 $ 45,630.00 $ 46,476.30 $ 46,866.30 $ 184,067.60 $ $ 7,300.00 10,772.72 3.9% B C D E F Fixed Costs WB500 $310.00 5970 WB660 $465.00 3940 Vary-with-Revenue Costs Sales Commissions 3.5% Weighted Average Contribution Margin Break Even Units (weighted) WB500 WB660 Cost Volume Profit Analysis 2 3 4 Product Information 5 Unit Sales Price 6 Units Manufactured and sold 7 Sales Mix (% of total units sold) 8 9 10 vary with Unit Costs 11 Direct Material 12 Direct Labor 13 Other Vary-with-Unit Costs 14 Total Vary with Unit Costs 15 16 Break Even Analysis 17 Contribution Margin Per Unit 18 19 Break Even Point in Units/Product 20 Break Even in Sales Dollars 21 $15.82 $15.82 $19.12 $19.12 Break Even Variable costs Direct Material Direct Labor Other Vary-with-Unit Costs Sales Commissions Total Variable Costs WB500 WB660 $0.00 Break Even Contribution Margin Break Even Profit Before Tax