please help me with specific excel instructions and answers:

Week 6 Case Study (Case Study #5)^[i]

You continue to work with the general manager by trying to find additional variables to add to the model to improve predictions. You discover that some locations are close to an interstate highway whereas others are not. The general manager also tells you that he believes that the shops are busiest in the winter. You discover that half of the locations reported their data from a winter month. You decide to add the two new dummy variables for each location based on this information. You will use the Access and Winter worksheet in the QuickFix Vehicles Case Study Data.xlsx workbook for this case study. Using the additional variables, you decide to perform the following steps.

1. Multiple Regression with Dummy Variables
   1. Run a multiple regression using the Bays, Population, Access, and Winter variables. Label your results in an Excel workbook using the prompt number.
   2. Write the regression equation for the Bays, Population, Access, and Winter model using the variable names, intercept coefficient, and slope coefficients from the regression output. Write your answer in the box below.

1. Interpret the slope coefficients for the model. Write your answer in the box below.

1. Is there individual significance for each variable for the Bays, Population, Access, and Winter model assuming alpha = 0.05? Write your answer in the box below.

1. Multiple Regression with an Interaction Variable
   1. Create an interaction variable using Population and Access (Population X Access). Label your results in an Excel workbook using the prompt number.
   2. Run a multiple regression using the Bays, Population, Access, Winter, and Population X Access variables. Label your results in an Excel workbook using the prompt number.
   3. Write the regression equation for the Bays, Population, Access, Winter, and Population X Access model using the variable names, intercept coefficient, and slope coefficients from the regression output. Write your answer in the box below.

1. Is there individual significance for each variable for the Bays, Population, Access, Winter, and Population X Access model assuming alpha = 0.05? Write your answer in the box below.

1. Multiple Regression with an Interaction Variable Removing the Population Variable
   1. Since the Population variable is not individually significant, run a model using the Bays, Access, Winter, and Population X Access variables. Label your results in an Excel workbook using the prompt number.
   2. Write the regression equation for the Bays, Access, Winter, and Population X Access model using the variable names, intercept coefficient, and slope coefficients from the regression output. Write your answer in the box below.

1. Is there individual significance for each variable for the Bays, Access, Winter, and Population X Accessmodel assuming alpha = 0.05? Write your answer in the box below.

1. Select the best fitting model from Case Study #5 and provide an explanation of how you reached your conclusion including the measure of goodness-of-fit that you used. Write your answer in the box below.

1. Indicate the percent of variation in Vehicles Served that is explained by the explanatory variables in overall best fitting model. Write your answer in the box below.

1. Predict the number of vehicles served for a location where Bays is 4 and Population is 50,000 with convenient interstate access (Access = 1) in the winter (Winter = 1) using the Bays, Access, Winter, and Population X Access model. Remember that the data used population in thousands to develop the model so you will need to use 50 instead of 50,000 in the equation to calculate the predicted value. Write your answer in the box below.

1. Modified Multiple Regression for Interval Estimation.
   1. Run a modified multiple regression model using Bays, Access, Winter, and Population X Access to construct a confidence interval and prediction interval for a location where Bays is 4 and Population is 50,000 with convenient interstate access (Access = 1) in the winter (Winter = 1) using the Bays, Access, Winter, and Population X Access model. Remember that the data uses population in thousands to develop the model so you will need to use 50 instead of 50,000 in the model. Label your results in an Excel workbook using the prompt number.
   2. Using the modified multiple regression model, construct the 95% confidence interval for the mean expected number of vehicles served for a location where Bays is 4 and Population is 50,000 with convenient interstate access (Access = 1) in the winter (Winter = 1). You can use the interval estimation tool provided in Canvas to assist with calculation of the interval. Label your results in an Excel workbook using the prompt number. Also, write your answer in the box below.

1. Using the modified multiple regression model, construct the 95% prediction interval for the expected number of vehicles served for a location where Bays is 4 and Population is 50,000 with convenient interstate access (Access = 1) in the winter (Winter = 1). You can use the interval estimation tool provided in Canvas to assist with calculation of the interval. Label your results in an Excel workbook using the prompt number. Also, write your answer in the box below.

Vehicles Served	Bays	Population in Thousands
200	3	15
351	3	22
382	3	35
294	3	52
223	3	47
309	3	26
302	3	45
369	3	25
312	3	16
289	3	10
304	3	11
233	3	15
313	3	48
285	3	51
298	3	16
224	3	34
403	3	22
282	3	12
299	3	36
200	3	15
366	3	22
385	3	35
291	3	52
238	3	47
308	3	26
289	3	45
368	3	25
312	3	16
292	3	10
306	3	11
226	3	15
301	3	48
278	3	51
283	3	16
233	3	34
404	3	22
278	3	12
301	3	36
214	4	30
250	4	37
288	4	42
352	4	45
345	4	48
410	4	62
259	4	63
331	4	54
401	4	29
425	4	37
428	4	58
407	4	19
340	4	50
340	4	38
328	4	41
427	4	51
330	4	29
410	4	42
339	4	57
427	4	60
403	4	24
216	4	30
254	4	37
289	4	42
359	4	45
347	4	48
399	4	62
245	4	63
316	4	54
394	4	29
421	4	37
438	4	58
410	4	19
339	4	50
355	4	38
314	4	41
433	4	51
315	4	29
396	4	42
332	4	57
437	4	60
392	4	24
325	5	25
317	5	29
344	5	36
376	5	39
369	5	44
494	5	72
377	5	26
273	5	66
273	5	63
436	5	25
377	5	34
358	5	65
355	5	32
370	5	71
357	5	69
357	5	25
353	5	27
293	5	35
366	5	28
373	5	62
457	5	42
317	5	25
316	5	29
345	5	36
379	5	39
376	5	44
498	5	72
369	5	26
287	5	66
284	5	63
440	5	25
372	5	34
373	5	65
366	5	32
368	5	71
346	5	69
359	5	25
356	5	27
282	5	35
363	5	28
372	5	62
448	5	42
318	6	49
354	6	54
512	6	77
464	6	74
402	6	50
468	6	66
485	6	64
400	6	47
380	6	57
397	6	38
394	6	37
321	6	40
395	6	44
378	6	76
459	6	62
392	6	36
393	6	36
380	6	60
397	6	44
318	6	49
363	6	54
513	6	77
453	6	74
387	6	50
480	6	66
475	6	64
391	6	47
374	6	57
382	6	38
380	6	37
323	6	40
389	6	44
382	6	76
463	6	62
394	6	36
403	6	36
374	6	60
385	6	44
495	7	56
325	7	57
509	7	93
491	7	86
520	7	57
336	7	79
328	7	86
416	7	85
508	7	67
332	7	46
432	7	84
430	7	63
411	7	51
356	7	72
503	7	81
416	7	74
335	7	45
408	7	46
418	7	45
416	7	58
509	7	56
330	7	57
523	7	93
506	7	86
535	7	57
333	7	79
318	7	86
412	7	85
518	7	67
330	7	46
446	7	84
432	7	63
420	7	51
347	7	72
488	7	81
421	7	74
350	7	45
414	7	46
416	7	45
430	7	58