A B D E F G H K M N O P 1 Q1. Assume Altec Manufacturing Inc. has greatly exceeded the budget target. Its CVP Operating Income Statement for 2022 is as follows: 2 SP Sales Volume 3 Sales $ 12,750,000 $ 150.00 per unit 85,000 Units 4 Variable costs Variable Cost per unit 5 Cost of goods sold $ 7,815,750 $ 91.95 per unit 6 Selling & administrative expenses 1,553,800 $ 18.28 per unit 7 Total variable costs $ 9,369,550 8 9 Contribution margin $ 3,380,450 11 Fixed costs 12 Cost of goods sold 1,453,494 13 Selling & administrative expenses 1,148,806 2,602,300 14 Total Fixed costs 15 16 Operating income $ 778,150 17 Required: 18 What is the seling price Altec needs to charge to earn the target operating income of $1,500,000? It expects to produce and sell 90,000 units in 2023, variable cost per unit will increase by 10% compared with 2022, fixed costs remain unchanged. 19 Please solve the problem using excel Goal Seek Funcions. 20 21 Solutions: 22 Selling Price 23 Variable cost 24 Cost of goods sold 25 Selling & administrative expenses per unit 26 Total Variable costs 27 CM per unit 28 ja 90,000 Unit 29 30 Altec Manufacturing Inc. Projected Income Statement 31 Projected Income Statement 32 Year Ending December 31, 2023 33 34 Sales Revenue 35 Variable costs 36 Contribution margin 37 Fixed costs 38 Net operating income 39 40 K L M N O Q R S S W Y Z A B C D E F G H 1 1 Q2. Assume Altec Manufacturing Inc. has greatly exceeded the budget target. Its CVP Operating Income Statement for 2022 is as follows: 2 SP Sales Volume 3 Sales $ 12,750,000 $ 150.00 per unit 85,000 Units 4 Variable costs Variable Cost per unit 5 Cost of goods sold $ $ 7,815,750 $ 91.95 per unit 6 Selling & administrative expenses 1,553,800 $ 18.28 per unit 7 Total variable costs $ 9,369,550 8 Contribution margin $ 3,380,450 11 Fixed costs 12 Cost of goods sold 1,453,494 13 Selling & administrative expenses 1,148,806 2,602,300 14 Total Fixed costs 15 16 Operating income $ 778,150 17 Required: 18 At what level of unit sales would Altec be indifferent compared with Q 1? In other words, at what level of unit sales would Altec earn the same target operating income of $1,500,000? Assume the CEO expects to acquired a new equipment which would employ a computer expert system that intergrates the production process, and it would significantly reduce labour costs. 19 Assume the selling price is sill $167 per unit. Under this new cost structure plan, variable cost will reduce by 15%, and fixed cost will increased by 40%. 20 Please solve the problem using excel Goal Seek Funcions 21 22 Solutions: 23 Selling Price $ $ 167 24 Variable cost 25 Cost of goods sold 26 Selling & administrative expenses per unit 27 Total Variable costs 28 CM 29 Q Unit 30 31 Sales Revenue 32 Variable costs 33 Contribution margin 34 Fixed costs 35 Net operating income 36 per unit A B K D E F G H Its CVP Operating Income Statement for 2022 is as follows: $ 85,000 Units 1 Q3. Assume Altec Manufacturing Inc. has greatly exceeded the budget target. 2 3 Sales 4 Variable costs 5 Cost of goods sold $ 6 Selling & administrative expenses 7 Total variable costs 7,815,750 1,553,800 12,750,000 $ 150.00 per unit Variable Cost per unit $ 91.95 per unit $ 18.28 per unit 9,369,550 $ 8 Contribution margin $ 3,380,450 11 Fixed costs 12 Cost of goods sold 1,453,494 13 Selling & administrative expenses 1,148,806 2,602,300 14 Total Fixed costs 15 16 Operating income $ 778,150 17 Required: : 18 At what level of unit sales and selling price would Altec earn the same target operating income of $2,000,000? 19 Assume the selling price should be between $165 and $185, and the level of unit sales can not be more than 200,000 due to the constrained resources. Variable cost per unit and fixed cost remained unchanged. 20 Please solve the problem using excel Solver Funcions 21 22 Solutions: 23 Selling Price 24 o 25 Variable cost per unit per unit Unit 26 27 Cost of goods sold 28 Selling & administrative expenses 29 Total Variable costs 30 CM 31 32 33 Sales Revenue 34 Variable costs 35 Contribution margin 36 Fixed costs 37 Net operating income 39 40 41 M hours A B C D E F G H I J L 1 Q4. Altec Manufacturing Inc. is planing to produce and sell two new products: AS 106 and BS 107. The resources(in kg and hours) for producing each type of productc are as follows: 2 3 3 AS 106 BS 104 4 Per-unit resource requirements Available 5 Plastic 1 2 2 8,000 kg 6 Production 4 5 29,000 hours 7 Assembly 8 9 9 55,000 8 9 Required: Compute the optimal numbem of products of each type that Altec Manufacturing Inc. should make and sell to maximize operating income using Excel Solver Functions. 10 11 12 Altec Manufacturing Inc. 13 14 AS106 BS107 15 Number to make Total Contribution Margin 16 Unit Contribution Mar 165 195 17 18 Resources Per-unit resource requirements Used Available 19 Plastic 1 2 8,000 kg 20 Production 4 4 5 5 29,000 hours 21 Assembly 8 9 55,000 hours 22 23 NE