Below is the December 2009 sales data bysales representative for Papier Nouveau,
ID: 2774487 • Letter: B
Question
Below is the December 2009 sales data bysales representative for Papier Nouveau, a distributor of commercial printing supplies. As the Director of HR for Papier Nouveau, you are responsible for calculating monthly incentives. In previous months, bonuses were based on sales volume, however this month you are considering basing them on profit margin. Create pivot tables to help you answer the following questions in analyzing the December sales:
a) What is the highest volume of our highest margin product sold in December?
b) What is the average total profit generated for the month?
c) What is the total number of commercial copiers sold during December
Employee ID
Region
Product
Profit Margin per Unit
Volume Sold
11048
SW
Cartridges
$ 7.00
200
11048
SW
Paper (ream)
$ 25.00
1,900
11048
SW
Printers
$ 103.00
7
12345
NE
Commercial Copiers
$ 593.00
4
12345
NE
Paper (ream)
$ 25.00
2,000
12345
NE
Printers
$ 103.00
8
26427
MW
Cartridges
$ 7.00
600
26427
MW
Paper (ream)
$ 25.00
7,300
26427
MW
Printers
$ 103.00
4
62380
NW
Fax Machine
$ 121.00
7
62380
NW
Laminating Machine
$ 375.00
3
62380
NW
Printers
$ 103.00
21
67890
SW
Cartridges
$ 7.00
820
67890
SW
Commercial Copiers
$ 593.00
6
67890
SW
Paper (ream)
$ 25.00
980
73628
NW
Commercial Copiers
$ 593.00
7
73628
NW
Fax Machine
$ 121.00
8
73628
NW
Paper (ream)
$ 25.00
4,900
82745
SE
Cartridges
$ 7.00
500
82745
SE
Commercial Copiers
$ 593.00
2
82745
SE
Paper (ream)
$ 25.00
750
92857
NE
Laminating Machine
$ 375.00
5
92857
NE
Paper (ream)
$ 25.00
200
92857
NE
Printers
$ 103.00
11
95202
SE
Fax Machine
$ 121.00
2
95202
SE
Paper (ream)
$ 25.00
5,000
95202
SE
Printers
$ 103.00
9
Employee ID
Region
Product
Profit Margin per Unit
Volume Sold
11048
SW
Cartridges
$ 7.00
200
11048
SW
Paper (ream)
$ 25.00
1,900
11048
SW
Printers
$ 103.00
7
12345
NE
Commercial Copiers
$ 593.00
4
12345
NE
Paper (ream)
$ 25.00
2,000
12345
NE
Printers
$ 103.00
8
26427
MW
Cartridges
$ 7.00
600
26427
MW
Paper (ream)
$ 25.00
7,300
26427
MW
Printers
$ 103.00
4
62380
NW
Fax Machine
$ 121.00
7
62380
NW
Laminating Machine
$ 375.00
3
62380
NW
Printers
$ 103.00
21
67890
SW
Cartridges
$ 7.00
820
67890
SW
Commercial Copiers
$ 593.00
6
67890
SW
Paper (ream)
$ 25.00
980
73628
NW
Commercial Copiers
$ 593.00
7
73628
NW
Fax Machine
$ 121.00
8
73628
NW
Paper (ream)
$ 25.00
4,900
82745
SE
Cartridges
$ 7.00
500
82745
SE
Commercial Copiers
$ 593.00
2
82745
SE
Paper (ream)
$ 25.00
750
92857
NE
Laminating Machine
$ 375.00
5
92857
NE
Paper (ream)
$ 25.00
200
92857
NE
Printers
$ 103.00
11
95202
SE
Fax Machine
$ 121.00
2
95202
SE
Paper (ream)
$ 25.00
5,000
95202
SE
Printers
$ 103.00
9
Explanation / Answer
a. Highest margin product sold is Commercial Paper. Highest volume sold is 7
b. Average Total profit for the month = 613094 / 25254 = 24.28
c. Total number of Commercial Copiers sold are 19
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.