Link data file: https://drive.google.com/file/d/13f0wiDPNeoVaAtIh_NfqVyDPMOAhJ6Ya/view?usp=sharing

InstaClick Delivery Service

Kathy Roe is the fleet manager for InstaClick Delivery Service. She is responsible for managing the trucks and drivers that make deliveries to grocery stores in Colorado. Kathy wants to analyze shipping data and track the delivery times and accumulated mileage of the company drivers. Kathy has compiled a shipping log detailing the routes of four drivers from the past three months and needs your help creating the PivotTables and PivotCharts to analyze the data.

Complete the following:

1. Start Excel and open theInstaClick.xlsxdatafile. Save the workbook asxx_Reports.xlsx(replace "xx" with your initials).

2. In the Documentation sheet, enter your name and the date.

3. TheDriving Logworksheet contains driving records of four InstaClick Delivery Service drivers. Each record in the Log table contains a separate segment of a daily trip by a driver and provides the date and times for each segment. In theDriving Timefield, determine the time required for each segment in hours by calculating the difference between the End Time field and the Start Time field and multiplying that difference by 24. (Hint:See the Terms and Definitions worksheet for help in constructing the function.)

4. In theTime Goalfield of the Log table, you will display the time that it should take each driver to drive between cities (as determined by the company). To display that time value, use an INDEX MATCH search. For the INDEX function, use the CityTimes table in the Travel Times worksheet containing the recommended travel time between cities as the lookup array. For the row number, use the MATCH function with Start City field as the lookup value, the Cities table as the lookup array, and 0 for the match type (to do an exact match). For the column number, use the MATCH function with End City field as the lookup value, Cities table as the lookup array, and 0 for the match type. (Hint:You can copy this formula from the Terms and Definitions worksheet.)

5. In theTime Overfield, calculate the amount by which the driver was over the recommended travel time in minutes by subtracting the Time Goal field from the Driving Time field and then multiplying that difference by 60. (Hint:See the Terms and Definitions worksheet for help in constructing the function.)

6. In theMileagefield, use an INDEX MATCH function to calculate the mileage for each segment driven. Refer to Step 4 as you create the function and use the CityDistances table in the Travel Distances worksheet containing the distance between pairs of cities as the lookup array for the INDEX function.(Hint: You can copy this formula from the Terms and Definitions worksheet.)

7. In theDriving Summary worksheet, Kathy entered a formula in cell C10 to calculate the average daily mileage for the first driver. Copy the formula in cell C10 into the range C11:C13 to calculate the average daily mileage for the other three drivers.

8. In cell C16 of the Driving Summary worksheet, Kathy has entered a formula to calculate the average daily driving time for the first driver. Copy the formula in cell C16 into the range C17:C19 to calculate the average daily driving times for the other drivers.

9. Kathy wants to track the number of minutes the drivers are over their scheduled driving times. Use data in theDriving Log worksheetto create a PivotTable in the Driver Times worksheet in cell B4. Rename the PivotTable asDriving Times Pivot. Place the Time Over field in the Values area, place the Date field in the Rows area, and then remove the Month field from the Rows area.

10. Next, create a PivotChart from the Driving Times Pivot table. Move the PivotChart to the Driving Summary worksheet placing it in the range E9:G19. Change the chart type to a line chart. Remove the field buttons and the legend from the chart. Change the chart title toMinutes Over Time Goal.

11. Kathy wants to track the daily distance driven by the drivers. In theDriving Log worksheet, create another PivotTable from the Log table, placing them in the Driver Miles worksheet in cell B4. Rename the PivotTable usingDriving Miles Pivotas the name. Place the Mileage field in the Values area, place the Date field in the Rows area, and remove the Month field.

12. Again, create a PivotChart from the Driving Miles Pivot table. Move the PivotChart to the Driving Summary worksheet in the range I9:K19. Change the chart type to a line chart. Remove the field buttons and the chart legend. Change the title toDistance in Miles.

13. Insert a slicer for the Driver field in the range E4:K7. Display the slicer buttons in four columns and connect the slicer to both PivotTables.

14. Click each slicer button to verify that you can view time and distance charts for each individual driver over the past three months.

15. Driver D600-622 is new to the job, and Kathy wants to know whether that driver's travel times have improved in the last three months. Use the slicer to display the charts for only that driver displaying the general trend of the minutes over the company's time goals each day.

16. Save and submit the workbook as directed by your instructor.