Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Please provide with excel formulas. Thank you Section D 21 The following are to be completed in the CVP Analysis Worksheet Click in cell B3,
Please provide with excel formulas.
Thank you
Section D 21 The following are to be completed in the CVP Analysis Worksheet Click in cell B3, note that you can choose a product from the drop down list. This allows the company to select which product they wish to perform a CVP for. Ensure that calculations will return the correct answers regardless of which product is selected. Question Instruction Marks Done D1 2 O 0 0 0 D5 In B4 use a formula to retrieve the Unit Sales Price from the Fixed Costs sheet for the product shown in B3. In B5 use a formula to retrieve the Unit Sale from the Fixed Costs sheet for the product shown in B3. In B6 use a formula to retrieve the Total Fixed Costs from the Fixed Costs sheet for the product shown in B3. In E5 use the information in the Labour Costs sheet to calculate the Direct Labour Cost per unit for the product shown in B3. In E6 use the information in the BOM to calculate the Direct Material Cost per unit for the product specified in B3. In E14 calculate the Variable Selling & Admin Cost per unit (as a %). In H4 calculate the Contribution Margin per Unit (this is the Unit Sales Price less the Variable Manufacturing Cost per unit and the Variable Selling & Admin Cost per unit) In H5 calculate your break-even volume (in units). Apply an appropriate rounding function to get the answer to 0 decimal places (Consider whether to use ROUND, ROUNDUP or ROUNDDOWN). (This answer is very important so double check it!) In H6 calculate the Break-Even Sales Value ($ sales or break even volume in units). 2 0 0 0 D9 010 0 In H9 to H14 calculate the Break-Even Variable Costs using the calculated figures in H5 or H6 (where appropriate). Note, each answer is worth / mark which will only be awarded if the answer and formula are correct. In H17 calculate the Break-Even Contribution Margin. In H18 calculate the Break-Even Profit Before Tax. (If we hadn't rounded Break Even Units this should be 0) D11 D12 0 0 B C H Cost Volume Profit Analysis 3 Product Name 4 Unit Sales Price Unit Sales 6 Total Fixed Costs $ $ X-800 330.00 330.00 Variable Costs per Unit Vary with Unit Costs Direct Labor Direct Material Factory Overhead Other Vary-with-Unit Costs Total Vary with Unit Costs Break-Even Analysis Contribution Margin Per Unit Break Even Volume in Units Break Even Sales Value 120.00 80.00 200.00 $ Vary-with-Revenue Costs Sales Commissions Other Vary-with-Revenue Costs Total Vary with Revenue Costs Break Even Variable Costs Direct Labor Direct Material Factory Overhead Other Vary-with-Unit Costs Sales Commissions Other Vary-with-Revenue Costs Total Variable Costs 5.0% 1.5% Income Tax State Tax Federal Tax 10.0% 5.0% Contribution Margin Profit Before Tax Fixed Costs and Pricing for the Year X-800 ZF300 5 7 8 9 Fixed Cost Items Rent Utilities Admin Costs Insurance | $ $ $ $ 62,000.00 14,000.00 25,100.00 12,300.00 $ $ $ $ 60,000.00 14,200.00 26,100.00 12,500.00 $ $ 11 Unit Sales Price 12 Total Units Sold 5,350.00 330 5,550.00 350Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started