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 income, tax income, and net income. The solution demonstrates the use of Excel formulas to calculate tax, net income, total net income, average net income, highest and lowest net incomes, and counts based on specific criteria. It also includes a chart comparing the income and net income of each business. The assignment leverages functions like TODAY, COUNT, IF, SUM, AVERAGE, MAX, MIN, and COUNTIF to derive insights from the data. References to resources on advanced Excel functions are also provided. Desklib offers a platform to explore similar solved assignments and study materials for students.

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