Financial Data Analysis: Utilizing Excel Formula Functions and Charts

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.

PROJECT PART 1 AND
PART 2
PART 2: DATASET B

TABLE OF CONTENTS
 DATASET
 SOLVING GIVEN DATASET
 FORMULA TAB FUNCTIONS
 CHART SHOWING EARNINGS VS NET EARNINGS
 REFERENCES

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

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

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")

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)

CONT.
High/ Low Income = IF(B4> 300000,
"High", "Low")
Tax =B4*$D$2

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")

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)