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 a 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).