Please answer the following questions using the data in the attached Excel file. You are thinking...

Please answer the following questions using the data in the attached Excel file.

You are thinking of investing in Abercrombie and Fitch Co. (ANF). The returns for ANF are embedded in an Excel document below (Source: yahoo.com).

1. For the investment in ANF that you are considering, for all of 2012 determine the following items:

a) the mean return

b) the median return

c) the standard deviation

d) the variance

e) the coefficient of variation

The weekly rates of return for ANF stock and the overall market are embedded. Weekly returns of the markets are based on the S&P Index. Using linear regression analysis, please answer the following questions.

a) Write the equation for the relationship between ANF’s stock price and the overall market. In other words, what is the estimated relationship between ANF’s stock price and the overall market?

b) What percentage of the variability in ANF’s stock price can be explained by the relationship to the returns of the overall market? (Note: this is a measure of the stock’s market-related risk.)

c) Would you recommend using the estimated linear relationship as an estimate of the likely change in ANF’s stock price given an estimate of the likely change in the S&P index? Why or why not? Be specific in your answer.

Weekly Investment Data

	S&P Index							ANF - Abercrombie & Fitch Co.
Date	Open	High	Low	Close	Volume	Adj Close	Return	Open	High	Low	Close	Volume	Adj Close	Return
12/24/2012	1430.15	1430.15	1401.8	1418.1	2121390000	1418.1	-0.84%	47.33	48.04	44.87	46.16	1608500	46.16	-2.55%
12/17/2012	1413.54	1448	1413.54	1430.15	4108678000	1430.15	1.17%	47	49.1	46.2	47.37	2724100	47.37	1.26%
12/10/2012	1418.07	1438.59	1411.88	1413.58	3383768000	1413.58	-0.32%	47.48	47.97	46.17	46.78	2844000	46.78	0.84%
12/3/2012	1416.34	1423.73	1398.23	1418.07	3386154000	1418.07	0.13%	46.03	46.63	44.79	46.39	2408200	46.39	1.09%
11/26/2012	1409.15	1419.7	1385.43	1416.18	3390836000	1416.18	0.50%	45.1	46.33	44.02	45.89	3931800	45.89	3.36%
11/19/2012	1359.88	1409.16	1359.88	1409.15	2688502500	1409.15	3.62%	41.26	44.52	41.09	44.4	3451900	44.4	9.06%
11/12/2012	1379.86	1388.81	1343.35	1359.88	3621476000	1359.88	-1.45%	31.41	42.9	30.74	40.87	11496600	40.71	29.69%
11/5/2012	1414.02	1433.38	1373.03	1379.85	3602274000	1379.85	-2.43%	32.69	34.43	31.16	31.52	2099400	31.39	-3.86%
10/31/2012	1410.99	1434.27	1405.95	1414.2	3746493300	1414.2	0.16%	30.79	33.42	30.18	32.78	2638400	32.65	7.19%
10/22/2012	1433.21	1435.46	1403.28	1411.94	3397482000	1411.94	-1.48%	31.92	32.18	30.06	30.58	1904400	30.46	-4.45%
10/15/2012	1428.75	1464.02	1427.24	1433.19	3692620000	1433.19	0.32%	33.02	33.56	31.75	32.01	2308400	31.88	-2.89%
10/8/2012	1460.93	1460.93	1425.53	1428.59	3115478000	1428.59	-2.21%	32.16	34.4	32.07	32.96	2085600	32.83	0.92%
10/1/2012	1440.9	1470.96	1439.01	1460.93	3429462000	1460.93	1.41%	33.28	34.05	32.14	32.66	1857200	32.53	-3.70%
9/24/2012	1459.76	1463.24	1430.53	1440.67	3394752000	1440.67	-1.33%	36.65	36.65	33.84	33.92	2321400	33.78	-8.03%
9/17/2012	1465.42	1467.07	1449.98	1460.15	3705514000	1460.15	-0.38%	39.09	39.25	36.65	36.88	2212700	36.73	-6.30%
9/10/2012	1437.92	1474.51	1428.98	1465.77	4004608000	1465.77	1.94%	36.4	39.65	35.1	39.36	4790200	39.2	7.90%
9/4/2012	1406.54	1437.92	1396.56	1437.92	3564977500	1437.92	2.23%	35.86	36.77	34.43	36.48	1656300	36.33	1.34%
8/27/2012	1411.13	1416.17	1397.01	1406.58	2628268000	1406.58	-0.32%	36.29	37	35.37	35.99	1585600	35.85	-0.55%
8/20/2012	1417.85	1426.68	1398.04	1411.13	2943798000	1411.13	-0.50%	35.71	37.44	34.95	36.19	2348100	36.05	1.21%
8/13/2012	1405.87	1418.71	1397.32	1418.16	2824746000	1418.16	0.87%	32.11	36.25	31.69	35.93	4546500	35.62	12.26%
8/6/2012	1391.04	1407.14	1391.04	1405.87	3182784000	1405.87	1.07%	29.38	32.29	28.91	32.01	3519500	31.73	9.00%
7/30/2012	1385.94	1394.16	1354.65	1390.99	3883892000	1390.99	0.36%	36.39	36.75	28.64	29.37	7705300	29.11	-20.09%
7/23/2012	1362.34	1389.19	1329.24	1385.97	4031190000	1385.97	1.71%	35.85	37	34.66	36.75	2309600	36.43	0.11%
7/16/2012	1356.5	1380.39	1345.07	1362.66	3608082000	1362.66	0.43%	35.42	36.97	33.77	36.71	2861100	36.39	3.47%
7/9/2012	1354.66	1361.54	1325.41	1356.78	3333824000	1356.78	0.16%	33.38	35.71	32.45	35.48	3820600	35.17	5.87%
7/2/2012	1362.33	1374.81	1348.03	1354.68	2801175000	1354.68	-0.55%	34.81	35.14	33.1	33.51	2195800	33.22	-1.83%
6/25/2012	1334.9	1362.17	1309.27	1362.16	3752304000	1362.16	2.03%	30.25	34.7	29.51	34.14	3268800	33.84	11.17%
6/18/2012	1342.42	1363.46	1324.41	1335.02	4027288000	1335.02	-0.58%	31.41	32.46	30.29	30.71	2327600	30.44	-2.62%
6/11/2012	1325.72	1343.32	1306.62	1342.84	3715250000	1342.84	1.30%	32.45	32.74	30.26	31.54	3239800	31.26	-1.94%
6/4/2012	1278.29	1329.05	1266.74	1325.66	3887776000	1325.66	3.73%	32.17	33.41	31.27	32.16	2580800	31.88	0.06%
5/29/2012	1318.9	1334.93	1277.25	1278.04	4050725000	1278.04	-3.02%	36.49	36.6	31.82	32.14	3554400	31.86	-11.28%
5/21/2012	1295.73	1328.49	1295.73	1317.82	3765818000	1317.82	1.74%	36.37	37.01	34.45	36.23	3505500	35.91	1.44%
5/14/2012	1351.93	1351.93	1291.98	1295.22	4251866000	1295.22	-4.30%	46.22	46.77	35.67	35.89	6231600	35.4	-23.01%
5/7/2012	1368.79	1373.91	1343.13	1353.39	3941332000	1353.39	-1.15%	50.41	50.41	45.19	46.62	2962000	45.98	-7.73%
4/30/2012	1403.26	1415.32	1367.96	1369.1	3833174000	1369.1	-2.44%	50.84	54.05	49.78	50.52	3129300	49.83	-0.70%
4/23/2012	1378.53	1406.64	1358.79	1403.36	3790184000	1403.36	1.80%	48.36	51.3	46.98	50.88	2106000	50.18	3.72%
4/16/2012	1370.27	1392.76	1365.38	1378.53	3701492000	1378.53	0.60%	47.59	49.68	46.01	49.05	2171500	48.38	4.00%
4/9/2012	1397.45	1397.45	1357.38	1370.26	3818638000	1370.26	-1.99%	49.1	49.11	46.75	47.16	1787300	46.52	-5.52%
4/2/2012	1408.47	1422.38	1392.92	1398.08	3659032500	1398.08	-0.74%	50.84	52.87	49.74	49.92	1855800	49.24	0.63%
3/26/2012	1397.11	1419.15	1391.56	1408.47	3698456000	1408.47	0.81%	51.72	52.03	48.92	49.61	2095900	48.93	-3.17%
3/19/2012	1404.17	1414	1386.87	1397.11	3682996000	1397.11	-0.50%	52.39	53.25	50.46	51.23	2056600	50.53	-2.36%
3/12/2012	1370.78	1405.88	1366.69	1404.17	4281244000	1404.17	2.43%	50.67	54.1	50.35	52.47	3129300	51.75	3.29%
3/5/2012	1369.59	1374.76	1340.03	1370.87	3676690000	1370.87	0.09%	48.18	51.14	47.45	50.79	2656800	50.1	5.21%
2/27/2012	1365.2	1378.04	1354.92	1369.63	3782622000	1369.63	0.28%	47.82	48.69	45.67	48.28	3208200	47.62	0.29%
2/21/2012	1361.22	1368.92	1352.28	1365.74	3680180000	1365.74	0.33%	48.8	48.92	47.03	48.14	2276800	47.48	-0.34%
2/13/2012	1343.06	1363.4	1340.8	1361.23	3882884000	1361.23	1.38%	45.35	50.44	43.41	48.48	4778300	47.64	7.56%
2/6/2012	1344.32	1354.32	1335.92	1342.64	3861272000	1342.64	-0.17%	40.38	45.79	40.26	45.07	4189500	44.29	11.25%
1/30/2012	1316.16	1345.34	1300.49	1344.9	4225678000	1344.9	2.17%	46.68	47.45	40.25	40.51	6803000	39.81	-14.22%
1/23/2012	1315.29	1333.47	1306.06	1316.33	4080966000	1316.33	0.07%	44.89	47.56	43.77	47.23	3111900	46.41	6.10%
1/17/2012	1290.22	1315.49	1290.22	1315.38	4121290000	1315.38	2.04%	45.46	48.02	44.4	44.51	3630100	43.74	-1.06%
1/9/2012	1277.83	1296.82	1274.55	1289.09	3854788000	1289.09	0.88%	44.3	45.82	43.56	44.99	4016700	44.21	0.18%
1/3/2012	1258.86	1284.62	1258.86	1277.81	3877267500	1277.81	1.61%	49.58	50.26	44.52	44.91	2866900	44.13	-8.06%
12/28/2011	1265.38	1265.85	1248.64	1257.6	2299986600	1257.6		49.02	49.19	47.94	48.84	922000	48

Expert Solution

Please refer to the table while checking the answers				SUMMARY OUTPUT

		Market	ANF Stock Price	Regression Statistics
		1418.1	46.16	Multiple R	0.070500058
Mean Return	0.25%	1430.15	47.37	R Square	0.004970258
Median Return	0.24%	1413.58	46.78	Adjusted R Square	-0.014540129
Standard Dev	8.09%	1418.07	46.39	Standard Error	49.58342729
Variance	0.65%	1416.18	45.89	Observations	53
Coeff Of variation	31.8	1409.15	44.4
		1359.88	40.71	ANOVA
		1379.85	31.39		df	SS	MS	F	Significance F
		1414.2	32.65	Regression	1	626.3054	626.3054	0.254749	0.615925
		1411.94	30.46	Residual	51	125384.3	2458.516
		1433.19	31.88	Total	52	126010.6
		1428.59	32.83
		1460.93	32.53		Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
		1440.67	33.78	Intercept	1397.960671	39.85006	35.08051	2.24E-37	1317.958	1477.963	1317.958	1477.963
		1460.15	36.73	X Variable 1	-0.490712323	0.972233	-0.50473	0.615925	-2.44255	1.461127	-2.44255	1.461127
		1465.77	39.2
		1437.92	36.33
		1406.58	35.85
		1411.13	36.05
		1418.16	35.62
		1405.87	31.73
		1390.99	29.11
		1385.97	36.43
		1362.66	36.39
		1356.78	35.17
		1354.68	33.22
		1362.16	33.84
		1335.02	30.44
		1342.84	31.26
		1325.66	31.88
		1278.04	31.86
		1317.82	35.91
		1295.22	35.4
		1353.39	45.98
		1369.1	49.83
		1403.36	50.18
		1378.53	48.38
		1370.26	46.52
		1398.08	49.24
		1408.47	48.93
		1397.11	50.53
		1404.17	51.75
		1370.87	50.1
		1369.63	47.62
		1365.74	47.48
		1361.23	47.64
		1342.64	44.29
		1344.9	39.81
		1316.33	46.41
		1315.38	43.74
		1289.09	44.21
		1277.81	44.13
		1257.6	48
Liner regression output using solver is only giving output with R^2 = 0.005 which is less and hence cannot be used , we can use multiple linear regresssion of order 3 or higher.

jeff jeffy answered 2 years ago

You have an Excel file attached to this link. You are to first transpose the data...

You have an Excel file attached to this link. You are to first transpose the data so that, instead of being in the horizontal format, it will be converted to the vertical form. Then to run a Multiple Regression and see which of the independent variables are significant and whether the overall model is significant. You also should be able to comment on the goodness of the fit. All of these require that you have thoroughly watched the video lectures...

Please use ONLY one Excel file to answer the below questions. ( including the formula using...

Please use ONLY one Excel file to answer the below questions. ( including the formula using for Excel) In 2011, when the Gallup organization polled investors, 34% rated gold the best long-term investment. In April of 2013 Gallup surveyed a random sample of U.S. adults. Respondents were asked to select the best long-term investment from a list of possibilities. Only 241 of the 1005 respondents chose gold as the best long-term investment. By contrast, only 91 chose bonds. Compute the...

Answer the following problems in an Excel file. Please upload only one Excel file with all...

Answer the following problems in an Excel file. Please upload only one Excel file with all of your answers, including #3 (which requires an explanation rather than a calculation). All problems must be solved using the PV and FV functions in Excel. If I deposit $8,000 in a bank account that pays interest of 1.5%, compounded annually, how much will I have in the account after 10 years? If I deposit $8,000 in a bank account that pays simple interest...

Please provide an excel file that can answer questions similiar to this one by chaning the...

Please provide an excel file that can answer questions similiar to this one by chaning the data and inputing new data 1.You sell short 100 shares of the GTY stock at $80 per share. Assume your broker requires an initial margin of 40% and a maintenance margin of 25%. 1)If the stock price drops to $70, what is the percentage margin? Initial total stock value: $80(100)=$8,000 If the stock price drops to $70, Total stock value: $70(100)=$7,000 Required margin deposit...

Answer the following questions in an Excel file. Each questions with multiple parts requires a separate...

Answer the following questions in an Excel file. Each questions with multiple parts requires a separate answer. Label your steps and show each answer (1, 2, 3, and 4) in a separate Excel tab. For problems that require a written answer, use a text box in Excel to record the text. Calculate the PV of $5,000 received 10 years from now compounded annually at discount rates of: 1% 4% 12% Calculate the FV of $5,000 invested for 20 years using...

Please create an Excel file with the calculated answers to the questions to the Excel homework...

Please create an Excel file with the calculated answers to the questions to the Excel homework for this week. The questions are listed in the attached PDF file. Please also go over the Excel file attached to this assignment in order to familiarize yourself with the different ways Excel can be used to solve Time Value of Money problems when multiple cash flows are involved. There are three worksheets in the Excel file. This Excel file with examples is just...

The data in the attached excel file comes from Consumer Reports and was collected over a...

The data in the attached excel file comes from Consumer Reports and was collected over a two-year period. It gives the average mpg over a 195-mile trip, the weight of the vehicle (pounds), engine displacement (liters), number of cylinders, horsepower, type of transmission (0 = manual, 1 = automatic), the number of gears and whether the car was foreign (1) or domestic (0). Part a: Build a model for predicting the average mpg based on the data in the attached...

Using the data from the csv file, answer the questions with rstudio # number_children - The...

Using the data from the csv file, answer the questions with rstudio # number_children - The number of children in the home # internet - Does the home have internet access? # mode - The way the household took the survey # own - Do the residents own with or without a mortgage or rent? # language - The primary language spoken in the home # decade_built - The decade the home was built 1) In how many households, wife’s...

Please see attached file and the link below and respond to following questions: 1. Is climate...

Please see attached file and the link below and respond to following questions: 1. Is climate change really a problem? 2. How much investment should firms really put into reducing emissions. 3. From a strategic standpoint, what are the practical implications of these changes/predicted changes? Sea transport has a relatively green image because ships emit less carbon dioxide per tonne and per kilometre than rail, truck or air transport. Yet, given its scale and rapid growth, it’s a major source...

Answer the following questions using the NYC2br.MTW file. Data were collected from a random sample of...

Answer the following questions using the NYC2br.MTW file. Data were collected from a random sample of two-bedroom apartments posted on Apartments.com in Manhattan and Brooklyn. A. What is one type of graph that could be used to compare the monthly rental rates of these two-bedroom apartments in Manhattan and Brooklyn? Explain why this is an appropriate graph. B. Using Minitab Express, Construct the graph you described in part A to compare the Manhattan and Brooklyn apartments in this sample. C....

Question