Muir Manufacturing produces two popular grades of commercial carpeting among its
ID: 3172628 • Letter: M
Question
Muir Manufacturing produces two popular grades of commercial carpeting among its many other products. In the coming production period, Muir needs to decide how many rolls of each grade should be produced in order to maximize profit. Each roll of Grade X carpet uses 50 units of synthetic fiber, requires 25 hours of production time, and needs 20 units of foam backing. Each roll of Grade Y carpet uses 40 units of synthetic fiber, requires 28 hours of production time, and needs 15 units of foam backing. The profit per roll of Grade X carpet is $200 and the profit per roll of Grade Y carpet is $160. In the coming production period, Muir has 3000 units of synthetic fiber available for use. Workers have been scheduled to provide at least 1800 hours of production time (overtime is a possibility). The company has 1500 units of foam backing available for use.
A. Develop an EXCEL Spreadsheet model for this problem and solve (Excel please)
Explanation / Answer
This is a linear optimization problem with the following characteristics:
Decision Variables: X and Y where X is the number of units of X produced and Y is th number of units of Y produced.
Therefore we need to maximize our objective function that is: PxX + PYY where Px is the profit per unit of X and PY is the profit per unit of Y. There are 3 constraints used in this optimization problem: The total synthetic fibre, The production time and the foam backing. The following excel sheet gives the final output of the Solver in Excel Optimized solution of the problem according to which X=60 and Y =0 that is 60 units of X to be produced and 0 units of Y.
I will explain some of the cells in the given excel sheet how i got them :
In the total column the profit is calculated by the excel function : SUMPRODUCT(H10:I10,H9:I9)
Where H10:I10 are the 2 decision variables X and Y and
H9:I9 are the profit per unit on X and Y . Similarly all other columns are found out in the Total column.
Inputs X Y Total Synthetic fiber 50 40 3000 <= 3000 Prod. Time 25 28 1500 <= 1800 Foam backing 20 15 1200 <= 1500 Profit per unit 200 160 12000 Number of units 60 0Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.