1. Read:
Data Analysis: , How, and Why to Do Data Analysis for Your Organization
2. Download this Salary Comparison Workbook & and review the worksheet(s)
to answer the complete the following.
On worksheet one we see a comparison of the same employees and what their earnings would have been over 12 pay
periods.
Using Microsoft Word…
1. Give at least one descriptive analysis of the data.
2. By inserting formulas as needed to you draw conclusions and explain what you did to come to the
conclusion. (For example: When summing the gross salaries for each section we see in this Option one would be…
… but if we change the base salary to…) Develop at least three conclusions using diagnostic analysis.
3. Offer at least one predictive analysis statement.
4. Give a prescriptive analysis which includes why.
-Save and submit your Word document. Callbri (Body)
v 11 A A
E
de Wrap Text
Paste
B I U HV CAV
BEE
Merge & Center v
$ ~ % 9 78
Conditional Format
Cell
Sort &
Find &
Ideas
Sensitivity
Formatting as Table
Styles
Format v
Filter
Select
A1
x v fx
A
B
C
D
E
F
G
H
K
L
M
N
O
OPTION 1: Salary + Static Commission
Quarterly
2 Employee
4/2/2
4/9/2
4/16/21
4/23/21
4/30/21
5/7/21
5/14/21
5/21/21
$/28/21
6/4/21
6/11/21
6/18/21
Total Sales
Commission
Gross Salary
John
53240
71477
79857
55525
12465
25167
50306
62545
34315
75290
96573
622,764.00 S
31,138 20 $ 35,938.20
James
35887
72898
98718
43823
85514
37839
17907
15894
52931
67756
70595
677,827.00 $
33,891.35 $ 38,691.35
Karer
$2284
19262
$4702
85055
26001
9279
82341
28471
8914
47892
10739 $
588,692.00 $
29,434.60 $ 34,234.60
David
56003
40030
79150
49855
79715
8986
41537
93991
35019
9207
37276
52758 $
747,267 00 $
37,363.95 $ 42,163.35
Sheryl
80789
24272
32788
70068
69732
5469
14013
21873
25577
40898
69740
28395 5
532,837.00 5
26,641 85 $ 31,441.85
Bud
61542
97010
59210
10046
41106
63961
72861
7459
53764
46672
80151
63710 $
724,628.00 $
36,231.40 $ 41,031 40
9 Total Sales
S
339,745.00 5
330,949.00 $
404,425.00 $
314,372.00 5
314,533.00 S
364,318.00 $
278,965.00 $
298,898.00 $
230,077.00 $
411,839,00 $
402,227.00 $
203,667.00
10
11
12 Quarterly Commission
5%
13 Weekly Salary
$400.00
14
15
16
17
18
OPTION 2: Salary + Dynamic Commission
Quarterly
19 Employee
4/2/21
4/9/21
4/16/21
4/23/21
4/30/21
5/7/21
5/14/21
5/21/21
5/28/21
6/4/21
6/11/21
6/18/21
Total Sales
Commission
Gross Salary
20
John
53240
77477
79857
55525
12465
25167
50306
62545
$4319
75290
36573
O S
622, 764.00 $
12.455.28 $ 17,255.28
21
James
35887
72898
9871
43823
85514
37835
17907
45894
52931
67756
10595
S
677,827.00 $
33,891.35 $ 38,691.35
2.2
Kare
52284
19262
5470
85055
23
26001
92796
82341
28471
8914
17892
10739 $
588,692.00 $
5,886.92 5 10,686.92
David
56003
40030
79150
49855
79715
8986
41537
93991
35015
92074
37276
52758 $
747,267.00 $
67,254.03 $ 72,054.03
24
Sheryl
80789
24272
3278
70068
69732
54697
14013
21873
5577
4089
69740
28395 $
532,837.00 $
5,328.37 $ 10,128.37
25
Bud
61547
97010
59210
10046
41106
63961
72861
74595
53764
46672
80151
63710 $
724,628.00 5
36,231.40 $ 41,031.40
26 Total Sales
339,745.00 $
330,949.00 S
404,425.00 $
314,372.00 $
27
314,533.00 $
364,318,00 $
278 965.00 S
298,898.00 $
230,077.00 $
411,839.00 5
402.227.00 S
203,667.00
28
29 Quarterly Sales
Commissions
30 Over $725,000
0.09
31 $675,000-5724,999
0.05
32 $600,00 -$674,000
0.02
33 Under $600,000
0.01
34
5 Weekly Salary
400,00
36
37
38