Course: 202401_1034-BA_216_02-FIRST YEAR ACCO..... Chapter 23 Data Analysis C CengageNOWv2 | Online teaching and learning resource.... 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. ChatGPT Download spreadsheet RawActivity CaseData-642120.xlsx 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 adc 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 det 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 sprea 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: Playground - Op 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.

Activity Raw Data Analysis Using Excel Completing this activify will hele you learn to: 1. analyze raw activiny data to find the activity rate. 2. create Excel calculatiens by referencing data in anoser sheret. 2. create an Gicel table that combines information from two tables using lookup functions (Moocup). 4. create phivot tables for use in apgregating data. 5. onalye data by uning ticen with pivot tabies. Mequlred! X 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). Fal in the table below for the first ten records. 2. Create a new Excel sheet that eggregates 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 toble." b. In this plyot table sheet, fiter out the data associated with the "Unknown" drivers. 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, fitter 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