Scenario

Congratulations! You have now been promoted to office manager at the car dealership you worked for in an earlier project. You have a meeting with the owner and need to be able to show how well the sales team and the different stores are doing.

As you work, use the Standards of Professionalism to clean up the workbook and make it look sharp. This includes labeling and formatting the worksheets appropriately. On all sheets, ensure numbers have the proper format and please check that all columns are wide enough to display the data.

The logic functions we have learned should be helpful. For the dropdown lists, you can get a list of unique salespersons, store names, and car makes using the UNIQUE formula or the Remove Duplicates function.

Requirements

Sales sheet

• Using Excel formulas, create a new column called "New Sales Price" that replaces the last two digits in each row of the "Sales Price" column with your age. For example, if your age is 26 and one of the sales numbers is 27,340 it would become 27,326 in the new column.

• Using Excel formulas, create a new column called "New Salesperson" that replaces all salespeople named "Arthur" with your first name.

New sheet: Dashboard

• Create a drop-down list that includes the names of all fourteen new salespeople. For the salesperson selected in the drop-down list, calculate the following on the dashboard:

o The total of "New Sales Price" for that salesperson

o The average of that salesperson's "New Sales Price"

o The total number of cars sold by that salesperson

• Create another drop-down list that includes the names of all three stores. For the selected store, calculate the following on the dashboard:

o The total of "New Sales Price" for that store o The average of that store's "New Sales Price"

o The total number of cars sold at that store

• The owner wants to quickly find the number of cars purchased by gender for a selected car make (e.g. Toyota). Please create two drop-down lists—"Gender” and “Car Make”—with one cell that shows the count of cars sold for the combination of the selected options (e.g. How many females have purchased a Honda?).

New sheet: Pivot tables

Create the following three pivot tables. For each one, be intentional about which placement for row and column headings makes the data easiest to read.

1. The total "New Sales Price" for each new salesperson and inventory type.

2. The number of cars sold by month and store.

3. The number of buyers (units sold), by gender, for each car make and model.

Data