Company operates an off-airport parking facility which allows travelers to park their vehicles and transports them to the Airport for their flights. The company incurred the following operating costs related to the number of cars it parked and the number of passengers transported it transferred for the first eight months of 2015:
|
Month
|
Operating Costs
|
Cars
|
Passengers
|
|
January
|
$ 6,170
|
1,800
|
2,300
|
|
February
|
6,830
|
2,100
|
2,600
|
|
March
|
6,280
|
1,990
|
2,350
|
|
April
|
8,600
|
2,640
|
3,450
|
|
May
|
6,720
|
2,050
|
2,550
|
|
June
|
7,930
|
2,250
|
3,100
|
|
July
|
6,900
|
2,360
|
2,800
|
|
August
|
6,830
|
1,960
|
2,650
|
Use the data provided and the directions below to identify the 'best' cost driver.
Open an Excel 2010 file
Set up three worksheets in one Excel 2010 file (.xlsx) with the first sheet for your summary, and the other two worksheets for each cost driver.
Copy, paste, and professionally format the data on worksheets 2 and 3 with the data table on each worksheet containing data for one driver.
Name the worksheet tabs with the respective driver names.
Run a separate linear regression for each driver. Include a line fit plot.
Properly format the axes scales (i.e., $ if applicable), axes labels, chart titles, legend labels, and add a trend line of the expected data. (HINT: Select 'labels' when you run the regression to auto-display axes, chart title, and legend labels.)
Display a trend line for predicted operating costs for each chart.
Reset the x-axis to display from a minimum of 2,000 and a maximum of 3,600 passengers.
Reset the x-axis to display from a minimum of 1,700 and a maximum of 2,800 cars.
Set each worksheet's view to a 90% zoom level.
Adjust the scale to a reasonable relevant range so that the data points are not too clustered. Adjust the chart's width and height as needed to display on the worksheet when opened.
Label sheet 1 as 'Summary' and set the zoom view to 110%. On this sheet, address the following in paragraph form. Using merge and center to place the answer to part A in cells A3 to H3, the answer to part B in cells A5 to H5, and the answer to part C in cells A7 to H7. Limit the column width of these columns to 8 characters (in normal view).
Interpret R2 and address goodness of fit for each driver. Indicate which driver has the best goodness of fit. Concisely justify your choice.
Explain the specific significance of the linear slope for each driver. Indicate which driver has the better slope. Concisely justify your choice.
Interpret economic plausibility for each cost driver. Indicate which cost driver(s) are plausible, if any. Concisely justify your choice. (Do not address, t-tests, or other statistical factors.)
Deliverables:
Due Date: Tuesday, July 22 at 9:00 AM
Be sure you file opens and displays correctly on a PC computer using Excel 2010 prior to submitting.