| a. Do proper formatting of the data with using borders, font size and appropriate alignment of the content. |
|
|
|
| A.See Table |
|
|
|
|
|
|
|
|
|
|
| b. Make summary of Products Sales by providing |
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
| |
Total Sum of Sales |
|
|
|
|
|
|
|
|
| |
10032628.85 |
|
|
|
|
|
|
|
|
|
| |
Average of Sales |
|
|
|
|
|
|
|
|
| |
3553.889072 |
|
|
|
|
|
|
|
|
|
| |
Standard Deviation of Sales |
|
|
|
|
|
|
|
|
| |
1843.242979 |
|
|
|
|
|
|
|
|
|
| |
Variance of Sales |
|
|
|
|
|
|
|
|
| |
3392467.068 |
|
|
|
|
|
|
|
|
|
| |
Maximum Sales |
|
|
|
|
|
|
|
|
| |
14082.8 |
|
|
|
|
|
|
|
|
|
| |
Minimum Sales |
|
|
|
|
|
|
|
|
| |
482.13 |
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
| c. Analyse the sales happened by product line & status using pivot table |
|
|
|
|
|
|
| Count of SALES |
Column Labels |
|
|
|
|
|
|
|
|
| Row Labels |
Classic Cars |
Motorcycles |
Planes |
Ships |
Trains |
Trucks and Buses |
Vintage Cars |
(blank) |
Grand Total |
|
| Cancelled |
16 |
|
12 |
18 |
1 |
|
13 |
|
60 |
|
| Disputed |
3 |
6 |
2 |
1 |
|
|
2 |
|
14 |
|
| In Process |
14 |
|
|
|
|
11 |
16 |
|
41 |
|
| On Hold |
12 |
1 |
9 |
8 |
1 |
4 |
9 |
|
44 |
|
| Resolved |
8 |
|
12 |
12 |
|
5 |
10 |
|
47 |
|
| Shipped |
914 |
324 |
271 |
195 |
75 |
281 |
557 |
|
2617 |
|
| (blank) |
|
|
|
|
|
|
|
1 |
1 |
|
| Grand Total |
967 |
331 |
306 |
234 |
77 |
301 |
607 |
1 |
2824 |
|
| |
|
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
| c. Analyse the sales happened by product line & status using pivot table |
|
|
|
|
|
|
| A.See Table |
|
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
| e. Analyse the Quantities ordered by each Country in Year 2019,2020 and 2021 for different Quarters using Pivot Table. |
|
|
| Row Labels |
Sum of SALES |
|
|
|
|
|
|
|
|
|
| 2019 |
3516979.54 |
|
|
|
|
|
|
|
|
|
| 1 |
445094.69 |
|
|
|
|
|
|
|
|
|
| 2 |
562365.22 |
|
|
|
|
|
|
|
|
|
| 3 |
649514.54 |
|
|
|
|
|
|
|
|
|
| 4 |
1860005.09 |
|
|
|
|
|
|
|
|
|
| 2020 |
1791486.71 |
|
|
|
|
|
|
|
|
|
| 1 |
1071992.36 |
|
|
|
|
|
|
|
|
|
| 2 |
719494.35 |
|
|
|
|
|
|
|
|
|
| 2021 |
4724162.6 |
|
|
|
|
|
|
|
|
|
| 1 |
833730.68 |
|
|
|
|
|
|
|
|
|
| 2 |
766260.73 |
|
|
|
|
|
|
|
|
|
| 3 |
1109396.27 |
|
|
|
|
|
|
|
|
|
| 4 |
2014774.92 |
|
|
|
|
|
|
|
|
|
| Grand Total |
10032628.85 |
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
| f. Present the data in different colours for values in field Status and ProductLine using conditional formatting. |
|
|
|
| ORDERNUMBER |
QUANTITYORDERED |
PRICEEACH |
ORDERLINENUMBER |
SALES |
|
|
|
|
|
|
| 10107 |
30 |
95.7 |
2 |
2871 |
|
|
|
|
|
|
| 10121 |
34 |
81.35 |
5 |
2765.9 |
|
|
|
|
|
|
| 10134 |
41 |
94.74 |
2 |
3884.34 |
|
|
|
|
|
|
| 10145 |
45 |
83.26 |
6 |
3746.7 |
|
|
|
|
|
|
| 10159 |
49 |
100 |
14 |
5205.27 |
|
|
|
|
|
|
| 10168 |
36 |
96.66 |
1 |
3479.76 |
|
|
|
|
|
|
| 10180 |
29 |
86.13 |
9 |
2497.77 |
|
|
|
|
|
|
| 10188 |
48 |
100 |
1 |
5512.32 |
|
|
|
|
|
|
| 10201 |
22 |
98.57 |
2 |
2168.54 |
|
|
|
|
|
|
| 10211 |
41 |
100 |
14 |
4708.44 |
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
| h. Calculate the total sales of all “Shipped”, “Cancelled”, “Disputed”, “In Process”, “On Hold”, “Resolved” products. (Hint:Use pivot table or sumif function) |
| Row Labels |
Sum of SALES |
|
|
|
|
|
|
|
|
|
| Cancelled |
194487.48 |
|
|
|
|
|
|
|
|
|
| Disputed |
72212.86 |
|
|
|
|
|
|
|
|
|
| In Process |
144729.96 |
|
|
|
|
|
|
|
|
|
| On Hold |
178979.19 |
|
|
|
|
|
|
|
|
|
| Resolved |
150718.28 |
|
|
|
|
|
|
|
|
|
| Shipped |
9291501.08 |
|
|
|
|
|
|
|
|
|
| Grand Total |
10032628.85 |
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
| i. Split the productcode field into two using text functions Example :S10_1678 -> S10 in one column and 1678 in another column. |
|
| A. left(S10_1678,3) |
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
| j. Highlight the top 10% values of QuantityOrdered field using Conditional Formatting |
|
|
|
|
|
| A.See Table |
|
|
|
|
|
|
|
|
|
|