IE 305 Iowa State University Recommendations for The Best Stock to Invest Project Please read the following documents and look over the project for my engi

IE 305 Iowa State University Recommendations for The Best Stock to Invest Project Please read the following documents and look over the project for my engineering economics course. Project 2: Stock Project
IE 305 XW Summer 2020
Due Wednesday, July 8 at 11:59 pm
One of Engineering Economists Inc.’s (EEI) personal clients is Rachel Schuter. Rachel intends to
invest $10,000 on August 1, 2020, and she intends to end her investment on July 31, 2023. Rachel
is deciding whether to buy stock in Facebook or Walmart. She has hired EEI for advice which
stock to purchase.
For the purpose of this project, you must recommend one or the other (or neither)—you cannot
diversify the investment of this $10,000. Because Rachel is adopting a value-based buy-and-hold
strategy, if she buys stock in one of these companies on August 1, 2020, she will hold it for three
years before selling all of the stock.
Historical Stock Prices
You can use the following links to access Facebook and Walmart. You can select “Historical
Data” to see how closing stock prices were estimated, but do not attempt to determine them
yourself.
•
•
https://beta.finance.yahoo.com/quote/FB
https://beta.finance.yahoo.com/quote/WMT/
Figure 1 Walmart dividends
1
You will want to use the “Historical Data” feature to determine historical dividends for Walmart.
Click on “Show: Dividends Only” to view the dividends and the dates. Also click Apply (see
Figure 1). The number represents how much dividends were paid to shareholders per share.
Table 1 depicts the historical stock closing prices for this project. You can see there are a total of
20 prices over 20 quarters. The estimated closing price on July 31, 2020 will be the purchase
price of the stock on August 1 (which is quarter 0 in the Excel sheet template “Project 2
solutions.xlsx”).
Table 1 Historical closing stock prices
Date
Facebook
Walmart
7/31/2015
89.73
64.73
10/31/2015
104.24
58.84
1/31/2016
106.92
66.34
4/30/2016
118.81
70.78
7/31/2016
126.12
71.44
10/31/2016
118.42
70.43
1/31/2017
135.54
70.93
4/30/2017
151.46
78.60
7/31/2017
171.97
78.07
10/31/2017
177.18
97.23
1/31/2018
178.32
90.01
4/30/2018
191.78
82.54
7/31/2018
175.73
95.86
10/31/2018
140.61
97.65
1/31/2019
161.45
98.99
4/30/2019
177.47
101.44
7/31/2019
185.67
114.26
10/31/2019
201.64
119.09
1/31/2020
192.47
107.68
4/30/2020
225.09
124.06
7/31/2020
??
??
Estimating stock prices for Facebook
The stock price for Facebook has uncertainty. We are going to assume that Facebook’s stock
price in one quarter obeys the following equation
= −1 ∗
where is the price of the stock at quarter and is a random variable that follows a
lognormal distribution.
For each quarter 0 through 12 in spreadsheet you should simulate a lognormal random variable
in row 12. In the “lognormal random variable” dialog box, set the “50th %ile” equal to 1.03
2
and “0.9th %ile” equal to 1.16. This means there is a 50% probability the price of the stock will
increase in the next quarter by 3% or less and there is a 90% probability the price of the stock
will increase by 16% or less. Repeat this process for all 13 quarters (quarter 0 to 12). Each
quarter should have a different lognormal random variable. There should NOT be the same
values in each quarter!
The stock price in a quarter is the stock price in the previous quarter multiplied by the lognormal
random variable. To calculate the stock price in quarter 0, multiply the stock price on April 30,
2020 (which is $225.09 from Table 1) by the lognormal random variable. For example, if the
lognormal random variable for quarter 0 equals 1.10, then the price in quarter 0 equals
$225.09*1.10 = $247.60.
The stock price in quarter 0 equals the buying price. It should automatically change in Excel cell
B7 based on the calculation in cell D13. The number of shares that Rachel will purchase will be
based on the buying price assuming the entire amount of $10,000 is used to purchase Facebook
stock. It is acceptable to purchase fractions of shares of stock.
Repeat the process of calculating the stock price in each quarter for the next 12 quarters by
multiplying the price in the previous quarter by the lognormal random variable. At the end of
quarter 12, Rachel will sell this stock at the price in quarter 12.
Although you are calculating the stock price for each quarter, there is 0 cash flow in quarters 1
through 11 because Rachel is not selling or buying additional stocks. There are no dividends for
Facebook.
Estimating stock prices for Walmart
The stock price for Walmart has uncertainty, but the randomness follows a different formula than
for Facebook’s stock. We are going to assume that Walmart’s stock price in one quarter obeys
the following equation
= −1 +
where is the price of the stock at quarter and is a random variable that follows a normal
distribution.
For each quarter 0 through 12 in spreadsheet you should simulate a normal random variable in
row 24. To calculate the mean and standard deviation of the random variable , calculate the
differences in Walmart’s opening prices between adjacent quarters. For example, the difference
between 10/31/2015 and 7/31/2015 is 58.84-64.73 = -5.89, and the difference between 1/31/2016
and 10/31/2015 is 66.34-58.84 = 7.50. There should be a total of 19 differences. Use the average
of those differences as the mean of the random variable . Use the standard deviation (STDEV.S
in Excel) of those differences as the standard deviation of the random variable .
Each of the 13 quarters should have a different normal random variable. There should NOT be
the same values in each quarter!
Walmart’s stock price in a quarter is the stock price in the previous quarter added to the normal
3
random variable. To calculate the stock price in quarter 0, add the stock price on April 30, 2020
(which is $124.06 from Table 1) to the normal random variable. For example, if the normal
random variable for quarter 0 equals 1.32, then the price in quarter 0 equals $124.06+1.32 =
$125.38.
The stock price in quarter 0 equals the buying price. It should automatically change in Excel cell
B19 based on the calculation in cell D25. The number of shares bought is based on the buying
price assuming the entire amount of $10,000 is used to purchase Walmart stock. It is acceptable
to purchase fractions of shares of stock.
Repeat the process of calculating the stock price in each quarter for the next 12 quarters by
adding the price in the previous quarter by the normal random variable. At the end of quarter 12,
Rachel will sell this stock at the price in quarter 12.
Dividend information
Dividends will come into play for Walmart stock. You will see from historical data that
dividends often occur quarterly, and that their value often remains the same for 4 quarters before
going up. For evaluating the future cash flows, always assume that dividends remain constant for
4 consecutive quarters. Assume the dividends will rise by $0.01 after remaining constant for 4
consecutive quarters. This is close to the historical change.
Assume all future dividends will be paid at the end of the quarter. For example, if a dividend is
actually paid on August 9, 2020, you can assume that dividend occurs on October 31, 2020.
Dividends are paid on a per share basis. For example, if dividends for a quarter are $0.50 and
Rachel has 100 shares of stock, total dividends for that quarter would be $50. Treat dividends as
income at the end of each quarter, and calculate taxes on those dividends for each quarter at the
applicable tax rate.
Rachel will not receive a dividend in quarter 0 (which corresponds to the dividend paid on May
7, 2020) although you should consider the dividend in quarter 0 as 1 of the 4 consecutive
quarters. Rachel will receive a dividend in quarter 12 if she purchases Walmart stock.
Tax information
When an individual sells a stock, the individual pays taxes on gains only. For example, if Rachel
buys stocks for $10,000 and sells them for $10,000, there is no tax. If she sells the stocks for less
than $10,000, there will be a tax refund.
For individuals, long-term capital gains and qualified dividends (those received after one year)
are taxed at 15% at the federal level. For the first year though (i.e., August 1, 2020 through July
31, 2020), they are taxed as ordinary income at 24%. For the second and third years, they are
taxed at 15%. When Rachel sells the stock at the end of quarter 12, the gains will be taxed at a
rate of 15%.
4
The state of Iowa income tax rate is 8.53%. The state of Iowa does allow an individual to deduct
what he or she pays in federal income taxes. Thus, you should use the combined tax rate formula
covered in class (see chapter 9 in the textbook).
Compute taxes and dividends every quarter. It makes things much easier to do so. If you have a
stock that is projected to receive dividends in all 12 quarters, then all 12 columns will have
values contained in the “NET” row.
Present worth analysis
All dollar amounts are given in actual dollars here. The investment, dividends, taxes, and
projected prices are based on actual dollars.
You should calculate the average annual (general) inflation rate based on the Consumer Price
Index (CPI) over the past four years. The CPI in 2019 was 255.657, and the CPI in 2015 was
237.017. You should assume the inflation rate will remain the same for the next 3 years.
Rachel Schuter’s inflation-free annual interest rate i’ is 1.5%, which is approximately the return
she would receive from purchasing a certificate of deposit.
Use the annual market interest rate based on the inflation-free interest rate and the inflation rate
to compute the net present worth of Facebook and Walmart. At least a few different valid
methods exist to compute the present worth of Walmart’s cash flow, depending on the
assumptions you make about the compounding effects. You only need to use one method, and
they all should return similar results. Hint: you do not need to make this present worth
calculation overly complicated.
The net present worth in cells S15 and S16 are random and should be the output cells for
SIPMath.
Deliverable
You should calculate the net present worth for the Facebook stock and the Walmart stock.
Conduct 100,000 trials in SIPMath. Use the Excel sheet “Project 2.xlsx” for help with the
calculation. You should concentrate of filling in the yellow-shaded cells.
You should write a professional memorandum (memo) providing Rachel Schuter with your
financial analysis for each stock. The Excel sheet contains suggested metrics for the results of the
financial analysis. See the attached Word document “Writing a professional memo.docx” for
expectations and guidelines on writing a professional memo.
You should upload your memo and your Excel sheet via Canvas. You can work on this project
with one other individual or you can work on it individually. You can but are not required to
work with the same individual you worked with for Project 1. Only one person per group needs
to upload the memo and Excel sheet, but make sure that each person’s name is on the memo.
The due date for this project is Wednesday, July 8. Your grade will be docked by 10 points for
each day that you submit the memo after Wednesday, July 8.
5
The memo should contain 4-5 paragraphs. The first paragraph should be an executive summary
that summarizes your analysis. One paragraph should discuss the financial analysis of purchasing
the Facebook stock, and the other paragraph should discuss the financial analysis of purchasing
the Walmart stock. Each paragraph should provide the advantages and disadvantages of
purchasing that particular stock.
You may also want to include graphs (e.g., histograms, cumulative density plots) in your memo
to illustrate the uncertainty in the net present worth of each stock. You can generate these plots
by clicking on “Graphs” in the SIPmath Modeler Tools toolbar. In order to generate these plots,
the cell must be defined as an Output cell. The number of bins for the histogram should be set to
100. You should also spend some time formatting the plot (e.g., adding axis titles, changing the
chart title, making sure the font on the graph is large enough) so that it looks professional and
readable in the memo. A video will be posted by Thursday giving some instruction on how to
create professional-looking plots from the SIPMath graphs.
The final one or two paragraphs should briefly compare between Facebook and Walmart stock.
You should explain which stock Rachel should purchase if she does not want to take on a lot of
risk (e.g., risk averse) and which stock Rachel should purchase if she is willing to take more risk.
Finally, you should make a recommendation of which stock is better and on what basis you are
making your recommendation. There is not a single correct answer, and either stock could be
recommended.
6
Project 2: Stock Project
IE 305 XW Summer 2020
Due Wednesday, July 8 at 11:59 pm
One of Engineering Economists Inc.’s (EEI) personal clients is Rachel Schuter. Rachel intends to
invest $10,000 on August 1, 2020, and she intends to end her investment on July 31, 2023. Rachel
is deciding whether to buy stock in Facebook or Walmart. She has hired EEI for advice which
stock to purchase.
For the purpose of this project, you must recommend one or the other (or neither)—you cannot
diversify the investment of this $10,000. Because Rachel is adopting a value-based buy-and-hold
strategy, if she buys stock in one of these companies on August 1, 2020, she will hold it for three
years before selling all of the stock.
Historical Stock Prices
You can use the following links to access Facebook and Walmart. You can select “Historical
Data” to see how closing stock prices were estimated, but do not attempt to determine them
yourself.
•
•
https://beta.finance.yahoo.com/quote/FB
https://beta.finance.yahoo.com/quote/WMT/
Figure 1 Walmart dividends
1
You will want to use the “Historical Data” feature to determine historical dividends for Walmart.
Click on “Show: Dividends Only” to view the dividends and the dates. Also click Apply (see
Figure 1). The number represents how much dividends were paid to shareholders per share.
Table 1 depicts the historical stock closing prices for this project. You can see there are a total of
20 prices over 20 quarters. The estimated closing price on July 31, 2020 will be the purchase
price of the stock on August 1 (which is quarter 0 in the Excel sheet template “Project 2
solutions.xlsx”).
Table 1 Historical closing stock prices
Date
Facebook
Walmart
7/31/2015
89.73
64.73
10/31/2015
104.24
58.84
1/31/2016
106.92
66.34
4/30/2016
118.81
70.78
7/31/2016
126.12
71.44
10/31/2016
118.42
70.43
1/31/2017
135.54
70.93
4/30/2017
151.46
78.60
7/31/2017
171.97
78.07
10/31/2017
177.18
97.23
1/31/2018
178.32
90.01
4/30/2018
191.78
82.54
7/31/2018
175.73
95.86
10/31/2018
140.61
97.65
1/31/2019
161.45
98.99
4/30/2019
177.47
101.44
7/31/2019
185.67
114.26
10/31/2019
201.64
119.09
1/31/2020
192.47
107.68
4/30/2020
225.09
124.06
7/31/2020
??
??
Estimating stock prices for Facebook
The stock price for Facebook has uncertainty. We are going to assume that Facebook’s stock
price in one quarter obeys the following equation
= −1 ∗
where is the price of the stock at quarter and is a random variable that follows a
lognormal distribution.
For each quarter 0 through 12 in spreadsheet you should simulate a lognormal random variable
in row 12. In the “lognormal random variable” dialog box, set the “50th %ile” equal to 1.03
2
and “0.9th %ile” equal to 1.16. This means there is a 50% probability the price of the stock will
increase in the next quarter by 3% or less and there is a 90% probability the price of the stock
will increase by 16% or less. Repeat this process for all 13 quarters (quarter 0 to 12). Each
quarter should have a different lognormal random variable. There should NOT be the same
values in each quarter!
The stock price in a quarter is the stock price in the previous quarter multiplied by the lognormal
random variable. To calculate the stock price in quarter 0, multiply the stock price on April 30,
2020 (which is $225.09 from Table 1) by the lognormal random variable. For example, if the
lognormal random variable for quarter 0 equals 1.10, then the price in quarter 0 equals
$225.09*1.10 = $247.60.
The stock price in quarter 0 equals the buying price. It should automatically change in Excel cell
B7 based on the calculation in cell D13. The number of shares that Rachel will purchase will be
based on the buying price assuming the entire amount of $10,000 is used to purchase Facebook
stock. It is acceptable to purchase fractions of shares of stock.
Repeat the process of calculating the stock price in each quarter for the next 12 quarters by
multiplying the price in the previous quarter by the lognormal random variable. At the end of
quarter 12, Rachel will sell this stock at the price in quarter 12.
Although you are calculating the stock price for each quarter, there is 0 cash flow in quarters 1
through 11 because Rachel is not selling or buying additional stocks. There are no dividends for
Facebook.
Estimating stock prices for Walmart
The stock price for Walmart has uncertainty, but the randomness follows a different formula than
for Facebook’s stock. We are going to assume that Walmart’s stock price in one quarter obeys
the following equation
= −1 +
where is the price of the stock at quarter and is a random variable that follows a normal
distribution.
For each quarter 0 through 12 in spreadsheet you should simulate a normal random variable in
row 24. To calculate the mean and standard deviation of the random variable , calculate the
differences in Walmart’s opening prices between adjacent quarters. For example, the difference
between 10/31/2015 and 7/31/2015 is 58.84-64.73 = -5.89, and the difference between 1/31/2016
and 10/31/2015 is 66.34-58.84 = 7.50. There should be a total of 19 differences. Use the average
of those differences as the mean of the random variable . Use the standard deviation (STDEV.S
in Excel) of those differences as the standard deviation of the random variable .
Each of the 13 quarters should have a different normal random variable. There should NOT be
the same values in each quarter!
Walmart’s stock price in a quarter is the stock price in the previous quarter added to the normal
3
random variable. To calculate the stock price in quarter 0, add the stock price on April 30, 2020
(which is $124.06 from Table 1) to the normal random variable. For example, if the normal
random variable for quarter 0 equals 1.32, then the price in quarter 0 equals $124.06+1.32 =
$125.38.
The stock price in quarter 0 equals the buying price. It should automatically change in Excel cell
B19 based on the calculation in cell D25. The number of shares bought is based on the buying
price assuming the entire amount of $10,000 is used to purchase Walmart stock. It is acceptable
to purchase fractions of shares of stock.
Repeat the process of calculating the stock price in each quarter for the next 12 quarters by
adding the price in the previous quarter by the normal random variable. At the end of quarter 12,
Rachel will sell this stock at the price in quarter 12.
Dividend information
Dividends will come into play for Walmart stock. You will see from historical data that
dividends often occur quarterly, and that their value often remains the same for 4 quarters before
going up. For evaluating the future cash flows, always assume that dividends remain constant for
4 consecutive quarters. Assume the dividends will rise by $0.01 after remaining constant for 4
consecutive quarters. This is close to the historical change.
Assume all future dividends will be paid at the end of the quarter. For ex…
Purchase answer to see full
attachment