Question

The number of plumbing repair jobs performed by Auger's Plumbing Service in each of the last nine months are listed below.

Month Jobs Month Jobs Month Jobs  

March 353 June 374 September 399

April 387 July 396 October 412

May 342 August 409   November 408

a. Assuming a linear trend function, forecast the number of repair jobs Auger's will perform in December using the least squares method.

b. What is your forecast for December using a three-period weighted moving average with weights of .6, .3, and .1? How does it compare with your forecast from part (a)?

In excel if possible.

Answer 1

a)

x	y	(x-x̅)²	(y-ȳ)²	(x-x̅)(y-ȳ)
1	353	16.00	1133.44	134.67
2	387	9.00	0.11	-1.00
3	342	4.00	1995.11	89.33
4	374	1.00	160.44	12.67
5	396	0.00	87.11	0.00
6	409	1.00	498.78	22.33
7	399	4.00	152.11	24.67
8	412	9.00	641.78	76.00
9	408	16.00	455.11	85.33

	ΣX	ΣY	Σ(x-x̅)²	Σ(y-ȳ)²	Σ(x-x̅)(y-ȳ)
total sum	45	3480	60	5124.0	444.00
mean	5.00	386.67	SSxx	SSyy	SSxy

sample size ,   n =   9          
here, x̅ = Σx / n=   5.00   ,     ȳ = Σy/n =   386.67  
                  
SSxx =    Σ(x-x̅)² =    60.0000          
SSxy=   Σ(x-x̅)(y-ȳ) =   444.0          
                  
estimated slope , ß1 = SSxy/SSxx =   444.0   /   60.000   =   7.4000
                  
intercept,   ß0 = y̅-ß1* x̄ =   349.6667          
                  
so, regression line is   Ŷ =   349.6667   +   7.4000   *x

Ŷ =   349.66667   +   7.400000   *   10   =   423.667

b)

Month	Demand	Moving average= 0.1 * 353 + 0.3 * 387 + 0.6 * 342
1	353
2	387
3	342
4	374	356.6
5	396	365.7
6	409	384
7	399	401.6
8	412	401.7
9	408	407.8
10		408.3

Please note that we have give maximum weightage to recent month sales as itwightage order is not given.

Forecast in Part(a) is more than Part(b)

Please revert back in case of any doubt.

Please upvote. Thanks in advance.