Question

Special Order; Opportunity Cost Alton Inc. is working at full production capacity producing 20,000 units of a unique 11-23 product. Manufacturing costs per unit for the product are as follows: $9 Direct materials Direct labor Manufacturing overhead 10 Total manufacturing cost per unit $27 The per-unit manufacturing overhead cost is based on a $4 variable cost per unit and $120,000 fixed costs. The nonmanufacturing costs, all variable, are $8 per unit, and the sales price is $45 per unit. Sports Headquarters Company (SHC) has asked Alton to produce 5,000 units of a modification of the new product. This modification would require the same manufacturing processes. However, because of the nature of the proposed sale, the estimated nonmanufacturing costs per unit are only $4 (not $8). Alton would sell the modified product to SHC for $35 per unit. Required Set up an Excel spreadsheet to answer the following questions: 1. What is the impact on short-term operating profit of accepting the special sales order from SHC? 2. Suppose that Alton Inc. had been working at less than full capacity to produce 16,000 units of the product when SHC made the offer. What is the minimum price per unit that Alton should accept for the modified product under these conditions? (Round answer to 2 decimal places.) Explain. 3. To prove (or provide support for your answer to Requirement 2), use Goal Seek in Excel to determine the minimum price (per unit) that Alton should accept for the special sales order.

Answer 1

	Existing scenerio for 20000 units
			Per unit	Total
		Revenue	$45	$900,000
		Less: Costs
		Direct Material	$9	$180,000
		Direct Labour	$8	$160,000
		Fixed manufacturing overhead	$6	$120,000
		Variable manufacturing overhead	$4	$80,000
		Non-Manufacturing overhead	$8	$160,000
		Profit	$10	$200,000
1	New proposal for 5000 units		Modified product		Existing product
			Units	5000	Units	15000	Total
			Per unit	Total	Per unit	Total
		Revenue	$35	$175,000	$45	$675,000
		Less: Costs
		Direct Material	$9	$45,000	$9	$135,000
		Direct Labour	$8	$40,000	$8	$120,000
		Fixed manufacturing overhead	$6	$30,000	$6	$90,000
		Variable manufacturing overhead	$4	$20,000	$4	$60,000
		Non-Manufacturing overhead	$4	$20,000	$8	$120,000
		Profit	$4	$20,000	$10	$150,000	$170,000
		Hence, by accepting the modified product and selling it @35 would result in decrease in operating profit of $30,000.
2		Alton Inc. is operating at 16,000 units capacity.
		If it accepts production of modified 5,000 units then it can produce only 15,000 of the existing product.
		Hence, there will be a loss of revenue on 1,000 units
		Hence, revenue required to earned on 1000 units=	$45,000	(45*1000)
		Revenue required to be earned on 4000 units =	$124,000	(31*4000)
		total revenue	$169,000
		Units	$5,000
		Hence price per unit =	$33.80	(169,000/5000)
3	Goal seek
	Step -1	Selling price	$0.00
		Units sold	5000
		Revenue	$0
		Total revenue required(as calculated in part 2)	$169,000
			-$169,000
	Step -2	Open goal seek from Data tab and insert value as
		Selling price	$33.80
		Units sold	5000
		Revenue	$169,000
		Total revenue required(as calculated in part 2)	$169,000
			$0

3 Goal seek Step -1 Selling price Units sold Revenue Total revenue required(as calculated in part 2) $0.00 5000 $0 $169,000 -$169,000 Goal Seek Set cell: $D$68 Step-2 Open goal seek from Data tab and insert value as To value: By changing cell: $D$64 Selling price Units sold Revenue Total revenue required(as calculated in part 2) --- $0.00 5000 $0 $169,000 $169,000 OK OK Cancel