Instructions

Overview- The projects in lesson 5 will provide you an opportunity to practice using Excel Pivot Tables. The pivot table feature is very powerful, and very useful, especially for gaining business intelligence. Understanding how to create, and manipulate, Pivot Tables, is an essential skill.

Project 1

In this project, you will explore data regarding truck routes/deliveries. You are a business analyst for a logistics company, and have been asked to produce reports an upcoming meeting.

1. Open the PivotTable - Truck Route.xlsx file.

2. Review the data to gain an understanding of what is provided in the spreadsheet.

a. Your manager has asked you to produce three pivot tables, showing the following summaries:

i. Total net revenue, by destination, for each year. Your manager also wants to be able to change the data shown using different truck sizes.

ii. Total maintenance hours, by truck size, for each year.

iii. Total driver hours, by destination, for each year. Your manager also wants to be able to change the data shown using different truck sizes.

b. Each pivot table should be created on its own worksheet.

3. Saves the file as Project 5-1_YourLastName.

Project 2

For this project, imagine you are working as a Business Analyst for a Department Store. You have been provided data for the past 5 month's sales (Nov, Dec, Jan, Feb, Mar), and have been asked to provide useful business intelligence, that can be used to improve decision making.

1. Open the Department Sales.xlsx file.

2. Review the data to gain an understanding of what is provided in the spreadsheet.

3. You have been asked to provide two separate BI reports, from this data. You realize Pivot tables would be a useful method for showing this data, and decide to create the following Pivot tables:

a. Total sales value, by department, for each month.

b. Total number of items sold, by department, for each month. You also include the ability to change the data by year.

c. Each pivot table should be created on its own worksheet.

4. Save the file as project 5-2_YourLastName.