Tasks 1 – 5 Using the information available in the Excel file Master Revenue W2019.xlsx and the business template 2Care4U Template.xltx prepare 5 workbooks, one per province – each one with as many sheets as locations in that province: The Ontario.xlsx workbook must include all these sheets The Quebec.xlsx workbook must include all these sheets The New Brunswick.xlsx workbook must include all these sheets The Nova Scotia.xlsx workbook must include all these sheets 2 The PEI.xlsx workbook must include all these sheets Don’t forget to enter your information in the documentation sheet. Subtask – Calculate 2019 KPIs Each location information must be used to calculate the key performance indicators (KPI) for 2019 based on this business rule: The goal is to have a 10% increase in revenue in 2019. To reach it, the company has established three indicators: Low, when revenue reaches 95% of 2018, Medium, when it reaches 2.5% above of 2018 and High, if it reaches the 10% increase.
Subtask – Generate 2018 Revenue Sparline Each location must also include a 2018 Revenue Sparkline in cell B17 – a line sparkline that shows its high and low points. Sparkline example Subtask – Calculate a Provincial Summary including Sparkline and Scatter Plot Once all locations have been created, each Provincial workbook must also include a Summary sheet where all these locations are reconciled. Besides the 2019 KPI and Sparkline, the summary sheet also needs to include a Scatter plot with Straight Lines as depicted below: 3 Scatter Plot example Task 6 – Create the 2Care4U Revenue 2018 workbook Once you have all 5 Provincial workbooks, use the template 2Care4U Template.xltx to generate a Summary for the 5 provinces named: 2Care4U Revenue 2018 – this file will have only two worksheets: The latter summarizes the 2018 revenue for all five Provinces using data available in the five Provincial workbooks. [To be able to do this work you must have all five workbooks open and in the same directory!]
#Foundations #Information #Systems #Business #amp #Winter
