Need help with D1 to D12
Section D 19 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.) O O O O O O 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 A B C D E F G 4 Fixed Costs WB500 $340.00 5690 WB660 $470.00 3755 Vary-with-Revenue Costs Sales Commissions 3.5%) Weighted Average Contribution Margin Break Even Units (weighted) WB500 WB660 $16.44 $16.44 $18.76 $18.76 Cost Volume Profit Analysis 2 3 Product Information 5 Unit Sales Price 6 Units Manufactured and Sold 7 Sales Mix (% of total units sold) ) 8 9 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 22 23 24 25 26 27 28 29 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 30 31 32 33 34 35 36 37 38 39 40 41 42 43 AA Information Labour Costs HR Fixed Costs BOM CVP Analysis Profit Volume Forecast Inventory + Fixed Costs Fixed Cost Items Rent Utilities Admin Costs Insurance $ $ $ $ Quarter 1 32,300.00 $ 3,560.00 $ 7,215.00 $ 2,580.00 $ Quarter 2 32,300.00 $ 3,560.00 $ 7,430.00 $ 3,200.00 $ Quarter 3 33,398.20$ 3,560.00 $ 7,220.00 $ 2,580.00 $ Quarter 4 Quarterly Average 33,398.20 $ 32,849.10 3,560.00 $ 3,560.00 7,360.00 $ 7,306.25 3,200.00 $ 2,890.00 % of Total 70% 8% 16% 6% Quarterly Totals $ 45,655.00 $ 46,490.00 $ 46,758.20 $ 47,518.20 Total Fixed Costs $ 186,421.40 Highest Quarterly Admin Costs: Average Monthly Rent: Rent % Increase: $ $ 46,758.20 32,800.00 3.4% A B D E F G H J K L M Bill of Materials 1 2 3 Product Quantity Price Each $14.29 1 4 W3660 5 WB660 6 WB660 4 1 1 $1.99 Product Decsription Block Rear 14-X Spring 5b Latch Bracke Axle an Mine $2.99 1 Bracket 552 1 8 4 1 1 1 1 Nut Super Large Foot 2593 4 1 1 g 8 2 4 1 - 2 2 4 4 4 Part Code PX1004683 PX242979 PX373195 PX374089 PX374089 PX37450 PX374561 PX577199 PX723206 PX723541 F123542 PX723542 PX723557 PX723566 PX723593 PX723594 DY223596 PX723596 PX723742 PX723743 PX723745 PX723820 PX725125 PX792653 PX796490 . PX797516 PX807530 ABUS PX807532 PX807539 PX807540 PX809486 PX820261 PX820262 PX820263 * PX820266 FABRUZDO PX820728 PX820728 PXB20834 ND 20854 PX823385 PX823386 PX823978 PX823978 PX1004683 6 7 WB660 wBobu 8 WB660 9 9 WB660 10 WB660 11 WB660 12WDEO 12 WB500 12 WRECO 13 WB660 14 WB500 15 WOCCO 15 WB660 we 16 WB500 4 W 17 WB500 unce 18 WB660 10 m 19 WB500 wcc 20 WB660 MO 21 WB500 waren 22 WB500 - 23 WB660 24 WB500 e 25 WB660 ince 26 WB660 27 WB660 - Wood 28 WB660 29 WB500 29 WB500 30 W3660 30 WB660 31 W1660 32 W3660 33 WB500 34 WR500 34 WB500 25 WOCO 35 WB500 26 WREEN 36 WB660 2 WRCON 37 WB500 38 WB500 39 WB660 40 WB660 41 WB660 44 wed 42 WB500 Arm 320 Arm 35 Foot I. Pin 10 Bole Bolt Large Bolt Small Bolt Socket Socket Leg Foot Sq Nut : Rivet Use: 1004688-16 . Nut Wing Spring 3a Grip Pin 12 . Pin 33 Vice Screw Sa 4 Total Price - $14.29 $7.96 $2.99 $53.11 $2.49 $14.64 $7.64 $ $1.66 $2.59 $ $12.96 1 $1.99 $17.28 $17.99 $3.98 $10.36 $1.99 410 $4.18 $12.44 . $8.36 $8.36 dar $15.28 402 $10.36 43 $4.32 $2.99 $3.98 Side $3.32 11.10 $44.18 $7.00 8 $53.11 $2.49 $1.83 $ $1.91 $1.66 $ $2.59 $3.24 $1.99 $1 og $216 $2.16 $1.99 $2.59 $1.99 $2.09 $3.11 $2.09 . $2.09 $1.91 $2.59 $2.16 $2.99 $1.99 $1.66 $22.09 . $3.50 11.79 $1.74 $15.59 $12.64 $2.39 $2.39 $14.29 $1.99 $1 gg $1.99 $9.29 $2.29 $1.66 $1.83 $24.14 $24.14 $14.29 4 2 2 1 2 D 2 2 2 Vice Screw Sa 8 1 1 Washer Special $13.92 Block 12/A Block Front 1 2 2 1 1 2 2 2 $15.59 $12.64 $4.78 $14.29 $ $3.98 $3.98 $2.29 $6.64 $14.64 $24.14 $24.14 $28.58 RH Nut Block Rear Knob Koh Knob Single Grip Screw 2.4mm Screw Special Vice Jaw Front Vice Jaw Front Block Rear 14-X - 1 4 8 1 1 1 2 43 WB500 44 45 Las 46 42 47 48 Information Labour Costs P HR Fixed Costs BOM CVP Analysis Profit Volume Forecast Section D 19 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.) O O O O O O 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 A B C D E F G 4 Fixed Costs WB500 $340.00 5690 WB660 $470.00 3755 Vary-with-Revenue Costs Sales Commissions 3.5%) Weighted Average Contribution Margin Break Even Units (weighted) WB500 WB660 $16.44 $16.44 $18.76 $18.76 Cost Volume Profit Analysis 2 3 Product Information 5 Unit Sales Price 6 Units Manufactured and Sold 7 Sales Mix (% of total units sold) ) 8 9 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 22 23 24 25 26 27 28 29 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 30 31 32 33 34 35 36 37 38 39 40 41 42 43 AA Information Labour Costs HR Fixed Costs BOM CVP Analysis Profit Volume Forecast Inventory + Fixed Costs Fixed Cost Items Rent Utilities Admin Costs Insurance $ $ $ $ Quarter 1 32,300.00 $ 3,560.00 $ 7,215.00 $ 2,580.00 $ Quarter 2 32,300.00 $ 3,560.00 $ 7,430.00 $ 3,200.00 $ Quarter 3 33,398.20$ 3,560.00 $ 7,220.00 $ 2,580.00 $ Quarter 4 Quarterly Average 33,398.20 $ 32,849.10 3,560.00 $ 3,560.00 7,360.00 $ 7,306.25 3,200.00 $ 2,890.00 % of Total 70% 8% 16% 6% Quarterly Totals $ 45,655.00 $ 46,490.00 $ 46,758.20 $ 47,518.20 Total Fixed Costs $ 186,421.40 Highest Quarterly Admin Costs: Average Monthly Rent: Rent % Increase: $ $ 46,758.20 32,800.00 3.4% A B D E F G H J K L M Bill of Materials 1 2 3 Product Quantity Price Each $14.29 1 4 W3660 5 WB660 6 WB660 4 1 1 $1.99 Product Decsription Block Rear 14-X Spring 5b Latch Bracke Axle an Mine $2.99 1 Bracket 552 1 8 4 1 1 1 1 Nut Super Large Foot 2593 4 1 1 g 8 2 4 1 - 2 2 4 4 4 Part Code PX1004683 PX242979 PX373195 PX374089 PX374089 PX37450 PX374561 PX577199 PX723206 PX723541 F123542 PX723542 PX723557 PX723566 PX723593 PX723594 DY223596 PX723596 PX723742 PX723743 PX723745 PX723820 PX725125 PX792653 PX796490 . PX797516 PX807530 ABUS PX807532 PX807539 PX807540 PX809486 PX820261 PX820262 PX820263 * PX820266 FABRUZDO PX820728 PX820728 PXB20834 ND 20854 PX823385 PX823386 PX823978 PX823978 PX1004683 6 7 WB660 wBobu 8 WB660 9 9 WB660 10 WB660 11 WB660 12WDEO 12 WB500 12 WRECO 13 WB660 14 WB500 15 WOCCO 15 WB660 we 16 WB500 4 W 17 WB500 unce 18 WB660 10 m 19 WB500 wcc 20 WB660 MO 21 WB500 waren 22 WB500 - 23 WB660 24 WB500 e 25 WB660 ince 26 WB660 27 WB660 - Wood 28 WB660 29 WB500 29 WB500 30 W3660 30 WB660 31 W1660 32 W3660 33 WB500 34 WR500 34 WB500 25 WOCO 35 WB500 26 WREEN 36 WB660 2 WRCON 37 WB500 38 WB500 39 WB660 40 WB660 41 WB660 44 wed 42 WB500 Arm 320 Arm 35 Foot I. Pin 10 Bole Bolt Large Bolt Small Bolt Socket Socket Leg Foot Sq Nut : Rivet Use: 1004688-16 . Nut Wing Spring 3a Grip Pin 12 . Pin 33 Vice Screw Sa 4 Total Price - $14.29 $7.96 $2.99 $53.11 $2.49 $14.64 $7.64 $ $1.66 $2.59 $ $12.96 1 $1.99 $17.28 $17.99 $3.98 $10.36 $1.99 410 $4.18 $12.44 . $8.36 $8.36 dar $15.28 402 $10.36 43 $4.32 $2.99 $3.98 Side $3.32 11.10 $44.18 $7.00 8 $53.11 $2.49 $1.83 $ $1.91 $1.66 $ $2.59 $3.24 $1.99 $1 og $216 $2.16 $1.99 $2.59 $1.99 $2.09 $3.11 $2.09 . $2.09 $1.91 $2.59 $2.16 $2.99 $1.99 $1.66 $22.09 . $3.50 11.79 $1.74 $15.59 $12.64 $2.39 $2.39 $14.29 $1.99 $1 gg $1.99 $9.29 $2.29 $1.66 $1.83 $24.14 $24.14 $14.29 4 2 2 1 2 D 2 2 2 Vice Screw Sa 8 1 1 Washer Special $13.92 Block 12/A Block Front 1 2 2 1 1 2 2 2 $15.59 $12.64 $4.78 $14.29 $ $3.98 $3.98 $2.29 $6.64 $14.64 $24.14 $24.14 $28.58 RH Nut Block Rear Knob Koh Knob Single Grip Screw 2.4mm Screw Special Vice Jaw Front Vice Jaw Front Block Rear 14-X - 1 4 8 1 1 1 2 43 WB500 44 45 Las 46 42 47 48 Information Labour Costs P HR Fixed Costs BOM CVP Analysis Profit Volume Forecast
