Question

Consider the following three market price data.

(a) Enter the following sales data using Excel.

sales = x1	sales = x2	sales = x3	Price A ($)
10	10	10	100
10	10	13	99
10	10	15	98
12	10	10	98
12	12	10	97
12	14	10	95
14	10	10	94

(b) Use LINEST to find a linear price function for the above data for three markets. (Note: You will be using the format (Price = b + m1*x1 + m2*x2 + m3*x3) where Price = P(x1, x2, x3) and the values for b, m1, m2, and m3 are all numerical values. Enter all values correct to 2 decimal places.)
P(x1, x2, x3) =  +  *x1 +  *x2 +  *x3

(c) Use the values for b and the three slopes to predict the price when the sales are x1 = 15, x2 = 20, and x3 = 15.
P(15, 20, 15) = $  

(d) Repeat (a) - (c) when one additional price data is used where x1 = 14, x2 = 12, x3 = 12 and Price A = $89. (Note: Add this new information into your spreadsheet by adding an extra row at the bottom.)

New linear price function
P(x1, x2, x3) =  +  *x1 +  *x2 +  *x3

New price prediction
P(15, 20, 15) = $

Answer 1

(a)

Just open an empty excel file and enter the following data table as it in the file.

This would look like:

(b) Then in cell E1 write m3, F1 write m2, G1 write m1 and H1 write b

Then select the cells E2, F2, G2, H2 and write the function:

=LINEST(D2:D8,A2:C8,TRUE,FALSE)

in which the first argument is known y's, the second is known x's, the third is true if you want constant coefficient and the fourth is true if you want additional regression statistics.

This would look like:

Then press Ctrl+Shift+Enter to get the regression coefficients m3,m2,m1,b in the cells E2,F2,G2,H2 respectively.

This would look like:

that means

m3=-0.49

m2=-0.53

m1=-1.50

b=125.64

(c) For x1=15,x2=20,x3=15

Price A=m1*x1+m2*x2+m3*x3+b

Price A=(-1.5*15)+(-0.53*20)+(-0.49*15)+125.64

Price A=85.19

(d) When you enter more data in th table then it would look like:

Then repeat the same procedure for m3,m2,m1,b as done in (b) and results will look like:

and then results will come on pressing Ctrl+Shift+Enter

New values of regression coefficients are:

m3=-0.81

m2=-0.68

m1=-2.01

b=136.35

and then for predicting the price

For x1=15,x2=20,x3=15

Price A=m1*x1+m2*x2+m3*x3+b

Price A=(-2.01*15)+(-0.68*20)+(-0.81*15)+136.35

Price A=80.45