Business case study. It has only two questions

Page 1 of 7

Monte Carlo Simulation for Stock Price Prediction

This exercise shows everyone a useful application of Monte Carlo simulation, predicting the possible

stock price in a few months. Similar to the example I showed everyone, we need to use RAND() to

generate random numbers for the unknown factor in each scenario. We then need to use the

VLOOKUP() function to find the value corresponding to the random number. The unknown factor in this

assignment is the stock price change.

Since we have known that the stock price change follows a discrete probability distribution, we should

first build the probability interval (as shown in the picture below), so that we can find the stock price

change corresponding to the random numbers in different scenarios.

In the data file, I have built the probability interval for you. So you don’t need to build the interval again.

For your practical work, you can use the similar design format, which has three sections in the data set:

Listing the unknown factor in the section above, and then listing the probability distribution information

in the section in the middle, and then listing the simulation in the section below. Please see the data file.

Part a.

We have known that stock price change is the unknown factor. In addition, the stock price changes over

the 3-month period are independent. Therefore, when we want to predict the price in 3 months, in 6

months, in 9 months, and in 12 months, we must predict the stock price change separately. We do the

following:

In cell B3, type in =VLOOKUP(RAND(),$A$15:$D$21,3,TRUE)

Then, drag the autofill button of B3 to B4, B5, and B6, as shown in the picture below. By doing this, we

ask the software to generate an independent random number for each time period, and then the

software looks up each random number, and finds the corresponding stock price change for 3 months, 6

months, 9 months, and 12 months.

In cell B8, type in =33+B3

Page 2 of 7

This will be the predicted price in 3 months. Drag autofill button of B8 to B9, B10, and B11. These are the

predicted stock prices in 6 months, 9 months, and 12 months.

We are ready to create simulation scenarios. Suppose we are interested in building 200 scenarios. From

cell A24 to A223, I created a number sequence from 1 to 200. For your practical work, you can create

any number of scenarios, e.g., 1000 scenarios, 8000 scenarios, etc. Certainly, the large the number is,

the more accurate the analyses will be. Here I chose 200 to simply the demonstrations.

In cell B24, type in =B8

In cell C24, type in =B9

In cell D24, type in =B10

In cell E24, type in =B11

Select the range A24 to E223, and then Click Data Tab on the top, and then What if Analysis, and then

Data Table

Page 3 of 7

In the Data Table window, click on cell D1 for the Column input cell option, and then click OK.

Note:

1. Why do we choose Column input cell?

Whether we choose Row input cell or Column input cell depends on how we organize the changing values.

In our case, the changing values from cell B8 to B11 are listed in one column. So we should choose Column

input cell option.

2. Why do we choose D1?

In fact, you can choose any empty cell as the value for the Column input cell. Because we have RAND()

function in B8 to B11, the software will generate new values every time when we perform the simulation.

We only need to choose an empty cell to inform the software we want to use the “Column input cell”

option to copy the math equations we built in cell B8 to B11. The software will calculate the stock prices

in 3 months, 6 months, 9 months, and 12 months automatically, and fill out the rest of the simulation

scenarios.

You can see the predicted stock prices in 200 scenarios for 3 months, 6 months, 9 months, and 12 months

after clicking the OK button.

Part b.

The purpose of Monte Carlo simulation is to make better decisions. Thus, creating the simulation scenarios

is only half way through in the decision support process. Next, we want to estimate the stock prices in 3

months, 6 months, 9 months, and 12 months. I will introduce two options to you.

Option 1: Average the simulated price.

Since we predicted 200 possibilities for the stock prices, we can then use the average value of these 200

possible prices as the final predicted price.

In cell B224, type in =AVERAGE(B24:B223)

This will be the predicted price in 3 months.

Page 4 of 7

In cell C224, type in =AVERAGE(C24:C223)

This will be the predicted price in 6 months.

In cell D224, type in =AVERAGE(D24:D223)

This will be the predicted price in 9 months.

In cell E224, type in =AVERAGE(E24:E223)

This will be the predicted price in 12 months.

This option is intuitive, but it has several weaknesses. For instance, except for an average value, we don’t

have any other insight about the prediction.

Option 2: More Insights

We use the following strategy to get more insights about our predictions. I use the 12 month prediction

as an example. You can use similar procedures to get more information about the 3 month prediction, 6

month prediction, and 9 month prediction.

In cell I25, type in =COUNTIF($E$24:$E$123,"31")

By doing this, we can calculate the frequency of $37 twelve month later.

In cell I26, type in =COUNTIF($E$24:$E$123,"32")

By doing this, we can calculate the frequency of $38 twelve month later.

In cell I27, type in =COUNTIF($E$24:$E$123,"33")

By doing this, we can calculate the frequency of $39 twelve month later.

In cell I28, type in =COUNTIF($E$24:$E$123,"34")

By doing this, we can calculate the frequency of $40 twelve month later.

In cell I29, type in =COUNTIF($E$24:$E$123,"35")

By doing this, we can calculate the frequency of $41 twelve month later.

In cell I30, type in =COUNTIF($E$24:$E$123,"36")

By doing this, we can calculate the frequency of $42 twelve month later.

In cell I31, type in =COUNTIF($E$24:$E$123,"37")

By doing this, we can calculate the frequency of $37 twelve month later.

Page 5 of 7

In cell I33, type in =(I25+I26)/200

By doing this, we can calculate the probability that the stock price is lower than $37 in 12 months.

Select cell I25 to I31, and then click Insert tab on top, and then choose Column, 2-D Column to create a

predicted price distribution in 12 months.

Select the chart we just created, and then click Design tab on the top, and then click Select Data

Page 6 of 7

Click Edit under Horizontal (Category) Axis Labels.

Select cell I25 to I31. With your selection, the range in the window will change accordingly. Click OK after

the selection.

Page 7 of 7

You can see that the labels on the x axis have changed to $37, $38, $39, … $43, click OK.

By doing this, we can create a frequency distribution chart to show data users which price is likely to

happen 12 months later. This visual demonstration of analysis results is greatly appreciated by higher level

managers.