Assignment Task:
The FiatLux lamp company manufactures metal lamps using a variety of different metals and their production is constrained by how much of each metal they have on hand. For the next month, the company has available a total of 450 lbs. of brass, 400 lbs. of copper, 400 lbs. of steel, and 50 pounds of iron. Each lamp style requires different amounts of each metal based on a proprietary design. For the next month they will make three styles of lamps: Eiffel, Arch and Taj. Based on the selling price to retailers, each Eiffel lamp made creates a profit of $40, each Arch lamp made creates a profit of $50 and each Taj lamp made creates a profit of $30. Each Eiffel lamp requires 0.5 lbs. of brass, one lb. of copper, 0.5 lbs. of steel and two lbs. of iron. Each Arch lamp requires no brass, one lb. of copper, four lbs. of steel and 0.2 lbs. of iron. Each Taj lamp requires five lbs. of brass, one lb. of copper, 0.5 lbs. of steel and 0.1 lbs. of iron. Contracts require that at least 15 of each type of lamp must be made next month. FiatLux wants to know how many of each style of lamp to produce next month, given the limited amount of materials.
Formulate this as a linear programming problem to maximize FiatLux's profit. Assume that fractional lamps are allowed to be made in the month (to be completed the following month). Use Solver in Excel to find the optimal solution.
(i) In the optimal solution, how many lamps of each style should be made?
(ii) What is the optimal profit?
(iii) Paste your complete Excel spreadsheet below showing the optimal solution.
- Maximum profit
- Number of Eiffel lamps produced
- Number of Arch lamps produced
- Number of Taj lamps produced