Task 1:

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:

The optimized solution shows maximum savings of $  41,833.72 by producing the following widgets:

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