University Ceramics manufactures plates, mugs, and steins that include the campus name and logo for sale in campus bookstores. The time required for each item to go through the two stages of production (molding and finishing), the material required (clay), and the corresponding unit profits are given in the following table, along with the amount of each resource available for tomorrow's eight-hour shift. Plates Mugs Steins Available Molding (minutes) 4 6 3 2,400 Finishing (minutes) 8 14 12 7,200 Clay (ounces) 5 4 3 3,000 Unit Profit $3.10 $4.75 $4.00 A linear programming model has been formulated in a spreadsheet to determine the production levels that would maximize profit. The solved spreadsheet model and corresponding sensitivity report are shown below. A 1 B D E F G Plates 2 3 4 Unit Profit $3.10 Mugs $4.75 Steins $4.00 Resource Required per Unit Used Available 5. Molding (minutes) 4 6 3 2.400
I need explaination and spreadsheet with formulas and step to step interpretation please.....
The attached are first two pages of required assisance and below is the rest of instructions please consider all of them:
c. Suppose the CEO says, “Projects 3 and 4 must be undertaken but not both.”
Describe the constraint.
d. Suppose the CEO says, “Projects 4 cannot be undertaken unless projects 1 and 3
also are both undertaken.” Describe the constraints. Hint: You may need to add more than one constraint for this part.
3. Multi-product Production Planning. Hart Manufacturing makes three products. Each product requires manufacturing operations in three departments: A, B, and C. The labor-hour requirements, by department, are as follows:
Department Product 1 Product 2 Product 3
A 1.50 3.00 2.00
B 2.00 1.00 2.50
C 0.25 0.25 0.25
During the next production period, the labor-hours available are 450 in department A, 350 in department B, and 50 in department C. The profit contributions per unit are $25 for product 1, $28 for product 2, and $30 for product 3.
a. [Math model] Formulate a linear programming model algebraically for maximizing total profit contribution.
b. [Spreadsheet model] Solve the linear program formulated in part (a) by using Excel Solver. How much of each product should be produced, and what is the projected total profit contribution?
c. After evaluating the solution obtained in part (b), one of the production supervisors noted that production setup costs had not been taken into account. She noted that setup costs are $400 for product 1, $550 for product 2, and $600 for product 3. If the solution developed in part (b) is to be used, what is the total
profit contribution after taking into account the setup costs? d. [Math model] Management realized that the optimal product mix, taking setup costs into account, might be different from the one recommended in part (b).
Formulate a mixed-integer linear program that takes setup costs into account.
e. [Spreadsheet model] Solve the mixed-integer linear program formulated in part (d) by using Excel Solver. How much of each product should be produced, and what is the projected total profit contribution? Compare this profit contribution to that obtained in part (c).
Step by step
Solved in 3 steps with 1 images
