A futon manufacturer makes two types of wood futon bed frames, Model A and Model B. The wood used for the frame is purchased from two sources, Supplier 1 and Supplier 2. To keep quality of the product high and consistent the manufacturer requires that each bed frame be made entirely out of the wood from either Supplier 1 or Supplier 2. Supplier 1 can supply a total of 7,500 board feet of wood and Supplier 2 can supply 10,000 board feet in the coming season. Due to the difference in quality, the amount of wood required to produce a frame is different. Model A requires either 10 board feet of wood from Supplier 1 or 20 board feet from Supplier 2, while Model B requires either 5 board feet of wood from Supplier 1 or 8 board feet from Supplier 2. Model A can be sold for $500 each and Model B for $300. Answer the following questions independently.
a. Formulate a spreadsheet model; and determine how the company can maximize its revenue. That is, specify how many units of each model to make and How much wood to purchase for each model from each supplier. What is the total revenue?
b. To attract different types of customers, the company wants to keep Model A between 25% and 40% of the total production. Modify the model and resolve the problem. Also generate a sensitivity analysis report for later use. Determine the new optimal (wood) purchase and (bed frame) production plan.
c. The material and processing costs per board foot of wood from Supplier 1 and Supplier 2 are $6 and $5, respectively. Finishing cost for each unit of Model A is $40 and for Model B is $80. Formulate the problem to maximize the total profit.
d. From question a, generate a parameter analysis report to show how the optimal solution and the total revenue are affected by the change of the price for Model A, ranging from $100 to $1500 with an increment of $50. How many different optimal solutions have appeared in that range of price for Model A? Show the range of price for each optimal solution.
e. For question b, generate a sensitivity analysis report. If the wood available from the two suppliers decreases by 1000 board feet each, will you be able to compute the impact of the total revenue from the report? Why or why not? What would the impact be? Would the optimal solution change?