02 Apr Task In this assignment, you will work on a project which includes the following: Running a regression of stock returns against changes in exchange rates Interpret the findings of the
Task
In this assignment, you will work on a project which includes the following:
- Running a regression of stock returns against changes in exchange rates
- Interpret the findings of the regression analysis
- Estimate the degree of economic exposure based on the results from the regression analysis
- You will do all of the above twice, first using some made-up data provided below (Economic Exposure Sheet) ; and a second time using an actual MNC you will select. (McKesson) Excel for that company is is also attached.
Instructions
Download the spreadsheet “Economic Exposure”. The file contains the following data over a 61month interval (these are data I made up for Part 1): (Sheet is attached on file)
- the stock price of a multinational company,
- the value of the European Currency Unit (ECU), quoted $/ECU.
PART 1
Step 1: Use the price series for the company to calculate monthly returns. Note that 61 months of price data will generate 60 months of returns. Similarly, use the ECU series to calculate the monthly percentage changes in the dollar value of the ECU. Show the two series of changes in two separate columns next to the original prices and exchange rates.
Step 2: Regress the return on the company against the percentage changes in the ECU. Stated differently, the return on the company is the dependent (y-axis) variable and the change in the value of the ECU is the independent (x-axis) variable. Report the slope coefficient and its p-value, and the R-squared of the regression. The regression output should be shown in the Excel file.Step 3: In one Word page, interpret the results as follows:
- Provide an interpretation for each of the i) p-value of the estimated slope coefficient, ii) slope coefficient, and iii) R-squared
- How does the value of the company change when the dollar depreciates?
- How much economic exposure does the company have? Here you reach a conclusion based on your answers to 1 and 2 above.
You will submit one Excel file with the regression data and results and one Word file with the interpretation. However, you may choose to provide your interpretation in the Excel file and submit just one Excel file and no Word file if you prefer. This is Part 1 of the assignment.
PART 2
Now select a Multinational Company that has a least 60 monthly return observations available at finance.yahoo.com and estimate the firm’s level of economic exposure using regression analysis, as in Part 1. Exchange rate information is available at, for example Historical ratesLinks to an external site. As part of your answer, provide the following information:
- Identify the Multinational Company – (Mckesson)
- Assess qualitatively the firm’s exposure to exchange rate risk after examination of the firm’s revenue information by geographical segment (for example, from SEC 10-K filings at U.S. SECURITIES AND EXCHANGE COMMISSIONLinks to an external site. and then “Filings”). Use this to briefly explain your choice of currencies to use in the regression analysis.
- Provide and interpret the results of your regression analysis as you did in Part 1. Assess quantitatively the level of economic exposure of the firm relative to movements in the currency or currencies you selected. In your interpretation, follow the structure of Part 1.
- Important: Make sure the dates for exchange rates and stock prices match when you put them both in the same Excel file as in Part 1. Use “adjusted prices” if you get your monthly stock prices from Yahoo FinanceLinks to an external site. (i.e., stock prices adjusted for stock splits)
Also, watch the assignment video at the bottom of the Module 9 page.
https://youtu.be/xN5LRjuf2wU
data
| Month | Stock | Dollar per |
| Price | ECU | |
| 1 | 35.550 | 1.1845 |
| 2 | 36.175 | 1.1920 |
| 3 | 36.050 | 1.2085 |
| 4 | 36.175 | 1.2144 |
| 5 | 36.300 | 1.2032 |
| 6 | 36.175 | 1.2107 |
| 7 | 36.800 | 1.2071 |
| 8 | 36.425 | 1.2083 |
| 9 | 36.425 | 1.2052 |
| 10 | 35.425 | 1.2220 |
| 11 | 34.050 | 1.2326 |
| 12 | 33.425 | 1.2376 |
| 13 | 37.675 | 1.2205 |
| 14 | 38.550 | 1.2240 |
| 15 | 38.050 | 1.2315 |
| 16 | 38.800 | 1.2359 |
| 17 | 39.050 | 1.2442 |
| 18 | 39.300 | 1.2407 |
| 19 | 39.800 | 1.2324 |
| 20 | 39.175 | 1.2348 |
| 21 | 39.550 | 1.2097 |
| 22 | 39.050 | 1.2094 |
| 23 | 38.800 | 1.2100 |
| 24 | 38.425 | 1.2075 |
| 25 | 38.925 | 1.2123 |
| 26 | 39.050 | 1.2265 |
| 27 | 40.800 | 1.2344 |
| 28 | 40.550 | 1.2277 |
| 29 | 40.800 | 1.2255 |
| 30 | 40.550 | 1.2381 |
| 31 | 40.550 | 1.2349 |
| 32 | 40.425 | 1.2363 |
| 33 | 38.550 | 1.2363 |
| 34 | 39.300 | 1.2321 |
| 35 | 38.550 | 1.2349 |
| 36 | 38.675 | 1.2313 |
| 37 | 37.800 | 1.2303 |
| 38 | 37.800 | 1.2332 |
| 39 | 37.925 | 1.2341 |
| 40 | 37.800 | 1.2345 |
| 41 | 38.300 | 1.2295 |
| 42 | 38.050 | 1.2382 |
| 43 | 38.675 | 1.2323 |
| 44 | 38.425 | 1.2357 |
| 45 | 38.425 | 1.2326 |
| 46 | 38.925 | 1.2313 |
| 47 | 38.925 | 1.2367 |
| 48 | 38.675 | 1.2379 |
| 49 | 38.300 | 1.2394 |
| 50 | 37.550 | 1.2994 |
| 51 | 37.800 | 1.2994 |
| 52 | 40.050 | 1.2352 |
| 53 | 39.800 | 1.2387 |
| 54 | 39.800 | 1.2447 |
| 55 | 39.425 | 1.2538 |
| 56 | 39.425 | 1.2701 |
| 57 | 39.300 | 1.2684 |
| 58 | 40.050 | 1.2689 |
| 59 | 39.550 | 1.2738 |
| 60 | 39.675 | 1.2694 |
| 61 | 39.640 | 1.2743 |
,
Data Provided
| Month | Stock | Dollar per | ||
| Price | ECU | |||
| 1 | 35.550 | Rate of Return | 1.1845 | Change in Exchange Rate |
| 2 | 36.175 | 1.8% | 1.1920 | 0.6% |
| 3 | 36.050 | -0.3% | 1.2085 | 1.4% |
| 4 | 36.175 | 0.3% | 1.2144 | 0.5% |
| 5 | 36.300 | 0.3% | 1.2032 | -0.9% |
| 6 | 36.175 | -0.3% | 1.2107 | 0.6% |
| 7 | 36.800 | 1.7% | 1.2071 | -0.3% |
| 8 | 36.425 | -1.0% | 1.2083 | 0.1% |
| 9 | 36.425 | 0.0% | 1.2052 | -0.3% |
| 10 | 35.425 | -2.7% | 1.2220 | 1.4% |
| 11 | 34.050 | -3.9% | 1.2326 | 0.9% |
| 12 | 33.425 | -1.8% | 1.2376 | 0.4% |
| 13 | 37.675 | 12.7% | 1.2205 | -1.4% |
| 14 | 38.550 | 2.3% | 1.2240 | 0.3% |
| 15 | 38.050 | -1.3% | 1.2315 | 0.6% |
| 16 | 38.800 | 2.0% | 1.2359 | 0.4% |
| 17 | 39.050 | 0.6% | 1.2442 | 0.7% |
| 18 | 39.300 | 0.6% | 1.2407 | -0.3% |
| 19 | 39.800 | 1.3% | 1.2324 | -0.7% |
| 20 | 39.175 | -1.6% | 1.2348 | 0.2% |
| 21 | 39.550 | 1.0% | 1.2097 | -2.0% |
| 22 | 39.050 | -1.3% | 1.2094 | -0.0% |
| 23 | 38.800 | -0.6% | 1.2100 | 0.0% |
| 24 | 38.425 | -1.0% | 1.2075 | -0.2% |
| 25 | 38.925 | 1.3% | 1.2123 | 0.4% |
| 26 | 39.050 | 0.3% | 1.2265 | 1.2% |
| 27 | 40.800 | 4.5% | 1.2344 | 0.6% |
| 28 | 40.550 | -0.6% | 1.2277 | -0.5% |
| 29 | 40.800 | 0.6% | 1.2255 | -0.2% |
| 30 | 40.550 | -0.6% | 1.2381 | 1.0% |
| 31 | 40.550 | 0.0% | 1.2349 | -0.3% |
| 32 | 40.425 | -0.3% | 1.2363 | 0.1% |
| 33 | 38.550 | -4.6% | 1.2363 | 0.0% |
| 34 | 39.300 | 1.9% | 1.2321 | -0.3% |
| 35 | 38.550 | -1.9% | 1.2349 | 0.2% |
| 36 | 38.675 | 0.3% | 1.2313 | -0.3% |
| 37 | 37.800 | -2.3% | 1.2303 | -0.1% |
| 38 | 37.800 | 0.0% | 1.2332 | 0.2% |
| 39 | 37.925 | 0.3% | 1.2341 | 0.1% |
| 40 | 37.800 | -0.3% | 1.2345 | 0.0% |
| 41 | 38.300 | 1.3% | 1.2295 | -0.4% |
| 42 | 38.050 | -0.7% | 1.2382 | 0.7% |
| 43 | 38.675 | 1.6% | 1.2323 | -0.5% |
| 44 | 38.425 | -0.6% | 1.2357 | 0.3% |
| 45 | 38.425 | 0.0% | 1.2326 | -0.3% |
| 46 | 38.925 | 1.3% | 1.2313 | -0.1% |
| 47 | 38.925 | 0.0% | 1.2367 | 0.4% |
| 48 | 38.675 | -0.6% | 1.2379 | 0.1% |
| 49 | 38.300 | -1.0% | 1.2394 | 0.1% |
| 50 | 37.550 | -2.0% | 1.2994 | 4.8% |
| 51 | 37.800 | 0.7% | 1.2994 | 0.0% |
| 52 | 40.050 | 6.0% | 1.2352 | -4.9% |
| 53 | 39.800 | -0.6% | 1.2387 | 0.3% |
| 54 | 39.800 | 0.0% | 1.2447 | 0.5% |
| 55 | 39.425 | -0.9% | 1.2538 | 0.7% |
| 56 | 39.425 | 0.0% | 1.2701 | 1.3% |
| 57 | 39.300 | -0.3% | 1.2684 | -0.1% |
| 58 | 40.050 | 1.9% | 1.2689 | 0.0% |
| 59 | 39.550 | -1.2% | 1.2738 | 0.4% |
| 60 | 39.675 | 0.3% | 1.2694 | -0.3% |
| 61 | 39.640 | -0.1% | 1.2743 | 0.4% |
Regression for Data Provided
| SUMMARY OUTPUT | ||||||||
| Regression Statistics | ||||||||
| Multiple R | 0.4646355891 | |||||||
| R Square | 0.2158862306 | 22% of variation or movement of value over time in company value of company is explained by changes in exchange rate between dollar and EUR | ||||||
| Adjusted R Square | 0.2023670277 | |||||||
| Standard Error | 0.0209482897 | |||||||
| Observations | 60 | |||||||
| ANOVA | ||||||||
| df | SS | MS | F | Significance F | ||||
| Regression | 1 | 0.0070076273 | 0.0070076273 | 15.9688579209 | 0.0001840734 | |||
| Residual | 58 | 0.0254521887 | 0.0004388308 | |||||
| Total | 59 | 0.0324598161 | ||||||
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
| Intercept | 0.0033596922 | 0.0027234078 | 1.2336353662 | 0.2223147329 | -0.0020918008 | 0.0088111852 | -0.0020918008 | 0.0088111852 |
| Change in Exchange Rate | -1.0050835634 | 0.2515157827 | -3.9961053441 | 0.0001840734 | -1.5085471032 | -0.5016200236 | -1.5085471032 | -0.5016200236 |
| <0.05 =Coeff on X variable significantly different from 0 | ||||||||
| $ per EUR | neg 1.005% |
McKesson
| Month | Stock | Dollar per | ||
| Price | ECU | |||
| 1 | 112.31 | Rate of Return | 1.129772 | Change in Exchange Rate |
| 2 | 114.41 | 1.9% | 1.12345 | -0.6% |
| 3 | 117.18 | 2.4% | 1.119172 | -0.4% |
| 4 | 129.34 | 10.4% | 1.130053 | 1.0% |
| 5 | 133.73 | 3.4% | 1.121734 | -0.7% |
