Excel finance task

L

levskari

New Member
Joined
Apr 21, 2023
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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.


Task.xlsx
ABCDEF
2
31. 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
52. With the use of a formula please calculate the total sales of segment "Foundry" of all companies in the highlighted cell C5Foundry
6
73. 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
ABCDEFGHIJ
1Company CodeMonthYearCompany product codeSegmentBusiness Unit Quantity (metric tons) Revenue (EUR) Freight Cost (EUR) Gross profit
28474B620158474B-103MetallurgyBPI27 750,002 013 892,47411 088,33
38474B620158474B-203ConstructionBPI9 450,00704 150,00143 990,43
48474B620158474B-301FoundryBPI1 200,00119 387,9931 092,78
58474B620158474B-501RegionalBPI650,00114 729,10-
68474P620158474P-201ConstructionBPI2 760,0026 496,00550,40
78474P620158474P-202ConstructionBPI19,044 094,45-
88474P620158474P-204ConstructionBPI1 372,10213 762,6824 942,78
98474P620158474P-205ConstructionBPI2,005 500,001 144,17
108474P620158474P-207ConstructionBPI11 500,00871 391,56230 092,10
118474P620158474P-207ConstructionBPI4 348,98558 799,736 244,00
128474P620158474P-301FoundryBPI129,0012 083,125 320,01
138474P620158474P-301FoundryBPI63,009 321,01754,08
148474P620158474P-408SpecialtiesBPI6 321,00592 072,83113 204,23
158474P620158474P-408SpecialtiesBPI2 770,00308 370,0060 226,69
168474P620158474P-409SpecialtiesBPI35,4219 728,00-
178474P620158474P-410SpecialtiesBPI4 000,00434 158,4292 767,00
188474P620158474P-410SpecialtiesBPI40,002 500,004 513,29
198474P620158474P-412SpecialtiesBPI4 230,0292 059,501 036,40
208474P620158474P-501RegionalBPI550,0076 485,88-
218478620158478-201ConstructionBPI5 654,3942 863,02-
228478620158478-204ConstructionBPI578,0425 253,64-
238478620158478-301FoundryBPI299,348 591,02-
248487MPA620158487MPA-203ConstructionMPA509,4677 162,45-
258487MPA620158487MPA-204ConstructionMPA50,0022 000,0012 396,54
268487MPA620158487MPA-301FoundryMPA5 493,571 413 747,84162 626,41
278487MPA620158487MPA-409SpecialtiesMPA937,26168 744,7127 117,05
288487MPA620158487MPA-412SpecialtiesMPA48,307 836,68-
298488MPA620158488MPA-203ConstructionMPA2 663,77525 089,7468 345,87
308488MPA620158488MPA-301FoundryMPA20 560,764 562 468,41231 472,81
318488MPA620158488MPA-302FoundryMPA129,46143 805,30-
328488MPA620158488MPA-401SpecialtiesMPA151,4422 902,20617,60
338488MPA620158488MPA-404SpecialtiesMPA28,0013 529,50790,01
348488MPA620158488MPA-405SpecialtiesMPA2 182,44628 440,48115 591,53
358488MPA620158488MPA-408SpecialtiesMPA96,6416 044,2824,75
368488MPA620158488MPA-409SpecialtiesMPA606,70127 355,00-
378488MPA620158488MPA-410SpecialtiesMPA5 986,20707 821,182 836,65
388488MPA620158488MPA-411SpecialtiesMPA65,3518 105,03614,36
398488MPA620158488MPA-412SpecialtiesMPA803,08283 819,2412 233,88
408488MPA620158488MPA-501RegionalMPA1 086,8017 932,373 752,16
418488SCF620158488SCF-106MetallurgySCF6 350,323 903 541,0791 370,04
428488SCF620158488SCF-108MetallurgySCF2 000,0057 500,00-
438499MPA620158499MPA-301FoundryMPA14 897,583 307 749,9388 955,34
448499MPA620158499MPA-302FoundryMPA300,0015 130,224 030,72
458499MPA620158499MPA-403SpecialtiesMPA18,515 771,65-
468499MPA620158499MPA-412SpecialtiesMPA106,1437 252,929 398,50
478499MPA620158499MPA-501RegionalMPA7,00813,92-
488500620158500-106MetallurgySCF3 554,382 668 594,7155 526,58
sales analysis

Task.xlsx
ABCDE
1Company CodeProduct groupSegmentProduct codeCost per unit
28474B103Metallurgy8474B-10336,29
38474B203Construction8474B-20337,26
48474B301Foundry8474B-30149,74
58474B501Regional8474B-50188,25
68474P201Construction8474P-2014,80
78474P202Construction8474P-202107,51
88474P204Construction8474P-20477,90
98474P205Construction8474P-2051375,00
108474P207Construction8474P-20737,89
118474P207Construction8474P-20737,89
128474P301Foundry8474P-30146,83
138474P301Foundry8474P-30146,83
148474P408Specialties8474P-40846,83
158474P408Specialties8474P-40846,83
168474P409Specialties8474P-409278,46
178474P410Specialties8474P-41054,27
188474P410Specialties8474P-41054,27
198474P412Specialties8474P-41210,88
208474P501Regional8474P-50169,53
218478201Construction8478-2013,79
228478204Construction8478-20421,84
238478301Foundry8478-30114,35
248487MPA203Construction8487MPA-20375,73
258487MPA204Construction8487MPA-204220,00
268487MPA301Foundry8487MPA-301128,67
278487MPA409Specialties8487MPA-40990,02
288487MPA412Specialties8487MPA-41281,13
298488MPA203Construction8488MPA-203137,99
308488MPA301Foundry8488MPA-301155,33
318488MPA302Foundry8488MPA-302777,58
328488MPA401Specialties8488MPA-401105,86
338488MPA404Specialties8488MPA-404338,24
348488MPA405Specialties8488MPA-405201,57
358488MPA408Specialties8488MPA-408116,22
368488MPA409Specialties8488MPA-409146,94
378488MPA410Specialties8488MPA-41082,77
388488MPA411Specialties8488MPA-411193,93
398488MPA412Specialties8488MPA-412247,39
408488MPA501Regional8488MPA-50111,55
418488SCF106Metallurgy8488SCF-106430,29
428488SCF108Metallurgy8488SCF-10820,13
438499MPA301Foundry8499MPA-301155,42
448499MPA302Foundry8499MPA-30235,30
458499MPA403Specialties8499MPA-403218,30
468499MPA412Specialties8499MPA-412245,69
478499MPA501Regional8499MPA-50181,39
488500106Metallurgy8500-106525,55
Avg cost
Cell Formulas
RangeFormula
D2:D48D2=CONCATENATE(A2,"-",B2)
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Welcome to the Board!

This appears to be homework, test, or some other sort of class assignment.
Note that it would not be ethical for us to do your assignments for you.

If you post what you have tried, some people may be willing to critique your solution attempts and/or give you some pointers to point you in the correct direction.
 
Upvote 0
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Financial task excel
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Saher Naji
  • Question Question
MAO
Replies
1
Views
512
igold
igold
H
  • Question Question
Formula to automatically copy select row data across multiple columns into new sheet
Replies
2
Views
1K
hionman
H
A
  • Question Question
Multiple Data Validation and Over all Data
Replies
1
Views
396
JW00
J
S
  • Question Question
Circular iterations and using the max function.
Replies
0
Views
700
Satterfield
S
M
  • Question Question
Tracking historical costs changes in a home made profitability dashboard
Replies
2
Views
617
mikaelaleksandrowicz
M

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top