Silver-Meal Heuristic: Dynamic MRP Lot-Sizing Cost Per Period

Choose a lot for changing weekly demand

Use known net requirements of 80, 100, 60 and 180 units, a setup cost of $300 per order, and holding cost of $1.50 per unit per week. Assume replenishment at the start of a period, no shortages, no capacity limit and constant costs. This is a planning heuristic, not a guarantee of a globally optimal schedule.

For each candidate horizon, divide setup plus holding cost by the number of covered periods. Stop at the first strict increase and choose the preceding horizon. Equal costs continue in this worksheet. Do not round costs before comparing them.

Build the cost table

Enter the inputs shown in the practice sheet. E6 validates numeric nonnegative costs and demands; B2 must be positive because this example starts at the first period needing a lot. Zero demand later in the horizon is allowed. H2:H5 calculate the four raw average costs.

For example, H4 covers three weeks: week 1 demand has no holding cost, week 2 is held one week and week 3 two weeks. Format the output as currency without changing its underlying value.

=IF(E6<>"OK",E6,(E1+E2*SUMPRODUCT(TAKE(B2:B5,3),SEQUENCE(3,1,0,1)))/3)

Select the first increase automatically

E8 selects three periods in the initial example; E9 returns 240 units. It checks adjacent costs in order, rather than taking the global minimum of H2:H5.

=IF(E6<>"OK",E6,IF(H3>H2,1,IF(H4>H3,2,IF(H5>H4,3,4))))

Calculate the selected quantity

E9 adds the demands covered by E8. Changing the trial horizon E3 does not override the automatically selected horizon.

=IF(ISNUMBER(E8),SUM(TAKE(B2:B5,E8)),E8)

Experiment with a trial horizon

E11 evaluates E3 independently. Choose an integer from 1 to 4; blank, text, fractional or out-of-range horizons return a message instead of passing invalid dimensions to TAKE.

=IF(E6<>"OK",E6,IF(NOT(ISNUMBER(E3)),"Check horizon",IF(OR(E3<1,E3>4,E3<>INT(E3)),"Check horizon",(E1+E2*SUMPRODUCT(TAKE(B2:B5,E3),SEQUENCE(E3,1,0,1)))/E3)))

Restart after the chosen lot

For the initial data, order 240 units at the start of week 1 and restart at week 4, where the remaining 180 units require another setup. The full illustrative schedule costs $330 in holding plus two $300 setups: $930. The executable sheet selects the next lot only; for another cycle, replace B2:B5 with requirements from the new start and adapt the horizon if fewer periods remain.

Skip initial zero-demand periods before starting a cycle. A general scheduler must also account for lead times, available stock, capacity, lot multiples and its actual remaining horizon; this fixed four-period demonstration does not implement those constraints.

Why this is not a global-minimum search

Try setup 100, holding cost 1 and demands 1, 101, 0, 0. Average costs are 100, 100.5, 67 and 50.25. The first increase selects one period, even though later averages fall. Looking ahead for the smallest number would be a different rule.

Try setup 100, holding cost 1 and demands 1, 100, 0, 0: the first two averages tie at 100. Under the explicit strict-increase policy here, the search continues and covers all four periods.

Further reading

• UC Berkeley IEOR 250 — Silver–Meal first-increase procedure, problem 5
• Microsoft Support — TAKE function
• Microsoft Support — SUMPRODUCT function