Answer:
$267,250
Step-by-step explanation:
To solve this linear programming problem using Excel's Solver, we need to set up the objective function and constraints. Let's break down the problem step by step:
Objective Function:
We want to maximize the company's profit. The profit for each unit of product A is $145, and for each unit of product B, it is $75. Therefore, the objective function is:
Maximize Z = 145A + 75B
Constraints:
Management requires that at least 1850 units be manufactured each month:
A + B ≥ 1850
Product A requires 5 hours of labor per unit, and product B requires 3 hours. The cost of labor is $15 per hour, and there are 8000 hours available per month:
5A + 3B ≤ 8000
Non-negativity constraints:
A ≥ 0
B ≥ 0
Now, let's set up Excel's Solver to find the optimal production schedule:
Open Excel and enter the constraints and objective function in a spreadsheet. You can label cells for A and B as variables.
In a cell, enter the objective function: "=145A+75B" (without quotes) where A and B are the cell references for the number of units of product A and B.
Enter the constraints:
A + B ≥ 1850
5A + 3B ≤ 8000
A ≥ 0
B ≥ 0
Open the "Solver" add-in in Excel. You can find it under the "Data" or "Analysis" tab, depending on your Excel version.
In Solver, set the objective to "Maximize" by selecting the cell with your objective function.
Set the variable cells to be A and B.
Add constraints as follows:
A + B ≥ 1850
5A + 3B ≤ 8000
A ≥ 0
B ≥ 0
Click "Solve."
Excel's Solver will find the optimal values for A and B that maximize the profit while satisfying the constraints.
Answer:
Using Excel's Solver, the optimal production schedule to maximize the company's profit is as follows:
Manufacture 1850 units of Product A (A = 1850)
Manufacture 0 units of Product B (B = 0)
The monthly profit will be:
Z = 145A + 75B
Z = 145 * 1850 + 75 * 0
Z = $267,250
So, the company should manufacture 1850 units of Product A and none of Product B each month to maximize their monthly profit, which will be $267,250.