Reference no: EM133808996

Preparing the Excel File

Overview
This case illustrates how an interactive dashboard can be built in Excel to analyze data. As you analyze this case you will gain a deeper understanding of the company's sales and how this information can be used for decision-making. You will be using the interactive dashboard to answer several questions about a company's sales. Read all the instructions in this document before starting the case. The terms "case" and "project" are used interchangeably throughout this document. Get Help Now!

Background
This data set is based on retail sales transactions for Big Superstores. The data set contains more than 1,000 sales transaction records from 2023 (prior year) through 2024 (current year). Each sales record contains information on product, region, division, and more. All data and names are fictitious. You can assume each transaction listed is an individual sale. All customers have Net30 payment terms, and all transactions are sold on account regardless of distribution channel. All sales are taxable.
Data dictionary
In your data set, the following field names are used along with their description.
Date: The calendar date of the sale
Year: Either "current" or "prior"
Customer number: An alphanumeric field containing a unique number for each customer
Region: Central, East, North, South, West
Customer type: Individual or Business
Division: The segments of our company and include Books, Electronics, or Office Supplies
Product: Within each division group, there are 3 - 5 products
Division product combo: This field combines Division and Product.
Distribution channel: How the product was sold: Catalog, Store, Website
Cost: This is the cost of the item(s) sold on an order. Costs vary by Division; however, the cost ratio is the same for all products in a division.
Sales revenue: The total amount of the sale
Sales tax amount: The sales tax on the sales revenue. All sales are taxable. The Sales tax rate is based on the distribution channel. One rate is used for store sales and second rate is used for website and catalog sales.
Total invoice: Sales revenue + Sales tax amount
Invoice Status: This field indicates if the customer has paid the invoice (paid) or not (open).