Module 3 Chapter-F2F

docx

School

Delaware County Community College *

*We aren’t endorsed by this school

Course

1112

Subject

Finance

Date

Feb 20, 2024

Type

docx

Pages

Uploaded by JudgeCloverHornet26

Module 3 – Stock Price Data Background In this module, you will be working with stock price data for Amazon.com. This stock is listed on the NASDAQ Stock Market (the second largest U.S. stock market, after the New York Stock Exchange). You will analyze the change in the stock prices over time as well as the value of investments over time. The Excel workbook for this module consists of 2 tabs. 1. The source tab. 2. The “Table” tab contains the full history of Amazon stock prices from its start in May 1997 until November 2017. For each trading day of the stock, a record of each of the following is posted.  Open = the price when the market opened in the morning.  Close = the price when the market closed in the afternoon.  High = the highest price during that trading day.  Low = the lowest price during that trading day.  Volume = number of shares of the stock traded that day.  Adj Close (Adjusted Close) = a price adjusted to make prices comparable over time. Note that the dates are listed from most recent to least recent. The Adjusted Close price on specific day reflects all the dividends and splits since that day. If no such dividends or splits have occurred since that day, the adjusted close equals the close on that day. The change in adjusted closing price is used to calculate the total return of an investment made on some previous date in history. Part 0: Percent Change Expressing the percent (or relative) change is often more effective when you are concerned with comparisons over time than the absolute change of the quantity. For an example, an absolute 1

change of 10 units is almost unnoticeable when the value of a quantity observed is 1000 units. However, that same 10 units more than doubles the value of a quantity of 8 units. To begin this Module, view the screencast Percent Growth Models . Part 1: Daily Changes in Stock Prices 1) Create a column in Excel that shows the difference , in dollars and cents, between the High and Low prices for each day. Call the column Daily Range . Note: you will write an Excel formula in the first row of data and drag it down the whole column. a. What was the Daily Range on 4/23/15? 2) Create a column in Excel that shows the percentage increase of the High over the Low. Call the column Daily Range % . Show your answer as a percentage with two decimal places, e.g., 10.12%. a. What was the Daily Range % on 5/27/15? 3) Create a column in Excel that shows the difference , in dollars and cents, between the Adjusted Close that day and the Adjusted Close the previous day . Call the column Daily Change . 2 Screencast

a. What was the Daily Change on 3/17/16? 4) Create a column in Excel that shows the percentage change (either increase or decrease) of the Adjusted Close that day compared to the Adjusted Close the previous day . Call the column Daily Change % . Show your answer as a percentage with two decimal places, e.g., 10.12%. a. What was the Daily Change % on 6/6/14? 5) For this problem, the Excel function XLOOKUP is useful. a. Which day in the data set had the largest Daily Range? b. Which day in the data set had the smallest Daily Range? c. Which day in the data set had the largest Daily Range %? 3

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

d. Which day in the data set had the smallest Daily Range %? e. Which day in the data set had the largest positive Daily Change (i.e., biggest increase from the previous day)? f. Which day in the data set had the most negative Daily Change (i.e., biggest drop from the previous day)? g. Which day in the data set had the largest positive Daily Change %? h. Which day in the data set had the most negative Daily Change % (i.e., biggest percentage drop)? 6) For what percentage of days in the data set was the Adjusted Close on the day lower than the Adjusted Close the previous day? Show your answer as a percentage with two decimal places, e.g., 10.12%. 4

7) For what percentage of days in the data set was the Adjusted Close on a day higher than the Adjusted Close the previous day? Show your answer as a percentage with two decimal places, e.g., 10.12%. Is the sum of the answer to this question and the previous question equal to 100%? Why or why not? 8) For what percentage of days in the data set was the Daily Change percentage a. strictly greater than 0%? b. greater than or equal to 2.00%? 9) For what percentage of days in the data set was the Daily Change percentage strictly greater than 2.00% and the volume traded was strictly less than 8,000,000? 5

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Part 2: Graphing Using all the trading days on the “Table” tab, create a graph of Adjusted Close price as a function of time. Label the horizontal axis with dates in a readable manner. Here is an example of a nicely labeled graph using Close prices for 2015. Create a graph for all the trading days in the data set. 250 300 350 400 450 500 550 600 650 700 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Price (USD) Date AMZN Close Price, 2015 Check your work by going to finance.yahoo.com and examining the historical price charts for AMZN over the whole life of the stock. 7

Part 3: Applying Adjustments The data set shows the Adjusted Close prices. Adjustments help make the prices comparable over time and are necessary when the stock splits or pays dividends. (For this class, don’t worry if you don’t understand those actions.) The data set does not show the adjustments for the other prices given (Open, High, and Low). However, you can adjust those other prices using the ratio of the Adj. Close to the Close price. 1) Find the Adjustment Ratio (=Adj. Close / Close price) for every trading day in the data set. Fill in the blank cells in the table below: Date Adj. Close/Close 6/2/97 0.083 8/23/99 1/5/01 10/25/05 1.000 3/4/16 8

2) Create a line graph for the Adjustment Ratio as a function of trading days (Hint: Use the data for all the days in the data set, not just the values from the table in Question 1.) 3) Looking at the graph, how many adjustments were made? 4) Create columns in the data for Adjusted High price (=Adjustment Ratio x High) and Adjusted Low price (=Adjustment Ratio x Low). Use those columns to fill in the blank cells in the following table. Date Adj. High Adj. Low 8/21/97 7/7/99 60.00 8/1/03 41.63 5) Create a line graph showing both the Adjusted High and Adjusted Low prices for days from the first trading day (5/15/97) until December 31, 1999. 9

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Part 4: When to Use Close vs. Adj. Close For each of the following scenarios, indicate whether this calculation would require (a) the close price, (b), the adjusted close price, or (c) both. (i) You want to know how many shares you can afford on 8/6/98 if you have $10,000. (ii) You want to know by what percentage the value of the stock has changed from 2/3/99 to 7/7/00. (iii) You want to know what it will cost to buy 30 shares on 10/29/98. 10

(iv) You bought 30 shares on 6/18/98. You want to know what your investment would be worth on 10/13/00. (v) You want to know the Adjustment Ratio on 8/6/99. 11

Part 5: Changes in Stock Prices Over Time 1) Write formulas in Excel, using cell references, to calculate the following. a. How much did the Adjusted Close price change, in dollars and cents, from the first trading day in the data set (5/15/97) until the most recent day in the data set (11/15/17)? b. How much did the Adjusted Close price change, in percentage growth, from the first trading day in the data set (5/15/97) to the most recent day in the data set (11/15/17)? Show your answer as a percentage with two decimal places, e.g., 10.12%. 2) Find how much the Adjusted Close price changed, in dollars and cents, from the first trading day (5/15/97) to each date shown in the table below. Date Adj. Close on given day Change in Adj. Close, from (5/15/97) until the given day 5/15/97 1.96 $0 1/2/98 4.96 $3.00 1/3/00 1/2/02 $9.00 1/3/05 3) Find how much the Adjusted Close price changed, as a percentage, from the first trading day (5/15/97) to each date shown in the table below. Date Adj. Close on given day Change in Adj. Close, as a percentage, from (5/15/97) until the given day 5/15/97 12

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

1/2/98 153.06% 1/3/00 1/2/02 459.18% 1/3/05 4) You have $25,000 available to invest in AMZN stock on the first day of trading in the data set (5/15/97), purchased at the Close price ( not the Adjusted Close), purchasing only whole shares. Fill in the following table: Date Close Price Amount Available to Invest Shares Purchased Amount Invested Unspent Cash 5/15/97 a. How many shares did you buy? b. How much did you spend? c. Calculate the value of your investment on the following dates. Hint: The percentage growth in investment value is the same as the percentage growth in the Adjusted Close price. Date Adj. Close Price on Change in Adj Close from (5/15/97) % Change in Adj. Close, from (5/15/97) Net Gain/Loss of Current value of investment (including initial 13

given day until the given day until the given day investment investment), on the given day Purchase Date (5/15/97) 1/2/98 $3.00 153.06% $38,235.46 $63,215.96 1/3/00 1/2/02 $9.00 459.18% $139,686.88 1/3/05 11/15/17 Part 6: Investments 1) You have $10,000 available to invest in AMZN stock on 1/3/00, purchased at the Close price, purchasing only whole shares. a. How many shares did you buy? 14

b. How much did you spend? c. Calculate the value of your investment on the following dates. Date Value of investment on the given day 1/3/00 (date of original investment) 1/2/02 1/2/04 1/2/09 $6,033.96 Most recent trading day in the data set (11/15/17) 15

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

2) You have $10,000 available to invest in AMZN stock on 9/16/98, purchasing only whole shares at the Close price, and then sold it on 5/3/99. You then used the proceeds of the sale to buy (whole) shares again on 11/14/02 at the Close price. a. How many shares did you buy on 9/16/98? Your answer should be a whole number. b. How much did you spend on 9/16/98? Show your answer in dollars and cents. c. By what percentage did the Adj. Close price grow from 9/16/98 to 5/3/99? Show your answer as a percentage with two decimal places, e.g., 10.12%. d. What was the value of your shares when you sold on 5/3/99? Show your answer in dollars and cents. e. What was the Close price on 11/14/02? Show your answer in dollars and cents. f. How many shares did you buy on 11/14/02? Your answer should be a whole number. g. How much did you spend on 11/14/02? Show your answer in dollars and cents. h. Counting the original (9/16/98) purchase and the second purchase (11/14/02), how much unspent cash did you have? Show your answer in dollars and cents. i. By what percentage did the Adj. Close price grow from 11/14/02 to the last trading day in the data se t(11/15/17)? Show your answer as a percentage with two decimal places, e.g., 10.12%. j. How much were the shares you purchased on 11/14/02 worth on the last trading day in the data set (11/15/17)? Show your answer in dollars and cents. k. Using the value of the shares on the last trading day in the data set (11/15/17) plus the unspent cash, by what percentage did your original $10,000 grow? Show your answer as a percentage with two decimal places, e.g., 10.12%. 16

Part 7: Dollar-Cost Averaging “Dollar Cost Averaging” means sticking to a regular investment pattern, such as investing $1,000 every year, without trying to anticipate whether the price of the stock (or other investment) will rise or fall. 1) You bought as many whole shares of AMZN stock as you could with $1,000 at the Close price on the first trading day of each calendar year from 2001 until 2010. Fill in the blank cells in the following table that show how many shares you bought and owned in each year. a. Table of Shares Bought in Each Year and Total Owned As of Close Price # New shares just purchased Total # of shares owned 1/2/01 72 72 1/2/02 1/2/03 51 1/2/04 1/3/05 22 1/3/06 1/3/07 1/2/08 311 1/2/09 1/4/10 b. How much were the shares owned worth on the most recent trading day in the data set (11/15/17)? Is it correct to simply multiply (the total number of shares owned as of 1/4/10) by (the Close price on the most recent trading day)? Why or why not? 17

c. How much did you spend in total on these shares? (The answer will be under $10,000: somewhat less than $1,000 per year for ten years.) 2) Fill in the table below, under the assumption that you bought as many whole shares of AMZN stock as you could with $1,000 at the Close price on the first trading day of the month of July each year. a. Fill in the blank cells in the following table: As of Close Price # New shares just purchased Total # of shares owned 7/2/01 68 68 7/1/02 7/1/03 7/1/04 19 186 7/1/05 7/3/06 241 7/2/07 7/1/08 13 7/1/09 7/1/10 b. How much were the shares owned worth on the most recent trading day in the data set (11/15/17)? 18

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

c. How much did you spend in total on these shares? (The answer will be under $10,000: somewhat less than $1,000 per year for ten years.) Part 8: Compound Annual Growth and Extrapolation 1) How much did AMZN Adjusted Close grow, as a percentage, from the last trading day of 2012 (12/31/12) to last trading day of 2013 (12/31/13)? Show your answer as a percentage with two decimal places, e.g., 10.12%. 2) The compound annual growth rate (CAGR) for a span of years is a growth rate that, when compounded annually, yields the actual percentage growth over the whole period. Watch the screencast CAGR for more information on computing the CAGR. (Use 1 year is equal to 365.25 days.) What was the compound annual growth rate (CAGR) in Adjusted Close from the last trading day of 2008 (12/31/08) to the last trading day of 2013 (12/31/13)? Show your answer as a percentage with two decimal places, e.g., 10.12%. 3) If the annual growth rate in Adjusted Close from the last day of 2013 to the last day of 2014 was the same as the CAGR in Adjusted Close from the end of 2008 through 2013 calculated above, what would the Adjusted Close of the stock be on the last trading day of 2014 (12/31/14), assuming no further adjustments and annual compounding? Show your answer in dollars and cents, e.g., 150.25. 19 Screencast

4) If the annual growth rate in Adjusted Close from the last day of 2013 through the last day of 2016 was the same as the CAGR from the end of 2008 through 2013 calculated above, what will the Adjusted Close of the stock be on the last trading day of 2016 (12/30/16), assuming no further adjustments and annual compounding? Show your answer in dollars and cents, e.g., 150.25. 5) Create a graph in Excel that shows a) the actual daily stock prices (using Adjusted Close prices) from the end of 2008 through the last trading day in the data set (11/15/17). b) the Adjusted Close over that period if it grew smoothly at the 2008-2013 CAGR. Show the smooth growth trend starting from the end of 2008 out through the end of 2018. 6) How many years are in the data set (5/15/97 to 11/15/17)? Show your answer as a number with two decimal places, e.g., 10.25. 7) What was the CAGR of the Adjusted Close from the first trading day in the data set (5/15/97) to the last trading day in the data set (11/15/17)? 20

8) Create a graph in Excel that shows a) the actual daily stock prices (using Adjusted Close prices) from the first trading day (5/15/97) through the last trading day in the data set (11/15/17), and b) the Adjusted Close if it grew smoothly at the CAGR. Use the CAGR computed over the whole data set. Show the smooth growth trend starting from the first trading day (5/15/97) out through the end of 2019. 9) Write the equation for the curve of the smooth growth of the Adjusted Close as a function of time t , where t is in days since the first trading day (5/15/97). 21

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Selected Answers Part 1 2) On 5/27/15, Daily Range % = 1.61%. 3) On 3/17/16, Daily Change = -14.83. 5) a) 6/9/17 (largest daily range, 85.99) b) 6/26/97 (smallest daily range, 0.12) c) 9/1/98 (largest daily range %, 33.33%) d) 3/17/17 (smallest daily range %, 0.38%) e) 10/27/17 (largest increase, 128.52) f) 1/29/16 (largest drop, -48.35) g) 11/26/01 (largest daily increase %, 34.47%) 6) Down 49.20% of the days (2539 out of 5161 trading days--excluding the very first day-- because there's no previous day to compare to). 7) Up 50.13% of the days (2587 out of 5161 trading days). Note that 49.20% + 50.13% does not equal 100%. There are 35 trading days where the adjusted close price was the same as the previous day. Part 3 2) Note: this graph was created using all the points in the data set, not just the 5 you found in the problem above. 22

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1 5/15/97 5/15/00 5/15/03 5/15/06 5/15/09 5/15/12 5/15/15 Adj Close/Close Ratio 3) 3 adjustments were made. 5) 0.00 20.00 40.00 60.00 80.00 100.00 120.00 5/15/97 9/ 15/97 1/ 15 /9 8 5/15/98 9/15 /98 1/1 5 /99 5 / 1 5/99 9 /15/99 AMZN Adj High and Low Prices: May 1997- Dec 1999 Part 5 1) a) $1,124.73 b) 57384.18% 4) a) 1063 Part 6 23

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

1) a) 111 c) Value on most recent day: $125,062.59 2) a) 118 b) $9,971.00 c) 436.01% e) $21.21 f) 2519 h) $46.42 i) 5212.07% k) 28281.79% Part 7 1) b) $378,567.84. Multiplying the number of shares owned as of 1/4/10 by the Close price on the most recent trading day will give the correct value IF there have been no adjustments over that time period (i.e., Adj Close = Close). c) $9,805.29 Part 8 1) 58.96% 4) 1364.36 5) 0 500 1000 1500 2000 2500 3000 3500 12/31/08 12/31/10 12/31/12 12/31/14 12/31/16 12/31/18 AMZN Adj Close Price with Extrapolation of Dec 2008-Dec 2013 Growth 8) 24

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

0 500 1000 1500 2000 2500 5/16/97 11/21/01 5/29/06 12/4/10 6/11/15 12/17/19 AMZN Adj Close Price with Extrapolation of 5/1997-11/2017 Growth 9) p(t) = 1.96 * (1+0.3633)^(t/365.25) 25

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version