Project | Accounting homework help

FinalProject.docx

Final Case Project – Big Superstores

Student Case Handout – 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.

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.

1. Date: The calendar date of the sale

1. Year: Either “current” or “prior”

1. Customer number: An alphanumeric field containing a unique number for each customer

1. Region: Central, East, North, South, West

1. Customer type: Individual or Business

1. Division: The segments of our company and include Books, Electronics, or Office Supplies

1. Product: Within each division group, there are 3 – 5 products

1. Division product combo: This field combines Division and Product.

1. Distribution channel: How the product was sold: Catalog, Store, Website

1. 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.

1. Sales revenue: The total amount of the sale

1. 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.

1. Total invoice: Sales revenue + Sales tax amount

1. Invoice Status: This field indicates if the customer has paid the invoice (paid) or not (open).

General instructions for the case

You will be using Excel to build an interactive dashboard to answer a set of multiple-choice questions in eLearning as well as 2 short answer questions.

You will start with an Excel file containing a set of sales transactions and then modify the data file as instructed. Each section needs to be completed in its entirety before proceeding to the next section. You will have to submit your excel file to eLearning when you are done. This will be requested as one of your questions.

Be sure to save your Excel file frequently.

Step 1 - Prepare the Sales Data worksheet.

In this step, you will open the Excel file, establish the table, and find and fix any errors in the data.

· Open your assigned Excel file.

· Convert the data on the worksheet to a table called RawData and rename the worksheet Sales Data .

Now, it’s time to analyze your data for issues. It is your job to determine what the errors are and to correct them. Review the data provided and the description of the data in the data table. Hint: the Division, Product, and Sales Tax Amount columns should be your focus. If you find an error, then you need to correct the data. You will need to use the other data in the worksheet and the data dictionary to help determine data errors. Be sure to save your file with the corrected data.

HINT: Make a note of what you had to fix and how you found the errors.

Step 2- Working with Divisions and Regions.

· Create a pivot table using the RawData table. Place the pivot table in a new worksheet called Div_Region. The pivot table should display the Division in the rows of the pivot table. The columns of the pivot table should display the total sales revenue for each region and the percentage of that region’s total sales revenue of the grand total sale revenue. Do not separate this by year.

For example: The grand total of all sales revenue for all divisions and regions is $5000. Division A-South Region has $500 sales revenue for the two years. Division A would have 10% of the sales revenue.

· Add a slicer to this PivotTable for Year

Points will be awarded not only for the correct data but also for proper formatting of the values presented, as well as custom labels of the field. Include Grand Totals for rows and columns. Use common formatting standards and common sense labels.

Step 3 – Working with Products

Insert a column in your RawData table (at the far right) to calculate gross profit in dollars for each sale. (HINT: Think about how to treat sales tax – is this revenue?) HINT: Refresh your table and be sure the RawData table includes this new column.

Create a new pivot table from the RawData. Place this PivotTable in a new worksheet called Products.

Use the data to prepare an analysis of the total product sales revenue for the current year only for each product. Show the sales revenue in order from greatest to least. Then insert a calculated field in the pivot table that calculates the gross profit % for each product. This must be a calculated field on your pivot table and cannot be a column in your RawData. HINT: You will use the gross profit dollars column you added above. Points will be awarded not only for the correct data and formula but also for proper formatting of the values presented, as well as custom labels of the field. Include Grand Totals for rows.

Step 4 – Forecasting (SEE NOTE BELOW FOR MAC USERS)

If you are MAC USER – see the note below:

You have been asked to predict the total revenue for the 1st 6 months of 2025 based on the sales revenue of 2024.

You are going to use the Forecast Sheet tool in Excel. Before you can do this, you need to prepare some data. Create a new worksheet called Forecast Sheet. On this worksheet, prepare a table with the Total Sales Revenue by month for the current year. HINTS: You must set the table up with proper dates (Use the last day of the month) and other proper labels. See the example below.

For example: (Not the actual numbers)

Month	Sample Total Revenue
1/31/24	$10,234
2/28/24	$9,456
3/31/24	$11,435
4/30/24	$50,111
5/31/24	$45,456
6/30/24	$14,566
….. continue for the rest of the year

Once your data is ready, select the data and select Forecast Sheet from the Data tab of your Ribbon. You can leave all defaults as is on the Forecast Sheet tool. Be sure to set the Forecast end to 6/30/2025. Your result will be a new forecast for the 1st 6 months of 2025.

HINT: If you get a message about dates not being consistent, use the last day of each month as your date.

Place this forecast chart on your dashboard. (Element #3 below). NOTE: If you are a MAC user, you will need to create the table of sales revenue data, and then make a line chart that shows all revenue for 2024 by month and add a linear trendline. Format the trendline to forecast forward for 6 months.

Creating the Dashboard

Create a new worksheet titled Dashboard and make this the first sheet in your workbook. This is where you will combine several elements in a visual representation. There will be 4 charts on your dashboard. The elements should be arranged in a visually appealing manner and all charts should be able to be seen at the same time when the workbook is opened. Please use a consistent color scheme with all elements. Use titles and labels where appropriate along with reasonable axes. Grades will be assigned based on your proper choices.

For some of the elements, you are not creating a PivotChart and may have to create a separate table or calculation to prepare the element. You can create a new worksheet called Other Calcs to help you create that data.

Element #1: Total Sales Revenue by Region for the Current Year

Create a static (not a pivot chart) bar chart that presents Total Sales Revenue by Sales Region for the Current year.

Element #2: Proportion of Sales Revenue by Product

On your “Other Calcs” worksheet create a new pivot table from the Raw Data Table. Use Product in the rows and present the total sales revenue for each product in the current year as a % of the total sales revenue. Sort it from largest to smallest percentage. To make it cleaner, only show those products whose sales are 15% or more of the total in the current year and group the other products together to represent “ all other” products. Make a chart of this and put it on your dashboard. Be sure your final chart does not show the details of what is in “All other”. You must determine the proper type of chart to show.

Make a note of how many products had to be combined in the “all other” group.

Element #3: Forecast

The 3rd element of the analysis is the forecast of total sales revenue prepared above. NOTE: If you are a MAC user, you will have a line chart with a trendline.

Element #4: Your choice.

Using the RawData provided, you select the 4th chart element to be presented that includes an analysis of something related to either the distribution channel or the payment status. The analysis should be reasonable, and you will be explaining it and your reasoning as part of your assignment. I should be able to understand your analysis based on your chart elements (title, axis, labels, etc.)

ACCT 3312 –Fall 2024 1 Final Project

FinalProject.docx

Final Case Project – Big Superstores

Student Case Handout – 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.

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.

1. Date: The calendar date of the sale

1. Year: Either “current” or “prior”

1. Customer number: An alphanumeric field containing a unique number for each customer

1. Region: Central, East, North, South, West

1. Customer type: Individual or Business

1. Division: The segments of our company and include Books, Electronics, or Office Supplies

1. Product: Within each division group, there are 3 – 5 products

1. Division product combo: This field combines Division and Product.

1. Distribution channel: How the product was sold: Catalog, Store, Website

1. 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.

1. Sales revenue: The total amount of the sale

1. 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.

1. Total invoice: Sales revenue + Sales tax amount

1. Invoice Status: This field indicates if the customer has paid the invoice (paid) or not (open).

General instructions for the case

You will be using Excel to build an interactive dashboard to answer a set of multiple-choice questions in eLearning as well as 2 short answer questions.

You will start with an Excel file containing a set of sales transactions and then modify the data file as instructed. Each section needs to be completed in its entirety before proceeding to the next section. You will have to submit your excel file to eLearning when you are done. This will be requested as one of your questions.

Be sure to save your Excel file frequently.

Step 1 - Prepare the Sales Data worksheet.

In this step, you will open the Excel file, establish the table, and find and fix any errors in the data.

· Open your assigned Excel file.

· Convert the data on the worksheet to a table called RawData and rename the worksheet Sales Data .

Now, it’s time to analyze your data for issues. It is your job to determine what the errors are and to correct them. Review the data provided and the description of the data in the data table. Hint: the Division, Product, and Sales Tax Amount columns should be your focus. If you find an error, then you need to correct the data. You will need to use the other data in the worksheet and the data dictionary to help determine data errors. Be sure to save your file with the corrected data.

HINT: Make a note of what you had to fix and how you found the errors.

Step 2- Working with Divisions and Regions.

· Create a pivot table using the RawData table. Place the pivot table in a new worksheet called Div_Region. The pivot table should display the Division in the rows of the pivot table. The columns of the pivot table should display the total sales revenue for each region and the percentage of that region’s total sales revenue of the grand total sale revenue. Do not separate this by year.

For example: The grand total of all sales revenue for all divisions and regions is $5000. Division A-South Region has $500 sales revenue for the two years. Division A would have 10% of the sales revenue.

· Add a slicer to this PivotTable for Year

Points will be awarded not only for the correct data but also for proper formatting of the values presented, as well as custom labels of the field. Include Grand Totals for rows and columns. Use common formatting standards and common sense labels.

Step 3 – Working with Products

Insert a column in your RawData table (at the far right) to calculate gross profit in dollars for each sale. (HINT: Think about how to treat sales tax – is this revenue?) HINT: Refresh your table and be sure the RawData table includes this new column.

Create a new pivot table from the RawData. Place this PivotTable in a new worksheet called Products.

Use the data to prepare an analysis of the total product sales revenue for the current year only for each product. Show the sales revenue in order from greatest to least. Then insert a calculated field in the pivot table that calculates the gross profit % for each product. This must be a calculated field on your pivot table and cannot be a column in your RawData. HINT: You will use the gross profit dollars column you added above. Points will be awarded not only for the correct data and formula but also for proper formatting of the values presented, as well as custom labels of the field. Include Grand Totals for rows.

Step 4 – Forecasting (SEE NOTE BELOW FOR MAC USERS)

If you are MAC USER – see the note below:

You have been asked to predict the total revenue for the 1st 6 months of 2025 based on the sales revenue of 2024.

You are going to use the Forecast Sheet tool in Excel. Before you can do this, you need to prepare some data. Create a new worksheet called Forecast Sheet. On this worksheet, prepare a table with the Total Sales Revenue by month for the current year. HINTS: You must set the table up with proper dates (Use the last day of the month) and other proper labels. See the example below.

For example: (Not the actual numbers)

Month	Sample Total Revenue
1/31/24	$10,234
2/28/24	$9,456
3/31/24	$11,435
4/30/24	$50,111
5/31/24	$45,456
6/30/24	$14,566
….. continue for the rest of the year

Once your data is ready, select the data and select Forecast Sheet from the Data tab of your Ribbon. You can leave all defaults as is on the Forecast Sheet tool. Be sure to set the Forecast end to 6/30/2025. Your result will be a new forecast for the 1st 6 months of 2025.

HINT: If you get a message about dates not being consistent, use the last day of each month as your date.

Place this forecast chart on your dashboard. (Element #3 below). NOTE: If you are a MAC user, you will need to create the table of sales revenue data, and then make a line chart that shows all revenue for 2024 by month and add a linear trendline. Format the trendline to forecast forward for 6 months.

Creating the Dashboard

Create a new worksheet titled Dashboard and make this the first sheet in your workbook. This is where you will combine several elements in a visual representation. There will be 4 charts on your dashboard. The elements should be arranged in a visually appealing manner and all charts should be able to be seen at the same time when the workbook is opened. Please use a consistent color scheme with all elements. Use titles and labels where appropriate along with reasonable axes. Grades will be assigned based on your proper choices.

For some of the elements, you are not creating a PivotChart and may have to create a separate table or calculation to prepare the element. You can create a new worksheet called Other Calcs to help you create that data.

Element #1: Total Sales Revenue by Region for the Current Year

Create a static (not a pivot chart) bar chart that presents Total Sales Revenue by Sales Region for the Current year.

Element #2: Proportion of Sales Revenue by Product

On your “Other Calcs” worksheet create a new pivot table from the Raw Data Table. Use Product in the rows and present the total sales revenue for each product in the current year as a % of the total sales revenue. Sort it from largest to smallest percentage. To make it cleaner, only show those products whose sales are 15% or more of the total in the current year and group the other products together to represent “ all other” products. Make a chart of this and put it on your dashboard. Be sure your final chart does not show the details of what is in “All other”. You must determine the proper type of chart to show.

Make a note of how many products had to be combined in the “all other” group.

Element #3: Forecast

The 3rd element of the analysis is the forecast of total sales revenue prepared above. NOTE: If you are a MAC user, you will have a line chart with a trendline.

Element #4: Your choice.

Using the RawData provided, you select the 4th chart element to be presented that includes an analysis of something related to either the distribution channel or the payment status. The analysis should be reasonable, and you will be explaining it and your reasoning as part of your assignment. I should be able to understand your analysis based on your chart elements (title, axis, labels, etc.)

ACCT 3312 –Fall 2024 1 Final Project