Here is an existing solution described by words of a problem that I have to put into MS Excel. I have started doing that.
- Could you explain how to calculate the cells of these ranges (yellow color in the attached Excel file) while also you will be inserting the missing formulas in the Excel File?
- At that point can you please to solve it with MS Solver and give me a print screen of the MS solver wndow?
Buckeye Manufacturing produces heads for engines used in the manufacture of trucks. The production line is highly complex, and it measures 900 feet in length. Two types of engine heads are produced on this line: the P-Head and the H-Head. The P-Head is used in heavy-duty trucks and the H-Head is used in smaller trucks. Because only one type of head can be produced at a time, the line is either set up to manufacture the P-Head or the H-Head, but not both. Changeovers are made over a weekend; costs are $500 in going from a setup for the P-Head to a setup for the H-Head, and vice versa. When set up for the P-Head, the maximum production rate is 100 units per week and when set up for the H-Head, the maximum production rate is 80 units per week.
Managerial Report
Calculate a production and changeover schedule for the next eight weeks. Be sure to note how much of the total cost is due to production, how much is due to inventory, and how much is due to changeover.
SOLUTION:
A mixed integer programming model can be used advantageously to assist in developing recommendations. We describe such a model here; it has 48 decision variables and 64 constraints. We show here how to use Microsoft Excel to formulate and solve the problem. The spreadsheet at the end shows how we set up the problem and the optimal solution. We describe the model now.
Variables
There are variables for production, inventory, setup, and changeover in each week.
Pi = number of P-Heads produced in week i
Hi = number of H-Heads produced in week i
IPi = number of P-Heads in inventory at end of week i
IHi = number of H-Heads in inventory at end of week i
SPi = 1 if line is setup for P in week i, 0 if setup for H
Changei = 1 if a changeover occurs at the beginning of week i, 0 otherwise
Constraints
There are constraints for production capacity, inventory balance, maintenance of safety stock, and enforcement of changeovers. Also, Excel requires that you identify the 0-1 (binary) variables in the Solver dialog box. The constraints as specified in the Excel Solver dialog box are as follows (references are to cells of the spreadsheet):
B20:C27 ≤ B34:C41 production capacity, or nonnegativity of slack
G20:G27 ≥ H34:H41 forces Changei to 1 when a changeover occurs
G20:G27 ≥ I34:I41
D20:E27 ≥ D6:E13 ending inventory ≥ safety stock
D34:E41 = B6:C13 beg. inv. + production – end inv. = demand
F18:F25 = Bin setup variables must be binary
Even though the Changei variable must also be integer it is not necessary to require it because minimization will never let it be any bigger than it has to be. And, the second set of constraints force it to be ≥ 1 whenever the setup variable changes from 1 to 0 or from 0 to 1.
Objective
We want to minimize total cost which is represented by cell J23 in the spreadsheet. It is the sum of production cost, inventory cost, and changeover cost.
The Spreadsheet
The first 14 rows of the spreadsheet contain the data for the problem; information on demand, safety stock, various costs and beginning inventories are given. Cells B20:G27, as shown contain the optimal solution to the problem. Before solving, those cells were empty.
Solution Comments
The spreadsheet contains the optimal solution. The minimum total cost is $119,154.35. The components of that cost are production: $117,374, inventory: $1280.35 and changeover: $500. From cells F20:F27 we see that the line will be setup to produce P-Heads in weeks 1-3 and H-Heads in weeks 4-8. Cell G23 shows that there will be a changeover from producing P-Heads to H-Heads at the beginning of week 4.
By adjusting the data for this problem (e.g. beginning inventories and the various costs) a number of variations of this problem can be created with the same basic model. Also, one might want to vary the safety stock requirements and the number of weeks in the planning horizon to create other variations of the problem.
