Homework answers / question archive / Foundations of Information Systems Business 2400 A B  C & G – Fall 2021 [All the files you need for this assignment are available in Brightspace- template

Foundations of Information Systems Business 2400 A B  C & G – Fall 2021 [All the files you need for this assignment are available in Brightspace- template

MS Excel

Share With

---

Foundations of Information Systems

Business 2400 A B  C & G – Fall 2021

[All the files you need for this assignment are available in Brightspace- template.xlsx, sales trends.xlsx, and master sales.xlsx]

Painted paradise is a brand of hotels in Canada. We are interested in analyzing their sales data for selected branches in Ontario (Ottawa and Toronto) and Quebec (Montreal and Quebec City). 

This hotel has three types of room, each can be booked with a different rate. Master sales file shows the total number of rooms that have been booked in each month in each location in 2020, as well as the rates for each type of room (rates are different for each location and each month). 

Another file named sales trends shows the 5 years sales data for each location in each month.  

Part 1:

1. Use the template file (Template.xlsx) to create 3 workbooks: one for each province (Ontario.xlsx and Quebec.xlsx) and one for the summary of the two provinces (name the summary file as hotel sales-summary.xlsx) 
2. In each provincial worksheet, you need to do some calculations. To do these calculations, you need the total booked rooms data (make sure to use the right data from the right sheet number which should match the last digit of your student number) and the rates data.

In each workbook, you should create sheets for each location, as well as a sheet for the summary.

For instance, for the Ontario, your workbook should have the following sheets: (rates and total booked rooms are copied from master sales data)

 

3. As indicated, make sure to use the provided template in each sheet. For each location, in the sales column, you need to calculate the total sales per month.

The company wants to prepare three indicators to use for setting their goals (KPI) for sales in 2020. KPI (Key Performance Indicator) is a measurable value that demonstrates how effectively a company is achieving key business objectives. 

To measure their KPI, the company has established three rules: if their sales reaches 90% of 2020, they will consider their sales status as Low, it will be considered as Medium, when sales reach 2% above of 2020, and High, if it reaches the 10% increase.

Calculate these three statuses in each sheet (i.e., for each location) as indicated in the template (Low, Mid, High columns). Use the three colored cells (C18, D18, and E18) to write the percentages to make these calculations easier (Hint: cell referencing). 

Each location must also include a Sparkline in cell B17 – a line sparkline that shows its high and low points.

  

Sparkline example

4. Once all locations of each province have been created, each Provincial workbook must also include a Summary sheet where all these locations are reconciled (show the total sales of province). Use the template for the summary sheet as well. Besides the 2020 KPI and Sparkline, the summary sheet also needs to include a Line Chart (2D) as depicted below:

 

 

Line Chart example

 

5. Once you have all 2 Provincial workbooks, you can complete the Summary workbook named:

Hotel Sales-summary.xlsx – this file will have only two worksheets: Documentation & Total Sales (use the same template for total sales sheet)

 

The total sales sheet summarizes the total 2020 sales for the company. You need to calculate the total sales using the data available in the two provincial workbooks, make sure to do these calculations efficiently! 

 [hint: To be able to do this work you must have two provincial workbooks saved in the same directory and opened at the same time] 

Likewise, include a Sparkline in cell B17 and a Scatter Plot for the revenue and the 2020 KPIs for the company. 

 

Part 2:

In the other file named Sales Trends.xlsx the company has kept data from these 5 years: 20142018.

Note that the data in this file needs some cleansing too.

1. First cleanse the data in the five-year sheet (note: check for any issues and remove them all).
2. In this file, after cleansing the main data in the five-year data sheet, create new sheets, one for each province, using cell referencing, in each provincial sheet, create a table with columns showing years, months, and sales values for each location.

Also add a new column and calculate the total provincial sales in each year and each month. 

For instance, for the Ontario sheet you will have the following table:

 

 

 

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!

 

3. Using tables that you created for each province, in each provincial sheet, generate a Pivot Table that Summarizes the Average Revenue for the province. 

In the pivot table, by 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 below]

 

Average of Ontario-Total Months	Years                         2014                2015                2016	2017	2018
January February March April May June July August September October November December	61348 63434 64021 64417 68110 63009                65305              67978              70255                65350              69007              65685                67752              70645              65423                69963              72283              67011                69973              72544              69012                67945              70354              69245 62021 62871 67117                69765       69354 65693          64747 64343      63920 66422                 69655       70746	73186 68352 69698 71092 72642 74696 74220 71460 70828 64169 64890 73443	75787
			69669 72023 75153
			76872
			76757
			75883
			74984 72938 65747 65376
			76038
Grand Total	65873.66667 68650.91667              66869	70723	73102.25

Pivot Table Example

4. 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 below]. This will add two new sheets to your workbook (Ontario chart and Quebec chart).

[Note: order of the data is important for depicting the trends]

Ontario Trend using Chart Style 5

 

5. Add a new sheet to summarize the total 5-year sales data for the paint paradise hotel. This sheet should contain a pivot table showing the total sales (sum of all 4 locations) for each month in each year.

Same as pivot tables for each province, add monthly sparklines showing the high/low values. 

 

6. In the same sheet that you created in step 4, add a clustered column chart-style 9 which shows total sales for each year.

Your file should have the following sheets:

 

Save your workbook as Sales Trends Solution.xlsx

 

Part 3:

In a Word document (name it as “Hotel-report”) write a maximum two-page summary report of your findings. What is the overall trend for painted paradise hotel? Describe your charts and what can be found based on them. Compare the results for two provinces as well.  

Submit these files – zipped into a file named [ExcelSolutionByLastName.zip]:

1. 1. Ontario.xlsx
   2. Quebec.xlsx
   3. Hotel Sales-Summary.xlsx
   4. Sales Trends Solution.xlsx
   5. Hotel-report.docx