Question

The following data was extracted from the marketing department in relation to the sales made after carrying out a number of promotions and fixing different prices.
Sales sh ‘millions’ 4 6 7 9 13 15
No of promotions 15 12 8 6 4 8
Selling price 30 24 20 14 10 4
i. Determine the equation of sales in relation to the number of promotions and price
ii. Determine the sales when the price is set at Sh 40 and the number of promotions is set at 10.

Answer 1

solution

Part i)

the multiple linear regression equation

$\hat y_i = \beta_0+\beta_1x_i+\beta_2x_i$

From our data,

$$(sales \ in \ millions)_i = \beta_0+\beta_1(promotions)_i+\beta_2(selling\ price)_i$$

Using Excel to fit the linear regression model

Step 1. The data is entered in Excel spreadsheet.

Sales are in cells A1:A7

no of promotions are in cells B1:B7

prices are in cells C1:C7

The first row of each column contains the column labels

Step 2. On the Data tab, click on Data Analysis

Step 3. On the pop-up menu, select Regression

step 4. Input Y Range as $A$1:$A$7

step 5. Input X Range as $B$1:$C$7

Step 6. Ensure the Labels is marked

Step 7. on Output Range, enter Cell $A$11

Finally, click OK.

The fitted regression parameters are displayed from cell $A$11

Parameters

$$\beta_0 = 16.0189, \beta_1= 0.1273\ and \ \beta_2 = -0.4790$$

Answer:

The regression equation becomes:

$$(sales \ in \ millions)_i = 16.0189 +0.1273 (promotions)_i - 0.4790(selling\ price)_i$$

Part ii)

When $Price = 40$ and $promotions = 10$

$$(sales \ in \ millions) = 16.0189 +0.1273 (10) - 0.4790(40)$$

$= -1.869\ million$

answer: the model suggests there will be a loss of 1,869,000 when price is 40 and the number of promotions are 10