Question 1

Below LEVEL 0 Logical Data Flow Diagram (DFD) depicts activities at MelbVic College. Using this figure as a reference, identify the following elements in the context diagram of the same process system for MelbVic College:

(i)         The system or process

(ii)        All the external entities (data sources and data destinations)

(iii)       All the data inflow(s) into the system

(iv)       All the data outflows(s) from the system

(i)       System/Process: MelbVic College’s Course Registration System

(ii)      External entities: Student (DS/DD), Instructor (DD)

(iii)     Data inflow: Registration details

(iv)     Data outflows: Fee notice, prerequisite notice, course closed notice, student acceptance notice,

course enrolment report

Question 2

Refer to the Excel workbook below and answer the questions that follow without the use of computer. You may refer to the Formulae List on Moodle.

Required:

1)    Write a formula in cell B3 that shows the name associated with the employee number entered in cell B2. Employee details are stored in the Employee Table.

B3: =VLOOKUP(B2, $A$40:$E$42, 4, FALSE)

2)    Construct a formula in cell E8 that will return the unit price for the product no in cell C8. This formula must be copied to cells E9:E16 without any modification.

E8: =VLOOKUP(C8, $B$32:$E$36, 4, FALSE)

3)    Cell A1 is a dynamic label based on Employee First Name. Show the formula that should appear in cell A1 to produce the dynamic label.

A1: ="Analysis Performed by " & B3

4)    Construct a formula that can be used to calculate the values in cells B22:B26. The formula in cell B22 must be constructed such that it can be copied to cells B23:B26 without any modification.

B22: =SUMIFS($D$8:$D$16,$C$8:$C$16,A22,$F$8:$F$16,$B$20)

5)    Construct a formula that can be used to calculate the values in cells C22:C26. The formula in cell C22 must be constructed such that it can be copied to cells C23:C26 without any modification.

C22: =SUMIFS($G$8:$G$16,$C$8:$C$16,A22,$F$8:$F$16,$B$20)

6)    Construct a formula that can be used to calculate the values in cells F23:C26. The formula in cell F23 must be constructed such that it can be copied to cells F24:F26 without any modification.

F23: =COUNTIFS($F$8:$F$16, E23, $G$8:$G$16, "<"&$F$21)

7)    Construct a formula that can be used to calculate the values in cells G23:G26. The formula in cell G23 must be constructed such that it can be copied to cells G24:G26 without any modification.

G23: =SUMIFS($G$8:$G$16, $F$8:$F$16, E23, $G$8:$G$16, "<" & $F$21)

8)    Construct a formula to go in cell B32 to parse the product no from the PdtNo- Warehouse-Description column in the Product Table such that it can be copied to cells B33:B36 without modification.

B32: =LEFT(A32, 3)

9)    Construct a formula to go in cell C32 to parse the warehouse from the PdtNo- Warehouse-Description column in the Product Table such that it can be copied to cells C33:C36 without modification.

C32: =MID(A32, 5, 3)

10)  Construct a formula to go in cell D32 to parse the product description from the PdtNo- Warehouse- Description column  in  the  Product  Table  such  that  it  can  be  copied  to  cells  D33:D36  without modification.

D32: =RIGHT(A32, LEN(A32) - 8)

11)  Construct a formula to go in cell C40 to parse the employee last name from the Employee_FullName column in the Employee Table such that it can be copied to cells C41:C42 without modification.

C40: =LEFT(B40, FIND(",",B40) - 1)

12)  Construct a formula to go in cell D40 to parse the employee first name from the Employee_FullName column in the Employee Table such that it can be copied to cells D41:D42 without modification.

D40: =RIGHT(B40, LEN(B40) - FIND(",", B40) - 1)

13)  Construct a conditional function in cell G20 . This formula must display:

• Gold when the total invoice amount for the filtered customer (cell B20) is greater than or equal to 50% of the total invoice amount for September (cell G17);

• Silver when the total invoice amount for the filtered customer (cell B20) is greater than or equal to 25% of the total invoice amount for September (cell G17);

• Bronze when the total invoice amount for the filtered customer (cell B20) is less than 25% of the total invoice amount for September (cell G17);

G20: =IF(C27>=0.5*G17,"Gold", IF(C27>=0.25*G17,"Silver", "Bronze"))     OR

G20: =IFS(C27>=0.5*G17, "Gold", C27>=0.25*G17, "Silver", TRUE, "Bronze")

Question 3 (Adapted from Romney et. al., 2013)

Harry Sleeze is the IT procurement manager for Indigo International. The company wants to develop a new information system and is looking at the following options: outsource, buy off-the-shelf or develop in-house. Harry has been provided with a request for a proposal that details the system’s requirements. As someone who has a lot of connections in the industry, he decides to ask some of his ‘mates’ to quote for outsourcing or supplying a turnkey system that will be compared with the in-house development proposal. Harry has seen this proposal and knows the cost of an in-house development.

REQUIRED:

Using the Stakeholder Analysis Framework, analyse the ethical dilemma(s) confronting  Harry Sleeze and arrive at an ethical decision.