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

Paradise is a brand of hotels in Canada. You have been hired by the company to analyze their sales data for selected branches in Ontario $($Ottawa and Toronto$).$

This hotel has three types of rooms, each can be booked at a different rate. The master sales file shows the total number of rooms that have been booked each month in each location in $2022,$ 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 of sales data for each location in each month.

Part $1$:

Use the template file $($Template$.$xlsx$)$ and rename it to Ontario.xlsx$.$

In this 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 which are available in the master sales workbook $($You can copy this data to a new sheet in your workbook$).$

In this workbook $($Ontario$.$xlsx$),$ you should create sheets for each location, as well as a sheet for the summary.

Your workbook should have the following sheets: $($rates and total booked rooms are copied from master sales data$)$

Documentation

Rates

Total booked rooms

Ottawa

Toronto

Summary

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 $2023.$ 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 $2022,$ they will consider their sales status as Low, it will be considered as Medium, when sales reach $2\%$ above $2022,$ 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, and High columns$).$ Use the three coloured cells $($C$18,$ D$18,$ and E$18)$ to write the percentages to make these calculations easier $($Hint: cell referencing$).$

Okay, so I walked myself through all of this and gotten to the point of having the total rates for everything, and total booked rooms, and the sales for both $2023$ KPI measures. I don't know how to get $2022$ information. What am I missing? I can't find it$.$ The sale trend sheet goes up to $2021,$ and I am confused. Also.. how do i make a sparkline on an excel on mac?