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.

Variables used:

1.) S_{t} is the set up cost in period t

2.) I_{t} is the inventory holding cost in period t

3.) D_{t} is the demand in period t

4.) X_{t} is the production quantity in period t

5.) Y_{t} is dummy variable representing 1, if X_{t} >0, else 0

6.) M is very large number

7.) E_{t} is ending inventory in period t carried to next period

8.) P is Production Cost per unit

Objective Function:

Objective is to minimize total cost. Total Cost consist of

Constraints:

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.

Great article.. giver tones of insight about the algorithm..

thanks for the insights…these are really help full…

THANK YOU SO MUCH