ICT100 Data Analysis With Excel And Report About Modern Information Systems Assessment 2 Answer

pages Pages: 4word Words: 890

Question :

T3 2019: ICT100 Foundations of Information Systems

Assessment 2

Case Study Assignment:  Individual case study assessment composed of two submissions in Moodle: Part 1- Excel sheet and Part 2- Report (1,000 words).

Assessment topic: Data Analysis with Excel and Report about modern information systems.

Purpose: In this assessment students are to critically analyse a given case study. Students will use an Excel spread sheet and its different functionalities to analyse the case study. This assessment contributes to learning outcomes b and d.

Value: 15%

Assessment Details:

FIRST BANK is aggressive in its strategy to grow its customer base by offering tailored financial solutions.  To ensure the bank is prepared to address the potential surge in transactions, the bank is evaluating different systems to replace its legacy core banking application.  The I.T. team is evaluating six systems (CB1 to CB6) that were presented to them by independent software vendors.  The given file Assessment2-Datafile.xlx  contains testing results for these core banking systems (XX is the last two digits of your student ID). The assignment requires students to analyse the given case study and provide their recommendation for selection of the best system for the company. The assignment has two main parts:

  1. Data Analysis with Excel: Analyse the given data in Assessment2-Datafile.xlx  after completing the tasks given.  Analysis is based on the use of various excel formulas and graphs learned in class. 
  2. Report: Write a report for First Bank that must include the following sections:
    • Introduction: Discuss the importance of competitive advantage associated with the use of modern information systems.
    • Information System Selection: Justify the selection of core banking system based on the results generated in the excel sheet. The justification must be based on the two graphs.
    • Importance of Data representation: Discuss the importance of correctly representing data for easy decision making.
    • Conclusion: Must summarise the important parts of the report and should include recommendations for using information systems for competitive advantage.
    • Reference List: At least two references from academic journals.  In addition, proper in-text referencing is to be followed. 

Details of Data Analysis with Excel:

Download the file Assessment2-Datafile.xlx  and complete the following tasks:

1. In the row labeled “Total Transactions”, calculate net transactions after deducting “Re-Entry Required due to system errors” from “Transactions”.

2. In the row labeled “Transaction Block”, calculate the transaction block by dividing Total Transactions by 50.

3. In the row labeled “Transactions per module (6 modules)”, calculate the transactions for each module by dividing Transaction Block by 6. 

4. In column H use the Sum function to give totals (for all systems – CB1 to CB6) for each of the rows (rows 4 to 8).

5. In column I determine the lowest value (for all systems – CB1 to CB6) for each of the rows (rows 4 to 8).

6. In column J determine the highest value (for all systems– CB1 to CB6) for each of the rows (rows 4 to 8).

7. In column K determine the arithmetic mean (for all systems – CB1 to CB6) for each of the rows (rows 4 to 8).

8. The forecasted Daily Revenue gained from each system is based on transactions per module that is used in analysis for sales and marketing campaigns.  There is a total of 6 modules. Use a lookup table with revenue that changes according to the number of Transactions per module. You will use the range B8 to G8 (Transactions per Module) for the formulas to calculate daily revenue. There is a table on the spreadsheet showing the Daily Revenue. Use either the Lookup function or VLookup function to find the daily revenue for each of the systems CB1 to CB6 (make sure to use absolute referencing in your formula for the array field).

9. Create a Clustered Column chart for the Total Transactions. Remember to select the correct ranges and ensure the title of the chart is “Total Transactions”.

10. Create a Pie chart showing the column “Re-entry required due to system errors”. Remember to select the correct ranges and ensure the title of the chart is “System Errors”.

Show More

Answer :

FInancial analysis 


Importance of competitive advantage is associated with use of modern information system in banks. Technology is used to prevent financial data, proprietary data, and confidential executive decisions. Technology can assist the businesses to keep the idea apart from competition. Information technology has remained powerful impact on the competitive advantage in term of cost and differentiation. This report divulges how well the graphs and charts could be used by the person to divulge the key information in effective manner so that it could help reader to understand the complicated data in just one glimpse.  Technology can affect value activities and allow the organisations to get competitive advantage to exploit change in competitive business environment. Linkages need activities to remain coordinated. Timely delivery needs operations, service activities, and outbound logistics. Broad scope will include four key dimensions such as geographical and industrial scope. Metal stamping utilises several physical procession as compared to information processor. 

Information system selection 

Management information system will include the flow of procedures to process data in an effective manner with an aim to support decision-making. The use of MIS is the key factor for the production of products and services by collecting information, productivity, hiring of new employees and tracking the stocks (Nikolić, Vesić, & Gavrilović, 2019). Modern information technology is innovative and web development organisation based on Saudi Arabia. Modern IT avails services in several areas such as recruitment, E-marketing, content management, and UX design. A superior trait attained by the company as far as low price with greater value of differentiation is concerned (Houston et al., 2015). With the ramified economic changes, technologies has been changed and by using the graphical presentation and various software tools such as excel and spreadsheet data, users could easily present the complicated data in easy manner. It helps layman to understand the trend and possible ups an down in the data just by looking at the graph. 

Importance of data representation

Decision-making is an important area through which organisational goals are achieved, overall motivation of employees is undertaken, proper allocation of resources take place, selecting the best alternative, evaluation of managerial performance, indispensable elements, and pervasive attribute (Wager, Lee, & Glaser, 2017). It is analysed that use of the graphs and other charts is used to strengthen the analysis of the data gathered and assist the possible readers and respondents to understand the trend and qualitative information in easy manner Data driven decision-making is quite important as its importance lies in continuous data. In addition to this, the use of graphical representation also helpful to respondents to evaluate the asked question and deliver their better respondent data for the research paper. If the data is properly represented then it will resolve the possible issues which might be encounter by the reader in the general research paper. The charts, diagram and other sort of tables divulge the trends of the complicated set of data which assists readers to understand in the one glimpse. Further, it also enable the organisations to create new emerging opportunities, generation of more revenue, optimising the current efforts and finally produce deeper insight for action. Data analysis in this situation helps to identify the gap occurring in real time, finance, marketing, sales, product development, and other several processes. The strategic decision making is highly dependent upon the right flow of information.

However, presentation of the data has been used and divulged as below

PARTICUALR CB1CB2CB3CB4CB5CB6TotalLowestHighestAverage
Re-Entry Required due to system errors5814033592228.888919246.44444
Total Transactions14421199.11113601897106515088471.111106518971481
Transaction Block292427382130169.4222212925.07
Transactions per module (6 modules)
Daily Revenue$1,000.00$1,300.00$1,600.00$1,900.00$2,200.00$1,800.00

 The above given data shows the daily revenue and transaction cost and b lock cost which has been divulged with the use of excel based table. However, the graphical presentation of the daily revenue and other parts have been given for the better understanding of gathered data 

reentry required due to system error

total transactions

If the data gathered is revealed in right manner such as with the implication of excel based formula and right computations then the strategic decisions taken would also be effective. For instance, if the computed data or shown data is wrong then the decision making based on the gathered data will also be wrong which will eventually result to the high loss to the organization. 


After assessing all the details, it has been found that the representation of the data is imperative to make the easy and effective decision making. For instance, use of the graphs and other charts is helpful to strengthen the analysis of the data gathered and helps reader to understand the proper trend and qualitative information in easy manner. The graphical presentation and use of excel formula interlink with each other and helps users to understand each and every cell and their interlinked with the particular set of data. Now in the end, it could be inferred that if the data is too much complicated then the use of excel formula based sheet is helpful to interlink the cells with each other and by using the graphs and charts the data interpretation is made easy.