How To Optimize Basic Storage With ABC Decision Support Systems.

Im a Industrial engineering first year student and with my group we made Basic Storage Optimization using ABC analyzing system on excel.

What our system does is,basically consists these steps, which are:

1:Find every items sale frequencies. Give them POINTS (Weighted Movement Score) based on their frequencies + Executive Priority. Categorize the items as 3 groups, based on their frequency + Executive Priority. Which are POINTS.

2:After that you want to get their cumulative percentage of POINTS.Then make groups based on their cumulative percentage.%80 is A %15 is B and %5 C group.

For example: Lets say we have 100 items worth 100 POINTS. 10 of these items worths 80. that would make that 10 items cumulative percentage %80.

So we have to put them to the A group.

As you can see we sort them by their importance.

A is the most important because they are the %80 of the demand.

So as you can guess A grouped items will be going in n out much more.

So we put A group closest to the door and C group farthest away.

So what that does is. Its Optimizes the walk time.

3:After groups set.Our data’s are ready to work. But there is a set of problems.

The problems are our data tends to change in certian times for unkown reasons.

Because in demand we have these effects: Trend Effect, Seasonal Effect,

Cyclical Effect,Irregular/Random Effect. It doesnt matter which one causes the problem. We have a simple solution for all of this.

We call it Executive Priority.

When you know a certian item will sell in a certian time. You can just simply update the Executive Priority.

Which will be added to your point and change you items group.

Heres my excel code:

Option Explicit

Sub DepoDSS_Guncelle()

Dim wsU As Worksheet Dim wsL As Worksheet

Dim sonSatirU As Long Dim sonSatirL As Long Dim i As Long

Dim toplamPuan As Double Dim kumulatif As Double Dim lokasyonIndex As Long Dim toplamLokasyon As Long

Dim dictKontrol As Object Dim kod As String Dim yoneticiOnceligi As Variant Dim frekans As Double Dim nihaiPuan As Double

Set wsU = ThisWorkbook.Worksheets(“Urunler”) Set wsL = ThisWorkbook.Worksheets(“Lokasyonlar”)

sonSatirU = wsU.Cells(wsU.Rows.Count, “F”).End(xlUp).Row sonSatirL = wsL.Cells(wsL.Rows.Count, “F”).End(xlUp).Row

‘ 1) AYNI URUN KODU KONTROLU Set dictKontrol = CreateObject(“Scripting.Dictionary”)

For i = 2 To sonSatirU kod = Trim(wsU.Cells(i, “F”).Value)

If kod <> “” Then If dictKontrol.exists(kod) Then MsgBox “Ayni Urun_Kodu birden fazla kez girilmis: “ & kod, vbExclamation Exit Sub Else dictKontrol.Add kod, 1 End If End If Next i

‘ 2) YONETICI ONCELIGI KONTROLU + NIHAI PUAN HESABI For i = 2 To sonSatirU

If Trim(wsU.Cells(i, “F”).Value) <> “” Then

If Trim(wsU.Cells(i, “I”).Value) = “” Then wsU.Cells(i, “I”).Value = 0 End If

yoneticiOnceligi = wsU.Cells(i, “I”).Value

If Not IsNumeric(yoneticiOnceligi) Then MsgBox “Yonetici_Onceligi sayisal olmali. Hata satiri: “ & i, vbExclamation Exit Sub End If

If yoneticiOnceligi <> 0 And yoneticiOnceligi <> 1 And yoneticiOnceligi <> 2 Then MsgBox “Yonetici_Onceligi sadece 0, 1 veya 2 olabilir. Hata satiri: “ & i, vbExclamation Exit Sub End If

frekans = Val(wsU.Cells(i, “H”).Value) nihaiPuan = frekans + (yoneticiOnceligi * 300)

wsU.Cells(i, “J”).Value = nihaiPuan

End If

Next i

‘ 3) LOKASYONLARI MESAFEYE GORE SIRALA With wsL.Sort .SortFields.Clear .SortFields.Add Key:=wsL.Range(“G2:G” & sonSatirL), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

.SetRange wsL.Range(“F1:J” & sonSatirL) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .Apply End With

toplamLokasyon = sonSatirL — 1

‘ 4) LOKASYON BOLGE TIPI For i = 2 To sonSatirL lokasyonIndex = i — 1

If lokasyonIndex <= toplamLokasyon 0.2 Then wsL.Cells(i, “H”).Value = “A” ElseIf lokasyonIndex <= toplamLokasyon 0.5 Then wsL.Cells(i, “H”).Value = “B” Else wsL.Cells(i, “H”).Value = “C” End If Next i

‘ 5) LOKASYON DOLULUKLARINI SIFIRLA For i = 2 To sonSatirL wsL.Cells(i, “I”).Value = “Bos” wsL.Cells(i, “J”).Value = “” Next i

‘ 6) URUNLERI NIHAI PUANA GORE SIRALA With wsU.Sort .SortFields.Clear .SortFields.Add Key:=wsU.Range(“J2:J” & sonSatirU), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

.SetRange wsU.Range(“F1:N” & sonSatirU) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .Apply End With

sonSatirU = wsU.Cells(wsU.Rows.Count, “F”).End(xlUp).Row

‘ 7) ESKI HESAP ALANLARINI TEMIZLE wsU.Range(“K2:N” & sonSatirU).ClearContents

‘ 8) TOPLAM NIHAI PUAN toplamPuan = Application.WorksheetFunction.Sum(wsU.Range(“J2:J” & sonSatirU))

If toplamPuan = 0 Then MsgBox “Toplam Nihai_Puan sifir olamaz.”, vbExclamation Exit Sub End If

‘ 9) KUMULATIF + ABC + HEDEF RAF + HEDEF BOLGE kumulatif = 0

For i = 2 To sonSatirU

If Trim(wsU.Cells(i, “F”).Value) <> “” Then

kumulatif = kumulatif + (wsU.Cells(i, “J”).Value / toplamPuan) wsU.Cells(i, “K”).Value = kumulatif wsU.Cells(i, “K”).NumberFormat = “0%”

If kumulatif <= 0.8 Then wsU.Cells(i, “L”).Value = “A” ElseIf kumulatif <= 0.95 Then wsU.Cells(i, “L”).Value = “B” Else wsU.Cells(i, “L”).Value = “C” End If

If i — 1 <= sonSatirL — 1 Then wsU.Cells(i, “M”).Value = wsL.Cells(i, “F”).Value wsU.Cells(i, “N”).Value = wsL.Cells(i, “H”).Value

wsL.Cells(i, “I”).Value = “Dolu” wsL.Cells(i, “J”).Value = wsU.Cells(i, “G”).Value Else wsU.Cells(i, “M”).Value = “Uygun_Raf_Yok” wsU.Cells(i, “N”).Value = “-” End If

End If

Next i

Application.CalculateFull MsgBox “Depo DSS guncellendi.”, vbInformation

End Sub