as attached

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

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

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

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

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

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.

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