This project uses sales and cost data for a fictitious cabinet hardware company, Cabinet Accessories Company (CAC). In this project, you (the student) will use Excel to create a variety of pivot tables, format the pivot tables, apply filters, and create calculated columns. You also need to cleanse the data set by finding and correcting errors in the data set. In addition, you will create a pivot chart.
Unit 3 Part I Resources
Tutorial
video – Contribution Margin Analysis Tutorial Video
Tutorial
Project Requirements – CAC Project Requirements Word File Download CAC Project Requirements Word Fil
Cabinet Accessory Company: Sales, Cost, and Gross Profit Analysis
(Managerial Accounting)
Introduction
This project uses sales and cost data for a fictitious cabinet hardware company, Cabinet
Accessories Company (CAC.) In this project, the student will use Excel to create a variety of
pivot tables, format the pivot tables, apply filters, and create calculated columns. The student
also needs to cleanse the data set by finding and correcting errors in the data set. In addition, the
student will create a pivot chart.
Data set Background
This dataset represents sales and cost data for a fictitious company, Cabinet Accessory Company
(CAC), covering the four-year period from 2014 to 2018. CAC sells cabinet hardware including
knobs and pulls. In the project, the student is asked to calculate break even from the data. The
project also focuses on the calculation of contribution margin from a set of sales data and how
that sales and cost data can be viewed from the perspective of the region, product line, etc.
Data dictionary
• customer_number: Description
• region: There are eight regions: Central, East coast, Midwest, Northeast, Northwest,
South, West, and International.
• date_of_sale: Description
• item: Description
• brand: Description
• collection: Description
• description: Description
• list_price: Description
• cost: Description
• quantity_sold: Description
Requirements
1. Create three columns in the Data worksheet that calculate sales revenue, variable cost,
and contribution margin for each sales record (each row.) Use the columns already named
in the Data worksheet “sales revenue,” “variable cost,” and “contribution margin,”
respectively. Format numbers in the three columns as Accounting with two decimal
places.
2. Create a pivot table that shows sales revenue by region for each of the four years and
quarters. Correct any errors in the original dataset in the worksheet that are revealed in
this initial pivot table. Once you have corrected the errors in the original data set, refresh
the pivot table to reflect the corrected data. Insert a pivot chart that shows the sales
revenue trend by region over the four-year period. In the pivot chart, the x-axis should be
years and the y-axis should be sales revenue. What trends do you see in the pivot table
and pivot chart?
Copyright ©2018 Wendy M. Tietz, LLC
3. Create a pivot table that shows sales revenue, variable cost, and contribution margin for
each year for each region.
4. Create a pivot table to answer the question “What was the most profitable brand in each
year, as measured by contribution margin?”
5. Create a pivot table to answer the questions: “Within each brand, what was the most
profitable collection in 2018, as measured by the contribution margin ratio? The least
most profitable collection for each brand?” Use the field “years” to filter the data to
include just the year of 2018. You will need to add a calculated field to the pivot table to
calculate the contribution margin ratio. Within each brand, sort the collections by
contribution margin ratio, from the largest to the smallest. Interpret your findings. Are
any collections unprofitable? Which one(s)?
6. Complete the worksheet named “segment margin.” You will calculate each region’s
segment margin and operating income for 2018. (Hint: Link to the totals in the pivot table
you created for Requirement 3.) You will get the fixed costs for calculating the segment
margins from the “fixed cost data” worksheet. Do you think any regions should be
dropped or otherwise restructured? Explain.
Copyright ©2018 Wendy M. Tietz, LLC
customer_number
region
478406 Central
480402 Central
479491 South
479471 Central
478187 South
479491 South
479491 South
479566 Central
479529 Central
479518 South
479529 Central
479518 South
479518 South
480137 Central
478782 Central
478782 Central
477402 South
480137 Central
478406 Central
479529 Central
479471 Central
479566 Central
479529 Central
480137 Central
479491 South
479491 South
480137 Central
476614 South
479471 Central
478303 Centrals
478782 Central
479529 Central
479491 South
479471 Central
478303 Central
479529 Central
date_of_sale item
brand
7/20/2017 Z115-128BNBDL Elements
3/5/2017 885-96PC
Jeffrey Alexander
6/19/2016 Z115-128DBAC Elements
1/2/2018 885-96NI
Jeffrey Alexander
8/12/2015 Z115BNBDL
Elements
10/26/2017 885-305PC
Jeffrey Alexander
6/1/2017 885-96SN
Jeffrey Alexander
12/18/2015 885-96PC
Jeffrey Alexander
6/3/2017 Z115-128DBAC Elements
12/23/2018 885-305SN
Jeffrey Alexander
10/26/2017 Z115-128DBAC Elements
7/6/2017 Z115-128PC
Elements
3/10/2018 Z115-96SN
Elements
2/22/2018 878AE
Elements
4/10/2017 Z115-128SN
Elements
9/26/2016 Z115-128SN
Elements
3/11/2016 885-224SN
Jeffrey Alexander
2/1/2016 878AE
Elements
1/18/2018 Z115-96DBAC
Elements
11/30/2017 Z115-96DBAC
Elements
11/1/2015 885-305PC
Jeffrey Alexander
5/19/2018 885-224PC
Jeffrey Alexander
6/2/2018 Z115-128PC
Elements
2/15/2017 878AE
Elements
7/3/2015 885-96SN
Jeffrey Alexander
7/3/2015 885-96SN
Jeffrey Alexander
2/5/2015 878AE
Elements
4/25/2018 885-305SN
Jeffrey Alexander
1/30/2015 885-305PC
Jeffrey Alexander
2/25/2018 Z115-96DBAC
Elements
5/20/2016 Z115-128PC
Elements
2/3/2018 885-305PC
Jeffrey Alexander
7/7/2016 885-96SN
Jeffrey Alexander
11/13/2016 885-224NI
Jeffrey Alexander
1/16/2016 Z115-128PC
Elements
12/11/2015 Z115-96BNBDL Elements
collection
Lindos
Hayworth
Lindos
Hayworth
Lindos
Hayworth
Hayworth
Hayworth
Lindos
Hayworth
Lindos
Lindos
Lindos
Kingsport
Lindos
Lindos
Hayworth
Kingsport
Lindos
Lindos
Hayworth
Hayworth
Lindos
Kingsport
Hayworth
Hayworth
Kingsport
Hayworth
Hayworth
Lindos
Lindos
Hayworth
Hayworth
Hayworth
Lindos
Lindos
description
128″ CC pull
96 mm CC pull
128″ CC pull
96 mm CC pull
Knob
305 mm CC pull
96 mm CC pull
96 mm CC pull
128″ CC pull
305 mm CC pull
128″ CC pull
128″ CC pull
96″ CC pull
Knob
128″ CC pull
128″ CC pull
224 mm CC pull
Knob
96″ CC pull
96″ CC pull
305 mm CC pull
224 mm CC pull
128″ CC pull
Knob
96 mm CC pull
96 mm CC pull
Knob
305 mm CC pull
305 mm CC pull
96″ CC pull
128″ CC pull
305 mm CC pull
96 mm CC pull
224 mm CC pull
128″ CC pull
96″ CC pull
list_price
12.98
13.83
12.98
13.83
4.15
57.99
13.83
13.83
12.98
57.99
12.98
12.98
4.87
3.29
12.98
12.98
24.4
3.29
4.87
4.87
57.99
24.4
12.98
3.29
13.83
13.83
3.29
57.99
57.99
4.87
12.98
57.99
13.83
24.4
12.98
4.87
cost
quantity_sold sales revenue
8.2
307
8.89
374
8.29
685
8.95
216
2.52
169
36.1
470
8.18
101
8.89
759
8.29
999
37.55
315
8.29
561
8.46
268
3.29
892
2.27
846
8.08
301
8.08
161
14.22
698
2.27
612
3.1
566
3.1
387
36.1
132
15.68
246
8.46
623
2.27
501
8.18
207
8.18
186
2.27
487
37.55
30
36.1
653
3.1
629
8.46
333
36.1
846
8.18
191
15.67
131
8.46
943
3.16
826
variable cost
contribution margin
Fixed cost data
For year of 2018
Direct fixed expenses
Common allocated costs
$
$
Central
12,312
6,700
East coast
International
Midwest
Northeast
Northwest
$
$
South
5,775
4,310
West
Total
Segment Margin Report
For Year of 2018
Central
Sales revenue
Less variable expenses
Contribution margin
Less direct fixed expenses
Segment margin
Less common fixed costs allocated to regions
Operating income (loss)
East coast
International
Midwest
Northeast
Northwest
South
West
Total of all regions
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
