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.

Wagner-Whitin Algo