Utilization of Constrained Resources Using Excel Backyard Helpers, Inc. is a small manufacturing company with 18 different gardening tools in its product line. All of the products are fabricated using the same equipment. Recently, sales demand has increased. Unfortunately, Backyard Helpers cannot produce enough products with existing equipment to meet that demand. Facilities can be expanded, and new equipment purchased, but it will be at least two years before that happens. The production manager needs to make production scheduling decisions now. The file linked here includes assumed information about demand, sales price, cost, and fabrication time on the shared equipment for each of Backyard Helpers products. Maximum machine time is 40,500 minutes per month. The demand for all products is spread equally throughout the month. Fixed costs (manufacturing, selling, and administrative) total $755,750 per month. Backyard Helpers maintains no inventory of finished goods: all units produced are sold during the month.

a. Ignore machine time limits in answering the following:

1. Which product has the highest contribution margin per unit? AnswerA1590A1590SA2000A2000SJK369JK745LWW7891LWW8025LWW9338LWW9799R25R28R30R25-DR28-DR30-DTWZ225TWZ399

How many units of that product should be produced each month? Answer

2. If Backyard could meet demand, what would be the total net operating income per month?

Note: Do not use a negative sign with your answers.

Total contribution margin	Answer
Fixed costs	Answer
Net operating income	Answer

b. If demand was unlimited for all products, which product should Backyard Helpers produce? AnswerA1590A1590SA2000A2000SJK369JK745LWW7891LWW8025LWW9338LWW9799R25R28R30R25-DR28-DR30-DTWZ225TWZ399

c. Given the maximum number of machine minutes per month and the expected demand per month, use Solver in Excel to answer the four questions that follow. To prepare your worksheet to use the Solver tool, add columns with formulas for (1) contribution margin per unit, (2) total quantity in units, (3) total machine minutes, and (4) total contribution margin. In the Solver tool window, enter the cell that you would like to maximize (Hint: It is a single cell), identify the variables (Hint: Use quantity in units column) and enter the constraints (Hint: There are two constraints relating to demand and machine minutes).

1. How many of the following products should be produced each month?

Note: Round quantities to the nearest whole unit.

i. R25 Answer

ii. JK369 Answer

2. Which products would be temporarily eliminated from Backyard Helpers product line under the Excel solution? AnswerA1590 and R28JK369 and R25-DR25-D and R28R25 and R28-DR25-D and R30R28 and R30-DR28 and TWZ399 3. What is the total net operating income per month given the machine time constraints and the quantities determined by Solver? $Answer

4. What is the maximum amount Backyard Helpers should be willing to pay to rent fabrication time from another company? (Assume transportation and other costs would total $50,000.) $Answer

d. In Questions c2, you identified products that would be temporarily eliminated if Backyard followed the Excel solution. Would management decide to continue to produce some of those products even if it means reducing the supply of some of the other products? Which of the following statements is true?

1. Eliminating the product would be recommended in all scenarios. 2. There are some scenarios where producing the products would still be desirable. 3. Data analysis is an objective process with rarely a need to deviate from the results True statement: Answer123

	Expected	Sales	Direct	Direct	Variable	Variable	Machine
	Demand per	Price Per	Materials	Labor	MOH Per	Selling	Minutes
Products	Month	Unit	Per Unit	Per Unit	Unit	Per Unit	Per Unit
A1590	1250	64.5	14.5	7.5	3.5	1.3	1.3
A1590S	1375	64.8	16	7.5	3.5	1.35	1.32
A2000	1550	68	17	7.5	3.5	1.5	1.4
A2000S	1410	70	18.5	7.5	3.5	1.6	1.42
JK369	625	120	65	10	6	2.5	1.5
JK745	750	140	80	10	6	3	1.55
LWW7891	3125	35	8	4	1.6	0.65	0.9
LWW8025	3250	37.5	9.5	4	1.6	0.7	0.8
LWW9338	3000	40	11	4	1.6	0.7	0.75
LWW9799	3400	45	12	4	1.6	0.75	1
R25	3250	15	2	2	1.4	0.25	0.5
R28	7500	18	2.5	2	1.4	0.3	0.75
R30	3875	22	3	2	1.4	0.35	0.8
R25-D	7500	18	3.5	2	1.4	0.3	0.6
R28-D	9375	21	4	2	1.4	0.35	0.65
R30-D	6250	25	5	2	1.4	0.4	0.7
TWZ225	1560	50	14	6	3.5	1.2	1.3
TWZ399	1875	55	16	6.5	3.5	1.25	1.35