Tag: Model Answer

Assignment Prompt & Instructions

[>Accounting homework help](https://www.sweetstudy.com/fields/business-finance-accounting)

## **Final Case Project – Big Superstores**

ACCT 3312

## **THIS IS AN INDIVIDUAL ASSIGNMENT.** **ANY INDICATION THAT THE PROJECT WAS DISCUSSED WITH OR WORKED ON WITH ANOTHER STUDENT (CURRENT OR PRIOR SEMESTERS) (ELECTRONICALLY, VIA TEXT/GROUPME, IN-PERSON, ETC,) WILL BE GROUNDS FOR REFERRAL TO THE OFFICE OF COMMUNITY STANDARDS AND CONDUCT.**

## **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 through 2024. 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

2. _Year_: Either “current” or “prior”

3. _Customer_ _number_: An alphanumeric field containing a unique number for each customer

4. _Region_: Central, East, North, South, West

5. _Customer_ _type_: Individual or Business

6. _Division_: The segments of our company and include Books, Electronics, or Office Supplies

7. _Product_: Within each division group, there are 3 – 5 products

8. _Division_ _product_ _combo_: This field combines Division and Product.

9. _Distribution_ _channel_: How the product was sold: Catalog, Store, Website

10. _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.

11. _Sales revenue_: The total amount of the sale

12. _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.

13. _Total invoice_: Sales revenue + Sales tax amount

14. _Invoice Status_: This field indicates if the customer has paid the invoice (paid) or not (open).

STORE 8.25%

WEBSITE 6.5%

### 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.)

– [\
\
2.ACCT3312FinalProjectData1.xlsx](https://www.sweetstudy.com/files/2-acct3312finalprojectdata1-xlsx "2.ACCT3312FinalProjectData1.xlsx")
– [\
\
1.ACCT3312FinalProject-Instructions.docx](https://www.sweetstudy.com/files/1-acct3312finalproject-instructions-docx-10284809 "1.ACCT3312FinalProject-Instructions.docx")

– a year ago