Question

In: Finance

Using CVS Health as the company, for the remaining items in this section you will analyze...

Using CVS Health as the company, for the remaining items in this section you will analyze the Year-Over-Year returns using the last 5 years of monthly stock prices . In your spreadsheet, calculate the Year-Over-Year (YoY) return for each month. (Please submit this worksheet in Excel with your written paper.) The calculation for this is:
(month - same month prior year) / (same month prior year)

What were the highest and lowest YoY returns during this period?

Calculate the mean or average of these YoY returns from your sample.

Calculate the standard deviation of the YoY returns. (In Excel, use the STDEV function.)

Data:

Date Open High Low Close Adj Close Volume
4/1/2013 null null null null null null
5/1/2013 58.97 60.7 57.2 57.580002 52.867352 116793800
6/1/2013 57.76 60.34 55.66 57.18 52.500076 100908400
7/1/2013 57.72 62.36 57.32 61.490002 56.457325 77412600
8/1/2013 62.01 62.22 57.07 58.049999 53.496353 109197700
9/1/2013 58.44 61.33 56.67 56.75 52.298328 110503300
######## 56.76 62.91 56.32 62.259998 57.37611 143629500
######## 62.42 67.64 61.93 66.959999 61.943008 100208600
######## 67.1 71.99 65.45 71.57 66.207596 110859300
1/1/2014 71.16 71.37 66.07 67.720001 62.646069 119291500
2/1/2014 67.52 73.62 64.95 73.139999 67.934837 125294600
3/1/2014 72.59 76.36 72.05 74.860001 69.532433 117059100
4/1/2014 74.56 76 72.37 72.720001 67.544716 116832300
5/1/2014 72.44 78.49 72.43 78.32 73.01786 91921800
6/1/2014 78.36 79.43 74.64 75.370003 70.267578 79743700
7/1/2014 75.89 79.21 75.42 76.360001 71.190552 67772600
8/1/2014 76.2 79.82 74.69 79.449997 74.336761 77040300
9/1/2014 79.47 82.57 79.22 79.589996 74.467728 80179600
######## 79.45 87.16 77.4 85.809998 80.287437 123864700
######## 86.03 92 83.61 91.360001 85.76873 75256400
######## 91.42 98.62 88.95 96.309998 90.415779 100036000
1/1/2015 96.52 101.59 93.68 98.160004 92.152557 97903300
2/1/2015 98.34 104.52 97.06 103.870003 97.860008 89111000
3/1/2015 104.1 105.46 101.5 103.209999 97.238174 95022700
4/1/2015 102.66 104.45 99.01 99.290001 93.544998 100712300
5/1/2015 100.93 104.93 97.87 102.379997 96.79026 80918600
6/1/2015 102.9 106.88 99.14 104.879997 99.15377 97978200
7/1/2015 105.75 113.65 103.63 112.470001 106.329376 83710300
8/1/2015 113.17 113.58 81.37 102.400002 97.115463 117145300
9/1/2015 100.64 103.59 94.54 96.480003 91.500969 100678100
######## 96.49 106 95.03 98.779999 93.682266 129349800
######## 99.45 101.08 90.1 94.089996 89.537056 111659900
######## 94.67 99.6 92.37 97.769997 93.038971 116258600
1/1/2016 96.17 97.15 92.35 96.589996 91.916077 106620800
2/1/2016 95.89 98.79 86.5 97.169998 92.883278 117548700
3/1/2016 98.16 104.4 97.58 103.730003 99.1539 89196000
4/1/2016 103.36 105 99.87 100.5 96.066383 75248300
5/1/2016 100.5 106.67 96.2 96.449997 92.575333 113171800
6/1/2016 96.46 98.67 91.75 95.739998 91.893852 128156800
7/1/2016 95.88 98.24 92.16 92.720001 88.995186 84627700
8/1/2016 92.58 98.66 92.25 93.400002 90.042908 107299600
9/1/2016 93.56 94.2 88.1 88.989998 85.791412 116749900
######## 88.79 88.92 82.55 84.099998 81.077179 134064600
######## 84.34 84.89 69.3 76.889999 74.49276 239272800
######## 76.62 81.24 76.11 78.910004 76.449791 156408500
1/1/2017 79.58 84.72 77.03 78.809998 76.352898 139682500
2/1/2017 78.65 81.86 74.05 80.580002 78.539711 145409900
3/1/2017 81.1 81.69 77.57 78.5 76.512383 145650700
4/1/2017 78.38 82.9 76.56 82.440002 80.352623 119158300
5/1/2017 82.44 83.3 75.69 76.830002 75.366234 145781900
6/1/2017 76.84 82.15 75.46 80.459999 78.927078 152652900
7/1/2017 80.77 81.34 76.48 79.93 78.407173 91104200
8/1/2017 79.9 80.3 74.59 77.339996 76.359863 112499800
9/1/2017 77.54 84 77.3 81.32 80.289429 104104400
######## 81.35 81.61 68.01 68.529999 67.661514 209664500
######## 68.82 77.79 66.45 76.599998 76.126869 177501500
######## 75.62 76.59 69.52 72.5 72.052193 221043800
1/1/2018 73.06 83.88 72.87 78.690002 78.203964 183092200
2/1/2018 78.3 79.2 67.31 67.730003 67.730003 177533800
3/1/2018 67.7 69.56 60.14 62.209999 62.209999 177442500
4/1/2018 62.06 64.99 60.45 64.279999 64.279999 25938500

Solutions

Expert Solution

Date Open High Low Close Adj Close Volume YOY
4/1/2013 null null null null null null
5/1/2013 58.97 60.7 57.2 57.58 52.86735 116793800
6/1/2013 57.76 60.34 55.66 57.18 52.50008 100908400
7/1/2013 57.72 62.36 57.32 61.49 56.45733 77412600
8/1/2013 62.01 62.22 57.07 58.05 53.49635 109197700
9/1/2013 58.44 61.33 56.67 56.75 52.29833 110503300
######## 56.76 62.91 56.32 62.26 57.37611 143629500
######## 62.42 67.64 61.93 66.96 61.94301 100208600
######## 67.1 71.99 65.45 71.57 66.2076 110859300
1/1/2014 71.16 71.37 66.07 67.72 62.64607 119291500
2/1/2014 67.52 73.62 64.95 73.14 67.93484 125294600
3/1/2014 72.59 76.36 72.05 74.86 69.53243 117059100
4/1/2014 74.56 76 72.37 72.72 67.54472 116832300
5/1/2014 72.44 78.49 72.43 78.32 73.01786 91921800 36.02%
6/1/2014 78.36 79.43 74.64 75.37 70.26758 79743700 31.81%
7/1/2014 75.89 79.21 75.42 76.36 71.19055 67772600 24.18%
8/1/2014 76.2 79.82 74.69 79.45 74.33676 77040300 36.86%
9/1/2014 79.47 82.57 79.22 79.59 74.46773 80179600 40.25%
######## 79.45 87.16 77.4 85.81 80.28744 123864700 37.83%
######## 86.03 92 83.61 91.36 85.76873 75256400 36.44%
######## 91.42 98.62 88.95 96.31 90.41578 100036000 34.57%
1/1/2015 96.52 101.59 93.68 98.16 92.15256 97903300 44.95%
2/1/2015 98.34 104.52 97.06 103.87 97.86001 89111000 42.02%
3/1/2015 104.1 105.46 101.5 103.21 97.23817 95022700 37.87%
4/1/2015 102.66 104.45 99.01 99.29 93.545 100712300 36.54%
5/1/2015 100.93 104.93 97.87 102.38 96.79026 80918600 30.72%
6/1/2015 102.9 106.88 99.14 104.88 99.15377 97978200 39.15%
7/1/2015 105.75 113.65 103.63 112.47 106.3294 83710300 47.29%
8/1/2015 113.17 113.58 81.37 102.4 97.11546 117145300 28.89%
9/1/2015 100.64 103.59 94.54 96.48 91.50097 100678100 21.22%
######## 96.49 106 95.03 98.78 93.68227 129349800 15.11%
######## 99.45 101.08 90.1 94.09 89.53706 111659900 2.99%
######## 94.67 99.6 92.37 97.77 93.03897 116258600 1.52%
1/1/2016 96.17 97.15 92.35 96.59 91.91608 106620800 -1.60%
2/1/2016 95.89 98.79 86.5 97.17 92.88328 117548700 -6.45%
3/1/2016 98.16 104.4 97.58 103.73 99.1539 89196000 0.50%
4/1/2016 103.36 105 99.87 100.5 96.06638 75248300 1.22%
5/1/2016 100.5 106.67 96.2 96.45 92.57533 113171800 -5.79%
6/1/2016 96.46 98.67 91.75 95.74 91.89385 128156800 -8.71%
7/1/2016 95.88 98.24 92.16 92.72 88.99519 84627700 -17.56%
8/1/2016 92.58 98.66 92.25 93.4 90.04291 107299600 -8.79%
9/1/2016 93.56 94.2 88.1 88.99 85.79141 116749900 -7.76%
######## 88.79 88.92 82.55 84.1 81.07718 134064600 -14.86%
######## 84.34 84.89 69.3 76.89 74.49276 239272800 -18.28%
######## 76.62 81.24 76.11 78.91 76.44979 156408500 -19.29%
1/1/2017 79.58 84.72 77.03 78.81 76.3529 139682500 -18.41%
2/1/2017 78.65 81.86 74.05 80.58 78.53971 145409900 -17.07%
3/1/2017 81.1 81.69 77.57 78.5 76.51238 145650700 -24.32%
4/1/2017 78.38 82.9 76.56 82.44 80.35262 119158300 -17.97%
5/1/2017 82.44 83.3 75.69 76.83 75.36623 145781900 -20.34%
6/1/2017 76.84 82.15 75.46 80.46 78.92708 152652900 -15.96%
7/1/2017 80.77 81.34 76.48 79.93 78.40717 91104200 -13.79%
8/1/2017 79.9 80.3 74.59 77.34 76.35986 112499800 -17.19%
9/1/2017 77.54 84 77.3 81.32 80.28943 104104400 -8.62%
######## 81.35 81.61 68.01 68.53 67.66151 209664500 -18.51%
######## 68.82 77.79 66.45 76.6 76.12687 177501500 -0.38%
######## 75.62 76.59 69.52 72.5 72.05219 221043800 -8.12%
1/1/2018 73.06 83.88 72.87 78.69 78.20396 183092200 -0.15%
2/1/2018 78.3 79.2 67.31 67.73 67.73 177533800 -15.95%
3/1/2018 67.7 69.56 60.14 62.21 62.21 177442500 -20.75%
4/1/2018 62.06 64.99 60.45 64.28 64.28 25938500 -22.03%
Highest 47.29%
Lowest -24.32%
Average 4.60%
Std dev 23.66%

Related Solutions

The following data (in millions) were adapted from recent financial statements of CVS Health Corporation (CVS)...
The following data (in millions) were adapted from recent financial statements of CVS Health Corporation (CVS) 1. Compute the accounts receivable turnover for Years 1 and 2. Round to one decimal place. Accounts Receivable Turnover Year 2 Year 1 2. Compute the number of days' sales in receivables for Years 1 and 2. Assume there are 365 days in the year, and round to the nearest day. Number of Days' Sales in Receivables Year 2 days Year 1 days 3....
How to find CVS Health WACC, Cost of Debt, Cost of Equity using CAPM model (Capital...
How to find CVS Health WACC, Cost of Debt, Cost of Equity using CAPM model (Capital Asset Pricing Model) for year 2017?
1. How would you characterize the ethical climate of CVS Health? What evidence is there to...
1. How would you characterize the ethical climate of CVS Health? What evidence is there to support your conclusion? If there have been any problems, try to determine whether this was due to the isolated actions of rogue managers or to more systemic, organization wide problems. 2 Does CVS Health undertake investments related to corporate social responsibility or the natural environment and sustainability that go beyond its narrow economic self-interest? If so, what kind of investments has it made? Whom...
Important Note For the remaining items you are to create an APA styled results presentation. This...
Important Note For the remaining items you are to create an APA styled results presentation. This will include a table of results, and written inference and interpretation. If you are unclear about APA styled tables, review carefully the examples provided in the linked Word document on the Course Index. Each item is worth 10 points (unless specified otherwise) and will be graded according to the following rubric: Calculation Numeric Results 5 points, Written Presentation 2 points, and APA style 3...
In 3-4 paragraphs Define CVS Health and Pharmacy's performance.
In 3-4 paragraphs Define CVS Health and Pharmacy's performance.
What can you find out about the vision of the chief executive officer of CVS Health?...
What can you find out about the vision of the chief executive officer of CVS Health? How would you characterize his or her style of leading and managing? Is the leadership approach he or she has adopted more traditional or contemporary. Describe this approach in detail Would you characterize the CEO as an effective leader? How would you explain his or her success or lack thereof based on text principles?
a. Please give me a common-sized financial analysis for the CVS company CVS Balance Sheet All...
a. Please give me a common-sized financial analysis for the CVS company CVS Balance Sheet All numbers in thousands Period Ending 12/31/2017 12/31/2016 12/31/2015 Current Assets Cash And Cash Equivalents 1,696,000 3,371,000 2,459,000 Short Term Investments 111,000 87,000 88,000 Net Receivables 13,181,000 12,164,000 11,888,000 Inventory 15,296,000 14,760,000 14,001,000 Other Current Assets 945,000 660,000 722,000 Total Current Assets 31,229,000 31,042,000 29,158,000 Long Term Investments - - - Property Plant and Equipment 10,292,000 10,175,000 9,855,000 Goodwill 38,451,000 38,249,000 38,106,000 Intangible Assets 13,630,000...
Section 404 of the Sarbanes Oxley Act requires auditors of a public company to analyze and...
Section 404 of the Sarbanes Oxley Act requires auditors of a public company to analyze and report on the effectiveness of the client's internal controls over financial reporting. Describe the responsibilities that auditors of public companies have to discover and report (a) significant deficiencies in internal controls and (b) material weaknesses in internal controls. Include a definition of each item in your answer. Under what condition or conditions can auditors issue an unqualified or clean opinion on the effectiveness of...
Analyze the following items on the balance sheet for your base company that would be important...
Analyze the following items on the balance sheet for your base company that would be important to an investor, and discuss whether your company’s performance related to these items appeared to be improving, deteriorating, or remaining stable. Justify your answer. Company1 Items 31-Dec-15 12/31/2016 12/31/2017 Equity ratio 12.7 12.6 12.13 Liability ratio 0.9 0.9 0.9 Good will $1,227 $1,227 $1,227 Treasury stock -3343 -3130 -2892 Retained earnings $11,253 $12,649 $14,408 Company2 Items 9/30/2015 9/30/2016 9/30/2017 Equity ratio 4.4 4.7 4.3...
What is cvs company dividend policy? Explain with reference
What is cvs company dividend policy? Explain with reference
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT