Part $1$:

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

$2.$ 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$)$

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

Each location must also include a Sparkline in cell B$17$ a line sparkline that shows its high and low points.

Sparkline example

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

Line Chart example