Question

3. Use the regression line formula to forecast how much a customer might spend on merchandise if that customer visited the store 13 times in a 6 month period. Consider the average monthly sales of 2014, $1310, as your base to:

* Calculate indices for each month for the next two years.

* Graph a time series plot.

4. In the Data Analysis Toolpak, use Excel's Exponential Smoothing option.

* Apply a damping factor of .5, to your monthly sales data.

*Create a new time series graph that compares the original and the revised monthly sales data.

ORDERS VS. SHIPMENTS				CUSTOMERS IN PAST 6 MONTHS				MONTHLY SALES ($)
Size	# Ordered	# Received		Customer #	# Visits	$ Purchases		Month	$ Sales
Extra Small	30	23		1	8	468		Jan	1375
Small	50	54		2	6	384		Feb	1319
Medium	85	92		3	8	463		Mar	1222
Large	95	91		4	2	189		Apr	1328
Extra Large	60	63		5	10	542		May	1493
2X Large	45	42		6	4	299		Jun	1492
				7	6	345		Jul	1489
				8	2	197		Aug	1354
				9	4	293		Sep	1530
				10	1	119		Oct	1483
				11	3	211		Nov	1450
				12	9	479		Dec	1495
				13	7	430		Jan	1545
				14	7	404		Feb	1454
				15	6	359		Mar	1322
				16	10	544		Apr	1492
				17	9	522		May	1678
				18	5	327		Jun	1645
				19	6	353		Jul	1580
				20	7	405		Aug	1493
				21	4	289		Sep	1719
				22	7	386		Oct	1573
				23	7	403		Nov	1629
				24	1	146		Dec	1680
				25	7	416
				26	9	485
				27	3	333
				28	7	241
				29	2	391
				30	6	268

Answer 1

Use the regression line formula to forecast how much a customer might spend on merchandise if that customer visited the store 13 times in a 6 month period.

Here we have to fit regression of purchase on visit.

We can find regression equation in excel.

steps :

ENTER data into excel sheet --> Data --> Data Analysis --> Regression --> ok --> Input Y Range : Select purchase data --> Input X Range : select number of visits data --> Click on labels --> Output Range : Select one empty cell --> ok

The regression equation is,

Purchase = 136.16 + 38.09*number of visits

Now we have to find Purchase for # of visits = 13

Purchase = 136.16 + 38.09*13 = 632.59

-----------------------------------------------------------------------------------------------------

Consider the average monthly sales of 2014, $1310, as your base to:

Calculate indices for each month for the next two years.

Graph a time series plot.

In the Data Analysis Toolpak, use Excel's Exponential Smoothing option.

Apply a damping factor of .5, to your monthly sales data.

Create a new time series graph that compares the original and the revised monthly sales data.

#N/A
1375
1347
1284.5
1306.25
1399.625
1445.813
1467.406
1410.703
1470.352
1476.676
1463.338
1479.169
1512.084
1483.042
1402.521
1447.261
1562.63
1603.815
1591.908
1542.454
1630.727
1601.863
1615.432