Cabinet Company Sales, Cost, and Gross Profit Analysis – Accounting & Finance Assignment Help

Assignment Task

Task 

Data set background
Cabinet Accessories Company (CAC) is a manufacturer and wholesaler of cabinet hardware, including knobs and pulls. Over the past year, the company has experienced a lot of turnover which has resulted in an entirely new upper management team. In order to steer the company in the right direction, they want to get an overall sense of where the company stands financially, and how to maximize profits in the future. Unfortunately, Carl from accounting knows that they have been collecting sales and cost data over a four-year period from 2014 to 2018. However, nothing has been done with this data as of yet. Your task, is to provide management with an insightful summary and analysis of the data. 

Data dictionary

•  customer_number: A random number that is assigned to each customer (retailer), that CAC sells their product to.
•  region: The retailers that CAC sells product to, are located and divided up into two general regions.
•  date_of_sale: The date that the item was purchased by the customer (retailer).
•  item: A specific alphanumeric code that corresponds to each unique item that CAC manufactures and carries in their inventory.
•  brand: CAC manufactures cabinet hardware under two different brand names: Elements and Jeffrey Alexander.
•  collection: Each brand has various collections that are similar in look and quality.
•  description: A brief written description of each item.
•  list_price: The dollar amount that CAC sold the items to the retailers for.
•  cost: The total of all the expenses needed to manufacture each item.
•  quantity_sold: The number of units sold to a retailer. 

There are multiple versions of each data set. The data you download is unlikely to precisely align with these instructions. These instructions are to be used as a general guide to help you work through your specific problem, not solve it. Note that when clicking the ?check figures? button in MyAccountingLab the cells referenced will change depending on how you set up your unique problem. These cells will rarely be in the same spot for you and is not an indication that you did something wrong.

Part One: Filling in the Missing Data in the Dataset
Carl has successfully uploaded the data file with the 2014-2018 sales and cost data. However, he already notices that in order for the data to be analyzed properly, there are some important calculations that are missing. Specifically, he wants to know the sales revenue, total cost and gross profit of each sales record (row). However, Carl is quite busy and has enlisted your help to help him update the data file with the missing calculations.

Requirement 1: Calculate Sales Revenue, Cost And Gross Profit

Part Two: Cleaning the Data
Now that you have filled in the missing calculations, it is important to check to see if the dataset contains any errors. A quick and easy way to do this is by using a Pivot Table.

Requirement 2: Create a pivot table that shows sales revenue by region for each of the four years and quarters.

Part Three: Analyzing the Data
Requirement 2: Based on the pivot table you just created, create a pivot chart that shows the sales revenue trend by region over the four-year
period.

Requirement 3: Create a pivot table that shows sales revenue, total cost, and gross profit for each year.

Requirement 4: Create a pivot table to answer the question was the most profitable brand in each year, as measured by gross profit?

Requirement 5: Within each brand, what was the most profitable collection in 2018, as measured by the gross profit percentage? The least most profitable collection for each brand?

Requirement 6: Create a pivot table to answer the question which region was the most profitable in 2018, as measured by the gross profit percentage?