Reference no: EM132382327
BUSINESS 2400 Foundations of Information Systems Assignment - Carleton University, Canada
Instructions: Please read the instructions carefully.
2Care4U is a service that connects personal social workers (PSW)s to individuals or retirement homes with headquarters in Ottawa. It has 9 locations in two provinces of Ontario and Quebec [5 locations in Ontario, 4 locations in Quebec]. Using the information available in the Excel file Master Revenue.xlsx and the business template 2Care4U Template.xltx prepare 2 workbooks, one per province, i.e., Ontario.xlsx, Quebec.xlsx- each one with as many sheets as locations in that province: For Ontario these are the sheets needed:
The Ontario.xlsx workbook must include all these sheets
|
Documentation
|
Summary
|
TorontoEast
|
TorontoWest
|
Ottawa
|
Kingston
|
London
|
As indicated, make sure to use the provided template for each sheet (i.e. location).
Be sure that the data you use is the one in the Master Revenue file that matches the last digit of your student number! Save each workbook accordingly, i.e., Ontario.xlsx.
KPI (Key Performance Indicator) is a measurable value that demonstrates how effectively a company is achieving key business objectives. For this company, their goal is to have a 10% increase in revenue in 2019.
To measure their KPI, 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. Make sure to calculate these values in each sheet (i.e. for each location) as indicated in the template.
Each location must also include a Sparkline in cell B17 - a line sparkline that shows its high and low points.
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 (Note: if scatter plot does not generate a chart like the picture (attached); use Line chart).
Once you have all 2 Provincial workbooks, generate a Summary for the 2 provinces named: 2Care4U Revenue 2018 - this file will have only two worksheets: Documentation & 2Care4U.
The latter summarizes the 2018 revenue for two Provinces using data available in the two Provincial workbooks. [To be able to do this work you must have two workbooks open and in the same directory!] Likewise, include a Sparkline in cell B17 and a Scatter Plot for the revenue and the 2019 KPIs for the company.
In the Revenue Trends.xlsx the company has kept data from the last 10 years [2009-2018]
For each one of the two provinces you will need to "mirror image" the data for the locations in that province, including as well as a column with the total for the province, it means you will create a sheet for each province, and in that sheet you will have the table includes all locations of that province, and the values which have been mirrored (not copied!) from the "ten year data" sheet.
Note: This assignment is about doing the tasks in the most efficient way; it means you should use formulas/methods that you have learnt at the tutorials to make your workbooks as efficient as possible, so if the data changes, the company would be able to still use the workbooks you have generated!
Using tables that you created for each province, generate a Pivot Table that Summarizes the Average Revenue for the province. Using conditional formatting, highlight in green colour the top 5 values and in red colour the bottom 5 values. Add monthly Sparklines depicting the high & low years for each [See example in attached file]
In a new sheet, create a Scatter Plot depicting the Provincial Trend - use a different Style for each one of the two Provinces [See Example in attached file].
The last worksheet is a Pivot Table Summarizing the 10-year Revenue data for 2Care4U - it includes all 9 locations revenues (total revenue)! It must show the total revenues for each month in each year.
It must also include monthly sparklines indicating the high/low years.
In the same worksheet create a Clustered column chart - Style 9
Save your workbook as Revenue Trends Solution.xlsx
In a Word document write a two-page summary report of your findings. What is the overall trend for 2Care4U? Which Province is setting the best trend? Based on the historical data, what are 2Care4U best years?
Submit these files - zipped into a file named [ExcelSolutionByLastName.zip]:
1. Ontario.xlsx
2. Quebec.xlsx
3. 2Care4U Revenue 2018.xlsx
4. Revenue Trends Solution.xlsx
5. 2Care4U Report.docx.
Attachment:- Foundations of Information Systems Assignment Files.rar