代做BISM7221 Information Systems Control, Governance and Audit SEMESTER 1 2024代写Python编程

BISM7221 Information Systems Control, Governance and Audit

Grocers 2 Go Assignment Specification

SEMESTER 1 2024

Purpose

This document provides the Assignment Specification for the assessment item “Business Consulting Report (IS Recommendations)” . You should refer to the associated Assessment Guideline for the Marking Rubric to understand how this assignment will be assessed.

Note: this assignment is an individual assignment and will be electronically submitted. You may discuss (but not collaborate on) the assignment with your peers. The work you submit should be yours, and yours alone.

Note that this task has been designed to be challenging, authentic and complex.  Whilst

students may use AI technologies, successful completion of assessment in this course will require students to critically engage in specific contexts and tasks for which artificial intelligence will provide only limited support and guidance.

A failure to reference generative AI use may constitute student misconduct under the Student Code of Conduct.

To pass this assessment, students will be required to demonstrate detailed comprehension of their written submission independent of AI tools.

About Grocers 2 Go

Grocers 2 Go (G2G) is an online (24/7) supplier of groceries to customers in Brisbane, Queensland. They take online orders for groceries and guarantee that all the groceries ordered will be delivered the next day – or the client will receive $2 off for every item ordered that is not received, and $2 off for each day the delivery is late. G2G’s offices are located in Brisbane, near the Rocklea Markets.

This guarantee is branded as “All there, on time – the G2G $2 Guarantee!” Customers can order any time of the day or night on any day of the week, and this guarantee will be honoured.

The business has done very well over recent years.  Even after the recent COVID pandemic, many   people have ordered from G2G due to its convenience and their guarantee. The company’s success was only paused a bit in 2022 during the devastating Brisbane floods that flooded the G2G headquarters at the Rocklea Markets near the Oxley Flood Plain, Rocklea, Brisbane.

G2G’s board of directors has retained you to provide them with consulting advice for improvements to their IT governance and IT operations, as well as to undertake a fraud assessment.

As part of your brief, you are therefore to consider how IT governance can be improved at G2G as well as consider operational and fraud issues as part of your IS audit role. You are developing a Consulting Report that will be provided to the Board of G2G.

G2G is customer-focused and G2G always aims to improve the customer experience. The G2G $2 Guarantee is a cornerstone of that relationship with clients. The $2 guarantee is calculated automatically.  Clients do not need to ask to receive the guarantee.

Despite their success, G2G knows that it needs to improve its bottom line – over the past several years it has been difficult to contain costs. G2G's directors are concerned that despite strong demand and activity, their profitability has not improved and, if anything, it has gotten worse.

You, as a consultant, are here to help G2G become both more effective and efficient.

You are provided with a SQL data file with system information in it for your analysis as part of your

review. You are also provided with case notes relating to discussions with key players in this scenario.

You need this information to answer the Guiding Questions in your Consulting Report, which are at

the end of this Specification.

Assignment Files

The data files for this assignment are located on Learn.UQ with this Case Description. This is a PostgreSQL backup file for uploading via DBeaver’s ‘Restore’ function.

A video is provided on Blackboard with details on using this file.  You will need to ensure that the connection is set to your server and that the database is connected to your own already-created   database (suggested:  'g2g') before running this script.

When you run this script, you will be presented with the below information. Check that your database passes all tests.

10	-----ACCOUNTS RECEIVABLE SYSTEM-----			TEST RESULT
20	customer	1636	1636	PASS
30	-----PAYROLL SYSTEM-----			TEST RESULT
40	employee	133	133	PASS
50	ft_salaries	26	26	PASS
60	payroll	52	52	PASS
70	payroll_detail	4585	4585	PASS
80	pt_hourlyrates	4	4	PASS
90	standard_hours_log	150	150	PASS
100	status_lookup	2	2	PASS
110	tax_rates	4	4	PASS
120	-----ACCOUNTS PAYABLE SYSTEM-----			TEST RESULT
130	payment_made	1417	1417	PASS
140	vendor	149	149	PASS
150	vendor_invoice	1476	1476	PASS
160	-----G2G GUARANTEE SYSTEM-----			TEST RESULT
170	g2g_guarantee	75191	75191	PASS

This PostgreSQL Backup File provides you with data for the year 2023 on it – this data is to be analysed by you as part of your consulting report.

Case Description

You are provided with the data files (described above) by Sarah Everett, the Database Administrator at G2g.

You are able to identify the information below from discussions with the managers of each department.

You are scheduled to have a deep discussion with Jessica Wulf – the Chief Executive Officer – once you have obtained the information below (see ‘Consulting with Clients over Coffee’).

IT Services

G2G has several key information systems. These systems manage their accounts receivable (customers and debtors), accounts payable (suppliers/vendors), payroll, and the G2G Guarantee. These information systems are all legacy systems developed a long time ago for G2G, and the     owners are determined to recoup their investment by keeping with those systems.

There is an IT support department. There are 7 people currently employed in the G2G IT department. The IT Manager is Ethan Hsu, and he prides himself on running a tightly knit team. Jenny Brown is the software developer (all software is written in a combination of Visual Cobol, Python, and APLX) and 2 software maintenance staff, as well as database administrator (DBA), Sarah Everett. There are also two IT support maintenance personnel. The team works as one when G2G is busy with projects, and all members of the team pitch in to complete work. Ethan oversees the IT team but lets them do   their work as they see fit; he relies on Sarah as his assistant manager.

Legacy systems are written in Visual Cobol and APLX and are the back-office systems that implement the website transactions.

The software developer, Jenny Brown, is a relatively new hire, and she develops software principally in Python, though has a working knowledge of Visual Cobol and no understanding of APLX at all (who does?). To address the gap, Herbert Geschwitz – the recently retired software developer – is retained on a contract of $5,000 per annum to maintain the software code for the legacy systems. This usually  takes about one day a week. Herbert helped build the original systems – the Accounts Receivable,

Payroll, Accounts Payable and the G2G Guarantee Payroll systems – back in 1983 when G2G was first starting out. The IT team held a retirement BBQ for Herb where he received a $50 JB HiFi voucher and a novelty ‘World’s Most Awesome Programmer’ coffee cup.

All team members are agile and flexible and ensure that the work is done as required. For example, Helen Abbot is in an IT support role, but has a software development background and regularly works on maintaining and updating the payroll system. All software development and maintenance staff work on the system to ensure that the important applications – like the G2G Guarantee system – continue to provide G2G with a competitive advantage.

Sarah Everett is the DBA at G2G, and she helped Herbert with building and maintaining the original systems when G2G went online. This was back in 2002 through her consulting company, EaZi

Everettz. She was later hired by G2G directly and continues to help build and maintain systems at G2G as well.

Sarah seems devoted to G2G and rarely – if ever – takes holidays.

Ethan Hsu really relies on Sarah and is sorry that due to cost-cutting Sarah's salary – which used to be relatively high, as she gave up her consulting career to work for G2G – has been reduced.

Although ostensibly Sarah's hours were reduced, Ethan knows that Sarah's hours have not really changed much at all.

IT Governance

G2G is a relatively small company with around 120-150 full time and casual employees.  Jessica Wulf is the Chief Executive Officer, and she makes all decisions. Katrina Lin is the Chief Financial Officer,   and Alli Nueundorf runs the sales team as Sales Manager.

G2G does not have an IT Steering Committee (Jessica says that "it's only another waste of time – besides, it's IT. Not what we do around here – it’s about the groceries!"). Jessica believes that she knows whether a project is worth funding 'just by looking at it' and besides, ‘business cases are all horse-hockey – not worth the laser printer ink it is printed with’ .

Instead, Ethan Hsu prepares the IT Budget each year based on the age of the equipment in place, and this budget is approved by the Executive Team of Jessica, Katrina, and Alli.

Once a year, Ethan attends the Strategy Day with the Executive Team; Ethan really likes the muffins   that he gets through that process. Every strategy day, Ethan asks for a budget to remove the creaky,   old information systems that were developed by Herbert, but Jessica is adamant that she wants to get her money's worth out of G2G's IT.

Physical Infrastructure, Disaster Recovery and Data Storage

G2G has its Data Centre in the basement of the Oxley Little Nippers Aquatic Centre. This is a building on the edge of the Oxley Flood Plain with a magnificent view of Oxley Creek. After the Brisbane flood  of 2011, this building was upgraded to a magnificent standard, and with the most recent floods in 2022 it was only out of action for a week. Jessica is pretty sure that will never happen again.

There is one UPS (Uninterruptible Power Supply) unit in the server room that is sufficient to power the data centre for six hours in the event of unexpected power outages. There are two air conditioning units in the data centre, which is located in the basement. It is powered down after-hours and on weekends.

Biometric controls lock the room. All members of the senior leadership team and the IT Team have  access to the data centre, as well as Alice Sloan, the G2G receptionist. Alice maintains the security logs for the data centre.

The data centre runs the servers for the information systems used by G2G. These run a combination of Linux (Mandrake Corporate Server 3, Linux 2.6.3) and Windows 2000. All information systems are now built on PostgreSQL Version 7; they were originally developed using the Ingres database management system.

With a laugh, Sarah notes that she refuses to upgrade because it would break all the information systems developed for G2G and, if it isn't broken, there is no need to try and 'fix it'.

All corporate files, however, are hosted on Dropbox Business. G2G uses Office 365 and Dropbox to  manage its corporate files and no backups of these files are taken as Dropbox has versioning of files for 90 days and Ethan says that it’s ‘too bad’ if someone needs a  file that is older than 90 days.

The custom-built accounts receivable, accounts payable, payroll and G2G Guarantee systems are automatically zipped each day and stored as an unencrypted file on OneDrive.

The business continuity plan (BCP) is maintained by Ethan Hsu. It was last updated two years ago when the new data centre in the Aquatic Centre's basement was constructed. Ethan regularly tests the BCP by sending a multiple-choice quiz to all staff members on what to do in the case of emergency.

Accounts Receivable System

Accounts receivable is the system that records how much customers owe G2G. This is a custom-built system. It has relationships with the Grocers 2 Go Guarantee System – which is where customers are refunded their G2G Guarantee.

In the data files, you are only provided with the 'customer' table. This system contains information on

customers, their credit limit and the amount owed by customers. A credit limit is provided to

customers as many customers have found themselves unemployed during the pandemic.

However, Jessica tells you that absolutely no customer is allowed to exceed their credit limits. She

receives regular reports on credit limits from the custom system and maintains a close eye on this

information. To ensure separation of duties, Alice Sloan, the receptionist, prints these regular reports and provides them to Jessica.

Accounts Payable System

Accounts payable is the system that records the money that G2G owes to its creditors. Again, this is a custom-built system. It has relationships with the payroll system – particularly in relation to the tasks undertaken by different employees in the Finance department. Creditors (vendors) are only paid by finance officers.

In the data files, you are provided with several tables.

The 'vendor' table records details about vendors, including the company name, address, any notes about the vendor, and the amount that G2G owes to the vendor. The amount owed to vendors is the   total of all amounts owed on each vendor's invoices (i.e., the ‘amount_owing’ field in vendor_invoice). Vendor invoices are recorded in the 'vendor_invoice' table. It records any invoices received from

vendors, including the amount of the invoice, the date the invoice was issued, and how much is left owing on the invoice. When the invoice is paid, the 'paid flag' field is set to 'Y'.

Payments are recorded in the 'payment_made' table; this table records the date the payment was

made, the amount paid on the invoice, and the related vendor and invoice. It also records the finance officer that processed the payment in the ‘finance_officer’ field, which stores the employee’s

employee number from the employee table. Only finance officers process these payments.

The payment_made table relates to the vendor, vendor_invoice, and employee tables. When a

payment is made, the amount_owing field in the vendor_invoice table is updated so that the amount owing in vendor_invoice matches the amount of the invoice less payments made as recorded in the payments_made table.

Katrina Lin advises that – due to the company's worsening cash position – as Chief Financial Officer she has been making sure that invoices are paid in full only when the terms (the number of days

allowed before the invoice becomes overdue) have been fully utilised; she admits that, occasionally, some invoices are paid later than that as cashflow is particularly poor right now.

This means that sometimes, invoices are only half-paid after 14 days, due to the worsening cash position but Katrina is adamant that all invoices are paid within two months.

Payroll System

Payroll is the system that records money paid by G2G to its employees. Again, this is a custom-built system, and it has relationships particularly with the Accounts Payable system (where the employee number of the finance officers that make payments to vendors is recorded).

In the data files, you are provided with several tables.

The 'employee' table is the centre of the system. This records all employees, their home address, the type of job they hold and whether they are full time or part time. The employee's next of kin is also identified. The employee table is related to the 'job' table, the 'status_lookup' table, the 'ft_salaries' table, the 'payroll' table, the 'standard_hours_log' table, and the ‘payments_made’ table.

The 'job' table provides a unique identifier for each job held at G2G. There are fourteen different types of job, including part-time and casual jobs. This table simply provides a description for each job. The    job table relates to the employee table, the ft_salaries table, and the pt_hourlyrates table.

The 'status_lookup' table simply describes the status code set out in the employee table, to which it

relates. Employees are either full time (and do not require standard hours to be recorded) or part time (and so standard hours are recorded).

The 'ft_salaries' table provides an historical listing of the salaries paid to full-time positions. When the salary changes for specific jobs, an extra row is added to the ft_salaries table together with the start   date. For example, job 11 – 'Database Administrator', the position held by Sarah Everett – started the year on $90,000 annual salary, and this was reduced to $80,000 on 1st  July. This information is used  to determine the total amount paid to individuals in the payroll detail table by dividing the annual

salary by 52.18 (this calculation turns salary figures into equivalent weekly payments – after allowing for leap years).

Similarly, the 'pt_hourly' rates table records changes to the hourly payments made to part-time

employees. Again, this table records the date of a rate change for each role. These roles only receive pay rises twice a year – once in January, and once in July.

As with the 'ft_salaries' and 'pt_hourly' tables, the 'standard_hours_log' table records changes to

standard hours for each employee. The most recent value in the standard_hours_log table matches to the current_standard_hours field in the employee table.

The payroll table summarises the details of all pay runs. Note that the ft_salaries, pt_hourly, and the standard_hours_log tables are applied as at the date of each payroll – so for example, if a salary is  changed on or before the date of the payroll, then that salary applies according to the rules in the

ft_salaries table. The payroll table records the payroll number, the date of the payment, the amount paid to all employees after tax, the tax paid, and the total salaries in that pay run. These amounts    summate their respective fields in the 'payroll_details' table. The payroll table relates only to the

payroll_detail table.

The 'payroll_detail' table records the net payment, taxation withheld, and the total payment made to each individual employee. The sum of the three figures recorded (net payment, taxation, and total

payment) equate to the respective value in the payroll table for each pay run (i.e., total_net, total_tax, and total_salaries in the payroll table respectively). Note that net payment is the total payment less     the taxation amount.

This table relates to the employee table, the status_lookup table, and the payroll table.

The taxation amount is determined by reference to the tax_rates table. This is a progressive tax

system. For example, a person earning $1,000 in a week will pay 0% tax on earnings up to $348.79,   then 19% tax on earnings between $348.79 to $862.42, and 33% tax on earnings exceeding $862.42  (all the way up to the next threshold). For a $1,000 payment, therefore, an employee would pay 0% of

$348.79, 19% of ($862.42 - $348.79), and 33% of ($1,000 - $862.42) = 0 + $97.59 + $45.40 = $142.99 in taxation.

Each week Katrina Lin asks Alice Sloan, the receptionist, to prepare the report for the payroll. Katrina then signs off on the payment made. Katrina asks Alice to do this so that the reporting duties are kept separate from the transaction recording duties of the finance officers working with Katrina.

Grocers 2 Go Guarantee System

The G2G system is another bespoke (custom) system at G2G. This system tracks all guarantees paid out to customers, underpins G2G’s slogan of “All there, on time – the G2G $2 Guarantee!”  G2G is

rightly proud of its customer relationships and uses the G2G Guarantee to cement that relationship through good, valuable service.

In the data files, you are provided with a single table, ‘g2g_guarantee’ .

This table identifies the order date and value of each delivery, how many items are not delivered even when ordered, and the number of days the delivery is late. The guarantee is recorded based on the     number of items not delivered and the number of days that the delivery is late.

Note that drivers pick items from the shelves in the warehouse and organise their own deliveries each day. Each week, the driver that delivers the greatest number of deliveries is given a $50 voucher to     spend on groceries as a performance incentive.

The guarantee is payable if items are not delivered, or the delivery is late. The guarantee refund is $2 per item not delivered and $2 per day late. It is calculated and stored in the guarantee field of the

g2g_guarantee table.

Alli Neuendorf, G2G's Sales Manager, tells you that – obviously – this guarantee has the potential to be expensive, which is why G2G focusses on delivering on its core promise to its customers.

Consulting with Clients over Coffee

You seek a detailed discussion with Jessica Wulf, the CEO.  You are to seek out Alice Sloan, the receptionist, when you want to speak to Jessica Wulf.