Information Communication Technology: Solving and Presenting Dataset

This document presents a solved Information Communication Technology (ICT) assignment involving the analysis of a given dataset. The dataset includes information about various businesses, such as mobile phone repair shops, stationery shops, and confectionery retailers, along with their business i...Read More

INFORMATION
COMMUNICATION
TECHNOLOGY
PART 2

TABLE OF CONTENTS
• DATASET
• SOLVING DATASET
• PRESENTING CHARTS
• REFERENCES

SOLVING GIVEN DATASET
A B C D E F
Date 3/7/2022
No of Businesses 6 Tax 21%
Business Income (£) Rank High/Low Income Tax Net Income
Mobile Phone Repair Shop 750070 1 High 157514.7 592555.3
Stationery Shop 1360600 1 High 285726 1074874
Confectionery Retailer 580000 2 High 121800 458200
Watch Retailer 150650 4 Low 31636.5 119013.5
Shoes Repair Shop 480000 2 High 100800 379200
Computer Repair Shop 230000 3 High 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

FORMULAS USED TO SOLVE GIVEN DATA
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()
• No of Businesses = COUNT(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)

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

CHART
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
• Murray, A., 2021. The Ten Power Functions of Excel. In Advanced Excel Success (pp. 57-
135). Apress, Berkeley, CA.
• ĎURIŠ, V. and ŠUMNÝ, T., 2020. DEVELOPMENT OF LOGICAL THINKING
USING EXCEL. Ad Alta: Journal of Interdisciplinary Research. 10(1).