ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
INSTRUCTION
2
3
1- Forecasting Model Sheet2- Inventory management
4
5
Model ResultThe model calculates the three-month forecast data for any SKU.Model ResultThe model gives the day-to-day level inventory movement. It also triggers the reordering time and quantity need to reorder with supplier.
6
7
Basic MethodologyThe model is designed based on winter's prediction methodology.Basic MethodologyThe model is designed based on EOQ and a continuous ordering system.
8
It includes all three parameters- level,trend and seasonality.The service level is taken as 95% i.e. out of 100 demand cycles, It will be able to fulfill the demand completely in 95 cycles, and in the rest, the partial demand will be met.
9
All three smoothing factors are considered i.e. Alpha, Beta and Gamma
10
The Error is minimized post process using solver.
11
12
Getting StartedEnter the real demand for a month ( lets assume for april in 5th year)Getting Started
Calculate the yearly demand and demand standard deviation using backside calculation side
13
Calculate level,trend and seasonal factor for the same month)Calculate EOQ, No of Orders need to be placed yearly, Cycle time.
14
Calculate RMSE for the same month.Calculate the safety stock and Reorder point for ROP value setting.
15
Minimize RMSE value using Solver to optimize alpha, beta, and gamma values.
In inventory management sheet, fill the details of day-wise retail and inventory received (if any)
16
Calculate the forecast value for the next three months i.e May, June, and July.
Trigger the reorder by EOQ value once closing inventory of day level goes down below ROP.
17
18
19
3- Dashboard4- Monthly Performance Indicator
20
21
Model ResultThe model gives the day-to-day inventory received (If any), opening inventory, SKU sold, and Closing inventory visual representation. It is a dynamic dashboard that will keep on changing based on real-time data.Model ResultThe model gives the monthly financial performance in terms of operating profit. The profit track is reflected in two ways. Firstly, it shows the SKU-specific profit earned by the warehouse and in second way, It gives the overall profit calculation for a retail customer.
22
23
Basic MethodologyThe dashboard works based on the data sheet generated i.e. forecasting, Inventory and backside calculation.
Basic Methodology
The monthly performance indicator sheet works on the basic idea of profit i.e. ( Revenue- cost)/ cost. It allocates all the fixed and variable cost of warehouse management to SKUs in proportion to the revenue generated.
24
25
Getting StartedUpdate the item received (if any) and SKU sold in inventory management sheet on daily basis.Getting StartedUpdate the no of specific SKUs sold wrt to customer data.
26
Update the real demand on monthly basis in forecasting sheet.
Update the overall manufacturing cost and selling price per SKU wrt to customer mapped.
27
Check the visual representation of each critical data in live dashboard.Put the overall monthly warehouse fixed and variable management cost in "Warehouse total Facility Storage & Ops cost".
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100