Application Problem #1

For this application, you will be working with an Excel table of vehicles that could be owned and used by a typical university. The Excel table provides data about each vehicle, as well as, the department using the vehicle. You will use the data and sort it into more readable lists, as well as summarize the data using PivotTables and PiviotCharts.

1. Open the file Vehicles and review the Excel list. Save the file as Exam#2Vehicle_your initials.
2. Complete the Documentation Sheet with your name and date, using a date function that will return the current date each time the worksheet is opened.
3. Convert the data in the Vehicles worksheet into a structured table. Choose a style of your choice to format the table - do not use the default style. You will use this same style to format all of the worksheets.
4. Name the structure table - VehiclesTbl
5. Add two records to the table:
6. • ID - 152
   • Year - 2018
   • Make - Dodge
   • Type - Van
   • Odometer - 37628
   • Department Assigned - Property
   • Price - 20347
   • Maintenance - 700

1. • ID - 130
   • Year - 2020
   • Make - GMC
   • Type - Pickup
   • Odometer - 10215
   • Department Assigned - Dining Services
   • Price - 20743
   • Maintenance - 1099
2. Sort the VehiclesTble in ascending order by the vehicle Make.
3. Copy the Vehicles worksheet and name the new sheet Vehicles by Department.
4. Sort the VehiclesTble in the Vehicles by Department worksheet in ascending order by Department Assigned and then in ascending order year. (HINT: Athletics should be at the top of the list.) Add a total row for the Maintenance field. Rename the row Total Maintenance Costs
5. Using Conditional Formatting, highlight the vehicles that have a maintenance cost greater than $3000
6. Copy the VehicleTbl in the Vehicles Worksheet and name the new sheet Filtered by Department. In this worksheet, display only the vehicles assigned to Police and Security and display only those vehicles with maintenance cost greater than or equal to $1500.
7. Copy the Vehicles by Department worksheet and name the new sheet Vehicle Amounts.
8. In the Vehicle Amounts worksheet, add subtotals for Price and Maintenance of the vehicles by Department Assigned. (HINT: what do you need to do to the VehiclesTbl before you can use the Subtotal feature?)
9. Using the VehiclesTbl in the Vehicles worksheet, develop a PivotTable in a separate worksheet. Name the worksheet, Maintenance Costs. Summarize the total maintenance costs by the vehicle Make field and each type of vehicle. Make would be the Rows and Type the columns. Add a filter to show the data by Department Assigned. Format the PivotTable using a similar style as the worksheets.
10. Add a PivotChart below the PivotTable. Develop Bar Chart of the maintenance costs by the four vehicle types. Add a chart title - Break Down of Maintenance Costs. Add a data table below the x-axis (see Chart Elements options).
11. Using the VehiclesTble in the Vehicles worksheet, develop a second PivotTable in a separate worksheet. Name the worksheet, Department Maintenance Costs. Develop PivotTable that shows the total maintenance cost by vehicle Make for each department. Add a Slicer to filter the table by Type; position the Slicer next to the table and size accordingly. Format the PivotTable and Slicer using a similar style as the worksheets.
12. Add a PivotChart below the PivotTable of a bar chart showing all of the maintenance costs per type by department. Add a chart title - Maintenance Costs by Department. If necessary create a legend for the make and ensure the department names are on the x-axis.
13. Answer the following questions in the Department Maintenance Cost Worksheet. Merge several cells beside the PivotTable to enter your answers.
    1. Review the two PivotTables developed and the Vehicle Amounts worksheet, what conclusion can be drawn for which department has the highest vehicle maintenance costs? Which make of vehicle has the highest maintenance costs? If you were the manager in charge of vehicles, which vehicles in which department would you review to see if they should be sold to avoid larger maintenance costs?
14. Completing Appendix B, introduced you to another way to filter the data using the Criteria Section. What advantage does using the Criteria Section for filtering data provide over using the table filters as you completed in step 10 above? Answer this question in a merged section beside the filter list in the Filtered by Department worksheet.
15. Also in Appendix B, you worked with several of the database functions to create a summary of the data. Explain the purpose of using the DAVERAGE function. How could you apply this function to the VehiclesTbl to further assist your decision making in step 17 (you do NOT have to develop the function just explain how you would use it). Answer this question below #18 in the Filtered by Department worksheet.
16. You will now answer questions related to Module 7 -- Insert a new worksheet at the end of the worksheets you created for the Vehicles problem. Name the worksheet Module 7 Questions. Answer the following questions about the features presented in Module 7. Place your answers in a merged cell, starting in cell A1 of the worksheet:
    • What are two advantages of using defined names in a workbook?
    • Why would you want to validate data to be entered into a workbook? Can you prevent incorrect data from being entered? How?
    • Provide two reasons for adding a comment to a worksheet cell.