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.
Spreadsheet Graphs:You are also required to create two (2) graphs.
1. Column chart: Show total orders by customers for all 3 years (2015-2018).
2. Column or Pie chart: By using the milk production data, create a chart showing illustrating these figures.
3. Create a Column or Pie chart: Your choice. This chart can reflect an element from any of the data tables that you feel will add value to the user.
All graphs must be dynamic to show visible changes instantly when the data is altered.
Task B: BUSINESS PROCESS MODELS
Produce one (1) process model (not hand drawn) using Swim Lane format from the details provided in the case study:
1. The first Swim Lane diagram ‘As-Is’ is provided. Use this model and the provided template to assist you in developing a second Swim Lane diagram that represents the proposed ‘To-Be’ application processes at UMC.
(Partly completed template will be provided)
Task C: The Mobile App
Develop a prototype mobile application (with 4-8 mock-up designs) that will allow the business to promote their milk products as well as allowing the business customers to complete a product order (including payments) . The app should also provide feedback to the business customers about the progress of their order and estimated time of delivery. Provide an overview of this app in your report including.
• Its purpose and links to the business functions.
• The alignment to design considerations including 3 design principles and 3 usability guidelines.
• The expected benefits, including plausible (researched) increases in business and efficiency gains.
Task D: General Questions.
1. What is the role of business process modelling for organisations such as UMC? What are the benefits of providing an As-Is model and a To-Be model to the General Manager. Include your Swim Lane Diagram (from Task B) as part of your explanation
2. Broadly explain the importance of information in organisations like UMC and provide an example of how Information Technology can be a useful tool for attracting new customers and gathering information. Provide some recommendations on how UMC could be modernised. Consider network improvements and cloud services as a part of your recommendations.
3. To promote the use of the new ordering system. UMC will need to develop a mobile compliant version of the new ordering system as either a native stand-alone app or as a mobile webpage app. Provide the general manager with a valid argument for the preferred method.
4. Discuss the differences between Web analytics, Social Network Analysis and Customer Relationship Management, including outlining the environment from which the information is collect from, the type of information it collects and the benefit from the information collected.
5. As UMC will be collecting information (personal and financial) from its clients, outline the privacy laws and cybersecurity measures that the company will need to consider to assure the customer that their information is safe and secure.