Reference no: EM132296207

Assignment -

Case Info: Karake, Inc. has received a special single customer order for its ErgoBed product line consisting of 1,200 super light beds. The customer specifically asked that each bed be exactly 100 pounds (Ibs). The customer will pay $170,000 for the whole order. Your boss tells you that the customer has demanded that out of the 1,200 beds, at least 19% (228 beds) should be ErgoBabe, 9% (108 beds) ErgoTwin, 6% (72 beds) ErgoQueen and 11% ErgoKing (132 beds). The distribution of the rest of the order is up to you.

Since this order was unusual for the company, Karake, Inc. had to come up with a new way to manufacture the beds. Their research department came up with 6 different possible materials: Wrought Iron, Bronze, Laminates, Pine, Mahogany and Oak. They noted that not every single material must be used. The research team also came up with the necessary distribution of each materials among the different beds. Each pound of the material is distributed among the 4 beds as shown in the table below. For example, if Karake, Inc. had 10 pounds of Wrought Iron, 1 would be used in the production of ErgoBabe, 1 in ErgoTwin, 4 in ErgoQueen and the leftover 4 in ErgoKing. The table below also contains the per pound cost of each material used in production. 1 pound of Wrought Iron costs Karake, Inc. $0.95 to buy.

NOTE THIS CHANGE: WHEN IT STATES ABOVE "The distribution of the rest of the order is up to you," I spread the other 55% and the NEW percentages are: 19% ErgoBabe, 64% ErgoTwin, 6% ErgoQueen, and 11% ErgoKing to equal 100%. I entered this in the spreadsheet.

	ErgoBabe	ErgoTwin	ErgoQueen	ErgoKing	Per Pound Cost of Material
Wrought Iron	0.1	0.1	0.4	0.4	$0.95
Bronze	0.2	0.1	0.4	0.3	$1.05
Laminate	0.5	0.2	0.1	0.2	$1.15
Pine	0.4	0.3	0.2	0.3	$0.8
Mahogany	0.4	0.2	0.2	0.2	$0.9
Oak	0.1	0.2	0.4	0.3	$1.35

Karake, Inc. currently has 20,000 lbs of Wrought Iron, 12,500 lbs of Bronze, 15,000 lbs of Laminates, 20,000 lbs of Pine, 35,0001bs of Mahogany and 50,000 lbs of Oak available in stock. The objective of the company is to maximize profits while meeting the demands set by its customer. Provide the overall distribution of the order in percentages not number of beds.

a. Change the name of the Excel File as instructed above. Change Sheet1 name to "Optimization".

b. Insert formulas to compute the total cost of the mix and the total profit.

c. Create a constraints table in the worksheet below cell B21 that identifies the constraints in this problem. Also, write the objective function in cell H18 and explain what you are trying to do with this function.

d. Use solver to determine the Optimal Mix and the Optimal Value of the objective function.

e. What is the Optimal Mix & Solution? Write your answer in cell H20.

f. Which constraints are binding? Highlight binding constraints in Yellow, in addition to writing your answer in cell H22. Additionally, explain what is meant by a binding constraint.

g. Produce a Sensitivity Report.

h. How much can the price of Wrought Iron decrease without changing the Optimal Mix? Calculate the new Optimal Value based on the changed price; DO NOT RESOLVE THE PROBLEM. Write your answer in cell H24.

i. Assume from now on, this new customer will become the sole customer for the ErgoBeds line. Which one material would you recommend not be kept in stock? Write your answer in cell H26.

Attachment:- Data File.rar