Question

In: Statistics and Probability

The number of cans of soft drinks sold in a machine each week is recorded below...

The number of cans of soft drinks sold in a machine each week is recorded below

WEEKLY PERIOD

1

2

3

4

5

6

7

8

SOFT DRINKS SOLD

122

85

92

98

110

108

115

102

WEEKLY PERIOD

9

10

11

12

13

14

15

16

SOFT DRINKS SOLD

95

98

105

125

85

106

140

84

WEEKLY PERIOD

17

18

19

20

21

22

23

24

SOFT DRINKS SOLD

92

85

78

105

111

152

140

108

A. USING EXCEL, develop forecasts for periods 1 to 24 using a trend line.

B. Calculate the MSE AND MAD

C. CALCULATE and R statistic and interpret it.

PLEASE SHOW WORK IN EXCEL-SNAPSHOT AND EXPLANATION OF METHOD.

THANK YOU!!

Solutions

Expert Solution


SOLUTION:

(a) The trend line equation is Y=a+bx

      Using Excel a=INTERCEPT(y values,x values)

                        b=SLOPE(Y values,X values)

              a=3.6714
              b=0.0833

The trend line equation is Y=3.6714+0.0833X

forecast values

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.225695094
R Square 0.050938276
Adjusted R Square 0.007799106
Standard Error 7.043439833
Observations 24
ANOVA
df SS MS F Significance F
Regression 1 58.57901687 58.57901687 1.18078944 0.288958336
Residual 22 1091.420983 49.61004469
Total 23 1150
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 3.671454219 8.250848476 0.44497899 0.660682239 -13.43975814 20.78266658 -13.4397581 20.78266658
X Variable 1 0.083386501 0.076737831 1.086641357 0.288958336 -0.075758019 0.242531021 -0.07575802 0.242531021
RESIDUAL OUTPUT
Observation Predicted Y Residuals
1 13.84460733 -12.84460733
2 10.75930679 -8.759306794
3 11.3430123 -8.3430123
4 11.84333131 -7.843331306
5 12.84396932 -7.843969316
6 12.67719631 -6.677196314
7 13.26090182 -6.260901821
8 12.17687731 -4.176877309
9 11.5931718 -2.593171803
10 11.84333131 -1.843331306
11 12.42703681 -1.427036812
12 14.09476683 -2.094766829
13 10.75930679 2.240693206
14 12.51042331 1.489576687
15 15.34556434 -0.345564343
16 10.67592029 5.324079707
17 11.3430123 5.6569877
18 10.75930679 7.240693206
19 10.17560129 8.824398712
20 12.42703681 7.572963188
21 12.92735582 8.072644183
22 16.34620235 5.653797647
23 15.34556434 7.654435657
24 12.67719631 11.32280369

(b) Calculate MSE and MAD in Excel

period soft drinks sold(At) Predicted Y(Ft) At-Ft (At-Ft)^2
1 122 13.84460733 108.1553927 11697.58896
2 85 10.75930679 74.24069321 5511.680528
3 92 11.3430123 80.6569877 6505.549665
4 98 11.84333131 86.15666869 7422.971561
5 110 12.84396932 97.15603068 9439.294298
6 108 12.67719631 95.32280369 9086.436902
7 115 13.26090182 101.7390982 10350.8441
8 102 12.17687731 89.82312269 8068.19337
9 95 11.5931718 83.4068282 6956.69899
10 98 11.84333131 86.15666869 7422.971561
11 105 12.42703681 92.57296319 8569.753513
12 125 14.09476683 110.9052332 12299.97074
13 85 10.75930679 74.24069321 5511.680528
14 106 12.51042331 93.48957669 8740.300949
15 140 15.34556434 124.6544357 15538.72833
16 84 10.67592029 73.32407971 5376.420665
17 92 11.3430123 80.6569877 6505.549665
18 85 10.75930679 74.24069321 5511.680528
19 78 10.17560129 67.82439871 4600.149061
20 105 12.42703681 92.57296319 8569.753513
21 111 12.92735582 98.07264418 9618.243537
22 152 16.34620235 135.6537976 18401.95282
23 140 15.34556434 124.6544357 15538.72833
24 108 12.67719631 95.32280369 9086.436902
total 2541 300 2241 216331.579

MSE=

      =216331.579/24
      =9013.8157

MAD=

       =2241/24
        =93.375

Therefore MSE is 9013.8157 and MAD is 93.375

(c) R=0.2256

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.225695094
R Square 0.050938276
Adjusted R Square 0.007799106
Standard Error 7.043439833
Observations 24

orchestra answered 2 years ago
Next > < Previous

Related Solutions

The number of cans of soft drinks sold in a machine each week for 12 weeks...
The number of cans of soft drinks sold in a machine each week for 12 weeks is provided. Develop forecasts using a three-period moving average. 338, 219, 278, 265, 314, 323, 299, 259, 287, 302, 320, 335 A)What is the MSE? (show answer to 2 decimal places) What is the forecast for week 13? (show answer to 2 decimal places) B)Use a weight of 1/2 for the most recent period, 3/8 for the next most recent period, and 1/8 for...
A soft drink vending machine dispenses cans of drinks at the JKUAT cafeteria. The machine waits...
A soft drink vending machine dispenses cans of drinks at the JKUAT cafeteria. The machine waits for students and/or staff to insert coins. All soft drinks cost KShs. 50 and the machine can only accept Ten, Twenty and Forty shilling coins. A customer must insert a total of fifty shillings for the machine to dispense drinks. (i.)      Give the formal definition of the above machine, as a Finite Automaton. (ii.)     Is the machine deterministic or non-deterministic? Justify your answer (iii.)    Draw a state...
Diet versus Regular Soft Drinks (take assumptions or random readings) Cans of soft drinks are so...
Diet versus Regular Soft Drinks (take assumptions or random readings) Cans of soft drinks are so common that many people have done much of this exploration without realizing it. You, too, have probably cooled soft drink cans in ice water for a picnic. Here is your opportunity to see if you can make some generalizations about their behavior in water.    Materials •     1 12-fl oz (355 mL) aluminum can of diet soft drink, unopened •     1 12-fl oz (355 mL) aluminum...
A soft drink manufacturer wishes to know how many soft drinks teenagers drink each week. They...
A soft drink manufacturer wishes to know how many soft drinks teenagers drink each week. They want to construct a 90% confidence interval with an error of no more than 0.07. A consultant has informed them that a previous study found the mean to be 5.6 soft drinks per week and found the standard deviation to be 1.4. What is the minimum sample size required to create the specified confidence interval? Round your answer up to the next integer.
Two computer stores recorded the number of computers sold in a week along with the sizes...
Two computer stores recorded the number of computers sold in a week along with the sizes of their hard drives. At a = .05, test the claim that the distribution of hard drives and the store where the computers were bought are not related. store one sold 13 20gb, 11 40gb, 11 80gb, 8 160gb. store two sold 104 20gb, 33 40gb, 15 80 gb, 10 160gb Hypothesis with claim Draw Curve Critical Value Test Value Decision Summary
Two computer stores recorded the number of computers sold in a week along with the sizes...
Two computer stores recorded the number of computers sold in a week along with the sizes of their hard drives. At a = .05, test the claim that the distribution of hard drives and the store where the computers were bought are not related. store one sold 13 20gb, 11 40gb, 11 80gb, 8 160gb. store two sold 104 20gb, 33 40gb, 15 80 gb, 10 160gb Hypothesis with claim Draw Curve Critical Value Test Value Decision Summary
The Brite Beverage Company bottles soft drinks into aluminum cans. The manufacturing process consists of three...
The Brite Beverage Company bottles soft drinks into aluminum cans. The manufacturing process consists of three activities: Mixing: water, sugar, and beverage concentrate are mixed. Filling: mixed beverage is filled into 12-oz. cans. Packaging: properly filled cans are boxed into cardboard "fridge packs". The activity costs associated with these activities for the period are as follows: Mixing $268,000 Filling 237,000 Packaging 95,000 Total $600,000 The activity costs do not include materials costs, which are ignored for this analysis. Each can...
Process Activity Analysis The Brite Beverage Company bottles soft drinks into aluminum cans. The manufacturing process...
Process Activity Analysis The Brite Beverage Company bottles soft drinks into aluminum cans. The manufacturing process consists of three activities: Mixing: water, sugar, and beverage concentrate are mixed. Filling: mixed beverage is filled into 12-oz. cans. Packaging: properly filled cans are boxed into cardboard "fridge packs". The activity costs associated with these activities for the period are as follows: Mixing $612,000 Filling 543,000 Packaging 165,000 Total $1,320,000 The activity costs do not include materials costs, which are ignored for this...
A distributor of soft drink vending machines knows fromexperience that the mean number of drinks...
A distributor of soft drink vending machines knows from experience that the mean number of drinks a machine will sell per day varies according to the location of the machine. At a local mall, two machines are placed in what the distributor believes to be two optimal locations (Location A and B). The number of drinks sold per day for each machine is recorded for a random sample of 20 days. For each day, the number of drinks purchased at...
To determine if their 14⁢oz filling machine is properly adjusted, Grover Soft Drinks has decided to...
To determine if their 14⁢oz filling machine is properly adjusted, Grover Soft Drinks has decided to use an x‾-Chart which uses the range to estimate the variability in the sample. Period   obs1   obs2   obs3   obs4   obs5   Sample Mean Sample Range 1   13.95   13.98   13.98   13.96   13.97   13.9680 0.03 2   14.02   13.98   13.98   14.00   13.97 13.9900 0.05 3   13.96   13.95   14.01   13.97   13.97   13.9720 0.06 4   13.98   13.99   14.03   13.97   14.01   13.9960 0.06 5   14.04   13.96   14.00   14.02   14.03   14.0100 0.08...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT