Summary
Highlights
This video will demonstrate how to maximize profit by optimizing production using Excel Solver. The example involves a company producing mice, keyboards, and USB hubs, with known profit per unit, labor hours per unit, production floor time per unit, and monthly demand. The objective is to maximize profit under constraints including available monthly labor hours (10,000), production time (1,700 hours), and not exceeding demand.
The first step is to set up the objective function. This involves entering initial monthly production units for each product. Then, calculate the monthly profit for each product by multiplying the profit per unit by the number of units produced. Summing these individual profits provides the total monthly profit, which is the value to be maximized.
Next, calculate the total labor hours and production hours required for the current production plan. Total labor hours are found by multiplying labor hours per unit by the number of units. Similarly, total production hours are calculated by multiplying production time per unit by the number of units. These totals will be compared against the given constraints.
With initial calculations, it's clear that simply meeting all demand (which would yield a $400,000 profit) is not feasible due to labor and production hour constraints. Manually adjusting production numbers can lead to a feasible solution, but it's not optimal. The video sets up the constraints for labor (10,000 hours), production (1,700 hours), and demand for each product, preparing for Excel Solver.
To prepare for Solver, best practices include color-coding the spreadsheet: green for the objective function (total profit), red for constraints (labor, production, and demand), and yellow for the changing cells (monthly production units). This visual organization clarifies the roles of different cells within the Solver setup.
If Solver is not available, it needs to be enabled via Excel Options > Add-ins > Excel Add-ins. Once enabled, Solver can be found under the 'Data' tab. In the Solver Parameters, the objective cell (total monthly profit) is set to 'Max' to maximize profit. The changing variable cells are the monthly production units (yellow cells).
Constraints are added to Solver: total labor hours must be less than or equal to 10,000; total production hours must be less than or equal to 1,700; and the production units for each product must be less than or equal to their respective monthly demands.
After adding all constraints, click 'Solve'. Excel Solver finds an optimal solution. In this example, the optimal production quantities are 4,889 mice, 20,000 keyboards, and 9,000 USB hubs, resulting in a maximum profit of $307,770. The results show that labor hours (all 10,000) were the binding constraint, meaning to increase profit further, this constraint would need to be addressed.
The example concludes, highlighting that the most time-consuming part of this process is setting up the spreadsheet correctly with clear objective functions and constraints. Proper organization ensures that Excel Solver can effectively identify optimal solutions for maximizing profit.