Optimize an Aggregate Production Plan with Excel Solver

Summary

In this video, the presenter demonstrates how to solve a scheduling problem using Excel's Solver tool. The problem involves managing forecasted demand, regular and overtime hours, subcontracting, and inventory for six months. The presenter explains how to calculate costs for regular time, overtime, subcontracting, and inventory holding, while also setting up constraints such as no backlogs and limiting subcontracting hours. By using Solver, the presenter efficiently minimizes the total cost and finds a better solution than the one presented in the book, showcasing the value of using Excel for optimization tasks.

Takeaways

• 😀 The problem involves scheduling and minimizing total costs for fulfilling forecasted demand over six months using regular time, overtime, subcontracting, and inventory management.
• 😀 The regular time allocation is fixed at 150 hours for the first four months, with an increase to 160 hours for the last two months.
• 😀 Each month, there are 10 hours of overtime available, which can be utilized to meet demand.
• 😀 Subcontracting can also be used to fulfill demand, with a maximum of 10 hours available per month.
• 😀 Regular time costs $50 per hour, overtime costs $75 per hour, and subcontracting costs $80 per hour.
• 😀 Inventory holding costs are $4 per unit on average during each month of the scheduling period.
• 😀 The goal is to minimize total costs, considering regular time, overtime, subcontracting, and inventory holding costs, while ensuring there are no backorders.
• 😀 Excel Solver is used to optimize the solution by adjusting the number of overtime and subcontracting hours to minimize total cost while meeting the demand and constraints.
• 😀 Key constraints include: no backlogs (no negative inventory), overtime hours cannot exceed available hours, and subcontracting hours are limited to 10 per month.
• 😀 Solver successfully finds an optimal solution that minimizes costs, which was more efficient than the solution provided in the book, confirming that Solver is a powerful tool for such problems.

Q & A

What is the primary goal of the scheduling problem in the video?

The primary goal of the scheduling problem is to minimize total costs for the next six months by balancing regular time, overtime, and subcontracting, while considering various constraints like inventory and backlog.

What resources are available for the scheduling problem?

The available resources are 150 hours of regular time for the first four months, with an additional 10 hours of regular time for the last two months. There are also 10 hours of overtime available each month, and subcontracting is possible with a maximum of 10 hours per month.

What are the costs associated with the scheduling problem?

The costs are $50 per hour for regular time, $75 per hour for overtime, and $80 per hour for subcontracting. Additionally, holding inventory costs $4 per unit per month.

What is the significance of the 'backlog' in the scheduling problem?

The backlog represents any unmet demand that exceeds available production. In the script, the backlog is calculated based on the difference between the required units and the produced units, with the constraint that there should be no backlog, as it is not allowed in this problem.

How is the ending inventory calculated for each month?

The ending inventory is calculated as the sum of the beginning inventory and the units produced during the month, minus the demand for that month.

What role does Excel Solver play in this scheduling problem?

Excel Solver is used to optimize the solution by adjusting the values of overtime and subcontracting hours in order to minimize the total cost while satisfying all constraints.

Why is subcontracting used in this problem, and how does it affect costs?

Subcontracting is used when regular time and overtime hours are insufficient to meet the demand. It has a higher cost compared to regular and overtime work ($80 per hour), which is why the goal is to minimize its use.

What are the constraints that need to be considered in the scheduling problem?

The constraints include a maximum of 10 hours of overtime per month, a maximum of 10 hours of subcontracting per month, no backlog (inventory cannot be negative), and all work hours must be integers.

How does the solution found by Excel Solver compare to the book’s solution?

The solution found using Excel Solver is more cost-effective than the solution in the book. The book suggested using subcontracting and overtime differently, resulting in a higher total cost. The Solver solution saved $80 by optimizing overtime and subcontracting usage.

What steps does the speaker recommend if Solver is not available in Excel?

If Solver is not available in Excel, the speaker recommends googling how to enable it, as many resources are available to guide users through the process of turning on Solver in Excel.