Hi tutors, I am having trouble understanding how to do these CVP analysis questions.
It is not a strong point of mine. Any help is appreciated.
mmwmmbwmp 10 i'|' NH NNi'I'I'iII'I'il I'CKDOO'Jmm-DUJ A Cost Volume Profit Analysis Product Information Unit Sales Price Units Manufactured and Sold Sales Mix (% oftotal units sold) Vary with U nit Costs Direct Material Direct Labor DtherVary-with-Unit Costs Total Varywith Unit Costs Break Even Analysis Contribution Margin Per Unit Break Even Point in Units/Product Break Even in Sales Dollars Fixed Costs Vary-with-Revenue Costs Sales Commissions Weighted Average Contribution Margin Break Even Units (weighted) Break Even Variable Costs Direct Material Direct Labor Other VarywithUnit Costs Sales Commissions Total Variable Costs Break Even Contribution Margin Break Even Prot Before Tax E Section D The following are to be completed in the CVP Analysis Worksheet D1 36 and C6 contain the number of units sold for the two different product types respectively. In 37 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. D2 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 311 and C11, use the information in the 30M to calculate the Direct material costs for one unit of each product. D4 In 312 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 317 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 319 and C19 use the previous result and the sales mix to work out the break-even point in units per product. D9 In 320 and C20 calculate the break even point in dollars for each product. 010 In F13 to F16 calculate the Break-Even Variable Costs for the two products. 011 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 than 0.) A B C D E G H Fixed Costs IN Fixed Cost Items Quarter 1 Quarter 2 Quarter 3 Quarter 4 Quarterly Average % of Total 4 Rent $ 30,400.00 $ 30,400.00 $ 31,464.00 $ 31,464.00 | $ 30,932.00 69% 5 Utilities 3,730.00 $ 3,730.00 3,730.00 3,730.00 3,730.00 8% 6 Admin Costs 6,990.00 7,200.00 7,200.00 $ 7,130.00 7,130.00 16% Insurance 3,120.00 2,900.00 3,120.00 S 2,900.00 S 3,010.00 7% 9 Quarterly Totals $ 44,240.00 44,230.00 $ 45,514.00 $ 45,224.00 10 11 Total Fixed Costs $ 179,208.00 12 13 Highest Quarterly Admin Costs: S 7,200.00 14 Average Monthly Rent: 10,310.67 15 Rent % Increase: 3039896.5% 16 17A CO 1 Labour Costs 2 4 Labour Costs per hour 25.00 5 6 Labour Hours per Unit WB500 1.4 8 WB660 2.1 9
