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. Products, Expected Demand per Month, Sales Price per Unit, Direct Material per Unit, Direct Labor per Unit, Variable MOH per Unit, Variable Selling per Unit, Machine Minutes per Unit.

A$15901,25064\mathrm{.}5014\mathrm{.}507\mathrm{.}503\mathrm{.}501\mathrm{.}301\mathrm{.}30$

A$1590$S $1,37564\mathrm{.}8016\mathrm{.}007\mathrm{.}503\mathrm{.}501\mathrm{.}351\mathrm{.}32$

A$20001,55068\mathrm{.}0017\mathrm{.}007\mathrm{.}503\mathrm{.}501\mathrm{.}501\mathrm{.}40$

A$2000$S $1,41070\mathrm{.}0018\mathrm{.}507\mathrm{.}503\mathrm{.}501\mathrm{.}601\mathrm{.}42$

JK$369625120\mathrm{.}0065\mathrm{.}0010\mathrm{.}006\mathrm{.}002\mathrm{.}501\mathrm{.}50$

JK$745750140\mathrm{.}0080\mathrm{.}0010\mathrm{.}006\mathrm{.}003\mathrm{.}001\mathrm{.}55$

LWW$78913,12535\mathrm{.}008\mathrm{.}004\mathrm{.}001\mathrm{.}600\mathrm{.}650\mathrm{.}90$

LWW$80253,25037\mathrm{.}509\mathrm{.}504\mathrm{.}001\mathrm{.}600\mathrm{.}700\mathrm{.}80$

LWW$93383,00040\mathrm{.}0011\mathrm{.}004\mathrm{.}001\mathrm{.}600\mathrm{.}700\mathrm{.}75$

LWW$97993,40045\mathrm{.}0012\mathrm{.}004\mathrm{.}001\mathrm{.}600\mathrm{.}751\mathrm{.}00$

R$253,25015\mathrm{.}002\mathrm{.}002\mathrm{.}001\mathrm{.}400\mathrm{.}250\mathrm{.}50$

R$287,50018\mathrm{.}002\mathrm{.}502\mathrm{.}001\mathrm{.}400\mathrm{.}300\mathrm{.}75$

R$303,87522\mathrm{.}003\mathrm{.}002\mathrm{.}001\mathrm{.}400\mathrm{.}350\mathrm{.}80$

R$25-$D $7,50018\mathrm{.}003\mathrm{.}502\mathrm{.}001\mathrm{.}400\mathrm{.}300\mathrm{.}60$

R$28-$D $9,37521\mathrm{.}004\mathrm{.}002\mathrm{.}001\mathrm{.}400\mathrm{.}350\mathrm{.}65$

R$30-$D $6,25025\mathrm{.}005\mathrm{.}002\mathrm{.}001\mathrm{.}400\mathrm{.}400\mathrm{.}70$

TWZ$2251,56050\mathrm{.}0014\mathrm{.}006\mathrm{.}003\mathrm{.}501\mathrm{.}201\mathrm{.}30$

TWZ$3991,87555\mathrm{.}0016\mathrm{.}006\mathrm{.}503\mathrm{.}501\mathrm{.}251\mathrm{.}35$

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. Question 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? i$.$ R$25$ ii$.$ JK$369$

Note: Round quantities to the nearest whole unit. $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? $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.)!!!$ PLEASE SHOW HOW YOU SOLVED QUESTIONS $1,2,3,$& $4$ WITH SOLVER $($ex: what was the set objective, variable cells, and constrains$)!!!$