ABCDEFGHIJKLMNOPQRSTUVWXY
1
EXCEL TEST - Level 1
Candidate Name:
2
3
1) Table Management & Data Analysis
4
In the Table you can find the results of a Google Ads campaign from January 2020 to August 2021.
5
INPUT TABLE PROVIDED - DO NOT WORK OR MODIFY HERE
6
PLEASE EXECUTE THESE TASKS:MonthTotal Product CostNr Of Products SoldMargin per Product
7
1Elaborate the Contribution % on Total Product Cost for each Month gen-2020€ 828,95351,84%
8
You need to calculate per % of Product Cost of each month respect to the total…Insert your results in a Column you can create anywhere in the Work Area!feb-2020€ 3.813,82400,76%
9
mar-2020€ 4.370,00550,76%
10
2
Apply the Conditional Formatting to highlight the Contribution Column, where good Contribution (Low) is in green and bad Contribution (High) is in red
apr-2020€ 3.224,62350,69%
11
Divide the numbers into two subsets, the Good and the Bad Contribution ranges, then apply formatting using different colours to differentiate the 2 sets.mag-2020€ 4.169,06821,50%
12
giu-2020€ 2.034,061352,64%
13
3Calculate the Unit Cost per Productlug-2020€ 1.875,181612,84%
14
Using the Nr of Product Sold, calculata the Unit Cost per Product of each Monthago-2020€ 1.116,55902,85%
15
set-2020€ 2.161,971141,87%
16
4Calculate the Total Sales per Productott-2020€ 1.284,35762,65%
17
Given the Margin per Product column, calculate the Total Sales per Product in each month. Use the Nr. Of products sold…nov-2020€ 1.316,64551,84%
18
dic-2020€ 1.213,02933,13%
19
5Pivot the Table by Dategen-2021€ 6.241,563192,49%
20
Reorganise the Table so that the Pivot is by Year and Month and all Dates are correcly clickable. Usa all available columns for the Pivot.feb-2021€ 4.478,141262,45%
21
mar-2021€ 6.636,451271,78%
22
6Calculate the Company Margin in Valueapr-2021€ 4.723,121693,27%
23
For each month calculate the Company Marging on the Products Sold each month.mag-2021€ 7.165,681862,96%
24
giu-2021€ 6.007,551182,07%
25
7Calculate the Company Margin in %lug-2021€ 4.831,951091,77%
26
For each month calculate the Company Marging on the Products Sold each month express it in % of Salesago-2021€ 2.000,00105,00%
27
28
8Add a new Column with the Company MarginS and update the Pivot Table to pick up the new column.€ 69.492,67
29
You need to include it into the Pivot both Margins type, the one in Value and the one in %
30
31
9Create a graph comparing Margin per Product monthly data for 2020 vs 2021
32
You are free to choose the graph you want as long as the Margin per Product is comparable for each month of the 2 years of analysis and they are next to each other
33
34
35
WORK BELOW THIS LINE ↓
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100