Task 1:
Decision Variables
: the amount of widget i to be produced in the FMS
Parameters
: the savings from producing the amount of widget i in the FMS instead of using the craftsman approach. It is calculated for each widget as follows:
= (craftsman time x craftsman cost) – (system time x system cost)
: the demand for widget i
: the time it takes to produce widget i
Formulation
s.t.
Explanation of the Formulation
The objective function’s goal is to maximize the savings by producing the widgets with the highest savings in the FMS. However, the number of widgets to be produced in the FMS is constrained by the total number of minutes in a 30-day month – 43,200 minutes. That fact is represented by constraint 3. Constraint 1 states that it is neither logical nor needed to produce more than demand. Without constraint 1, the program will use the entire number of minutes to produce the widget with the highest savings ignoring the demand for it and for the other widgets. Constraint 2 represents the non-negativity constraint and constraint 4 states that the number of widgets has to be an integer because the company can’t produce partial widgets.
Task 2:
The optimization formulation was solved using Excel Solver and the results are shown below:
| Widget | Savings/Widget | Optimized Number | Minutes Used | Max Savings |
| 1 | $ 8.65 | 0 | 0 | $ 41,833.72 |
| 2 | $ 12.72 | 0 | 0 | |
| 3 | $ 3.94 | 0 | 0 | |
| 4 | $ 11.92 | 0 | 0 | |
| 5 | $ 17.45 | 379 | 7390.5 | |
| 6 | $ 21.06 | 372 | 6621.6 | |
| 7 | $ 8.84 | 0 | 0 | |
| 8 | $ 3.77 | 0 | 0 | |
| 9 | $ 7.69 | 0 | 0 | |
| 10 | $ 9.26 | 0 | 0 | |
| 11 | $ 12.66 | 0 | 0 | |
| 12 | $ 1.76 | 0 | 0 | |
| 13 | $ 2.57 | 0 | 0 | |
| 14 | $ 15.99 | 150 | 3495 | |
| 15 | $ 3.35 | 0 | 0 | |
| 16 | $ 9.94 | 0 | 0 | |
| 17 | $ 2.51 | 0 | 0 | |
| 18 | $ 7.93 | 0 | 0 | |
| 19 | $ 19.94 | 127 | 3276.6 | |
| 20 | $ 8.93 | 0 | 0 | |
| 21 | $ 10.93 | 0 | 0 | |
| 22 | $ 5.83 | 0 | 0 | |
| 23 | $ 6.20 | 0 | 0 | |
| 24 | $ 12.29 | 0 | 0 | |
| 25 | $ 8.77 | 0 | 0 | |
| 26 | $ 6.51 | 0 | 0 | |
| 27 | $ 6.30 | 0 | 0 | |
| 28 | $ 5.04 | 0 | 0 | |
| 29 | $ 16.99 | 77 | 1678.6 | |
| 30 | $ 5.13 | 0 | 0 | |
| 31 | $ 3.76 | 0 | 0 | |
| 32 | $ 13.82 | 0 | 0 | |
| 33 | $ 10.99 | 0 | 0 | |
| 34 | $ 15.19 | 225 | 4387.5 | |
| 35 | $ 4.82 | 0 | 0 | |
| 36 | $ 2.91 | 0 | 0 | |
| 37 | $ 5.43 | 0 | 0 | |
| 38 | $ 8.87 | 0 | 0 | |
| 39 | $ 2.68 | 0 | 0 | |
| 40 | $ 8.51 | 0 | 0 | |
| 41 | $ 1.25 | 0 | 0 | |
| 42 | $ 4.88 | 0 | 0 | |
| 43 | $ 7.69 | 0 | 0 | |
| 44 | $ 2.05 | 0 | 0 | |
| 45 | $ 14.45 | 0 | 0 | |
| 46 | $ 9.30 | 0 | 0 | |
| 47 | $ 4.19 | 0 | 0 | |
| 48 | $ 19.75 | 329 | 4836.3 | |
| 49 | $ 9.57 | 0 | 0 | |
| 50 | $ 5.41 | 0 | 0 | |
| 51 | $ 1.81 | 0 | 0 | |
| 52 | $ 3.80 | 0 | 0 | |
| 53 | $ 9.93 | 0 | 0 | |
| 54 | $ 9.03 | 0 | 0 | |
| 55 | $ 17.88 | 77 | 2125.2 | |
| 56 | $ 7.06 | 0 | 0 | |
| 57 | $ 10.38 | 0 | 0 | |
| 58 | $ 10.70 | 0 | 0 | |
| 59 | $ 4.41 | 0 | 0 | |
| 60 | $ 6.91 | 0 | 0 | |
| 61 | $ 5.44 | 0 | 0 | |
| 62 | $ 9.75 | 0 | 0 | |
| 63 | $ 5.25 | 0 | 0 | |
| 64 | $ 3.88 | 0 | 0 | |
| 65 | $ 8.32 | 0 | 0 | |
| 66 | $ 3.59 | 0 | 0 | |
| 67 | $ 3.27 | 0 | 0 | |
| 68 | $ 13.36 | 0 | 0 | |
| 69 | $ 19.92 | 31 | 799.8 | |
| 70 | $ 4.62 | 0 | 0 | |
| 71 | $ 18.94 | 488 | 8588.8 | |
| 72 | $ 12.19 | 0 | 0 | |
| 73 | $ 12.08 | 0 | 0 | |
| 74 | $ 4.43 | 0 | 0 | |
| 75 | $ 11.49 | 0 | 0 | |
| 76 | $ 3.17 | 0 | 0 | |
| 77 | $ 12.57 | 0 | 0 | |
| 78 | $ 10.21 | 0 | 0 | |
| 79 | $ 5.85 | 0 | 0 | |
| 80 | $ 11.51 | 0 | 0 |
The optimized solution shows maximum savings of $ 41,833.72 by producing the following widgets:
| Widget | Savings/Widget | Optimized Number | Minutes Used |
| 5 | $ 17.45 | 379 | 7390.5 |
| 6 | $ 21.06 | 372 | 6621.6 |
| 14 | $ 15.99 | 150 | 3495 |
| 19 | $ 19.94 | 127 | 3276.6 |
| 29 | $ 16.99 | 77 | 1678.6 |
| 34 | $ 15.19 | 225 | 4387.5 |
| 48 | $ 19.75 | 329 | 4836.3 |
| 55 | $ 17.88 | 77 | 2125.2 |
| 69 | $ 19.92 | 31 | 799.8 |
| 71 | $ 18.94 | 488 | 8588.8 |
| Total | $ 41,833.72 | 43199.9 |
Also, it can be seen that the total number of minutes used to produce all the quantities for all widgets is 43,199.9 minutes which is less than the total available minutes. That indicates that capacity wasn’t exceeded.
Task 3:
Minimizing Holding Costs
Decision Variables
: the amount of widget i produced in period t
Parameters
: the cost for holding one widget i per period
: the demand for widget i in period t
: the time it takes to produce widget i
Formulation
s.t.
Explanation of the Formulation
The objective function’s goal is to minimize the total holding cost. The constraints indicate that we need to at least meet demand but can’t exceed our capacity per shift which is 480 minutes of available production time.
Minimize Lateness
The steps of the Greedy Algorithm are:
Step 0 (sort)
- List jobs in order by earliest due date
- Open first batch
Step 1 (assign)
- Consider the job with the earliest due date
- If feasible add as much of the order as possible to the smallest index batch
- If part of the order is still not feasible, open a new batch
Step 2 (check if done)
- Remove current job from the list
- If list is empty close all batches
- Else go to 1
Task 4:
Since the company uses Excel to store data and plan production, I decided to create an Excel VBA macro that runs their input from the “PartsToPlan” & “OrdersToPlan” sheets and create a production schedule that minimizes lateness using the Greedy Algorithm.
I made it simple by placing a button named “Greedy” in the “OrdersToPlan” sheet. That button will format the production schedule in the same sheet and create a table report that shows which products need to be produced in each shift and their quantities. It will also, report the number of minutes left in each shift in the bottom of the report as well as which orders we were able to fulfill in the “Assigned” column.
Below is a snapshot of what the report looks like:
The VBA code is commented below:
Sub Greedy_Click()
‘The code starts by deleting current solutions if any exists.
Dim delrange As Range
Set delrange = Range(Sheets(3).Cells(3, 6), Sheets(3).Cells(28, 28))
delrange.Cells.Clear
‘The second step in the code is to set up the sheet to display results by setting up the appropriate columns.
‘Column “Assigned” keeps updating to show how much of the order is fulfilled.
Sheets(3).Cells(1, 5).Value = “Assigned”
‘”Production Schedule” is the the schedule given by the greedy algorithm.
Sheets(3).Cells(1, 7).Value = “Production Schedule”
‘The “Time to Produce” is a column that shows how long it takes to produce each of the 26 products.
Sheets(3).Cells(2, 6).Value = “Time to Produce”
‘This piece of the code calculates “Time to Produce” from “PartsToPlan” sheet and paste it for each product into “OrdersToPlan” sheet.
FinalRow2 = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
For u = 3 To FinalRow2
widget = Sheets(2).Cells(u, 1).Value
TP = Sheets(2).Cells(u, 2).Value + Sheets(2).Cells(u, 3).Value + Sheets(2).Cells(u, 4).Value + Sheets(2).Cells(u, 5).Value + Sheets(2).Cells(u, 6).Value + Sheets(2).Cells(u, 7).Value + Sheets(2).Cells(u, 8).Value
FinalRow3 = Sheets(3).Cells(Rows.Count, 6).End(xlUp).Row
Sheets(3).Cells(FinalRow3 + 1, 6).Value = TP
Sheets(3).Cells(FinalRow3 + 1, 7).Value = widget
Next u
‘For each 8hr-shift, we have 480 available minutes.
For v = 1 To 21
Sheets(3).Cells(2, 7 + v).Value = v
Sheets(3).Cells(30, 7 + v).Value = 480
Next v
‘Here, we initiate the schedule by giving the value zero to all orders in the “Assigned” column. That means we haven’t assigned anything yet.
FinalRow = Sheets(3).Cells(Rows.Count, 1).End(xlUp).Row
For p = 2 To FinalRow
Sheets(3).Cells(p, 5).Value = 0
Next p
‘The greedy algorithm starts here.
‘Step 1: sort orders by earliest due shift.
Sheets(3).Activate
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add2 Key:=Range(Cells(2, 4), Cells(FinalRow, 4)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range(Cells(2, 1), Cells(FinalRow, 4))
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
‘k represents the orders, i represents the shifts, and j represents the 26 different products.
For k = 2 To FinalRow
For i = 1 To 21
‘We determine how many minutes left in each shift first.
MinutesLeft = Sheets(3).Cells(30, 7 + i).Value
For j = 1 To 26
MyProduct = Sheets(3).Cells(k, 2).Value
MyDemand = Sheets(3).Cells(k, 3).Value
Assigned = Sheets(3).Cells(k, 5).Value
‘We, then, determine how many of each product we can produce in shift i.
NeedAssign = MyDemand – Assigned
‘Also, we make sure that we only produce to match demand.
If MyDemand > Assigned Then
MatchProduct = Sheets(3).Cells(2 + j, 7).Value
‘For each product in each order, we match the name of product in the schedule to enter the amount in the appropriate row and account for the amounts needed and minutes available.
If MyProduct = MatchProduct Then
ProductMinutes = Sheets(3).Cells(2 + j, 6).Value
‘With the number of minutes left in shift i, we determine the number of product j that can be produced.
MyCapacity = Application.WorksheetFunction.RoundDown(MinutesLeft / ProductMinutes, 0)
‘Number of products that can be added to the batch/shift.
WillAssign = Application.WorksheetFunction.Min(MyCapacity, NeedAssign)
‘We add the amount of product j to products in batch/shift.
Sheets(3).Cells(2 + j, 7 + i).Value = Sheets(3).Cells(2 + j, 7 + i).Value + WillAssign
‘We add to the new quantity to the previously assigned quantity in the “Assigned” column to show the updated assigned quantity.
Sheets(3).Cells(k, 5).Value = Sheets(3).Cells(k, 5).Value + WillAssign
‘We take away from the original minutes left in shift i to allocate time for the production of product j.
Sheets(3).Cells(30, 7 + i).Value = Sheets(3).Cells(30, 7 + i).Value – (ProductMinutes * WillAssign)
Else
End If
Else
End If
Next j
Next i
Next k
End Sub
