Question

1. An accountant for a large department store has the business objective of developing a model to predict the amount of time it takes to process invoices. Data are collected from the past 32 working days, and the number of invoices processed and completion time (in hours) are stored (invoice.xlsx).

(a) At the level ? = 0.01, conduct a t test with the null hypothesis of zero correlation between the number of invoices and the completion time.

(b) Using the least-squares method, determine the regression coefficients ?0 and ?1 in the linear regression model with the number of invoices as an independent variable and the completion time as a dependent variable

(c) At the level ? = 0.01, is there evidence of a linear effect of the number of invoices on the completion time? Answer based on the F test in ANOVA analysis

(d) Determine the coefficient of determination, ?2, and interpret its meaning (e) Determine the standard error of estimate, and interpret its meaning
(f) At the level ? = 0.01, conduct a t test about the population slope ?1
(g) At the 99% confidence level, obtain the confidence interval of ?1

(h) Predict the expected completion time when the number of invoices is 100.
(i) Evaluate the assumptions of linearity and equal variance by constructing a residual plot with

? and ? axis for the number of invoices and the residual, respectively
(j) Evaluate the assumption of independence, particularly about autocorrelation, by constructing

a residual plot with ? and ? axis for the order of observations and the residual, respectively (k) Evaluate the assumption of normality by constructing a normal probability plot with ? and

? axis for the quantile and the residual, respectively

Invoices	Time
103	1.5
173	2.0
149	2.1
193	2.5
169	2.5
29	0.5
188	2.3
19	0.3
201	2.7
58	1.0
110	1.5
83	1.2
60	0.8
25	0.4
60	1.8
190	2.9
233	3.4
289	4.1
45	1.2
70	1.8
241	3.8
163	2.8
120	2.5
201	3.3
135	2.0
80	1.7
77	1.7
222	3.1
181	2.8
30	1.0
61	1.9
120	2.6

Answer 1

Sol:

install analysis tool pack in excel.

then go to

Data >Data analysis >Regression

you will get

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.928597392
R Square	0.862293116
Adjusted R Square	0.857702886
Standard Error	0.3673572
Observations	32
ANOVA
	df	SS	MS	F	Significance F
Regression	1	25.35114813	25.35115	187.854	1.87986E-14
Residual	30	4.04853937	0.134951
Total	31	29.3996875
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	0.48715367	0.131420445	3.706833	0.000849	0.218757316	0.755550024
invoices	0.012288152	0.000896554	13.70599	1.88E-14	0.010457145	0.014119159

SolutionB:

From the above output:

Regression eq is

Time=0.4872+0.0123*invoices

slope=0.0123

y intercept=0.4872

(c) At the level ? = 0.01, is there evidence of a linear effect of the number of invoices on the completion time? Answer based on the F test in ANOVA analysis

H0:There is no linear relationship between invoices and time

H1:There is linear relationship between invoices and time.

ANOVA
	df	SS	MS	F	Significance F
Regression	1	25.35114813	25.35115	187.854	1.87986E-14
Residual	30	4.04853937	0.134951
Total	31	29.3996875

F(1,30)=187.854

p=0.0000

p<0.01
Reject H0.

Accept H1

Conclusion:

there is sufficient evidence at 1% level of significance to conclude linear effect of the number of invoices on the completion time.

(d) Determine the coefficient of determination, ?2, and interpret its meaning (e

r sq=0.8623

86.23% variation in invoices is explained by model.

Good model

explained variance=86.23%

Unexplained variance=100-86.23=13.77%

e) Determine the standard error of estimate, and interpret its meaning

Se=0.3673572

(f) At the level ? = 0.01, conduct a t test about the population slope ?1

H0: no linear relationship exists between invoices and time

H0:?1=0

H1: linear relationship exists between invoices and time

H1:?1 not = 0

t=13.70599

p=1.8*106-14=0.0000

p<0.01

Reject H0.

Accept H1.

there is sufficient evidence at 1% level of significance to conclude that

linear relationship exists between invoices and time