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 (2T) in the number of time periods T in the planning horizon to merely square (T2). Finally, we demonstrate the tractability of this aggregated model in an Excel spreadsheet implementation with a numerical example with 26 time periods.