To be ready to optimise the company's data using Microsoft Excel, you need to perform the following tasks by applying the functions that have been practised in the workshops. You are expected to apply learning from the workshop sessions to the case organisation: . Clean up the CUSTOMER worksheet of the given data set. First find blank cell and fill them with a meaningful value, then remove extra spaces, and highlight the duplicated values in the Customer_ID column. (5 marks) Considering different types of cell references, calculate total price with insurance, and return customer price in the CAR worksheet. Please use 'total price with insurance column to calculate the return customer price instead of 'price per day' column. (6 marks) Use functions, formulas, and cell references to calculate all the values in the total sale and average columns in the AGENT worksheet. (4 marks) Create a combo chart to present total sales and average sales by each agent. Save it in a new worksheet. The chart should have a meaningful chart title, a legend, and appropriate axes labels. Use two formatting tools to format the chart to make it pleasant to view and easy to understand. (10 marks) Calculate the agents' commissions in the AGENT worksheet. Use the given commission rate from the commission table. (15 marks) There is a special promotion for return customer in order to encourage sales. Please calculate monthly payment in the CAR worksheet. (10 marks) Use VLOOKUP/HLOOKUP functions in the BOOKING worksheet and identify Return_Customer, Car_Price, Discount_Rate and Total_Price. (15 marks) In order to develop a marketing campaign with a loyal customer focus, you need to find out what ratio of your customers are returning/existing. Therefore, create a Pivot Table and save it in a new worksheet. The goal of this PivotTable is to summarise data and calculate total sales by transaction date and customer type (return customer). Use 'Booking Date' as a column label and 'Returning?' as a row label. (15 marks) Use the subtotal function and calculate the total sales by customer type (Returning/New) Format the worksheet to highlight the total and the grand total row for returning customers. (15 marks) Present the numbers appropriately using comma, dollar sign, and 2-decimal place, where applicable. (5 marks) Save your work using the following format: Lastname_Firstname_Assignment1.xlsx Submit your assignment files to Blackboard. The penalty for late submission will be a reduction by 5% or one grade (e.g. B+ to B) per day up to a maximum of five days. An assessment item will not be marked if it is more than five days late. Please provide answers to the following questions. We are looking for deep/analytical answers (not just descriptions). Please do not write long report-format answers. Q1: Considering Porter's Competitive Forces model for Fonterra (https://www.fonterra.comz/en.html), evaluate if each force is a weak, moderate, or strong force, and why! Please provide enough justification for all five forces. (15 marks) Q2. Answer the following questions in relation to ASB bank (https://www.asb.co.nz) (5 marks) What are the security issues ASB might face? - provide at least 3 (2 marks) Which steps can ASB take to makesure its business is secured? - provide at least 3 (3 marks) . C Q3: Please draw the Entity-relationship diagram for a university in New Zealand. Consider the following requirements: (10 marks) at least 4 entities/tables; at least 4 attributes/fields for each entity: the primary key and foreign key (if applicable) for each entity/table; the types of relationships in the diagram (if applicable: try to avoid many-to-many relationship and replace it with a join-table). Provide a short explanation for each designed relationship Q4: Read about Fonterra (https://www.fonterra.comz/en.html) and answer the following questions (10 marks) What are the four types of systems which can be used for the different levels of management in a business? (2 marks) Explain how these four types of systems can be used in the context of Fonterra (8 marks) Q5. Read about Countdown and Pack&Save and answer the following questions (10 marks) . C What are the ebusiness strategies used by Countdown? - discuss at least 3 (3 marks) What are the ebusiness strategies used by Pack&Save? - discuss at least 1 (1 mark) How can Pack&Save improve its ebusiness strategies? (6 marks) See next page for marcking criteria To be ready to optimise the company's data using Microsoft Excel, you need to perform the following tasks by applying the functions that have been practised in the workshops. You are expected to apply learning from the workshop sessions to the case organisation: . Clean up the CUSTOMER worksheet of the given data set. First find blank cell and fill them with a meaningful value, then remove extra spaces, and highlight the duplicated values in the Customer_ID column. (5 marks) Considering different types of cell references, calculate total price with insurance, and return customer price in the CAR worksheet. Please use 'total price with insurance column to calculate the return customer price instead of 'price per day' column. (6 marks) Use functions, formulas, and cell references to calculate all the values in the total sale and average columns in the AGENT worksheet. (4 marks) Create a combo chart to present total sales and average sales by each agent. Save it in a new worksheet. The chart should have a meaningful chart title, a legend, and appropriate axes labels. Use two formatting tools to format the chart to make it pleasant to view and easy to understand. (10 marks) Calculate the agents' commissions in the AGENT worksheet. Use the given commission rate from the commission table. (15 marks) There is a special promotion for return customer in order to encourage sales. Please calculate monthly payment in the CAR worksheet. (10 marks) Use VLOOKUP/HLOOKUP functions in the BOOKING worksheet and identify Return_Customer, Car_Price, Discount_Rate and Total_Price. (15 marks) In order to develop a marketing campaign with a loyal customer focus, you need to find out what ratio of your customers are returning/existing. Therefore, create a Pivot Table and save it in a new worksheet. The goal of this PivotTable is to summarise data and calculate total sales by transaction date and customer type (return customer). Use 'Booking Date' as a column label and 'Returning?' as a row label. (15 marks) Use the subtotal function and calculate the total sales by customer type (Returning/New) Format the worksheet to highlight the total and the grand total row for returning customers. (15 marks) Present the numbers appropriately using comma, dollar sign, and 2-decimal place, where applicable. (5 marks) Save your work using the following format: Lastname_Firstname_Assignment1.xlsx Submit your assignment files to Blackboard. The penalty for late submission will be a reduction by 5% or one grade (e.g. B+ to B) per day up to a maximum of five days. An assessment item will not be marked if it is more than five days late. Please provide answers to the following questions. We are looking for deep/analytical answers (not just descriptions). Please do not write long report-format answers. Q1: Considering Porter's Competitive Forces model for Fonterra (https://www.fonterra.comz/en.html), evaluate if each force is a weak, moderate, or strong force, and why! Please provide enough justification for all five forces. (15 marks) Q2. Answer the following questions in relation to ASB bank (https://www.asb.co.nz) (5 marks) What are the security issues ASB might face? - provide at least 3 (2 marks) Which steps can ASB take to makesure its business is secured? - provide at least 3 (3 marks) . C Q3: Please draw the Entity-relationship diagram for a university in New Zealand. Consider the following requirements: (10 marks) at least 4 entities/tables; at least 4 attributes/fields for each entity: the primary key and foreign key (if applicable) for each entity/table; the types of relationships in the diagram (if applicable: try to avoid many-to-many relationship and replace it with a join-table). Provide a short explanation for each designed relationship Q4: Read about Fonterra (https://www.fonterra.comz/en.html) and answer the following questions (10 marks) What are the four types of systems which can be used for the different levels of management in a business? (2 marks) Explain how these four types of systems can be used in the context of Fonterra (8 marks) Q5. Read about Countdown and Pack&Save and answer the following questions (10 marks) . C What are the ebusiness strategies used by Countdown? - discuss at least 3 (3 marks) What are the ebusiness strategies used by Pack&Save? - discuss at least 1 (1 mark) How can Pack&Save improve its ebusiness strategies? (6 marks) See next page for marcking criteria