“ETL” stands for Extract, Transform, and Load. The ETL process extracts, transforms, and loads data from multiple sources to a data warehouse (single data source) where that data can be used in decision-making. ETL is the foundation for data analytics.

KAT Concession Supply is a fictitious company that supplies food, paper products, and other items to fairs, amusement parks, and other organizations. Information in the KAT Concession Supply data sets includes information about its sales over a three-year period. The data from KAT Concession Supply has errors and irregularities in it and it must be cleaned.

An Excel interactive dashboard will be built after the data cleaning steps are completed. This dashboard contains two visualizations. The data needs to be cleaned before preparing the dashboard; otherwise, the dashboard information will be incorrect.

After completing Parts 1 and 2 for the project, answer the questions that follow the requirements.

Use Microsoft 365 Excel Desktop for Windows or Mac

Microsoft 365 Excel Desktop needs to be used for this interactive Excel dashboard. The project works in Excel for Windows and Excel for Mac. The online version of Excel is not recommended, as it is not full-featured. The project cannot be completed in Google Sheets (that project is different) or in Apple Numbers, due to limitations in those software tools. In addition, the mini version (app) of Excel offered for Chromebooks is not sufficient for the Excel dashboards. 

Before starting the project, students need to update their version of Excel. For instructions on how to update Excel, search for “how to update Microsoft 365 Excel” on the internet.

Tutorial videos

Complete step-by-step instructions are provided for this project. You have the choice of viewing the tutorial videos or downloading a pdf file of the annotated slides. The tutorials for this project have been split into two parts:

1. Part 1: Data cleaning tutorial (video | pdf)
2. Part 2: Dashboard tutorial (video | pdf)

Data set

You can download the data set for this project at this link. Be sure to download the Excel file from Dropbox, rather than simply viewing it. Look for the Download button on the page on Dropbox; usually it is near the upper left corner. Once you download the Excel file, go to Excel and then open the file from within Excel rather than just clicking the file to open it.

Requirements for Part 1: Data cleaning

To follow are the requirements for cleaning the data records in the data set.

1. Open the Excel data file provided.
2. Widen all columns to fit the data.
3. Find and remove duplicate records.
4. Remove spaces and nonprinting characters from the Transaction year (original) column, placing the results in the Transaction year (edited) column.
5. Replace all instances of “coffee” in Column H with “cola.”
6. Change the case of the contents of Warehouse state (original) to uppercase with a formula in the Warehouse state (edited) column.
7. Format Columns AE through AH as currency with zero decimal places. Format Column AI as currency with two decimal places.
8. Save the Excel file with your last name in the beginning of the file name.

Requirements for Part 2: Dashboard

To follow are the requirements for constructing a two-visualization interactive dashboard with the cleaned data set that you saved upon completion of Part 1.

1. Create a two-visualization dashboard that contains a slicer that is based on the field “Transaction year (edited).” Include the following two charts that are connected to the slicer:
   1. Total revenue for edible and nonedible products (column chart)
   1. Total revenue for the snow cone inventory type (horizontal bar chart)
2. Save the Excel file.

Questions

Using the dashboard that you just created, answer the following questions:

1. What is the total revenue for nonedible products for one year prior?
2. What is the total revenue for “Snow Cone Syrup, 32 oz bottle, lime” for two years prior?
3. What is the total revenue for edible products for the current year?
4. What is the total revenue for “Snow Cone Syrup, 32 oz bottle, watermelon” for one year prior?
5. What was the top revenue-generating Snow Cone product for two years prior?
6. Did edible products or nonedible products generate the most revenue in one year prior?

This project was developed by Dr. Wendy Tietz, CPA, CMA, CGMA, CSCA. All rights reserved.