Information Management – Assignment 1

Assessment Weighting: 25% of the course total

Total marks: 64 marks

Question 1 Due date: 10^th Oct. 2024

Question 2 Due date: 25^th Oct. 2024

Must be done individually

Must submit the following:

· Submit your answer sheet (paper copy) in college’s submission box and 

· Submit a digitised copy (e.g. pdf, word format), excel via SOUL account. Don’t email your assignment to your lecturer.

Penalty details:

Question 1 (12 marks) (write Excel formula only)

Convert the following problems from their English expressions to Excel formulas that allow Excel to calculate the answers. Refer to the worksheet ‘Sales April 2006’.

John Tam is the boss of NewBest, an electrical appliance shop in Mongkok. Five different products are sold in NewBest: FAN, OVEN, MicroWave Oven, Fridge and TV. John wants to see the sales, net profit, gross margin and operating expense totals for each product line in April 2006.

· The sales figures for the 5 products are given in cells C2 to G2. Write an Excel formula in cell B2 to calculate the total sales for all 5 products.

· The cost of goods sold figures for the 5 products are given in cells C3 to G3. Write an Excel formula in cell B3 to calculate the total cost of goods sold for all 5 products.

· Given that gross margin = sales – cost of goods sold. Write Excel formulas in cells C4 to G4 to calculate the gross margin for each of the five products.

· Write an Excel formula in cell B4 to calculate the total gross margin for all five products.

Each product of NewBest has the following operating expenses: Salaries & Commissions, Electricity, Media, Roll Show, Shop rental and Delivery. The amount for each of these operating expenses on each of the five products are given in cells C7 to G12.

· Write Excel formulas in cells B7 to B12 to calculate each operating expense total for all five products.

· Write Excel formulas in cells C13 to G13 to calculate the total expenses for each product. Also write the Excel formula in cell B13 for the total operating expenses for all products.

· Given that net profit = gross margin – total expense. Write Excel formulas to calculate the net profit in cells B14 to G14.

· Given that net profit as % of sales = net profit / sale * 100%. Write Excel formulas to calculate the net profit as % of sales in cells B15 to G15. (Assume that you cannot use Excel cell formatting function, you need to multiply 100.)

Worksheet: ‘Sales April 2006’

Based on the worksheet “Sales April 2006” and the Table 1 below, John wants to

forecast the net profits of OVEN and TV in May 2006. Please complete

the worksheet “Forecast May 2006” with Excel formulas assuming that all the operating expenses items are 

unchanged, and only “Sales” will increase/decrease – see table 1 below:

Table 1:

Worksheet: ‘Forecast May 2006’

In ‘Sales April 2006’ worksheet, how many formulae have you typed? How many formulae have you copied and pasted from other formulae?    [1.4 marks]

In ‘Forecast May 2006’ worksheet, how many formulae have you typed? How many formulae have you copied and pasted from other formulae?    [4 marks]

Important notes: 

· For those cells in worksheets ‘Sales’ that express % figures, multiply the formula by 100. Don’t use Excel’s Format Cell function - Percentage Number.

Question 2 (52 marks) 

Customers of ‘XXX’ supermarket often complain about the long queues at the check-out cashier counters, and the management of ‘XXX’ is worried about losing customers to its competitors. To cut down customers’ queuing time, streamline/increase staff productivity and cut down on labour costs, CEO of ‘XXX’ is going to develop new self-checkout counters. The following scenario describes the activities or operations for the ideal self-checkout systems:

The customer brings the items to be bought to the front self-checkout machine and starts scanning the barcode on each item one by one. Each time an item’s barcode is successfully scanned and read by the machine, the item’s name (and its brief description), selling price of the item with any discounts are displayed on the panel screen. The subtotal price of all items barcodes that have been scanned so far will also be displayed on the panel screen. When the customer finishes scanning all items to be bought, the customer selects the payment options which can be cash or credit card. For cash payment, customers insert into the machine the necessary amount of money displayed on the panel screen, the system displays the amount of cash that has been inserted so far by the customer. The system will give out the receipt when full payment is given and money changes (if any). For credit payments, customers scan the credit card on the card reader. If the bank of corresponding credit card confirms the payment, a receipt will be given out. Once receipts have been given out to customers, the inventory will be updated. System will time out after a period of inactivity, and customers can cancel the incomplete transaction before payment is done anytime.

From the above description, draw a data flow diagram showing the flow of input information (and/or data) and output information (and/or data) to and from processes and database stores within this self-checkout system and any external environmental elements that interact with this system. You MUST use SSADM notation – see notes on data flow diagrams.

In this diagram, you must identify the following:

(a) processes inside this self-checkout system,           (24 marks)    

(b) database stores, external environmental elements that interact with any of the processes identified in (a)                 (11 marks)

(c) input and output information (or data) to and from each of the processes, database stores and external environmental elements identified in (a) and (b),             (17 marks)                                         

(Hint: there are 2 environmental elements that interact with this self-checkout, and SIX main processes in this self-checkout system)