NAME: Problem #2 INPUT: Product A Product B Variable cost of Variable selling &
ID: 2517388 • Letter: N
Question
NAME: Problem #2 INPUT: Product A Product B Variable cost of Variable selling & administrative expenses per unit Total Fixed cost of goods sold Total Fixed Selling & administrative expenses 83.00 $ 53.00 S 3.00 $ 46.00 20.00 1.00 NOTE:. Each of the highlighted cells should contain an Excel formula Formulas should use cell references to refer back to input data. ds sold per unit 100,000.00 90,000.00 For percentages, go out 2 places after the decimal & round to one place. For units, round to whole units. For money, round to nearest cent. Adjust spacing as needed. Be sure to check that your unit of measure is correct (e.g. units, dollars, etc.) PURPOSE To determine which product would yield the highest profit Product A Product B OUTPUT Contribution margin per unit Contribution margin percentage Break even point in units Break even point in sales dollars Number of units to be sold to earn profit of $50,000 The profit if the company sold 20,000 units REQUIRED: 1.Create the Excel formulas required for each output listed above. Ue cell references back to Input data. 2. If the company had the money to produce only one of the products. What would be your recommendation? Explain.Explanation / Answer
Answer 1. Product A Product B SP per Unit 83.00 46.00 Variable Costs: Cost of Goods Sold 53.00 20.00 Selling & Admn. Exp. 3.00 1.00 Total Variable Costs 56.00 21.00 Contribution Margin Per Unit 27.00 25.00 Contribution Margin Ratio 32.53% 54.35% Fixed Costs: Selling & Admn. Exp. 100,000.00 90,000.00 BEP (In Units) 3,704 3,600 BEP (In $) 307,407.41 165,600.00 BEP (In Units + Target Profit - $50,000) 4,444 4,400 Contribution Margin = Sales - Variable Costs Contribution Margin Ratio = Contribution / Sales BEP (In Units) = Fixed Costs / Contribution Margin per Unit BEP (In $) = Fixed Costs / Contribution Margin Ratio BEP (In Units + Target Profit) = (Fixed Costs + Target Profit) / Contribution Margin per Unit Income Statement Product A Product B No. of Units Sold 20,000 20,000 Contribution Margin Per Unit 27.00 25.00 Total Contribution 540,000.00 500,000.00 Fixed Costs 100,000.00 90,000.00 Net Operating Income 440,000.00 410,000.00 Answer 2. Company should produce Product B as it has higher Contribution Margin Ratio. As, it attains the BEP, it will produce more profit because of higher contribution margin ratio.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.