Please use excel and show work

Diego Company manufactures one product that is sold for $72 per unit in two geographic regionsthe East and West regions. The following information pertains to the companys first year of operations in which it produced 43,000 units and sold 38,000 units.

Variable costs per unit:
Manufacturing:
Direct materials	$	22
Direct labor	$	14
Variable manufacturing overhead	$	3
Variable selling and administrative	$	5
Fixed costs per year:
Fixed manufacturing overhead	$	774,000
Fixed selling and administrative expense	$	346,000

The company sold 28,000 units in the East region and 10,000 units in the West region. It determined that $170,000 of its fixed selling and administrative expense is traceable to the West region, $120,000 is traceable to the East region, and the remaining $56,000 is a common fixed expense. The company will continue to incur the total amount of its fixed manufacturing overhead costs as long as it continues to produce any amount of its only product.

Direct materials	$ 22.00
Direct Labor	$ 14.00
Variable manufacuring overhead	$ 3.00
Total cost per unit	$ 39.00
Direct materials	$ 22.00
Direct Labor	$ 14.00
Variable manufacturing overhead	$ 3.00
Fixed manufacturing overhead	$ 18
Total cost per unit	$ 57.00
Sales	72
Variable costs
Direct materials	$ 22.00
Direct labor	$ 14.00
Variable manufacuring overhead	$ 3.00
Variable selling and administative	$ 5.00
Total variable costs	$ 44.00
Contribution marjin	$ 28.00
Number of units sold	$ 38,000.00
Total Contribution marjin	$ 1,064,000.00
Total contribution marjin	$ 1,064,000.00
Fixed expense
Fixed manufacturing overhead	$ 774,000
Fixed selling and administrative expense	$ 346,000	$ 1,120,000
Net operating income	$ (56,000.00)

5. Gross Margin Under Absorption costing
Sales ( 38,000 units x $ 72 )		$ 2,736,000
Less: COGS (38000 units * $57)		$ 2,166,000
Contribution margin		$ 570,000
Less: S/A exp
Variable S/A (38000 units * $5)	($ 190,000)
Fixed S/A	($ 346,000)	$ 536,000
6. Net operating Gain under Absorptioncosting		$ 34,000
7. Difference in Net income :
Net operating Loss under variable costing		($ 56,000 )
Net operating Gain under Absorption costing		$ 34,000
Difference ( Diff is always the absolute change )		$ 22,000
8(a) Break even point in units = Total fixed costs/Contribution margin per unit
Total fixed costs = Fixed manufacturing overhead + Fixed selling and administrative expenses = $774000 + $346000 = $1120000
Contribution margin per unit = SP VC = $72 - ($22 + $14 + $3 + $5) = $72 - $44 = $28
Break even point = $1120000/$28 =		40000 units
EXTRAAA ANS
(b) Answer: Above The break even point is 40000 units while the actual sales volume is 38000 units. Thus, the break even point is above the actual sales volume.
9. Breakeven point (units ) =Fixed cost /contribution per unit $1120000/$28 =		40000 units
Breakeven point in units will remain same even if number of units sold are reversed,the overall breakeven point will remain same .This is so because with same selling price ,variable cost per unit and fixed cost across both region ,the breakeven point will not be affected .

10. What would have been the companys variable costing net operating income (loss) if it had produced and sold 38,000 units?

11. What would have been the companys absorption costing net operating income (loss) if it had produced and sold 38,000 units?

12. If the company produces 5,000 fewer units than it sells in its second year of operations, will absorption costing net operating income be higher or lower than variable costing net operating income in Year 2? Higher or Lower

13. Prepare a contribution format segmented income statement that includes a Total column and columns for the East and West regions.

14. Diego is considering eliminating the West region because an internally generated report suggests the regions total gross margin in the first year of operations was $20,000 less than its traceable fixed selling and administrative expenses. Diego believes that if it drops the West region, the East region's sales will grow by 5% in Year 2. Using the contribution approach for analyzing segment profitability and assuming all else remains constant in Year 2, what would be the profit impact of dropping the West region in Year 2?

15. Assume the West region invests $33,000 in a new advertising campaign in Year 2 that increases its unit sales by 20%. If all else remains constant, what would be the profit impact of pursuing the advertising campaign?