bus 3

INstructions attached below

2

>Sheet

1

Prediction App

Months

Months

0

Months

1

Months

1

s

s

33

s

Probability

0.05

0.05

5

0.1

0

5

0.40

1 0.2

0.60

0

2 0.2

0.

3 0.1

0.90

4 0.1

Price in 3 Month Price in 6 Month Price in 9 Month Price in 12 Months

1 Price

2

.00

3

.00

4

5

6

.00

.00

8

.00

9
12
15
31
32

33
34

35
36
37
40
60
80
90

																																																								Stock	Price
Predicted Change in		3			0
Predicted Change in Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper	6
Predicted Change in	9
Predicted Change in	12
Stock	Price in 3 Month
		33
Stock	Price in 6 Month
Stock	Price in 9 Month
	3		4
Stock	Price in 12 Months	34
Discrete	Probability
Lower Bound	Upper Bound	Price Change ($)
0.00			0.0	5	-2
			0.1	-1
0.	15		0.	40			0.2
	0.	60
	0.	8
80		0.	90
1.00
Scenario	12 Month Insights
Frequency
$	31
$	32
$33.00
$34.00
$	35
7	$	36
$	37
10	P (Price < 37)
11
13
14
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
38
39
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
81
82
83
84
85
86
87
88
89
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200

Sheet2

Sheet3

Page1 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.

Business Case 4

BUSA 421 Data Mining

Fall 2020

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-

month period.

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.