Scenario

You work at a car dealership and have volunteered to use Excel help make it easier for people to see information that's important to them. Many customers want to understand the fuel efficiency (abbreviated as FE) of the cars they consider purchasing. Please create two summary sheets, following the minimum requirements below.

Requirements

On the Car_Fuel_Efficiencysheet, create a new column that calculates the rank for the "Annual Fuel Cost ‐ Conventional Fuel" column, with the lowest cost (smallest number) ranked as #1. If there is a tie, each item should be given an equal ranking.

Across all of your worksheets, remember to use the Standards of Professionalism(Links to an external site.), including naming the sheets in your workbook so they are more recognizable for the users, and ordering sheets appropriately. Please review the standards and the rubric carefully.

Summary sheet 1: Car fuel efficiency

Build a summary sheet (calledEfficiency summary) that will allow users to quickly pick two vehicles from two different drop‐down lists (data validation), using the column labeled "Car Name (for lookup)" from the Car_Fuel_Efficiencysheet to populate the lists. This should be the landing sheet when users open the workbook.

The summary sheet should display fuel information (for each of the selected cars) from these four columns using lookup functions:

• The ranking of the Annual Fuel Cost ‐ Conventional Fuel

• City FE ‐ Conventional Fuel

• Highway FE ‐ Conventional Fuel

• Combined FE ‐ Conventional Fuel

Have the cell with the "Combined FE ‐ Conventional Fuel" number shadedgreenif the car's "Combined FE ‐ Conventional Fuel" is above 30 andredif the car's "Combined FE ‐ Conventional Fuel" is below 20.

Summary sheet 2: Sales team performance

Data to be used on your summary sheet is on the sheet calledSales_Team_Numbers. It shows the total sales of each salesperson by month for the last year.

The summary sheet (calledSales performance) should allow the user to select two different salespeople (from two different drop‐down lists) and compare their total sales for the last year side by side. If you want to challenge yourself, a chart would make the comparison easier!

Data