Activity Raw Data Analysis Using Excel Completing this activity will help you learn to: 1. analyze raw activity data to find the activity rate. 2. create Excel calculations by referencing data in another sheet. 3. create an Excel table that combines information from two tables using lookup functions (VLOOKUP). 4. create pivot tables for use in aggregating data. 5. analyze data by using filters with pivot tables. Case scenario: You work at a quick-delivery startup, Exceptional Express. The company employs several delivery drivers whose job it is to pick up orders from customers and then deliver the product to local addresses within three hours. To apply activity-based costing, you need to analyze the delivery data collected by Exceptional Express and find the activity rate for each driver. The actual activity cost of the deliveries you determine will inform the budgeted activity cost for the coming year as well as allow the company to examine cost and activity results associated with the delivery drivers. Your assignment is to create a reusable Excel spreadsheet to analyze the cost and activity systematically because Exceptional Express has many deliveries and you want to be able to reuse and update the spreadsheet in the future. Required: Download spreadsheet RawActivityCaseData-b784fa.xlsx 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. HINT: STEP-BY-STEP WALKTHROUGH 1. Create a new Excel sheet in which you will find the delivery cost for each delivery. a. Create a new sheet that has information for each delivery. Name this sheet "lookup." b. 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. c. For each delivery, calculate the delivery cost as hourly rate for the driver multiplied by time (hours). Fill in the table below for the first ten records. 2. Create a new Excel sheet that aggregates the data contained in the lookup sheet to show each driver's activity analysis. a. Create a pivot table in a new sheet; name this new sheet "pivot table." b. In this pivot table sheet, filter out the data associated with the "Unknown" drivers. c. Find the number of deliveries for each driver. d. Find the total cost of deliveries for each driver. e. Find the average cost per delivery for each driver. Fill in the table below. Round the average cost per delivery to the nearest cent. 3. Explain what can be accomplished by analyzing this cost data. The input in the box below will not be graded, but may be reviewed and considered by your instructor. Activity Raw Data Analysis Using Excel Completing this activity will help you learn to: 1. analyze raw activity data to find the activity rate. 2. create Excel calculations by referencing data in another sheet. 3. create an Excel table that combines information from two tables using lookup functions (VLOOKUP). 4. create pivot tables for use in aggregating data. 5. analyze data by using filters with pivot tables. Case scenario: You work at a quick-delivery startup, Exceptional Express. The company employs several delivery drivers whose job it is to pick up orders from customers and then deliver the product to local addresses within three hours. To apply activity-based costing, you need to analyze the delivery data collected by Exceptional Express and find the activity rate for each driver. The actual activity cost of the deliveries you determine will inform the budgeted activity cost for the coming year as well as allow the company to examine cost and activity results associated with the delivery drivers. Your assignment is to create a reusable Excel spreadsheet to analyze the cost and activity systematically because Exceptional Express has many deliveries and you want to be able to reuse and update the spreadsheet in the future. Required: Download spreadsheet RawActivityCaseData-b784fa.xlsx 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. HINT: STEP-BY-STEP WALKTHROUGH 1. Create a new Excel sheet in which you will find the delivery cost for each delivery. a. Create a new sheet that has information for each delivery. Name this sheet "lookup." b. 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. c. For each delivery, calculate the delivery cost as hourly rate for the driver multiplied by time (hours). Fill in the table below for the first ten records. 2. Create a new Excel sheet that aggregates the data contained in the lookup sheet to show each driver's activity analysis. a. Create a pivot table in a new sheet; name this new sheet "pivot table." b. In this pivot table sheet, filter out the data associated with the "Unknown" drivers. c. Find the number of deliveries for each driver. d. Find the total cost of deliveries for each driver. e. Find the average cost per delivery for each driver. Fill in the table below. Round the average cost per delivery to the nearest cent. 3. Explain what can be accomplished by analyzing this cost data. The input in the box below will not be graded, but may be reviewed and considered by your instructor