Hello,
I was given the following financial task in excel.
To be honest i tried to solve it, but i would appreciate some help from your side in order to be sure.
I would veryt gratefull if you can help me.
1. Please add a column in "Sales analysis" in order to calculate the gross profit of each product code based on the cost per unit in sheet "Avg cost"
2. With the use of a formula please calculate the total sales of segment "Foundry" of all companies in the highlighted cell C5
3. Please create a pivot table in a new sheet which gives the revenue and gross profit figures of all Business Units per Segment.
I was given the following financial task in excel.
To be honest i tried to solve it, but i would appreciate some help from your side in order to be sure.
I would veryt gratefull if you can help me.
1. Please add a column in "Sales analysis" in order to calculate the gross profit of each product code based on the cost per unit in sheet "Avg cost"
2. With the use of a formula please calculate the total sales of segment "Foundry" of all companies in the highlighted cell C5
3. Please create a pivot table in a new sheet which gives the revenue and gross profit figures of all Business Units per Segment.
Task.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
2 | ||||||||
3 | 1. Please add a column in "Sales analysis" in order to calculate the gross profit of each product code based on the cost per unit in sheet "Avg cost" | |||||||
4 | ||||||||
5 | 2. With the use of a formula please calculate the total sales of segment "Foundry" of all companies in the highlighted cell C5 | Foundry | ||||||
6 | ||||||||
7 | 3. Please create a pivot table in a new sheet which gives the revenue and gross profit figures of all Business Units per Segment. | |||||||
8 | ||||||||
Intro |
Task.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | Company Code | Month | Year | Company product code | Segment | Business Unit | Quantity (metric tons) | Revenue (EUR) | Freight Cost (EUR) | Gross profit | ||
2 | 8474B | 6 | 2015 | 8474B-103 | Metallurgy | BPI | 27 750,00 | 2 013 892,47 | 411 088,33 | |||
3 | 8474B | 6 | 2015 | 8474B-203 | Construction | BPI | 9 450,00 | 704 150,00 | 143 990,43 | |||
4 | 8474B | 6 | 2015 | 8474B-301 | Foundry | BPI | 1 200,00 | 119 387,99 | 31 092,78 | |||
5 | 8474B | 6 | 2015 | 8474B-501 | Regional | BPI | 650,00 | 114 729,10 | - | |||
6 | 8474P | 6 | 2015 | 8474P-201 | Construction | BPI | 2 760,00 | 26 496,00 | 550,40 | |||
7 | 8474P | 6 | 2015 | 8474P-202 | Construction | BPI | 19,04 | 4 094,45 | - | |||
8 | 8474P | 6 | 2015 | 8474P-204 | Construction | BPI | 1 372,10 | 213 762,68 | 24 942,78 | |||
9 | 8474P | 6 | 2015 | 8474P-205 | Construction | BPI | 2,00 | 5 500,00 | 1 144,17 | |||
10 | 8474P | 6 | 2015 | 8474P-207 | Construction | BPI | 11 500,00 | 871 391,56 | 230 092,10 | |||
11 | 8474P | 6 | 2015 | 8474P-207 | Construction | BPI | 4 348,98 | 558 799,73 | 6 244,00 | |||
12 | 8474P | 6 | 2015 | 8474P-301 | Foundry | BPI | 129,00 | 12 083,12 | 5 320,01 | |||
13 | 8474P | 6 | 2015 | 8474P-301 | Foundry | BPI | 63,00 | 9 321,01 | 754,08 | |||
14 | 8474P | 6 | 2015 | 8474P-408 | Specialties | BPI | 6 321,00 | 592 072,83 | 113 204,23 | |||
15 | 8474P | 6 | 2015 | 8474P-408 | Specialties | BPI | 2 770,00 | 308 370,00 | 60 226,69 | |||
16 | 8474P | 6 | 2015 | 8474P-409 | Specialties | BPI | 35,42 | 19 728,00 | - | |||
17 | 8474P | 6 | 2015 | 8474P-410 | Specialties | BPI | 4 000,00 | 434 158,42 | 92 767,00 | |||
18 | 8474P | 6 | 2015 | 8474P-410 | Specialties | BPI | 40,00 | 2 500,00 | 4 513,29 | |||
19 | 8474P | 6 | 2015 | 8474P-412 | Specialties | BPI | 4 230,02 | 92 059,50 | 1 036,40 | |||
20 | 8474P | 6 | 2015 | 8474P-501 | Regional | BPI | 550,00 | 76 485,88 | - | |||
21 | 8478 | 6 | 2015 | 8478-201 | Construction | BPI | 5 654,39 | 42 863,02 | - | |||
22 | 8478 | 6 | 2015 | 8478-204 | Construction | BPI | 578,04 | 25 253,64 | - | |||
23 | 8478 | 6 | 2015 | 8478-301 | Foundry | BPI | 299,34 | 8 591,02 | - | |||
24 | 8487MPA | 6 | 2015 | 8487MPA-203 | Construction | MPA | 509,46 | 77 162,45 | - | |||
25 | 8487MPA | 6 | 2015 | 8487MPA-204 | Construction | MPA | 50,00 | 22 000,00 | 12 396,54 | |||
26 | 8487MPA | 6 | 2015 | 8487MPA-301 | Foundry | MPA | 5 493,57 | 1 413 747,84 | 162 626,41 | |||
27 | 8487MPA | 6 | 2015 | 8487MPA-409 | Specialties | MPA | 937,26 | 168 744,71 | 27 117,05 | |||
28 | 8487MPA | 6 | 2015 | 8487MPA-412 | Specialties | MPA | 48,30 | 7 836,68 | - | |||
29 | 8488MPA | 6 | 2015 | 8488MPA-203 | Construction | MPA | 2 663,77 | 525 089,74 | 68 345,87 | |||
30 | 8488MPA | 6 | 2015 | 8488MPA-301 | Foundry | MPA | 20 560,76 | 4 562 468,41 | 231 472,81 | |||
31 | 8488MPA | 6 | 2015 | 8488MPA-302 | Foundry | MPA | 129,46 | 143 805,30 | - | |||
32 | 8488MPA | 6 | 2015 | 8488MPA-401 | Specialties | MPA | 151,44 | 22 902,20 | 617,60 | |||
33 | 8488MPA | 6 | 2015 | 8488MPA-404 | Specialties | MPA | 28,00 | 13 529,50 | 790,01 | |||
34 | 8488MPA | 6 | 2015 | 8488MPA-405 | Specialties | MPA | 2 182,44 | 628 440,48 | 115 591,53 | |||
35 | 8488MPA | 6 | 2015 | 8488MPA-408 | Specialties | MPA | 96,64 | 16 044,28 | 24,75 | |||
36 | 8488MPA | 6 | 2015 | 8488MPA-409 | Specialties | MPA | 606,70 | 127 355,00 | - | |||
37 | 8488MPA | 6 | 2015 | 8488MPA-410 | Specialties | MPA | 5 986,20 | 707 821,18 | 2 836,65 | |||
38 | 8488MPA | 6 | 2015 | 8488MPA-411 | Specialties | MPA | 65,35 | 18 105,03 | 614,36 | |||
39 | 8488MPA | 6 | 2015 | 8488MPA-412 | Specialties | MPA | 803,08 | 283 819,24 | 12 233,88 | |||
40 | 8488MPA | 6 | 2015 | 8488MPA-501 | Regional | MPA | 1 086,80 | 17 932,37 | 3 752,16 | |||
41 | 8488SCF | 6 | 2015 | 8488SCF-106 | Metallurgy | SCF | 6 350,32 | 3 903 541,07 | 91 370,04 | |||
42 | 8488SCF | 6 | 2015 | 8488SCF-108 | Metallurgy | SCF | 2 000,00 | 57 500,00 | - | |||
43 | 8499MPA | 6 | 2015 | 8499MPA-301 | Foundry | MPA | 14 897,58 | 3 307 749,93 | 88 955,34 | |||
44 | 8499MPA | 6 | 2015 | 8499MPA-302 | Foundry | MPA | 300,00 | 15 130,22 | 4 030,72 | |||
45 | 8499MPA | 6 | 2015 | 8499MPA-403 | Specialties | MPA | 18,51 | 5 771,65 | - | |||
46 | 8499MPA | 6 | 2015 | 8499MPA-412 | Specialties | MPA | 106,14 | 37 252,92 | 9 398,50 | |||
47 | 8499MPA | 6 | 2015 | 8499MPA-501 | Regional | MPA | 7,00 | 813,92 | - | |||
48 | 8500 | 6 | 2015 | 8500-106 | Metallurgy | SCF | 3 554,38 | 2 668 594,71 | 55 526,58 | |||
sales analysis |
Task.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Company Code | Product group | Segment | Product code | Cost per unit | ||
2 | 8474B | 103 | Metallurgy | 8474B-103 | 36,29 | ||
3 | 8474B | 203 | Construction | 8474B-203 | 37,26 | ||
4 | 8474B | 301 | Foundry | 8474B-301 | 49,74 | ||
5 | 8474B | 501 | Regional | 8474B-501 | 88,25 | ||
6 | 8474P | 201 | Construction | 8474P-201 | 4,80 | ||
7 | 8474P | 202 | Construction | 8474P-202 | 107,51 | ||
8 | 8474P | 204 | Construction | 8474P-204 | 77,90 | ||
9 | 8474P | 205 | Construction | 8474P-205 | 1375,00 | ||
10 | 8474P | 207 | Construction | 8474P-207 | 37,89 | ||
11 | 8474P | 207 | Construction | 8474P-207 | 37,89 | ||
12 | 8474P | 301 | Foundry | 8474P-301 | 46,83 | ||
13 | 8474P | 301 | Foundry | 8474P-301 | 46,83 | ||
14 | 8474P | 408 | Specialties | 8474P-408 | 46,83 | ||
15 | 8474P | 408 | Specialties | 8474P-408 | 46,83 | ||
16 | 8474P | 409 | Specialties | 8474P-409 | 278,46 | ||
17 | 8474P | 410 | Specialties | 8474P-410 | 54,27 | ||
18 | 8474P | 410 | Specialties | 8474P-410 | 54,27 | ||
19 | 8474P | 412 | Specialties | 8474P-412 | 10,88 | ||
20 | 8474P | 501 | Regional | 8474P-501 | 69,53 | ||
21 | 8478 | 201 | Construction | 8478-201 | 3,79 | ||
22 | 8478 | 204 | Construction | 8478-204 | 21,84 | ||
23 | 8478 | 301 | Foundry | 8478-301 | 14,35 | ||
24 | 8487MPA | 203 | Construction | 8487MPA-203 | 75,73 | ||
25 | 8487MPA | 204 | Construction | 8487MPA-204 | 220,00 | ||
26 | 8487MPA | 301 | Foundry | 8487MPA-301 | 128,67 | ||
27 | 8487MPA | 409 | Specialties | 8487MPA-409 | 90,02 | ||
28 | 8487MPA | 412 | Specialties | 8487MPA-412 | 81,13 | ||
29 | 8488MPA | 203 | Construction | 8488MPA-203 | 137,99 | ||
30 | 8488MPA | 301 | Foundry | 8488MPA-301 | 155,33 | ||
31 | 8488MPA | 302 | Foundry | 8488MPA-302 | 777,58 | ||
32 | 8488MPA | 401 | Specialties | 8488MPA-401 | 105,86 | ||
33 | 8488MPA | 404 | Specialties | 8488MPA-404 | 338,24 | ||
34 | 8488MPA | 405 | Specialties | 8488MPA-405 | 201,57 | ||
35 | 8488MPA | 408 | Specialties | 8488MPA-408 | 116,22 | ||
36 | 8488MPA | 409 | Specialties | 8488MPA-409 | 146,94 | ||
37 | 8488MPA | 410 | Specialties | 8488MPA-410 | 82,77 | ||
38 | 8488MPA | 411 | Specialties | 8488MPA-411 | 193,93 | ||
39 | 8488MPA | 412 | Specialties | 8488MPA-412 | 247,39 | ||
40 | 8488MPA | 501 | Regional | 8488MPA-501 | 11,55 | ||
41 | 8488SCF | 106 | Metallurgy | 8488SCF-106 | 430,29 | ||
42 | 8488SCF | 108 | Metallurgy | 8488SCF-108 | 20,13 | ||
43 | 8499MPA | 301 | Foundry | 8499MPA-301 | 155,42 | ||
44 | 8499MPA | 302 | Foundry | 8499MPA-302 | 35,30 | ||
45 | 8499MPA | 403 | Specialties | 8499MPA-403 | 218,30 | ||
46 | 8499MPA | 412 | Specialties | 8499MPA-412 | 245,69 | ||
47 | 8499MPA | 501 | Regional | 8499MPA-501 | 81,39 | ||
48 | 8500 | 106 | Metallurgy | 8500-106 | 525,55 | ||
Avg cost |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2:D48 | D2 | =CONCATENATE(A2,"-",B2) |