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 (2T) in the number of time periods T in the planning horizon to merely square (T 2). Finally, we demonstrate the tractability of this aggregated model in an Excel spreadsheet implementation with a numerical example with 26 time periods.
This is a preview of subscription content, log in via an institution to check access.
Similar content being viewed by others
Notes
This is not the same as list price but that realized in practice after various discounts off the list price.
References
Aghezzaf E (2005). Capacity planning and warehouse location in supply chains with uncertain demands. J Opl Res Soc 56: 453–462.
Birge JR (1985). Aggregation bounds in stochastic linear programming. Math Program 31: 25–41.
Birge JR and Louveaux F (1997). Introduction to Stochastic Programming. Springer Verlag: New York.
Dormer A, Vazacopoulos A, Verma N and Tipi H (2005). Modeling and solving stochastic programming problems in supply chain management using Xpress-SP. In: Geunes J and Pardalos P (eds). Supply Chain Optimization. Springer Publishers: New York, pp. 307–354.
Eppen GD, Martin RK and Schrage L (1989). A scenario approach to capacity planning. Opns Res 37: 517–524.
Geoffrion A and Powers R (1995). Twenty years of strategic distribution system design: An evolutionary perspective. Interfaces 25 (5): 105–127.
Hoyland K and Wallace S (2001). Generating scenario trees for multistage decision problems. Mngt Sci 47: 295–307.
Iassinovski S, Artiba A, Bachelet V and Riane F (2003). Integration of simulation and optimization for solving complex decision making problems. Int J Prod Econ 85: 3–10.
Kamrad B and Ritchken R (1991). Multinomial approximating models for options with k state variables. Mngt Sci 37: 1640–1652.
Leung SCH, Wu Y and Lai KK (2006). A stochastic programming approach for multi-site aggregate production planning. J Opl Res Soc 57: 123–132.
Mula J, Poler R, Garcia-Sabtel JP and Lario FC (2006). Models for production planning under uncertainty: A review. Int Prod Econ 103: 271–285.
Nagali V, Hwang J, Sanghera D, Gaskins M, Pridgen M, Thurston T, Mackenroth P, Branvold D, Scholler P and Shoemaker G (2008). Procurement risk management (PRM) at Hewlett-Packard company. Interfaces 38: 51–60.
Olhager J, Rudberg M and Wikner J (2001). Long-term capacity management: Linking the perspectives from manufacturing strategy and sales and operations planning. Int J Prod Econ 69: 215–225.
Poojari CA, Lucas C and Mitra G (2008). Robust solutions and risk measures for a supply chain planning problem under uncertainty. J Opl Res Soc 59: 2–12.
Rockafellar RT and Uryasev S (2000). Optimization of conditional value-at-risk. J Risk 2 (3): 21–41.
Sen S (2001). Stochastic programming: Computational issues and challenges. In: Gass S and Harris C (eds). Encyclopedia of Operations Research and Management Science. Springer: New York, pp. 784–788.
Sheldon DH (2007). World Class Sales-and-Operations-Planning: A Guide to Successful Implementation and Robust Execution. APICS and J. Ross Publishing: Fort Lauderdale, Florida.
Sodhi M (2005a). LP modeling for asset-liability management: A survey of choices and simplifications. Opns Res 53: 181–196.
Sodhi M (2005b). Managing tactical supply chain planning risk at a global consumer electronics company. Prod Opns Mngt 14 (1): 69–79.
Sodhi M and Lee S (2007). An analysis of sources of risk in the consumer electronics industry. J Opl Res Soc 58: 1430–1439.
Sodhi M and Tang CS (2009). Modeling supply chain planning under demand uncertainty using stochastic programming: A survey motivated by asset-liability management. Intl J Prod Econ 121: 728–738.
Vollmann TE, Berry WL, Whybark DC and Jacobs FR (2005). Manufacturing Planning and Control Systems for Supply Chain Management. 5th edn, McGraw-Hill Professional: New York.
Wright SE (1994). Primal-dual aggregation and disaggregation for stochastic linear programs. Math Opns Res 19: 893–908.
Zipkin P (1980a). Bounds on the effect of aggregating variables in linear programs. Opns Res 28: 403–418.
Zipkin P (1980b). Bounds for row-aggregation in linear programming. Opns Res 28: 903–916.
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.
Author information
Authors and Affiliations
Corresponding author
Rights and permissions
About this article
Cite this article
Sodhi, M., Tang, C. Determining supply requirement in the sales-and-operations-planning (S&OP) process under demand uncertainty: a stochastic programming formulation and a spreadsheet implementation. J Oper Res Soc 62, 526–536 (2011). https://doi.org/10.1057/jors.2010.93
Received:
Accepted:
Published:
Issue Date:
DOI: https://doi.org/10.1057/jors.2010.93