Question

When one company (A) buys another company(B), some workers of company B are terminated. Terminated workers get severance pay. To be fair, company A fixes the severance payment to company B workers as equivalent to company A workers who were terminated in the last one year. A 36-year-old Mohammed, worked for company B for the last 10 years earning 32000 per year, was terminated with a severance pay of 5 weeks of salary. Bill smith complained that this is unfair that someone with the same credentials worked in company A received more. You are called in to settle the dispute. You are told that severance is determined by three factors; age, length of service with the company and the pay. You have randomly taken a sample of 40 employees of company A terminated last year. You recorded

Number of weeks of severance pay

Age of employee

Number of years with the company

Annual pay in 1000s

Weeks SP	Age	Years	Pay	Weeks SP	Age	Years	Pay
13	37	16	46	11	44	12	35
13	53	19	48	10	33	13	32
11	36	8	35	8	41	14	42
14	44	16	33	5	33	7	37
3	28	4	40	6	27	4	35
10	43	9	31	14	39	12	36
4	29	3	33	12	50	17	30
7	31	2	43	10	43	11	29
12	45	15	40	14	49	14	29
7	44	15	32	12	48	17	36
8	42	13	42	12	41	17	37
11	41	10	38	8	39	8	36
9	32	5	25	12	49	16	28
10	45	13	36	10	37	10	35
18	48	19	40	11	37	13	37
10	46	14	36	17	52	20	34
8	28	6	22	13	42	11	33
15	44	16	32	14	42	19	38
7	40	6	27	5	27	2	25
9	37	8	37	11	50	15	36

Answer the following questions based on your outputs. Provide the screen shots of your outputs wherever appropriate.

1. Find the regression equation and rank the variables the campaign type from the most explained to the least explained.

Answer 1

Dependent variable: Week SP

Independent variables: Age, Years, Pay

The regression analysis is done in excel by following steps

Step 1: Write the data values in excel. The screenshot is shown below,

Step 2: DATA > Data Analysis > Regression > OK. The screenshot is shown below,

Step 3: Select Input Y Range: 'Weeks SP' column, Input X Range: 'Age, Years, and Pay' column then OK. The screenshot is shown below,

The result is obtained. The screenshot is shown below,

Regression equation:

Significance of Independent variables

From, the result summary,

Coefficients	P-value
Age	0.616326	>	0.05	Not Significant
Years	0.000226	<	0.05	Significant
Pay	0.287475	>	0.05	Not Significant

The P-value for the independent variable Years is less than 0.05 at a 5% significance level hence we can conclude that only the independent variable Years is significant in the model.

Now, the ranking is done based on the p-values,

Coefficients	P-value	Rank
Years	0.000226	1
Pay	0.287475	2
Age	0.616326	3