Question

A firm has 89 units of a product on hand. Forecasts of demand are 28 units per week. An MPS quantity of 90 units is planned to arrive in period 3. Customer orders are 33 for period 1, 35 for period 2, and 27 for period 3.
What is the projected on-hand inventory at the end of period 3?

Solved in an excel format.

Answer 1

MPS quantity is ordered when projected on-hand inventory becomes negative. For the given problem the Planned MPS is 90 units in week 3.

Projected on-hand (POH) or net inventory before MPS = previous week inventory + MPS for the week – Week requirements

Week requirements = maximum (forecast in week, customer orders in week)

POH for week = Previous week inventory + MPS for the week – maximum (forecast in week, customer orders in week)

For the week 1, POH = 89 + 0 – (max(28,33) = 89 – 33 = 56,

For the week 2, POH = 56 + 0 – (max(28,35) = 56 – 35 = 21,

For the week 3, POH = 21 + 90 – (max(28,27) = 111 – 28 = 83

MPS is as follows:

Week	0	1	2	3
Forecast		28	28	28
Customer orders (committed)		33	35	27
Projected on-hand	89	56	21	83
MPS				90

Excel Model is as follows:

a Excel Model for MPS calculation BC 1 Week 2 Forecast Customer orders 3 (committed) 4 Projected on-hand -hand 89 56 5 MPS Available to Promise 6 (uncommitted) 21 B c 28 28 28 Formulas for excel Model A 1 Week 2 Forecast Customer orders 3 committed) 4 Projected on-hand 89 5 MPS Available to Promise 6 (uncommitted) =B4+C5-MAX(C2:03) (=C4+D5-MAX(D2:D3) =D4+E5-MAX(E2:E3)

ANS: POH at the end of the week 3 is 83 units