4. There is data for you in the tab called EComSales. It comes from the Federal...

4. There is data for you in the tab called EComSales. It comes from the Federal Reserve and represents quarterly e-commerce sales data in the U.S. for Quarter 4, 1999 to Quarter 4, 2019. Month 1=Q1, Month 4=Q2, Month 7=Q3, Month 10 = Q4. Run a regression forecasting sales for all 4 quarters in 2020. Print your regression results in a new tab. Rename that tab Answer Q4. In that cells below your regression results, forecast sales for Q1:2020, Q2:2020, Q3:2020, and Q4:2020. Round all answers to the nearest dollar in Excel and put a comma in so I can read it easier (do not round by hand or put the comma in by hand– set up excel to do the rounding and the comma for you).

IT IS NOT LETTING ME POST CORRECTLY, THE COLUMN OF 5553 IS FOR Q1, THE 6059 FOR Q2, THE 6892 FOR Q3 AND THE 5241 FOR Q4

	Year	Years since 1999 (X)	Q1	Q2	Q3	Q4
1999	0				5241
2000	1	5553	6059	6892	9104
2001	2	7923	7816	7737	10784
2002	3	9621	10076	10760	14166
2003	4	12358	12973	13909	17915
2004	5	16201	16502	17371	22523
2005	6	20142	20953	22171	28121
2006	7	25490	25817	26892	35135
2007	8	30403	31589	32352	42126
2008	9	34270	34260	33486	39576
2009	10	32284	32924	34494	45805
2010	11	37059	38467	40075	54320
2011	12	44243	45426	46159	64435
2012	13	51722	52542	53832	73827
2013	14	58355	60181	61344	83766
2014	15	66148	69715	71331	95830
2015	16	75918	79916	81769	109362
2016	17	86811	91969	93830	124697
2017	18	99805	107094	108905	145230
2018	19	115602	122934	124214	160894
2019	20	129015	139647	145833	187252

PLEASE EXPLAIN STEP BY STEP AND PUT EXCEL FORMULAS! THANK YOU

Expert Solution

The answer is:

Forecast	124027	82
Forecast	125736	83
Forecast	127446	84
Forecast	129155	85

The analysis is:

The formulas are:

To get answers:

The formulas are:

The output is:

Data			Forecasts and Error Analysis
Period	Demand (y)	Period(x)	Forecast	Error	Absolute	Squared	Abs Pct Err
Period 1	5241	1	-14434.9	19675.88	19675.88	3.87E+08	375.42%
Period 2	5553	2	-12725.5	18278.47	18278.47	3.34E+08	329.16%
Period 3	6059	3	-11016.1	17075.06	17075.06	2.92E+08	281.81%
Period 4	6892	4	-9306.66	16198.66	16198.66	2.62E+08	235.04%
Period 5	9104	5	-7597.25	16701.25	16701.25	2.79E+08	183.45%
Period 6	7923	6	-5887.85	13810.85	13810.85	1.91E+08	174.31%
Period 7	7816	7	-4178.44	11994.44	11994.44	1.44E+08	153.46%
Period 8	7737	8	-2469.04	10206.04	10206.04	1.04E+08	131.91%
Period 9	10784	9	-759.634	11543.63	11543.63	1.33E+08	107.04%
Period 10	9621	10	949.7716	8671.228	8671.228	75190202	90.13%
Period 11	10076	11	2659.177	7416.823	7416.823	55009266	73.61%
Period 12	10760	12	4368.582	6391.418	6391.418	40850223	59.40%
Period 13	14166	13	6077.987	8088.013	8088.013	65415950	57.09%
Period 14	12358	14	7787.392	4570.608	4570.608	20890453	36.99%
Period 15	12973	15	9496.798	3476.202	3476.202	12083982	26.80%
Period 16	13909	16	11206.2	2702.797	2702.797	7305112	19.43%
Period 17	17915	17	12915.61	4999.392	4999.392	24993919	27.91%
Period 18	16201	18	14625.01	1575.987	1575.987	2483734	09.73%
Period 19	16502	19	16334.42	167.5814	167.5814	28083.54	01.02%
Period 20	17371	20	18043.82	-672.824	672.8238	452691.8	03.87%
Period 21	22523	21	19753.23	2769.771	2769.771	7671631	12.30%
Period 22	20142	22	21462.63	-1320.63	1320.634	1744075	06.56%
Period 23	20953	23	23172.04	-2219.04	2219.039	4924136	10.59%
Period 24	22171	24	24881.44	-2710.44	2710.445	7346510	12.23%
Period 25	28121	25	26590.85	1530.15	1530.15	2341359	05.44%
Period 26	25490	26	28300.26	-2810.26	2810.255	7897534	11.02%
Period 27	25817	27	30009.66	-4192.66	4192.66	17578400	16.24%
Period 28	26892	28	31719.07	-4827.07	4827.066	23300561	17.95%
Period 29	35135	29	33428.47	1706.529	1706.529	2912242	04.86%
Period 30	30403	30	35137.88	-4734.88	4734.876	22419050	15.57%
Period 31	31589	31	36847.28	-5258.28	5258.281	27649521	16.65%
Period 32	32352	32	38556.69	-6204.69	6204.686	38498133	19.18%
Period 33	42126	33	40266.09	1859.908	1859.908	3459259	04.42%
Period 34	34270	34	41975.5	-7705.5	7705.497	59374681	22.48%
Period 35	34260	35	43684.9	-9424.9	9424.902	88828778	27.51%
Period 36	33486	36	45394.31	-11908.3	11908.31	1.42E+08	35.56%
Period 37	39576	37	47103.71	-7527.71	7527.712	56666455	19.02%
Period 38	32284	38	48813.12	-16529.1	16529.12	2.73E+08	51.20%
Period 39	32924	39	50522.52	-17598.5	17598.52	3.1E+08	53.45%
Period 40	34494	40	52231.93	-17737.9	17737.93	3.15E+08	51.42%
Period 41	45805	41	53941.33	-8136.33	8136.333	66199920	17.76%
Period 42	37059	42	55650.74	-18591.7	18591.74	3.46E+08	50.17%
Period 43	38467	43	57360.14	-18893.1	18893.14	3.57E+08	49.12%
Period 44	40075	44	59069.55	-18994.5	18994.55	3.61E+08	47.40%
Period 45	54320	45	60778.95	-6458.95	6458.954	41718089	11.89%
Period 46	44243	46	62488.36	-18245.4	18245.36	3.33E+08	41.24%
Period 47	45426	47	64197.76	-18771.8	18771.76	3.52E+08	41.32%
Period 48	46159	48	65907.17	-19748.2	19748.17	3.9E+08	42.78%
Period 49	64435	49	67616.58	-3181.58	3181.575	10122420	04.94%
Period 50	51722	50	69325.98	-17604	17603.98	3.1E+08	34.04%
Period 51	52542	51	71035.39	-18493.4	18493.39	3.42E+08	35.20%
Period 52	53832	52	72744.79	-18912.8	18912.79	3.58E+08	35.13%
Period 53	73827	53	74454.2	-627.196	627.1959	393374.7	00.85%
Period 54	58355	54	76163.6	-17808.6	17808.6	3.17E+08	30.52%
Period 55	60181	55	77873.01	-17692	17692.01	3.13E+08	29.40%
Period 56	61344	56	79582.41	-18238.4	18238.41	3.33E+08	29.73%
Period 57	83766	57	81291.82	2474.183	2474.183	6121582	02.95%
Period 58	66148	58	83001.22	-16853.2	16853.22	2.84E+08	25.48%
Period 59	69715	59	84710.63	-14995.6	14995.63	2.25E+08	21.51%
Period 60	71331	60	86420.03	-15089	15089.03	2.28E+08	21.15%
Period 61	95830	61	88129.44	7700.562	7700.562	59298660	08.04%
Period 62	75918	62	89838.84	-13920.8	13920.84	1.94E+08	18.34%
Period 63	79916	63	91548.25	-11632.2	11632.25	1.35E+08	14.56%
Period 64	81769	64	93257.65	-11488.7	11488.65	1.32E+08	14.05%
Period 65	109362	65	94967.06	14394.94	14394.94	2.07E+08	13.16%
Period 66	86811	66	96676.46	-9865.46	9865.464	97327375	11.36%
Period 67	91969	67	98385.87	-6416.87	6416.869	41176207	06.98%
Period 68	93830	68	100095.3	-6265.27	6265.274	39253661	06.68%
Period 69	124697	69	101804.7	22892.32	22892.32	5.24E+08	18.36%
Period 70	99805	70	103514.1	-3709.08	3709.085	13757309	03.72%
Period 71	107094	71	105223.5	1870.51	1870.51	3498808	01.75%
Period 72	108905	72	106932.9	1972.105	1972.105	3889198	01.81%
Period 73	145230	73	108642.3	36587.7	36587.7	1.34E+09	25.19%
Period 74	115602	74	110351.7	5250.295	5250.295	27565592	04.54%
Period 75	122934	75	112061.1	10872.89	10872.89	1.18E+08	08.84%
Period 76	124214	76	113770.5	10443.48	10443.48	1.09E+08	08.41%
Period 77	160894	77	115479.9	45414.08	45414.08	2.06E+09	28.23%
Period 78	129015	78	117189.3	11825.67	11825.67	1.4E+08	09.17%
Period 79	139647	79	118898.7	20748.27	20748.27	4.3E+08	14.86%
Period 80	145833	80	120608.1	25224.86	25224.86	6.36E+08	17.30%
Period 81	187252	81	122317.5	64934.46	64934.46	4.22E+09	34.68%
			Total	2.91E-10	948034.1	1.93E+10	3633.81%
Intercept	-16144.281		Average	3.59E-12	11704.12	2.39E+08	44.86%
Slope	1709.40522			Bias	MAD	MSE	MAPE
					SE	15648.29
Forecast	124027	82
Forecast	125736	83			Correlation	0.932705
Forecast	127446	84			Coefficient of determination	0.869938
Forecast	129155	85

orchestra answered 1 year ago

The data set for this question set (Tab Q1 in the Excel data file) comes from...

The data set for this question set (Tab Q1 in the Excel data file) comes from a research project that tracks the elderly residents in a community to monitor their cognitive function and general health. Based on the literature, education is considered a protective factor against dementia, and memory decline is usually the first sign of dementia. So the researchers would like to know whether education level (measured in number of years of formal schooling) is correlated with memory function...

The data for this section comes from data.world, and represents professional soccer players (data is a...

The data for this section comes from data.world, and represents professional soccer players (data is a few years old, I think from 2016) ```{r} players <- read.csv('https://s3.amazonaws.com/douglas2/data/players.csv', stringsAsFactors = F) ``` 1. Without splitting the club variable, determine how many of the 7,282 players are Forwards. ```{r} # Your Solution Here substring(players$club, 1, 7) [substring(players$club, 1, 7)=='Forwards'] 2. Create separate columns for position and club (information currently found in club column) ```{r} # Your Solution Here 3. Construct a barplot...

Consider the data set below.determine if this data comes from a normally distributed population. Draw a...

Consider the data set below.determine if this data comes from a normally distributed population. Draw a histogram and determine if it seems to be mound shaped Determine the number of outliers for your dataset Draw a normal probability plot. You should comment on whether theses measure suggest normality or not. Use all the measures in your discussion This data is the number of deaths each year by tornados in the past 30 years 59 32 50 53 39 39 33...

In the Excel data file, the tab labeled Question 1 contains data on the number of...

In the Excel data file, the tab labeled Question 1 contains data on the number of times boys and girls raise their hands in class. Conduct the t-test: Two-Sample Assuming Equal Variances. Males 9,8,4,9,3,8,10,8,9,10,7,6,12 Females 3,5,1,2,6,4,3,6,7,9,7,3,7,6,8,8 a. What is the null hypothesis? b. What is the research hypothesis? c. Why run a Two-Sample Assuming Equal Variances t-test? d. Interpret the findings. What are the results of the hypothesis test? Can you reject the null hypothesis?

Problem 4. The data {(1, 10),(2, 5.49),(3, 0.89),(4, −0.14),(5, −1.07),(6, 0.84)} comes from a model F(x)...

Problem 4. The data {(1, 10),(2, 5.49),(3, 0.89),(4, −0.14),(5, −1.07),(6, 0.84)} comes from a model F(x) = (r/ x )+ sx. Use least squares to estimate the parameters r, s.

Using the information on the Data tab of the spreadsheet , complete the cost of goods...

Using the information on the Data tab of the spreadsheet , complete the cost of goods manufactured for January. Show calculations for all items/questions Below is manufacturing data from Webster Company for January, 2020. Beginning raw materials inventory $ 321,300 Raw materials purchased $ 845,150 Ending raw materials inventory $ 275,475 Direct labor costs $ 104,590 Indirect materials $ 47,560 Indirect labor $ 29,876 Factory utilities and maintenance $ 148,890 Factory depreciation $ 19,876 Other factory related overhead $ 7,890...

The following information is taken from publicly traded retailers. The data comes from the balance sheet,...

The following information is taken from publicly traded retailers. The data comes from the balance sheet, income statement, and Item 2 on the companies' Form 10-K filings. Use the information to answer the requirements. Company Revenue (Millions) COGS Average Inventory Retail SQ footage (000s) Number of Stores Autozone (AZO) $ 11,221 $ 4,902 $ 3,913 $ 41,066 $ 6,202 Costco (COST) $ 151,576 $ 121,715 $ 10,437 $ 110,700 $ 762 Home Depot (HD) $ 108,203 $ 71,043 $ 13,337...

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...

it is claimed that the following data comes from a Poisson distribution with mean 5. test...

it is claimed that the following data comes from a Poisson distribution with mean 5. test the claim at a 0.01 level of significance score < = 3 4 5 6 > = 7 total frequency 6 9 10 7 4 36

Question: Assume the data in tab 11.a (Cancer mortality) represent data on cancer mortality rates for...

Question: Assume the data in tab 11.a (Cancer mortality) represent data on cancer mortality rates for the communities in your hospital's service area. Perform a statistical test to determine if the average cancer mortality in the rural communities is different from the average mortality in the urban communities. (Assume the mortality rates are normally distributed.) Offer an interpretation of your results. Remember to describe the mean cancer mortality rates. (4 points) Does this problem call for a one-tailed test or...

Question