PGP ’11 student, Kapil Vaish, An Operations enthusiast shares his insight on Excel Modeling
Excel Modeling for Wagner Whitin algorithm
The “Squared root formula” for steady state demand for economic lot size is well known. The calculation is predicated upon balancing ordering cost (Setup Cost) and holding cost. But when the assumption of steady state demand rate is dropped i.e. when demand for the future is known but are not constant and when setup cost and holding cost changes with periods then square root formula for the EOQ not necessary give the optimal result in deciding the lot size.
Wagner and Whitin gives the way to decide for a dynamic lot sizing technique in which above assumptions are dropped to get dynamic lot size optimal result. Assumptions they took was:
1.) Single product Variety
2.) Unit production cost in constant
3.) No orders are overdue/ No backorders
4.) No capacity constraints
5.) Zero lead time
Here I have applied the algorithm on a hypothetical data just to illustrate you how excel model can be very easy to come up with dynamic lot sizing method.
1.) St is the set up cost in period t
2.) It is the inventory holding cost in period t
3.) Dt is the demand in period t
4.) Xt is the production quantity in period t
5.) Yt is dummy variable representing 1, if Xt >0, else 0
6.) M is very large number
7.) Et is ending inventory in period t carried to next period
8.) P is Production Cost per unit
Objective is to minimize total cost. Total Cost consist of
When we apply the objective function and constraints in excel solver we get the optimal solution of lot size per period. An example is given in the link below to illustrate the process. Hope it would be helpful.