Fill This Form To Receive Instant Help

Help in Homework
trustpilot ratings
google ratings


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

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)

 

  1. 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

  1. 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

 

  1. 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!

 

  1. 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

  1. 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

 

  1. 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. 

 

  1. 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. Ontario.xlsx
    2. Quebec.xlsx
    3. Hotel Sales-Summary.xlsx
    4. Sales Trends Solution.xlsx
    5. Hotel-report.docx

Option 1

Low Cost Option
Download this past answer in few clicks

19.99 USD

PURCHASE SOLUTION

Already member?


Option 2

Custom new solution created by our subject matter experts

GET A QUOTE