You are to build a spreadsheet that will analyze delivery data. You want to find the actual activity cost, i.e., cost per delivery, for each driver.

1. If you analyze data in business, you are likely to have so many records of data that you simply cannot calculate everything manually. You need to design an Excel spreadsheet to automate the calculations. Before you create calculations in Excel, however, think about what analytics building blocks you need to help you solve your problem. List the major analytical steps you need to perform to solve this problem.

   Open the Excel file RawActivity_CaseData.xlsx.. Save the file as Lastname_Firstname_RawActivitySolution.xlsx (where Lastname is your actual last name and Firstname is your actual first name). Complete the following steps.

2. Create a new Excel sheet in which you will find the delivery cost for each delivery.

   1. Create a new sheet that has information for each delivery. Name this sheet lookup.

   2. In this lookup sheet, look up the hourly rate for the driver for each delivery. Do not enter this information manually! Use the VLOOKUP function.

   3. For each delivery, calculate the delivery cost as an hourly rate for the driver multiplied by time (hours).

3. Create a new Excel sheet that aggregates the data contained in the lookup sheet to show each drivers activity analysis.

   1. Create a pivot table in a new sheet; name this new sheet pivot table.

   2. In this pivot table sheet, filter out the data associated with the Unknown drivers.

   3. Find the number of deliveries for each driver.

   4. Find the total cost of deliveries for each driver.

   5. Find the average cost per delivery for each driver.

4. Evaluate your results. Are these the correct amounts? How can you tell if they are correct or not?

Date	Delivery ID	Driver	Time (Hours)
1/1	1	Frank	2.5
1/1	2	Deanna	2.5
1/1	3	Ben	1.5
1/1	4	George	1
1/1	5	Caroline	2
1/1	6	Irene	2.5
1/2	7	Unknown	1.5
1/2	8	George	2
1/2	9	Deanna	1.5
1/2	10	Unknown	2.5
1/2	11	Jenny	2
1/3	12	Ben	1
1/3	13	Ben	2.5
1/3	14	Deanna	2.5
1/3	15	Caroline	2
1/3	16	Ben	2

Name	Hourly rate
Adam	$ 17.00
Ben	$ 19.00
Caroline	$ 30.00
Deanna	$ 29.00
Elizabeth	$ 19.00
Frank	$ 26.00
George	$ 15.00
Hannah	$ 19.00
Irene	$ 17.00
Jenny	$ 26.00
Unknown	$ 20.00