Please use data in Excel to: St = a + b *t = -$8,937.7 + $1,908.5*t a. verify th

Question

Please use data in Excel to:

St = a + b *t = -$8,937.7 + $1,908.5*t   a. verify that the sales and time can write as:

Note: you are expected to enter the data in excel first and run a regression based on the model

St = a+b*t. Report the output results as your answer.

b. Can you forecast Microsoft’s sales for the year 2009?  

Year Sales Revenue ($millions) Natural Logarithim Common Logarithm Time Period Fitted Sales 1985 139.5 4.938 2.145 1 -7209.2 1986 202.1 5.309 2.306 2 -5120.6 1987 345.9 5.846 2.539 3 -3212.1 1988 590.8 6.381 2.771 4 -1303.6 1989 803.5 6.689 2.905 5 605 1990 1183.4 7.076 3.073 6 2513.5 1991 1843.4 7.519 3.266 7 4422.1 1992 2758.7 7.923 3.441 8 6330.6 1993 3753 8.23 3.574 9 8239.2 1994 4649 8.444 3.667 10 10147.7 1995 5937 8.689 3.774 11 12056.3 1996 8671 9.068 3.938 12 13964.8 1997 11358 9.338 4.055 13 15873.4 1998 14484 9.581 4.161 14 17781.9 1999 19747 9.891 4.296 15 19690.5 2000 22956 10.041 4.361 16 21599 2001 25296 10.138 4.403 17 23507.6 2002 28635 10.262 4.457 18 25416.1 2003 32187 10.379 4.508 19 27324.7 2004 36500 10.505 4.562 20 29233.2

Explanation / Answer

(a)

Calculation Summary

Sum of X = 210
Sum of Y = 221860.1
Mean X = 10.5
Mean Y = 11093.005
Sum of squares (SSX) = 665
Sum of products (SP) = 1270894.65

Regression Equation = = bX + a

b = SP/SSX = 1270894.65/665 = 1911.11977

a = MY - bMX = 11093.01 - (1911.12*10.5) = -8973.75263

= 1911.11977X - 8973.75263

where b=1911.11977

a= -8973.75263

x=t

y=s

s(t) = 1911.11977 * t - 8973.75263

(b)

for the year 2009, put t=25

s(t) = 1911.11977 * t - 8973.75263

= 38804.24162

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---