SPREADSHEET-BASED SUPPLY CHAIN SIMULATION FOR TEACHING RISK POOLING COMBINED WITH FACILITY LOCATION

Andrew Arthur Tiger

Angelo State University, United States

E-mail: atiger@angelo.edu

John Loucks

St. Edwards University, United States

E-mail: johnsl@stedwards.edu

Connor Burns

Renasant Bank, United States

E-mail: cburns@uu.edu

Submission: 1/8/2019

Revision: 2/10/2019

Accept: 2/27/2019

ABSTRACT

Inventory risk pooling is the use of centralized inventory to maintain lower overall inventory and safety stock. Generally, in a decentralized system with no risk pooling, a retailer maintaining its own inventory and safety stock requires higher levels of inventory than would be if the inventory was pooled at a centralized location. Most pedagogy relies on algebraic formula-based examples, which has two issues: (1) student struggle visualizing variability concepts and algebraic notation and (2) the location of the centralized inventory location and its effect on lead times are often ignored or assumed to be negligible. To address these issues, the spreadsheet-based simulation offers visual cues of the inventory reduction benefits, as well as demonstrating the impact of the facility location. The simulation was developed using MS Excel, and has a simple, easy-to-use interface, as well as easy-to-understand graphical and statistical output. Both undergraduate and MBA students respond favorably to the simulation because it helps them understand the math concepts, and it strengthens their spreadsheet skills. The inventory policy is an order-up-to-level with a weekly periodic review system. Backorders must be filled. Inputs include the number of customers, desired service level, weekly customer standard deviation of demand, lead times and inventory order-up-to levels. Outputs include weekly and annual inventory and service levels for both the decentralized and centralized (risk pooling) systems.

Keywords: risk pooling, facility location, supply chain modeling, spreadsheet-based simulation

1. INTRODUCTION

Inventory risk pooling spans multiple operations management practices: inventory management, logistics/supply chain, and facility location. Successful implementation of inventory risk pooling either reduces inventory levels while maintaining required service levels or increases service levels without increasing inventory levels. Because of its potential benefits, inventory risk pooling is covered in many textbooks (HOPP, 2008), scholarly journal articles (KUMAR; TIWARI, 2013; OZSEN; COULLARD; DASKIN, 2008; SCHUSTER PUGA; TANCREZ, 2017; SNYDER; DASKIN; TEO, 2007; ZHANG; et al., 2016), and teaching websites including YouTube (MARQUEZ, 2018; MILLAR, 2018; W.P. CAREY SCHOOL OF BUSINESS, 2018).

Of note, the W.P. Carey School of Business YouTube video has over 1.4 million views. However, our investigation of the pedagogy identified gaps that we address in this paper: (1) absence of a simple spreadsheet-based simulation teaching tool that demonstrates the power of inventory risk pooling and (2) the impact of the location of the distribution center (DC), that could negate or lessen the advantages of risk pooling in certain situations. For (2), much research exists that considers location. See the above citations; however, we found no pedagogy that combines risk pooling decisions with location decisions.

This paper presents and offers for use a spreadsheet-based supply chain simulation that compares a decentralized supply chain that offers no risk pooling benefits to a centralized supply chain that offers risk pooling benefits through the addition of a distribution center located between a manufacturing plant and customers. Figure 1 shows the network for each.

Figure 1: A decentralized supply chain vs. a centralized/risk pooling supply chain.

This paper’s organization and contributions are as follows. In the next section, the theory and benefits of inventory risk pooling are presented. These are summarized from many previous studies, and we try to present them as simply and clearly as possible. Next, the limitations or potential disadvantages of risk pooling are presented. Again, these are from the literature and usually address the assumptions of the underlying demand distributions.

Next, we discuss the importance of simulation, specifically spreadsheet-based simulation, as a valuable pedagogy because of simulation’s inherent trait of allowing the user to perform what-if analysis. In the modeling methodology section and appendix, the spreadsheet formulas and logic are explained in addition to assumptions, parameters, and limitations. In addition, the risk pool spreadsheet-based simulation is freely available here.

In the analysis section, the impact of the DC location is shown through use of the simulation. Additional scenarios demonstrate how risk pooling benefits can change based on the demand variability and the number of customers. Finally, we conclude by summarizing the research and suggest next steps including applications in real systems.

2. THEORY AND BENEFIT WHEN THE DC LOCATION IS NOT CONSIDERED

Hopp’s (2008) summarizes the principle of risk inventory variability pooling:

Combining sources of variability so that they can share a common buffer reduces the total amount of buffering required to achieve a given level of performance… The variability buffering law is very general and fundamentally simple. It is basically the law of averages causing multiple sources of variability to cancel one another out.

Mathematically, the standard deviation of aggregated demand, s_a, is the square root of the sum of each location’s variance (s_i)² plus covariance for each location pair as shown from Oeser (2010).

(1)

where r_i,j is the correlation coefficient of the value of the random variable for location i and j. In this paper, the correlation between any two locations/customers is assumed to be zero; therefore, the standard deviation of aggregate demand simplifies to

(2)

The power and benefit of this equation is that the standard deviation of aggregated demand is less than or equal to the sum of standard deviations of demand, s_i, at the n locations

(3)

As Hopp (2010) states, the effectiveness of pooling is affected by the magnitude of variability from the individual sources and the number of individual sources.

An Example - Assuming the desired service level is 95% and that each location’s mean and standard deviation of demand are ten and three, respectively, Table 1 and Figure 2 show the benefits of risk pooling. For ten locations, the sum of standard deviations of demand is equal to 30, which is much larger than the aggregated standard deviation of 15.6, or

15.6 = (4)

This produces a risk pooling safety stock that is only 32% of the safety stock without risk pooling.

Table 1: Risk pooling benefits in safety stock

Figure 2: Risk pooling benefits: safety stock levels vs. number of locations.

From Table 1 and Figure 2, inventory risk pooling can be extremely beneficial. However, occasions exist that risk pooling is not as beneficial as Table 1 and Figure 2 would indicate. Yang and Schrage (2009) demonstrated that product risk pooling is not always beneficial for supply chains with product substitutions and demand with distributions skewed to the right. Others have shown that risk pooling is not always favorable depending on the demand distributions.

Also, Schmitt et al. (2015) showed that decentralized supply chains reduce cost in situations where the supply chain has disruptions. Also, as stated earlier, Hopp (2010) states that the benefit is risk pooling is very dependent on how much variability exists and the number of locations that are pooling.

For systems with little variability or few customers, risk pooling does not offer substantial benefits. However, we struggled finding research that clearly demonstrates that the location of the pooling distribution center, relative to production and customers, is important in determining risk pooling benefit.

3. THE RISK POOLING SIMULATION MODEL

Simulation is a successful management science technique in business and industry. As a pedagogy, simulation allows students to see the impact of decisions without initially becoming mired in the mathematical notation of stochastic systems. See the equations above as examples.

One of the most famous simulation games is MIT’s beer game (FORRESTER, 1958), which teaches how the bullwhip effect occurs due to a lack of coordination and communication within supply chain levels. Simulation’s success as a training tool is evident by websites devoted to simulation for training and education such as Forio (HTTPS://FORIO.COM).

Simchi-Levi et al. (2008) offers a CD-based Risk Pool game as a supplement to their textbook, “Designing and Managing the Supply Chain, 3e”. Although in Spanish, a YouTube video does provide some instruction on its use (MANRIQUE, 2018). Tzimerman, Herer, and Shtub (2013) discuss the effectiveness of teaching supply chain management using simulation based training.

Anderson and Morrice (2000) used simulation games to teach the impact of information sharing on service capacity. Although some options do exist, the Simchi-Levi game is an executable file that does not allow any modification or offer visibility of its logic. Additionally, the inputs to the game are inventory decisions. Location decisions are not choices.

Spreadsheets can be converted into simulation by using their ability to generate random numbers such as MS Excel’s rand() function. The benefit of spreadsheets is the accessibility by students as well as allowing the students to understand how to use spreadsheets to model supply chain processes.

Boute and Lambrech (2009) and Tiger, Benco, and Fogle (2006) are two of many that have developed spreadsheet-based supply chain games similar to MIT’s beer game. However, the authors could not find a spreadsheet-based simulation of risk pooling.

Due to the lack of a pedagogical-focused spreadsheet-based simulation for risk pooling and absence of a distribution center’s location on the benefits of inventory risk pooling, we have developed a spreadsheet-based risk pooling simulation. The simulation allows users to determine the location of a distribution center relative to the production facility (i.e. the plant) and to the customers.

The simulation also allows user to control the amount of demand variability and the number of customers. All these dials affect the benefit of inventory risk pooling. For certain values, a decentralized supply chain without risk pooling is more beneficial with regard to inventory levels to achieve a desired service level.

The appendix provides more detail on how MS Excel was used to model a stochastic multi-period, multi-echelon supply chain. Provided with the same weekly demand, the simulation contrasts two systems: (1) a decentralized supply chain in which a plant ships directly to customers with a lead time of exactly ten weeks and (2) a risk pooling, centralized supply chain with a distribution center (DC) between the plant and customers. The user controls the location of the DC; consequently, the user controls the lead time between the DC and customers.

If the user wants to select a location closest to customers, a lead time of nine weeks should be selected, which will produce a nine-week lead time between the plant and DC. The total lead time from the plant to the customer is always a total of ten weeks. Figure 3 shows the two supply chain designs when the DC is one week away from customers.

Figure 3: Decentralized vs risk pooling: 10-week lead time vs. 9/1week lead times.

The simulation is composed of 100 weeks, where weekly demand is normally distributed with an average customer demand of 10 units. The user controls the number of customers as well as the weekly demand variability. The inventory policy at both the customers and DC is an order-up-to level. Each week, both customers and the DC determine how much, if any, to order.

Customer demand must be met. That is, lost sales do not occur. Receivables at the customer must first meet backorders before filling current orders. The spreadsheet model has fifteen worksheets; however, only the ‘Dashboard’ and ‘Output’ are visible, with the remaining worksheets hidden. Figure 4 shows the Dashboard, and Table A1 in the appendix provides a description for all worksheets within the file.

Figure 4: Risk pooling simulation dashboard.

In the ‘Dashboard’ worksheet, the user can change the simulation parameters, which are shown as green shaded cells. To maintain simplicity and purposefulness, only four inputs exist, and Table 2 provides their meaning and possible values. The worksheet is protected. Unprotecting allows the other parameters to be changed.

Table 2: Input parameters

Cell Reference	Description	Possible Values
C3	Number of Customers	1 – 10 (integer)
C4	Desired Service Level	80.00% - 99.99%
C7	Weekly Customer Demand SD	0.1 – 10.0
I4	Lead Time from Plant to DC	1 – 9 (integer)

The ‘Dashboard’ worksheet also provides the supply chain design and three charts that display the simulation results. The chart on the left shows the weekly on-hand inventory for each system for the 100 weeks. In Figure 4, it can be seen that risk pooling maintains less inventory than the decentralized system. In-transit inventory is not included in this chart.

In-transit inventory is not affected by differences in these supply chain designs; and is the same in both systems. The middle chart shows the average inventory level for both systems. Note that the risk pooling system has inventory at both the customers and the DC. In Figure 4 above, the risk pooling average level is about 25% less than the decentralized system.

The chart on the right shows the actual service level for each system. The service level is weekly-specific. That is, a service outage occurs for a week if at least one item is backordered.

Although the simulation could be modified in many different ways, we have strived for simplicity, focusing on how facility location, lead time, service level and demand variability impact inventory and service levels. Consequently, we have avoided using any type of costs or financial analysis.

Also, customer demand is normally distributed, and the average weekly demand is a constant ten units, allowing more focus on variability. The DC location is always equidistant from each customer, which is not totally realistic; however, we feel that the complexity added by allowing the DC to be closer to some customers exceeds its benefit. Minimum order and shipping quantities are not considered. Also, the periodic review interval is always one week.

A very powerful addition would be using VBA code or incorporating the @RISK add-in (@RISK: RISK ANALYSIS USING MONTE CARLO SIMULATION IN EXCEL AND PROJECT - PALISADE – PALISADE, 2018) to run a series of simulated trials for a range of inputs. However, the analysis, while realistic, becomes too intricate for the model to be applied as a teaching tool.

4. QUESTIONS AND ANALYSIS USING THE SIMULATION MODEL

As a teaching tool, the following four questions demonstrate the power of risk pooling by requiring the student to use the simulation in a series of experiments. Four different inputs are evaluated: (1) number of customers, (2) DC to Customer lead time, (3) weekly customer-specific demand standard deviation, and (4) desired service level.

For each question, the student should change the question-specific input over a range of values and record the subsequent inventory levels for both the decentralized supply chain and the risk pooling supply chain. The student can record the output in the existing spreadsheet, but it might be best to require them to copy/paste the inputs and outputs to another Excel file to ease homework submission (and grading).

After recording, the student can use Excel’s Scatterplot graph to display the inventory levels corresponding to the question-specific input. Note: For the results generated for this paper, we used VBA to record multiple runs of the model. Although not included the VBA-based model is available upon request.

4.1. Assuming service level, demand variability, and the lead time from DC to Customer remain constant at 95%, 4 units, and 1 week, respectively, what is the change in inventory when the number of customers increases?

Solution - Cells C4, C7, and I4 should be set to 95%, 4, and 1. Cell C3 should be changed from 1 to 2, 3 … 10. For each change in the number of customers, the inventory levels in cells L4 and L18 in the ‘Output’ worksheet should be recorded. The student should record multiple repeated results since the simulation output is variable. To simulate the same set of inputs again, the student can use the ‘F9’ function key. Repeated results help identify trends that a single simulation might not capture. Figure 5 is a sample output.

Figure 5: Inventory level vs. number of customers.

Risk pooling is clearly beneficial when the number of customers is increased. For the given scenario, the breakeven point appears to be around 3 customers. For both decentralized and risking pooling supply chains, the inventory increases are linear; however, the slope of risk pooling is less than the decentralized. Equations (5) and (6) provide the safety stock formula for a decentralized supply chain and a risk pooling supply chain including the weekly order interval, respectively (INVENTORY MANAGEMENT AND RISK POOLING, 2000).

(5)

(6)

where

Z	Z-score based on the desired service level percentage.
sd	demand standard deviation
N	number of customers
LT P to C+1	lead time from the plant to the customer in a decentralized supply chain with weekly order intervals
LT P to DC+1	lead time from the plant to the distribution center in a risk pooling supply chain with weekly order intervals
LT DC to C+1	lead time from the distribution center to the customer in a risk pooling supply chain with weekly order intervals

For our problem, the lead time is 10 weeks from the plant to customer for a decentralized system. For the risk pooling supply chain, the 10 weeks in separated into a 9-week lead time from plant to distribution center, followed by a one-week lead time from distribution center to customer.

Using those values, Table 3 shows that inventory levels are lower for a decentralized supply chain with 1 or 2 customers; almost the same for 3 customers, and higher with 4 or more customers.

Table 3: Safety Stock Inventory Levels vs Number of Customers

4.2. Assuming the service level, demand variability, and the number of customers remain constant at 95%, 4 units, and 10 customers, respectively, what is the change in inventory when the lead time from the distribution center to the customers changes?

Solution - Cells C3, C4, and C7 should be set to 10, 95%, and 4, respectively. Cell I4 should be changed to values from 1 to 9 in one unit increments, which will keep the total lead time at 10 weeks; however, the DC moves closer to the customer. For each change in the lead time from distribution center to customer, the inventory levels in cells L4 and L18 in the ‘Output’ worksheet should be recorded. The student should record multiple repeated results to help identify trends that a single observation might not capture. Figure 6 is a sample output.

Figure 6: Inventory levels vs. lead times (plant to dc).

From the graph, risk pooling is beneficial when the distribution center is closer to the customer. This result is somewhat intuitive. As the distribution center moves farther away from the customers and nearer to the plant, the power of risk pooling diminishes since the system essentially becomes a decentralized system with an extra layer of inventory. Additionally, the math proof provided in equations (5) and (6) can easily be shown that as the lead time increases, the benefit of risk pooling decreases. See Table 4.

Table 4: Inventory levels and lead times

4.3. Assuming the service level, lead time from distribution center to plant, and number of customers remain constant at 95%, 9 weeks, and 10 customers, respectively, what is the change in inventory when the demand standard deviation increases?

Solution - Cells C3, C4, and I4 should be set to 10, 95%, and 9. Cell C7 should be changed to values from 0 to 10. For each change in demand standard deviation, the inventory levels in cells L4 and L18 in the ‘Output’ worksheet, should be recorded. Again, the student should record multiple repeated results to help identify trends that a single observation might not capture. Figure 7 is a sample output. In the chart, the x-axis is the coefficient of variation, which is easily calculated since the average demand remains constant. As the scatterplot shows, risk pooling’s benefit grows as demand variability increases.

Figure 7: Inventory levels and demand variability.

4.4. Assuming the demand variability, lead time from plant to distribution center, and number of customers remain constant at 4 units, 9 weeks, and 10 customers, respectively, what is the change in inventory when the required service level changes?

Solution - Cells C3, C7, and I4 should be set to 10, 3, and 1. Cell C4 should be changed from values [75%, 99.99%]. Note, the simulation does not allow values less than 75% or more than 99.99%. For each change in the desired service level %, the inventory levels in cells L4 and L18 in the ‘Output’ worksheet should be recorded. Figure 8 is a sample output. As the scatterplot shows, risk pooling is preferred. Additionally, for higher service levels, risk pooling’s benefit increases. Also, as the service level approaches 100%, inventory levels for both supply chain systems increases nonlinearly. Another insight from Figure 8 is that risk pooling offers a higher service level compared to a decentralized system with the same inventory level.

Figure 8: Inventory levels vs. service level.

5. IMPACT AND CONCLUSION

Inventory risk pooling is a vital tool for supply chain design and inventory decisions, and most pedagogy focuses on simplified algebraic examples that ignore a critical aspect: the location of the distribution center.

To address this important consideration, we developed a spreadsheet-based risk pooling simulation that demonstrates the importance of risk pooling, including limitations of its effectiveness based on distribution center location, variability of demand, the number of customers, and service level requirements.

The simulation is easy to use and offers easy-to-understand graphics of inventory and service performance. Also included is a series of questions that can be used in the classroom to teach students how to use the simulation and analyze the effect of the above parameters.

Finally, the authors have effectively implemented similar models in business suggesting that the spreadsheet models offer more than pedagogical benefits. Industry and business rely heavily on Excel’s ability to model real world systems, and this simulation is an excellent application of prescriptive analytics.

Although no formal research has been performed to evaluate the effectiveness of the simulation model as a teaching tool, the authors have used the simulation model both in the classroom and as the basis for independent studies. In fact, the origins of the model began as an independent research study.

From course evaluations, as well as student and alumni feedback, learning how to develop prescriptive analytical tools within MS Excel is the most impactful outcomes from courses taught by the authors.

REFERENCES

ANDERSON, E. G. J.; MORRICE, D. J. (2000) A simulation game for teaching service-oriented supply chain management: does information sharing help managers with service capacity decisions? Production and Operations Management, March, v. 9, n. 1, p. 40-55.

ANON. (2000) Inventory management and risk pooling. Available: https://www.slideshare.net/pirama2000/3-inventory-management-and-risk-pooling. Access: 14/02/2019.

ANON. (2016) Supply Chain Risk Pooling: Definition & Purpose - Video & Lesson. Available: https://study.com/academy/lesson/supply-chain-risk-pooling-definition-purpose.html. Access: 14/02/2019.

ANON. (2018) @RISK: Risk Analysis using Monte Carlo Simulation in Excel and Project - Palisade – Palisade. Available: https://www.palisade.com/risk/. Access: 14/02/2019.

BOUTE, R. N.; LAMBRECHT, M. R. (2009) Exploring the bullwhip effect by means of spreadsheet simulation. INFORMS Transactions on Education, September, v. 10, n. 1, p. 1-9.

FORRESTER, J. W. (1958) Industrial dynamics: a major breakthrough for decision makers. Harvard Business Review, v. 36, p. 37-66.

HOPP, W. J. (2008) Supply chain science. 1 ed. Boston: McGraw-Hill/Irwin.

KUMAR, S. K.; TIWARI, M. (2013) Supply chain system design integrated with risk pooling. Computers & Industrial Engineering, February, v. 64, n. 2, p. 580-588.

MANRIQUE, E. D. (2018) The risk pool game: gameplay. Available: https://www.youtube.com/watch?v=pCAeHWIDX3M. Access: 14/02/2019.

MARQUEZ, A. (2018) Risk pooling 1. Available: https://www.youtube.com/watch?v=Y79qRqq4HhE. Access: 14/02/2019.

MILLAR, D. H. (2018) Inventory Management and Risk Pooling in the Supply Chain Part 1. Available: https://www.youtube.com/watch?v=jDI9MAV_pyw. Access: 14/02/2019.

OESER, G. (2010) Methods of Risk Pooling in Business Logistics and Their Application. Dissertation. Frankfurt: Europa-Universitat Viadrina Frankfurt (Oder).

OZSEN, L.; COULLARD, C. R.; DASKIN, M. (2008) Capacitated warehouse location model with risk pooling. Naval Research Logistics, June, v. 55, n. 4, p. 295-312.

SCHMITT, A. J.; SUN, A.; SNYDER, L. V.; SHEN, Z.J. M. (2015) Centralization versus decentralization: risk pooling, risk diversification, and supply chain disruptions. Omega, April, v. 52, p. 201-212.

SCHUSTER PUGA, M.; TANCREZ, J. S. (2017) A heuristic algorithm for solving large location–inventory problems with demand uncertainty. European Journal of Operational Research, June, v. 259, p. 413-423.

SIMCHI-LEVI, D.; KAMINSKY, P.; SIMCHI-LEVI, E. (2008) Designing and managing the supply chain, 3 ed. New York: McGraw-Hill.

SNYDER, L.; DASKIN, M. S.; TEO, C.P. (2007) The stochastic location model with risk pooling. European Journal of Operational Research, v. 179 n. 3, p. 1221-1238.

TIGER, A. A.; BENCO, D. C.; FOGLE, C. (2006) Teaching the importance of information, supply chain management, and modeling: the spreadsheet beer-like game. Issues in Information Systems, v. 7, n. 1, p. 108-113.

TZIMERMAN, A.; HERER, Y.; SHTUB, A. (2013) In: APMS: IFIP INTERNATIONAL CONFERENCE ON ADVANCES IN PRODUCTION MANAGEMENT SYSTEMS. Berlin, Proceedings …: Berlin: 2013.

W.P. CAREY SCHOOL OF BUSINESS (2018) Module 1: What is supply chain management? Available: https://www.youtube.com/watch?v=Mi1QBxVjZAw. Access: 14/02/2019.

YANG, H.; SCHRAGE, L. (2009) Conditions that cause risk pooling to increase inventory. European Journal of Operational Research, February, v. 192, n. 3, p. 837-851.

ZHANG, Y.; SNYDER, L. V.; QI, M.; ZHANG, Y. (2016) A heterogeneous reliable location model with risk pooling under supply disruptions. Transportation Research: Part B., January, V. 83, p. 151-178.

APPENDIX A: RISK POOLING SPREADSHEET SIMULATION DESIGN AND FORMULAS

This appendix provides more detail into how MS Excel formulae were used to model a multi-period, multi-echelon supply chain inventory system. The design is provided, including each worksheet’s purpose. Following, important formulas are explicitly explained including: generating random demand, inventory position, quantity on order, and delivery date (week).

Table A1 lists the worksheets in the model and their descriptions. Only the two worksheets ‘Dashboard’ and ‘Output’ are unhidden; however, the user can unhide any of the worksheets. Ten customer worksheets exist: C1 through C10. Each is identical except that demand is unique for each customer. For each customer worksheet, both the decentralized and risk pooling logic are contained. For risk pooling, another worksheet, ‘DC’, holds the logic for customer orders and replenishment quantities from the plant.

Finally, a separate worksheet shows a fixed order quantity model that allows the user to change the order interval and lead time. It is not related to the risk pooling model.

Table A1: Worksheet descriptions

Worksheet Name	Description
Dashboard	The input and primary output worksheet.
Output	Shows average inventory and service level output in tabular form.
DC	This worksheet holds the logic and formula for the distribution center. Order requests from each customer are aggregated. From the aggregation, plant orders are generated. If inventory is not sufficient to meet customer demand, customers with a lower ID are served first. That is, customer 1 is allocated before customer 2, and so on.
C1	The worksheet holds the logic and formulas for customers, including demand generation, inventory checks, reorder quantities and delivery from the Plant (decentralized), or DC (risk pooling). Both decentralized and risk pooling logic is within this worksheet. Random demand, normally distributed, based on the ‘Dashboard’ inputs is generated in column ‘F’.
C2 – C10	Same as worksheet ‘C1’, however demand values are unique for each customer.
FOI	Independent Fixed Order Interval simulation model.

To generate random demand from a normal distribution, each customer’s weekly demand is represented by

ROUND(NORMINV(RAND(),Dashboard!$C$6,Dashboard!$C$7),0) (7)

where the mean and standard deviation are in cells C6 and C7, respectively, of the ‘Dashboard’ worksheet.

For the decentralized policy, Inventory position formulas are shown in Figure A1. In the figure, the inventory position for week 2 is the sum of the beginning inventory (D5), order quantity delivered (E5), in transit inventory (M12) minus demand (F12) and backorders (I12). The formula logic is the same for both decentralized and risk pooling systems.

Figure A1: Customer formulas: inventory position, order quantity, and delivery week.

For risk pooling, additional logic is needed: quantity to be delivered. Quantity to be delivered, shown in Figure A2 takes into consideration if the distribution center has enough on-hand inventory to meet customer orders.

Figure A2: Quantity to be delivered references the dc worksheet.

For all risk pooling, an additional worksheet, DC, is used to aggregate customer order requests as well as allocate which orders are to be met when limited inventory exists. Figure A3 shows the amount delivered to customers while giving prior to customers with smaller IDs.

Figure A3: Excel formula for dc meeting demand for customer 2 while giving priority to customer 1.