Determining supply requirement in the sales-and-operations-planning (S&OP) process under demand uncertainty: a stochastic programming formulation and a spreadsheet implementation

Abstract

We show how to extend the demand-planning stage of the sales-and-operations-planning (S&OP) process with a spreadsheet implementation of a stochastic programming model that determines the supply requirement while optimally trading off risks of unmet demand, excess inventory, and inadequate liquidity in the presence of demand uncertainty. We first present the model that minimizes the weighted sum of respective conditional value-at-risk (cVaR) metrics over demand scenarios in the form of a binomial tree. The output of this model is the supply requirement to be used in the supply-planning stage of the S&OP process. Next we show how row-and-column aggregation of the model reduces its size from exponential (2^T) in the number of time periods T in the planning horizon to merely square (T²). Finally, we demonstrate the tractability of this aggregated model in an Excel spreadsheet implementation with a numerical example with 26 time periods.

Keywords:

• stochastic programming
• supply-chain planning
• demand uncertainty
• sales-and-operations planning (S&OP)
• conditional value-at-risk (cVaR)
• spreadsheet

Acknowledgements

The authors would like to thank Linus Schrage of University of Chicago for an inspiring presentation at the INFORMS Practice Conference in Phoenix in April 2009 about stochastic programming and spreadsheets. The comments of two anonymous referees have greatly helped us to clarify the approach and the contribution.

Notes

1 This is not the same as list price but that realized in practice after various discounts off the list price.