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

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.