The role of VaR in Enterprise Risk Management: (Note: There is an excel template that should be used for this case study in questions 2-3 and please use word for the answer to question #4.)
Mr. White is the CFO of a $46 million revenue service company and has excess cash funds to invest into a specific stock. Based upon his research, he has found that Starbucks is an excellent choice for several reasons, including a current stock beta of .64 (which means that the risk is low as it is below a threshold of 1.00). However, he wishes to use the VaR calculation to quantify the upside and downside risk to the company to help him recognize if Starbucks stock is a good choice. Please see below for the assignment parameters.
- Please visit the link below to gain a greater grasp of VaR and its application to risk.
a. Also, see: http://investexcel.net/value-at-risk-methods-spreadsheets/
b. And, https://www.investopedia.com/ask/answers/033115/how-can-you-calculate-value-risk-var-excel.asp
- Next, use Yahoo Finance to gain Starbucks (SBUX ticker symbol) monthly closing stock price from 6-1-2012 to 6-1-2017. Download the information into excel. (There should be 60 data rows) Then cut and paste the date and stock close columns of data into the attached excel template. (VAR Calculation Template) Once this is completed, the template will automatically calculate the dollar and percentage changes in monthly stock prices and graph the data accordingly.
- Using a VaR variance/covariance method tab in the excel template worksheet, calculate the value at risk with the following parameters for Mr. White inclusive of the following assumptions:
Portfolio Value: $188,500
Average return – 13.4%
Standard deviation – 10%
Confidence level – 95%
VAR Calculation Template
- Based upon the findings of #2 and #3, please use Word to answer:
a. What were your findings of Starbucks VaR benchmarks and what do they mean?
b. Further, please explain what you would advise Mr. White to do in terms of his choice of Starbucks investment for the company and what he should explain to the CEO as to possible risk impact on the company.
Sample Solution
