INstructions attached below
|Stock Price Prediction App|
|Predicted Change in 3 Months||0|
|Predicted Change in 6 Months||0|
|Predicted Change in 9 Months||1|
|Predicted Change in 12 Months||1|
|Stock Price in 3 Months||33|
|Stock Price in 6 Months||33|
|Stock Price in 9 Months||34|
|Stock Price in 12 Months||34|
|Lower Bound||Upper Bound||Price Change ($)||Probability|
|Scenario||Price in 3 Month||Price in 6 Month||Price in 9 Month||Price in 12 Months||12 Month Insights|
|10||P (Price < 37)|
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.
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
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
Page 3 of 7
In the Data Table window, click on cell D1 for the Column input cell option, and then click OK.
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
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.
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
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
Business Case 4
BUSA 421 Data Mining
Instructor: Prof. Brodnax
INCOME _ DISTRICT _ CONDITION
B&R Oil & Gas Ltd. stock is listed on the NASDAQ stock exchange currently at $33 per share. The
following probability distribution shows how the price per share is expected to change over a three-
a). Construct a simulation model that computes the value of the stock price in 3 months, 6 months,
9 months, and 12 months under the assumption that the change in stock price over any 3-month
period is independent of the change in stock price over any other 3-month period. Describe in detail
what the result from the data run indicates.
b). With the current price of $33 per share, simulate the price per share for the next four 3-month
periods. What is the average stock price per share in 12 months? Again, describe in detail what the
result from the data run indicates.
Provide a thorough analysis for your answers to both a. and b. in the analysis word document. Be
sure and use the correct notations that explains all equations and variables as appropriate. You
should paste in appropriate charts/graphs into the word document that explains your answer.
Remember, excel is a tool that helps take the data and transform it into information which then
allows you to make an informed decision. The analysis is not in the tool, but in the interpretation of
the results from the tool. The stock price prediction app excel template is posted with this business
case. Note, there will be changes in the prices after updates due to the random number generator
used in the app template. You may need to make adjustments to this template based on the
analyst’s guide. The analyst’s guide is also posted with this business case.
Please save your answers into the Analysis Word document. Also, post the excel worksheet as part
of the objective evidence (OE) as to how you derived your responses. Please post both documents in
the “Business Case 4 Dropbox” NLT 11:59 PM on 4 Dec.