Analyzing Business Data with Excel: Income, Tax, and Net Income

This assignment presents a comprehensive solution to a business dataset using Microsoft Excel. It involves analyzing business income, calculating income tax at a rate of 21%, and determining net income for various businesses, including mobile phone repair shops, stationery shops, confectionery retailers, watch retailers, shoe repair shops, and computer repair shops. The solution utilizes Excel formulas such as `TODAY()`, `COUNT()`, `IF()`, `SUM()`, `AVERAGE()`, `MAX()`, `MIN()`, and `COUNTIF()` to perform calculations and derive insights. The analysis includes ranking businesses based on their income, categorizing them as high or low income, and calculating total and average net incomes. Comparison charts are also provided to visually represent the relationship between income and net income. The assignment also includes references to academic articles that support the use of Excel for data analysis.

INFORMATION COMMUNICATION
TECHNOLOGY
PART 2

TABLE OF CONTENT
 DATASET SOLUTIONS
 FORMULA CONSIDERED TO SOLVE DATASET
 COMPARISON CHARTS
 REFERENCES

DATASET SOLUTIONS
A B C D E F
Date 3/7/2022
No of Businesses 6Tax 21%
Business Income (£) Rank High/Low Income Tax Net Income
Mobile Phone Repair
Shop 750070 1High 157514.7 592555.3
Stationery Shop 1360600 1High 285726 1074874
Confectionery Retailer 580000 2High 121800 458200
Watch Retailer 150650 4Low 31636.5 119013.5
Shoes Repair Shop 480000 2High 100800 379200
Computer Repair Shop 230000 3High 48300 181700
Total Net Income 2805542.8 Total Net Income of rank 2 837400
Average Net Income 467590.47 Average Net Income of Rank 1 833714.65
Highest Net Income 1074874 Count of Net Income >£500k 2
Lowest Net Income 119013.5 Count of Net Income <£500k 4

FORMULA CONSIDERED TO SOLVE
DATASET
A B C D E F
Date =TODAY()
No of Businesses =COUNT(B4:B9) Tax 0.21
Business Income (£) Rank High/Low Income Tax Net Income
Mobile Phone Repair Shop 750070 1
=IF(B4> 200000, "High",
"Low") =B4*$D$2 =B4-E4
Stationery Shop 1360600 1
=IF(B5> 200000, "High",
"Low") =B5*$D$2 =B5-E5
Confectionery Retailer 580000 2
=IF(B6> 200000, "High",
"Low") =B6*$D$2 =B6-E6
Watch Retailer 150650 4
=IF(B7> 200000, "High",
"Low") =B7*$D$2 =B7-E7
Shoes Repair Shop 480000 2
=IF(B8> 200000, "High",
"Low") =B8*$D$2 =B8-E8
Computer Repair Shop 230000 3
=IF(B9> 200000, "High",
"Low") =B9*$D$2 =B9-E9
Total Net Income =SUM(F4:F9) Total Net Income of rank 2 =SUM(F6,F8)
Average Net Income =AVERAGE(F4:F9)
Average Net Income of
Rank 1 =AVERAGE(F4:F5)
Highest Net Income =MAX(F4:F9)
Count of Net Income
>£500k
=COUNTIF(F4:F9,
">500000")
Lowest Net Income =MIN(F4:F9)
Count of Net Income
<£500k
=COUNTIF(F4:F9,
"<500000")

CONT…
 Date = Today()
 Number of Business =
Sum(B4:B9)
 Total Net Income = Sum(F4:F9)
 Average Net Income =
Average(F4:F9)
 Highest Net Income =
MAX(F4:F9)
 Lowest Net Income =
MIN(F4:F9)
Date 2/5/2022
No of Businesses
Total Net Income 2805542.8
Average Net Income 467590.47
Highest Net Income 1074874
Lowest Net Income 119013.5

CONT…
 Total Net Income of rank 2 =
SUM(F6,F8)
 Average Net Income of rank 1 =
AVERAGE(F4:F5)
 Count of Net Income >£500k =
COUNTIF(F4:F9, “>500000”)
 Count of Net Income <£500k =
COUNTIF(F4:F9, “<500000”)
Total Net Income of
rank 2 837400
Average Net Income
of Rank 1 833714.65
Count of Net Income
>£500k 2
Count of Net Income
<£500k 4

COMPARISON CHARTS
Mobile Phone
Repair Shop
Stationery Shop Confectionery
Retailer
Watch Retailer Shoes Repair
Shop
Computer Repair
Shop
0
200000
400000
600000
800000
1000000
1200000
1400000
1600000
750070
1360600
580000
150650
480000
230000
592555.3
1074874
458200
119013.5
379200
181700
Income vs Net Income of Business
Income (£) Net Income

REFERENCES
 Gündüz, M. and Asan, K., 2022. GEOstats: an excel-based data analysis program applying basic
principles of statistics for geological studies. Earth Science Informatics. 15(1). pp.705-712.
 Blayney, P. J. and Sun, Z., 2019. Using Excel and Excel VBA for preliminary analysis in big data
research. In Managerial Perspectives on Intelligent Big Data Analytics (pp. 110-136). IGI Global.
 Todorova, S., 2019. Statistics for data analysis using Microsoft Excel. Известия на Съюза на
учените-Варна. Серия Икономически науки. 8(2). pp.68-74.