ORDERS VS. SHIPMENTS CUSTOMERS IN PAST 6 MONTHS MONTHLY SALES ($) Size # Ordered # Received...

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.

Solutions

Expert Solution

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

orchestra answered 2 years ago

Next > < Previous

Related Solutions

For the Hawkins Company, the monthly percentages of all shipments received on time over the past...

For the Hawkins Company, the monthly percentages of all shipments received on time over the past 12 months are 80, 82, 84, 83, 83, 84, 85, 84, 82, 83, 84, and 83. (a) Choose the correct time series plot. (i) (ii) (iii) (iv) - Select your answer -Plot (i)Plot (ii)Plot (iii)Plot (iv) What type of pattern exists in the data? - Select your answer -Horizontal Pattern OR Trend Pattern (b)...

For the Hawkins Company, the monthly percentages of all shipments received on time over the past...

For the Hawkins Company, the monthly percentages of all shipments received on time over the past 12 months are 80, 82, 84,83,83,84,85,84,82,83,84, and 83. Using excel. A. Compute the MSE’s for two forecasts, one using a 2 month moving average and the other exponential smoothing with a value of 0.25. B. Which of these two forecasting approaches is preferred? Please be sure to explain why you have reached this conclusion.

Problem 6-11 For the Hawkins Company, the monthly percentages of all shipments received on time over...

Problem 6-11 For the Hawkins Company, the monthly percentages of all shipments received on time over the past 12 months are 80, 82, 84, 83, 83, 84, 85, 84, 82, 83, 84, and 83. Choose the correct time series plot. What type of pattern exists in the data? Horizontal Pattern Compare a three-month moving average forecast with an exponential smoothing forecast for α = 0.2. Which provides the better forecasts using MSE as the measure of model accuracy? Do not...

The sales of mouthwash at MARKAL Pharmacy over the past 6 months have average 2,000 cases...

The sales of mouthwash at MARKAL Pharmacy over the past 6 months have average 2,000 cases per month, which is the current order quantity. Markal's cost is $12.00 per case. The company estimates its cost of capital to be 12 percent. Insurance, taxes, breakage, handling, and pilferage are estimated to be approximately 6 percent of item cost. The estimated order cost is $38.00. What is economic order quantity? What is the total inventory cost under economic order quantity? Compare the...

1. A file that stores sales orders pending shipment of ordered goods or completion of services...

1. A file that stores sales orders pending shipment of ordered goods or completion of services is an example of a(n) ___________ file. A. master B. transaction C. open D. reference 2. Which of the following is not a reason that an external auditor would have an interest in an organization's internal control? A. The external auditor is required to evaluate internal control in planning an external audit, according to generally accepted auditing standards B. Internal control provides a measure...

A growing monthly perpetuity will start 6 months from today. If the discount rate is 6%...

A growing monthly perpetuity will start 6 months from today. If the discount rate is 6% APR compounded monthly, what is the value of the perpetuity today (at time t=0) if the growth rate is 1.2% APR compounded monthly and the first payment is $100? Enter answer as a dollar, rounded to nearest dollar.

ORDER TO CASH PROCESS Sales Department Customers send their orders by mail or fax to the...

ORDER TO CASH PROCESS Sales Department Customers send their orders by mail or fax to the sales department. Upon receiving an order, a sales clerk checks the credit worthiness via a terminal the customer record in Money Talk’s Customer Master file to review the available credit before creating a Sales Invoice record. For customers with no or insufficient available credit, the clerk submits the order to the credit manager for approval. If granted, the credit manager verbally authorizes and instruct...

OfficeMart Inc. has "cash and carry" customers and credit customers. OfficeMart estimates that 30% of monthly sales are to cash customers, while the remaining sales are to credit customers.

Schedule of Cash Collections of Accounts Receivable OfficeMart Inc. has "cash and carry" customers and credit customers. OfficeMart estimates that 30% of monthly sales are to cash customers, while the remaining sales are to credit customers. Of the credit customers, 20% pay their accounts in the month of sale, while the remaining 80% pay their accounts in the month following the month of sale. Projected sales for the next three months are as follows: October $114,000 November 143,000 December...

Assume the local Pearle Vision has the following information on the number of sales orders received...

Assume the local Pearle Vision has the following information on the number of sales orders received and order-processing costs. Month Sales Orders Order-Processing Costs 1 ........................................ 3,000 $82,700 2 ........................................ 1,500 50,375 3 ........................................ 4,400 120,700 4 ........................................ 2,800 81,900 5 ........................................ 2,300 69,775 6 ........................................ 1,200 43,100 7 ........................................ 2,000 62,500 Required a. Use information from the high- and low-volume months to develop a cost-estimating equation for monthly order-processing costs. b. Plot the data on a scatter diagram. Using...

In an excel spreadsheet, calculate the real interest rate over the past 24 months (using monthly...

In an excel spreadsheet, calculate the real interest rate over the past 24 months (using monthly data) for the 30 year Treasury bond rate as the nominal interest rate and assuming that expected inflation was equal to actual inflation (based on the change in CPI). Make sure to include the Fisher Equation.

Subjects