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.

	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	1,250	64.50	14.50	7.50	3.50	1.30	1.30
A1590S	1,375	64.80	16.00	7.50	3.50	1.35	1.32
A2000	1,550	68.00	17.00	7.50	3.50	1.50	1.40
A2000S	1,410	70.00	18.50	7.50	3.50	1.60	1.42
JK369	625	120.00	65.00	10.00	6.00	2.50	1.50
JK745	750	140.00	80.00	10.00	6.00	3.00	1.55
LWW7891	3,125	35.00	8.00	4.00	1.60	0.65	0.90
LWW8025	3,250	37.50	9.50	4.00	1.60	0.70	0.80
LWW9338	3,000	40.00	11.00	4.00	1.60	0.70	0.75
LWW9799	3,400	45.00	12.00	4.00	1.60	0.75	1.00
R25	3,250	15.00	2.00	2.00	1.40	0.25	0.50
R28	7,500	18.00	2.50	2.00	1.40	0.30	0.75
R30	3,875	22.00	3.00	2.00	1.40	0.35	0.80
R25-D	7,500	18.00	3.50	2.00	1.40	0.30	0.60
R28-D	9,375	21.00	4.00	2.00	1.40	0.35	0.65
R30-D	6,250	25.00	5.00	2.00	1.40	0.40	0.70
TWZ225	1,560	50.00	14.00	6.00	3.50	1.20	1.30
TWZ399	1,875	55.00	16.00	6.50	3.50	1.25	1.35

a. Ignore machine time limits in answering the following: 1. Which product has the highest contribution margin per unit? How many units of that product should be produced each month? 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. b. If demand was unlimited for all products, which product should Backyard Helpers produce? 1. How many of the following products should be produced each month? Note: Round quantities to the nearest whole unit. i. R25 K ii. JK369 2. Which products would be temporarily eliminated from Backyard Helpers product line under the Excel solution? 3. What is the total net operating income per month given the machine time constraints and the quantities determined by Solver? 1 k