Financial Data Analysis: Utilizing Excel Formula Functions and Charts

Verified

Added on  2023/06/11

|10
|724
|271
Practical Assignment
AI Summary
This assignment solution showcases the analysis of a dataset using Microsoft Excel. It involves utilizing various formula tab functions to calculate total net earnings, average net earnings, highest and lowest net earnings, and tax implications based on income levels. The solution includes formulas for conditional logic (High/Low Income) and calculations for specific positions. Furthermore, it presents a comparative chart illustrating earnings versus net earnings for players like Lionel Messi, Gareth Bale, Paul Pogba, Oscar, Cristiano Ronaldo, and Neymar, providing a visual representation of their financial data. The assignment also includes the formulas used in excel.
Document Page
PROJECT PART 1 AND
PART 2
PART 2: DATASET B
tabler-icon-diamond-filled.svg

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
TABLE OF CONTENTS
DATASET
SOLVING GIVEN DATASET
FORMULA TAB FUNCTIONS
CHART SHOWING EARNINGS VS NET EARNINGS
REFERENCES
Document Page
DATASET: A
Date
Player Counts Tax 45%
Player Income (£) Rank High/Low Income Tax Net Income
Lionel Messi 111000 1
Gareth Bale 34600 1
Paul Pogba 29500 2
Oscar 27500 2
Cristiano Ronaldo 108000 1
Neymar 90000 1
Total Net Earnings Total Net Earnings of Position 2
Average Net Earnings
Average Net Earnings of
Position 1
Highest Net Earnings Count of Net Earnings >£50000
Lowest Net Earnings Count of Net Earnings <£50000
Document Page
SOLVING GIVEN DATASET
Date
Player Counts Tax 45%
Player Income (£) Rank High/Low Income Tax Net Income
Lionel Messi 111000 1 49950 61050
Gareth Bale 34600 1 15570 19030
Paul Pogba 29500 2 13275 16225
Oscar 27500 2 12375 15125
Cristiano Ronaldo 108000 1 48600 59400
Neymar 90000 1 40500 49500
Total Net Earnings 220330
Total Net Earnings of
Position 2 31350
Average Net Earnings 36721.67
Average Net Earnings of
Position 1 47245
Highest Net Earnings 61050
Count of Net Earnings
>£50000 2
Lowest Net Earnings 15125
Count of Net Earnings
<£50000 4
tabler-icon-diamond-filled.svg

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
FORMULA TAB
Date =TODAY()
Player Counts
=COUNT(B4:B
9) Tax 0.45
Player Earnings (£k) PositionHigh/Low Income Tax Net Income
Lionel Messi 111000 1 =IF(B4> 30000, "High", "Low") =B4*$D$2 =B4-E4
Gareth Bale 34600 1 =IF(B5> 30000, "High", "Low") =B5*$D$2 =B5-E5
Paul Pogba 29500 2 =IF(B6> 30000, "High", "Low") =B6*$D$2 =B6-E6
Oscar 27500 2 =IF(B7> 30000, "High", "Low") =B7*$D$2 =B7-E7
Cristiano Ronaldo 108000 1 =IF(B8> 30000, "High", "Low") =B8*$D$2 =B8-E8
Neymar 90000 1 =IF(B9> 30000, "High", "Low") =B9*$D$2 =B9-E9
Total Net Earnings =SUM(F4:F9) Total Net Earnings of Position 2 =SUM(F6,F7)
Average Net Earnings
=AVERAGE(F
4:F9)
Average Net Earnings of Position
1 =AVERAGE(F4,F5,F8,F9)
Highest Net Earnings =MAX(F4:F9) Count of Net Earnings >£50000
=COUNTIF(F4:F9,
">50000")
Lowest Net Earnings =MIN(F4:F9) Count of Net Earnings <£50000
=COUNTIF(F4:F9,
"<50000")
Document Page
CONT.
Date =TODAY()
No of Players = COUNT(B4:B9)
Total Net Earnings = SUM(F4:F9)
Average Net Earnings = AVERAGE(F4:F9)
Highest Net Earnings = MAX(F4:F9)
Lowest Net Earnings = MIN(F4:F9)
Document Page
CONT.
High/ Low Income = IF(B4> 300000,
"High", "Low")
Tax =B4*$D$2
tabler-icon-diamond-filled.svg

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
CONT.
Net Income = B4-E4
Total Net Income of Position 2 = SUM(F6,F7)
Average Net Income of Position 1 = AVERAGE(F4,F5,F8,F9)
Count of Net Income >£50000 = COUNTIF(F4:F9, ">50000")
Count of Net Income <£50000 = COUNTIF(F4:F9, "<50000")
Document Page
CHART SHOWING EARNINGS VS NET
EARNINGS
Player
Earnings
(£k)
Net
Earnings
(£k)
Lionel Messi 111000 61050
Gareth Bale 34600 19030
Paul Pogba 29500 16225
Oscar 27500 15125
Cristiano Ronaldo 108000 59400
Neymar 90000 49500 Lionel Messi Gareth Bale Paul Pogba Oscar Cristiano
Ronaldo
Neymar
0
20000
40000
60000
80000
100000
120000
111000
34600 29500 27500
108000
90000
61050
19030 16225 15125
59400
49500
Earnings vs Net Earnings of Business
Earnings (£k) Net Earnings (£k)
Document Page
chevron_up_icon
1 out of 10
circle_padding
hide_on_mobile
zoom_out_icon
[object Object]