Objectives and organisation of tutorials:

.    Students will complete excel activities (including watching LinkedIn Learning videos) and case study readings prior to the tutorial

.    Students will work together to confirm their understanding of excel topics

.    Tutors will lead discussions on theoretical topics using the case study readings

.    Students will participate in quiz sessions to help reinforce learning. These quizzes are not assessed but the highest-ranked students will receive a prize at the end of the semester.

.    Students are strongly encouraged to attend all tutorials and take comprehensive notes. In   cases where a student cannot attend a particular tutorial, the student can complete the pre- tutorial activities on their own, watch the recordings, or follow up with the tutor during

consultation sessions.

Information Systems theory

1)   Read the case study ‘Maersk’s TradeLens: Digitalizing the global supply chain’ on Page 483- 485 of the course text. Answer the four questions:

1a. Assess the importance of the TradeLens project for Maersk

1b. What core problem-solving steps has Maersk gone through in developing the new system?

1c. Describe the problems encountered by the TradeLens project. What management, organization, or technology factors were responsible for these problems?

1d. What has Maersk done to mitigate the problems of the TradeLens project?

2)   Consider what type of activities does the Waterfall approach suit, and what types of information systems development does it suit?

•    Building a house?

•    Building an aeroplane?

•    Developing a mobile game?

•    Developing a payroll system?

•    An information system where time and money are not important, like an information system related to health, welfare etc.?

•    An information system where well defined and strict standards and requirements have to be followed? (e.g., a government reporting system).

•    What about an information system mandated by the government with a fixed budget and no scope for requirements change?

Excel: IF, OR, AND, functions

Watch the following Excel videos (click on the links).

●   Exploring IF logical tests and using relational operators(5 minutes 46 seconds)

●   Creating and expanding the use of nested IFs(5 minutes 43 seconds)

●    Using the AND, OR, and NOT functions with IF to create compound logical tests(6 minutes 54 seconds)

●     Watch three Excel videos on (IF and Nested IF) available on Blackboard/Learning Resources/Week 6/Tutorial 5 resources (total 25 minutes)

Note: ‘Optional’ videos are not assessed, but they are useful for the development of your Excel skills.

Files for this tutorial:

●   CH 3 IF.xlsx (used only for the LinkedIn videos)

●    IF_AND_OR_FUNCTIONS.XLSX (this file is only available on Blackboard and is used for the exercises below)

The files are available on Blackboard (under Learning Resources > Week 5 > Tutorial Resources).

Excel work & questions (file “IF_AND_OR_FUNCTIONS.XLSX” workbook)

1.  GRADES SHEET worksheet has the design shown below.

●     In the worksheet “Grades Sheet” we need a formula in E12 (for the sum of the assessment items) and in F12 to calculate the actual grade.  Using the Student Grade Allocation Table use the IF function to

display the student grade in cell F12.

Consider the syntax of an IF statement:

IF(condition_for_testing, result  if  test_true, result  if  test_false)

Excel syntax: IF(logical_test, value  if true, value  if  false)

How can we use the IF function to display the student grade in cell F12?

●     We must firstly understand the “Table” – this is a policy for allocating a grade for each overall mark in each course.  Notice that we have deliberately built our table from the ‘top down’, starting with a grade of 7 and then working down.  This will help us to correctly design the logic for our nested IF formula.   Then move on to the next stage of the formula.

TIP: Develop the nested IF formula incrementally – as demonstrated in the relevant videos.  When you have developed one part of the formula, carefully look at the results and check their accuracy. In other words, you need more than one IF.

●     When you have finished the full formula, check that you understand the overall design.  You will finish with several IF functions.  Understand how they relate to each other.  For example, counting the IF

functions from left to right – what is the false argument of the first IF function.  What is the false argument of the 2nd  IF function, and so on?

2.  CAKE SHOP worksheet has the design shown below.  We want to automate the three business rules shown in the file.  We have a list of customers’ children as shown – with their birth dates (you will need to format

the birthday data accordingly).

●     Business Rule 1: If the child is born after 2010 (i.e. on or after 1/1/2010) we want to send a small gift.

We want a formula in D4:D9 to write either YES (i.e. child is born after 2010) or NO (i.e. child is

older).  For the first part of the problem (i.e., BUSINESS RULE 1) we are looking to compare dates but only the YEAR component.  We can use the built-in function YEAR for this purpose.  Quickly

research the YEAR function via the Insert Function operator – it is very simple and very useful.  We

also use the correct relational operator for this (i.e. from the set >, <, >=, <=).  We need to know these and how/when to use them.  We enter the formula in cell D4 that compares the year of B4 (for John

Smith) to this cut-off date of 2010.  We want to be careful of the addressing in this formula (do we use RELATIVE, ABOSOLUTE or MIXED).  We then want to copy this formula down the cell range

D4:D9.  Check the results for correctness (always).

●     Business Rule 2:  If a child is born after 1/1/2010 AND that child is female, we want to send a gift.

We want an IF function in the range E4:E9 to achieve this.  We need to think logically here. We need to code this as an Excel function (say writing ‘SEND GIFT’ or ‘NO GIFT’ in the output cells E4:E9). Our logic will be as follows (expressed in English NOT Excel code):

IF D4 is YES and C4 is “F” then “SEND GIFT” else “NO GIFT”

Clearly, we want the IF function we have already developed – but we want to add the AND function to it (to cater for the two criteria of D4 being YES and C4 being F.  As we saw in the video, AND means  both tests must be true for the AND function to be true – it is very heavily used function in Excel.

Check out the AND function in Excel Help (press F1) or better still, via the Insert Function and look in the “Logical” categories – you will see AND, IF, IFERROR, OR, and others.  Now develop the

required formula.  This introduces us to a very important issue in terms of developing Excel functions  (not just IF functions).  We must make sure we correctly understand the business need and that we can then express the function in English – and then we develop the Excel code.  Make sure you understand what is happening in the functions we have developed so far.

●     Business Rule 3: Now we want to contrast this AND function with the OR function.  So try it – replace the AND with an OR – run the function – what result do we get?  Does the OR function implement our  business policy?  Describe the business policy it does implement?  Do any boys get a gift via your

Excel solution?  This is how we test Excel functions: we test to see if the function implements the business policy we want it to implement?

3.  BANK LOAN worksheet:  Now move to the Bank Loan worksheet.  Read following business policies and decide whether you would use and ‘AND’ or and ‘OR’ to implement them.  Enter the appropriate Excel

formula in cell B5 (of course, try one formula at a time for each of the 4 policies).

1.    Bank A lending policy: the customer must satisfy all of the following: savings of $5000, must have a permanent job, must be an Australian citizen. If the conditions are met then B5 should display

“APPROVED”, if the conditions are not met it should display “NOT APPROVED” .