Recently your car has begun to have some maintenance issues, so you decide to do some initial research into buying a replacement car. You find a great website that has pricing and efficiency data
for a large variety of used vehicles made in the years 2013-2017. You are wondering which auto
manufacturer offers the lowest maintenance costs for 2017 vehicles. Follow the steps to create a
more refined way to view and sort through the data you found on a car-pricing website in order to
make a buying decision.
Once your data formatted in a uniform manner and is easy to read, turn the data into a
table in a new tab.
2
Add a new column named Lowest to the right of the 2017 column that calculates the
lowest price for each model across the five years.
4
3
In a new worksheet Pivot Table, create a Pivot Table report to analyze the lowest prices
of each manufacturer and model by year.
4
Properly format values and column headings if they require adjustment.
5
5
You recently read an article that said cars with an efficiency rating of 3 are the most
cost efficient (have the lowest maintenance costs) in the long run. Adjust your report so
that you are only shown totals for cars with this rating. (Hint: add a report filter)
Sort the most recent data to determine which model/manufacturer is the best to start
with in order to keep maintenance costs low. Highlight your answer.
6
7
Now that you know which manufacturer to start with, go back to the Data tab and copy
the data from the best manufacturer into a new tab.
8
Create a new pivot table with the most recent cost data and determine which model of
car you should consider buying to meet your objective.
9
9
Highlight your result
10