SWIN BUR · NE * SWINBURNE UNIVERSITY OF TECHNOLOGY Swinburne University INF10003 - Assignment 3 Outline Group Approx. 3000 +/- 10% words Weighting: 40% Due date: See Unit Schedule Assignment 3 submission details · All pages should include all team members names and student numbers in the document footer . All references should be appropriately cited using the Harvard style · Submit your assignment as either file format: Microsoft Word document and macro enabled Excel document · Save your files as follows: [student number_A3.filetype] e.g. 1234567_A3.doc · Submit your assignment via the Canvas Link > Assignments> Assessment 3 Assignment Specifications The primary aim of this assessment is to understand the effect and impact that business processes, complimentary technologies and information systems have on an organisation. This means that you need to discuss how the organisation within the case study will use your proposed solutions to gain an additional advantage over its competitors. There is an expectation that your submission will be analytical, and will draw on theory to frame responses to tasks. Essential Resources Please make sure that you download the following files from the unit website. Required files: · UMC_case_study.pdf (case study) · UMC_W32_data_2018.xlsx · Process model.pptx Overview: (read the case study) The general manager of UMC would like to have a clearer understanding before these changes are implemented so he has asked you to submit a business report (with academic references) with the following information: - Complete the spreadsheet with the appropriate formulas (refer Task A) - Within the spreadsheet, create three (3) graphs (refer Task A) - Complete one (1) process models depicting the proposed 'To-Be' processes (refer Task B) - Develop a prototype mobile application with 4-8 mock-up designs (refer Task C) - Answering a series of general questions regarding the business and its journey towards a more technological business environment. (refer Task D) Task A: EXCEL for Business Intelligence UMC wants to evaluate the sales statistics for the past year and the current year to predict future sales. The general manager knows enough about Excel to realise it could be a useful tool so he has asked you to create a Microsoft Excel prototype which lists the milk product types with the client ordering details and the revenues collected. The partly completed spreadsheet from a random average week (week 32) is available as a separate attachment. Sales data is collected on a 52 week cycle. Spreadsheet Requirements: Complete the spreadsheet by using the correct formulas to fill in the missing data. 1. In the "Ordering Raw Data" table complete the "Equiv Litre per Crate/Carton" column In the "Ordering Raw Data" table complete the "Supplier Price per unit" and "Retail Sale price per unit" columns. 2. In the "Milk Products" table complete the "Supplier Price per unit" column. 3. In the "Customer Order (week32) review" table complete the "Total Orders 2017 (W32)". 4. Complete the milk production table The formulas required to reveal the missing data include Vlookup, Countif and Sum. Absolute addressing is also required.