2 3 Part Code 4 PX1004683 5 PX242979 6 PX373195 7 PX374089 8 PX374561 9 PX577199 10 PX723206 11 PX723541 12 PX723542 13 PX723557 14 PX723566 15 PX723593 16 PX723594 17 PX723596 18 PX723742 19 PX723743 20 PX723745 21 PX723820 22 PX725125 23 PX792653 24 PX796490 25 PX797516 26 PX807530 Quantity Product Decsription 1 Block Rear 14-X 4 Spring 5b 1 Latch 1 Bracket 552 1 Axle 8 Nut Super 4 Large Foot 1 Arm 320 1 Arm 35 4 Foot 1 Pin 10 8 Bolt 2 Large Bolt 4 Small Bolt 1 Socket 2 Socket Leg 4 Foot Sa 4 Nut 8 Rivet Use: 1004688-16 Nut Wing 2 Spring 3a 1 Grip HR Fixed Costs BOM CVP Analysis Price Each $13.74 $2.08 $2.61 $53.11 $3.11 $1.91 $1.74 $1.09 $2.59 $2.74 $2.19 $1.66 $2.49 $2.39 $2.16 $2.29 $2.99 $1.99 $2.29 $1.74 $2.19 $2.16 $3.24 Total Price $13.74 58.32 $2.61 $53.11 $3.11 $15.28 $6.96 51.99 $2.59 $10.96 $2.19 $13.28 $4.98 $9.56 $2.16 $4.58 $11.06 $7.96 $0.16 $13.92 58.76 $4.32 53.24 ... Profit Volume Forecast Inver 2 2 2 2 8 1 1 2 27 PX807531 28 PX807532 29 PX807539 30 PX807540 31 PX809486 32 PX820261 33 PX820262 34 PX820263 35 PX820266 36 PX820728 37 PX820728 38 PX820834 39 PX823385 40 PX823386 41 PX823978 42 PX823978 43 PX860085 1 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 $1.99 $1.99 $22.09 $3.33 $1.66 $16.24 $13.19 $2.19 $12.64 $2.29 $2.29 $2.39 $1.91 $1.99 $27.29 $27.29 $2.09 $3.98 $3.98 $44.18 56.66 $13.28 $16.24 $13.10 $4.38 $12.64 $4.58 $4.58 $2.39 $7.64 $15.92 $27.29 $27.29 $4.18 2 2 1 4 8 1 1 2 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.) 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. In F13 to F16 calculate the Break-Even Variable Costs for the two products. 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 01 G M N R V W Y z 44 Product W8550 3 BESO $211 $8.5 - Part Code Quantity Product Decription DX 1004583 Bock Roar 14-X DX 242979 Spring 50 DX373105 . Latch PX 374089 . Bracket 552 DX 374561 . DX577199 Nut Super PX723205 4 Large Foot PX723541 . Arm 320 PXC729542 Arm 35 PX 723557 4 Foot 0X720555 Din 10 PX723593 Boit P729594 2 Large Bot DX729595 Sina Bot DX723742 Socket PXC729749 Socket PX723745 4 Les 0X729920 Foot Sa 2725:26 4 Nut DX 792853 Rivet Use: 1004583-15 Nut Wing PX797515 Sprina DX 807590 . Grp DX807531 Pn 12 DX807532 Din 33 DX907599 Vice Screw Sa DX 807540 Vice Screw Sa PX 809486 Washer Specia DX820251 Block 12/A PX820242 : DX920269 RH Nut PX820285 . Block Rear PX820728 2 Khob PX820728 Knob DX820814 Single Grip DX 823335 4 Screw 24mm PX823385 Screw Special PX822978 . Vicedaw Front PX822978 : VicewFront DX980085 Screw 12mm Price Each Total Price $13.74 $13.74 $3.32 $2.51 $2.51 $53.11 $53 11 93.11 $1.91 $15.28 $1.74 $1.92 $1.09 $2.50 $2.50 $2.74 $10.08 $2.10 $2.10 51.55 $4.08 $2.39 $2.15 $2.15 $2.29 $2.00 $1108 $1.09 $705 $2.20 59.18 51.74 $13.92 $2.10 53.76 6 W3860 7 W8550 8 W950 WBLO 10 WBSSD 11 WESED 12 WB500 13 WBSSD 14 W8500 15 WESGO 16 W8500 17 WB500 18 W850 10 WB500 20 WESED 21 W8500 22 W3500 23 WESSO 24 W8500 25 WBGO 25 WESSO 27 WESED 28 WESED 29 W8500 30 W3540 31 W3860 32 W3550 33 W3500 34 W3500 35 WB500 35 W660 37 WB500 38 WB500 30 W3860 40 WESSO 41 WBESO 42 W8500 43 W8500 44 93.24 $1.00 $1.09 $22.09 $3.08 53.08 $44.18 $5.95 $13.28 $16.24 $13.10 51.55 Back Front $13.10 $12.54 $4.58 $453 $12.54 $2.20 $2.29 $2.39 51.91 $1.00 $27-29 $27.29 $2.00 SECAH A $15.02 $27-29 HR Fixed Costs BoM CVP Analysis Profit Volume Forecast Inventory + A B D F H 1 Labour Costs 2 3 4 Labour Costs per hour 28.00 5 6 Labour Hours per Unit 7 WB500 1.6) 8 WB660 2.1 9 10 Please Note: This sheet is locked and cannot be altered in any way. The values can be used in calculations but you will need to click on them rather than using named ranges. 11 12 13 14 15 16 17 18 19 20 21 Labour Costs HR Fixed Costs BoM CVP Analysis Profit Volume Forecast + : B D E HA Cost Volume Profit Analysis 1 Fixed Costs WB500 $335.00 5820 WB660 $470.00 3841 Vary-with-Revenue Costs Sales Commissions 3.5% Weighted Average Contribution Margin Break Even Units (weighted) WB500 WB660 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 $15.43 $15.43 $19.51 $19.51 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 Inventory + HR Fixed Costs BOM CVP Analysis Profit Volume Forecast 2 3 Part Code 4 PX1004683 5 PX242979 6 PX373195 7 PX374089 8 PX374561 9 PX577199 10 PX723206 11 PX723541 12 PX723542 13 PX723557 14 PX723566 15 PX723593 16 PX723594 17 PX723596 18 PX723742 19 PX723743 20 PX723745 21 PX723820 22 PX725125 23 PX792653 24 PX796490 25 PX797516 26 PX807530 Quantity Product Decsription 1 Block Rear 14-X 4 Spring 5b 1 Latch 1 Bracket 552 1 Axle 8 Nut Super 4 Large Foot 1 Arm 320 1 Arm 35 4 Foot 1 Pin 10 8 Bolt 2 Large Bolt 4 Small Bolt 1 Socket 2 Socket Leg 4 Foot Sa 4 Nut 8 Rivet Use: 1004688-16 Nut Wing 2 Spring 3a 1 Grip HR Fixed Costs BOM CVP Analysis Price Each $13.74 $2.08 $2.61 $53.11 $3.11 $1.91 $1.74 $1.09 $2.59 $2.74 $2.19 $1.66 $2.49 $2.39 $2.16 $2.29 $2.99 $1.99 $2.29 $1.74 $2.19 $2.16 $3.24 Total Price $13.74 58.32 $2.61 $53.11 $3.11 $15.28 $6.96 51.99 $2.59 $10.96 $2.19 $13.28 $4.98 $9.56 $2.16 $4.58 $11.06 $7.96 $0.16 $13.92 58.76 $4.32 53.24 ... Profit Volume Forecast Inver 2 2 2 2 8 1 1 2 27 PX807531 28 PX807532 29 PX807539 30 PX807540 31 PX809486 32 PX820261 33 PX820262 34 PX820263 35 PX820266 36 PX820728 37 PX820728 38 PX820834 39 PX823385 40 PX823386 41 PX823978 42 PX823978 43 PX860085 1 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 $1.99 $1.99 $22.09 $3.33 $1.66 $16.24 $13.19 $2.19 $12.64 $2.29 $2.29 $2.39 $1.91 $1.99 $27.29 $27.29 $2.09 $3.98 $3.98 $44.18 56.66 $13.28 $16.24 $13.10 $4.38 $12.64 $4.58 $4.58 $2.39 $7.64 $15.92 $27.29 $27.29 $4.18 2 2 1 4 8 1 1 2 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.) 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. In F13 to F16 calculate the Break-Even Variable Costs for the two products. 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 01 G M N R V W Y z 44 Product W8550 3 BESO $211 $8.5 - Part Code Quantity Product Decription DX 1004583 Bock Roar 14-X DX 242979 Spring 50 DX373105 . Latch PX 374089 . Bracket 552 DX 374561 . DX577199 Nut Super PX723205 4 Large Foot PX723541 . Arm 320 PXC729542 Arm 35 PX 723557 4 Foot 0X720555 Din 10 PX723593 Boit P729594 2 Large Bot DX729595 Sina Bot DX723742 Socket PXC729749 Socket PX723745 4 Les 0X729920 Foot Sa 2725:26 4 Nut DX 792853 Rivet Use: 1004583-15 Nut Wing PX797515 Sprina DX 807590 . Grp DX807531 Pn 12 DX807532 Din 33 DX907599 Vice Screw Sa DX 807540 Vice Screw Sa PX 809486 Washer Specia DX820251 Block 12/A PX820242 : DX920269 RH Nut PX820285 . Block Rear PX820728 2 Khob PX820728 Knob DX820814 Single Grip DX 823335 4 Screw 24mm PX823385 Screw Special PX822978 . Vicedaw Front PX822978 : VicewFront DX980085 Screw 12mm Price Each Total Price $13.74 $13.74 $3.32 $2.51 $2.51 $53.11 $53 11 93.11 $1.91 $15.28 $1.74 $1.92 $1.09 $2.50 $2.50 $2.74 $10.08 $2.10 $2.10 51.55 $4.08 $2.39 $2.15 $2.15 $2.29 $2.00 $1108 $1.09 $705 $2.20 59.18 51.74 $13.92 $2.10 53.76 6 W3860 7 W8550 8 W950 WBLO 10 WBSSD 11 WESED 12 WB500 13 WBSSD 14 W8500 15 WESGO 16 W8500 17 WB500 18 W850 10 WB500 20 WESED 21 W8500 22 W3500 23 WESSO 24 W8500 25 WBGO 25 WESSO 27 WESED 28 WESED 29 W8500 30 W3540 31 W3860 32 W3550 33 W3500 34 W3500 35 WB500 35 W660 37 WB500 38 WB500 30 W3860 40 WESSO 41 WBESO 42 W8500 43 W8500 44 93.24 $1.00 $1.09 $22.09 $3.08 53.08 $44.18 $5.95 $13.28 $16.24 $13.10 51.55 Back Front $13.10 $12.54 $4.58 $453 $12.54 $2.20 $2.29 $2.39 51.91 $1.00 $27-29 $27.29 $2.00 SECAH A $15.02 $27-29 HR Fixed Costs BoM CVP Analysis Profit Volume Forecast Inventory + A B D F H 1 Labour Costs 2 3 4 Labour Costs per hour 28.00 5 6 Labour Hours per Unit 7 WB500 1.6) 8 WB660 2.1 9 10 Please Note: This sheet is locked and cannot be altered in any way. The values can be used in calculations but you will need to click on them rather than using named ranges. 11 12 13 14 15 16 17 18 19 20 21 Labour Costs HR Fixed Costs BoM CVP Analysis Profit Volume Forecast + : B D E HA Cost Volume Profit Analysis 1 Fixed Costs WB500 $335.00 5820 WB660 $470.00 3841 Vary-with-Revenue Costs Sales Commissions 3.5% Weighted Average Contribution Margin Break Even Units (weighted) WB500 WB660 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 $15.43 $15.43 $19.51 $19.51 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 Inventory + HR Fixed Costs BOM CVP Analysis Profit Volume Forecast