Analytics to Simulate Likely Outcomes

Abstract

Decision makers deal with uncertainty when considering future scenarios. Future performance levels depend on multiple influences with uncertain future values. To estimate future performance, managers make assumptions about likely future scenarios and uncertain future values of performance drivers. Monte Carlo simulation can be used to simulate random samples using decision makers’ assumptions about performance driver values, and those random samples can then be combined to produce a distribution of likely future outcomes. Inferences from a simulated distribution of outcomes, given assumptions, can then be made to inform decision making and to adjust assumptions.

11.1 Electronic Supplementary Material(s)

Cybertruck master (XLSX 224 kb)

WFM master (XLSX 1281 kb)

Appendices

Excel 11.1: Analytics for Monte Carlo with Excel

To generate a sample of Normally distributed possibilities, given assumptions, first, specify the expected value and the standard deviation for a performance driver.

Organic Food and Nonfood Market

After reviewing the recent trend in the organic food and nonfood market, executives assume that the market will continue to grow, but at a slightly slower rate, with expected annual growth of 7.5% and a standard deviation of .75%. From the Data Analysis menu, choose Random Number Generation:

• Enter Number of Variables 4

• Number of Random Numbers 1000

• Distribution Normal

• Mean .07

• Standard deviation .0075

• Output Range L2

Add labels for the four columns of likely organic market growth in 2017 through 2020:

Create four columns of resulting organic market size in 2017 through 2020.

In column P, grow current market size, $47(B), in C6 with 2017 growth from column L (Lock the cell reference to C6 with function 4.)

In column Q, grow the 2017 market in column P with growth in column M:

Similarly, in columns R and S, grow the 2018 (then 2019) market with growth in column N (then O):

Excel 11.2: Analytics Identify the Range of Likely Possibilities, Given Assumptions

To identify the range of likely possibilities, given assumptions, in P1003 and P1004, use

• =percentile(P2:P1001,.975)

• =percentile(P2:P1001,.025)

• Then select these two cells, P1003:P1004,

• Shift+right

• Cntl+R

To right fill columns Q through S.

These will provide the 95% confidence interval for each column:

To visually assess the likely outcomes, given assumptions, plot the 95% lower and upper prediction interval bounds with actual market size in 2014 and 2015. Copy and paste those actual sizes in B5:B6 and paste below the prediction intervals. Set the upper and lower prediction interval bounds in each year. For example, the 2017, the upper bound

• =P1003

Now the forecast can be plotted. Select O1006:Q1012 and request a scatterplot,

Alt N D

Adjust the axes to make good use of space. Click on an axis, then Cntl+1 to open the axis menu. Change the vertical axis minimum to 40.

Change the horizontal axis minimum to 2015 and maximum to 2020.

• Add a vertical axis label

• Alt J C A A V

• Add a stand alone chart title

Change the color of one of the lines to match the other. Click on a line, right click, Format Series:

Click the paint can, Line, and change the color, then Marker, Fill and Border to change color.

To complete the analytics for WFM market share, repeat these steps. From uncertain store growth rates (columns T through W) grow WFM stores (columns X through AA). From uncertain sales per store growth rates (columns AB through AE), grow WFM sales per store (columns AF through AI). In columns AJ through AM, find WFM revenues from those, and then, in columns AN through AQ, find WFM share from WFM revenues and the organic market columns. Thus, assumptions regarding a total of twelve uncertainties (three for each of 4 years) will produce likely future outcomes.

Case 11.1: Forecasting Cybertruck’s 2024 Share of Pickup Sales

In November 2019, Tesla revealed its new electric pickup, the Cybertruck. The baseline model will sell for $39K, with a self driving option available in the future for $7K, given completion of software. The reveal included a “tug of war” with the Ford F150, the best selling pickup in the World (Cybertruck won). Ford will soon begin production of an electric F150.GM is also planning to launch an electric pickup, and there are a couple of small players offering electric pickups.

The reveal also pitted the new truck against Porsche 911 in an acceleration comparison (Cybertruck won). Cybertruck’s towing capacity greatly exceeds competitors’ trucks. However, the angular design turned off some investors. Tesla stock price fell about 7% after the reveal. While the modernistic design may appeal to pickup drivers on the West Coast, it seems unlikely that many truck owners in the Midwest would trade their traditional trucks for Cybertruck.

To see the Cybertruck: https://www.tesla.com/cybertruck.

Forecast Cybertruck’s 2024 share of the pickup sales, the first full year of production, given several assumptions:

• (i) Pickup truck sales have sometimes increased and sometimes decreased in recent years. These are shown in pickups.xlsx. Assume that growth in pickup sales in 2023 from 2022 will be −.10 to .02 (or −10% to 2%)of those in 2022 and growth in pickup sales in 2024 from 2023 will be −10% to 2% (or −.10 to .02).

• (ii) The Cybertruck received 146,000 preorders within 48 hours of the reveal. Tesla’s popular Model 3, by comparison, received 276,000 preorders within 48 hours of its reveal. That translated to sales of 145,846 Model 3s in 2018, the first full year of production, or 53% of early preorders. Assume that Cybertruck sales in 2024, the expected first full year of production, will be between 43% and 63% of early preorders.

• 1. Plot pickup sales in 2013 through 2024, given assumptions, showing the 95% prediction intervals for 2023 and 2024.

• 2. Given assumptions, what is the 95% prediction interval for Cybertruck sales in 2024, specifying units and rounding to two or three significant digits?

• 3. In 2022, electric pickup trucks accounted for 1.8% of pickup sales. Given assumptions, what is the 95% prediction interval for Cybertruck sales share (percent) of pickup sales in 2024, specifying units, rounding to two or three significant digits?

• 4. If you want to reduce the uncertainty in the 2024 share prediction, which assumption should you change?