The demand for college graduates with data analytics skills has exploded, while the tools and techniques are continuing to evolve and change at a rapid pace. This case illustrates how an interactive dashboard can be built in Excel to analyze data. As you analyze this case, you will be learning how to drill-down into a companys sales data to gain a deeper understanding of the companys sales and how this information can be used for financial reporting and decision-making. You will be using the interactive dashboard to answer several questions about a companys sales.
Background
The Jubilee Superstores data set is based on retail sales transactions. The data set contains more than 1,000 sales transaction records from 2020 through 2021.Each sales record contains information on product, region, division, and more. All data and names are fictitious.
Part 1 – Calculations Complete the assigned case in Pearson My Lab Accounting using the detailed instructions and resources.
Part 2 Analysis and Excel Workbook
Answer the case questions in a Word Document.
1. When recording a sales transaction, would Jubilee Superstores use the Sales revenue field or the Total invoice field for total revenue? How would Sales tax amount be handled when recording transactions? (i.e., which type of account would it be recorded to? Would it be classified as short or long term?)
2. Assume all Jubilee Superstores sales are on account. For each sales transaction, would the Sales revenue amount or the Total invoice amount be recorded as the debit (or increase) to Accounts receivable by Jubilee Superstores?
3. What is the total Books sales revenue? Adapted from case: Jubilee Superstores: Constructing an Interactive Excel Dashboard by Dr. Wendy Tietz Copyright 2021 Wendy M. Tietz, LLC
4. Which region had the most Office supplies sales revenue?
5. What are the trends in sales revenue for each of the three product divisions in the East? In the North?
6. What are the top five products by sales revenue in the Central region? In the North?
7. How much would Jubilee Superstores have credited to Sales revenue for the East region in 2021?
8. What would be total sales revenue for Jubilee Superstores in 2021? On what financial statement(s) would you find total sales revenue?
9. How could this dashboard help the company in its decision-making? What overall strategy questions could this dashboard address?