I have an excel assignment and need help with the below questions, from D1 to D12. Really need help
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 + Head Office Staff Date: 14/4/21 Employee ID Employee Name Job Title Office Start Date Years Service Days Leave Phone Extension S12716 Sam Mason Manager Melbourne 5.6 18 2782 S12717 Lemin Lu Admin Melbourne 21/9/15 9/10/15 26/12/15 5.5 15 2379 S12720 James Viney Engineer 5.3 5 2459 S12722 Will Chang Admin Sydney 3.0 9 2369 20/4/18 5/5/18 S12723 Admin Melbourne 2.9 23 2729 Divika Kapoor Bin Wang S12724 Admin 26/5/18 2.9 24 2148 Sydney Sydney S12725 Andy Forbes Admin 29/11/19 1.4 6 2170 S12726 Tom Peters Engineer Melbourne 23/1/20 1.2 19 2570 S12727 Elan Forbes Manager Sydney 3.0 16 2849 S12728 Phil Smith Admin Melbourne 15/4/18 1/4/18 22/4/16 3.0 30 2984 S12729 Jason Kerr Admin 5.0 10 2239 S12730 Chris Powe Admin Sydney Sydney Sydney 4.8 5 2468 13/7/16 21/6/20 S12733 Jordan Riddle Admin 0.8 9 2318 Exrension for: Sam Mason 2782 Use the data above to answer the following multiple choice questions. For each question change the correct option to TRUE (it will change to green), leave the others FALSE 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% 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 + Head Office Staff Date: 14/4/21 Employee ID Employee Name Job Title Office Start Date Years Service Days Leave Phone Extension S12716 Sam Mason Manager Melbourne 5.6 18 2782 S12717 Lemin Lu Admin Melbourne 21/9/15 9/10/15 26/12/15 5.5 15 2379 S12720 James Viney Engineer 5.3 5 2459 S12722 Will Chang Admin Sydney 3.0 9 2369 20/4/18 5/5/18 S12723 Admin Melbourne 2.9 23 2729 Divika Kapoor Bin Wang S12724 Admin 26/5/18 2.9 24 2148 Sydney Sydney S12725 Andy Forbes Admin 29/11/19 1.4 6 2170 S12726 Tom Peters Engineer Melbourne 23/1/20 1.2 19 2570 S12727 Elan Forbes Manager Sydney 3.0 16 2849 S12728 Phil Smith Admin Melbourne 15/4/18 1/4/18 22/4/16 3.0 30 2984 S12729 Jason Kerr Admin 5.0 10 2239 S12730 Chris Powe Admin Sydney Sydney Sydney 4.8 5 2468 13/7/16 21/6/20 S12733 Jordan Riddle Admin 0.8 9 2318 Exrension for: Sam Mason 2782 Use the data above to answer the following multiple choice questions. For each question change the correct option to TRUE (it will change to green), leave the others FALSE 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%
