Question

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 can you show me the formula that will need to be entered in Excel to solve the following Conduct a goodness of fit analysis which assesses orders of a specific item by size and items you received by size. Conduct a hypothesis test with the objective of determining if there is a difference between what you ordered and what you received at the .05 level of significance. Identify the null and alternative hypotheses. Generate a scatter plot, the correlation coefficient, and the linear equation that evaluates whether a relationship exists between the number of times a customer visited the store in the past 6 months and the total amount of money the customer spent. Set up a hypothesis test to evaluate the strength of the relationship between the two variables. Use a level of significance of .05. 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. 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.

Answer 1

Answer :

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