REQUIREMENT $1$:VISUALIZING AND ASSESSING VOLUMES

Background

IF offers several direct routes between cities across Canada, with each route identified by their origin $($starting$)$ and destination $($ending$)$ cities. While some airlines offer multi$-$leg flights, where a plane lands in multiple cities before arriving at the final destination, IF only offers direct, single$-$leg flights as customers prefer them.

IF's management team requires an easy waysto view the number of passengers flown each month, year over year. You have been tasked with creating an interactive dashboard that will allow a manager to switch between the different years and see aggregated passenger volumes, as well as specific visualizations.

Tasks

In the $"$R$01\_$SOURCE" worksheet, convert the range of data to an Excel table renamed as "PAX".

Create a new worksheet called $"$R$01\_$DASHBOARD" and create a dashboard based on the PAX table that resembles the template shown in Appendix $A,$ with each route in the dataset represented.

$2\mathrm{.}1.$ As shown in Appendix A$,$ the dashboard will show passenger volumes for each route by month based on the year specified in the top left portion of the dashboard.

$2\mathrm{.}1\mathrm{.}1.$ The year must be a dropdown that allows the user to select any year in the data set, and the passenger amounts must dynamically re$-$calculate when year is selected.

$2\mathrm{.}1\mathrm{.}2.$ All amounts must be formatted with commas and no decimal places.

$2\mathrm{.}1\mathrm{.}3.$ The dashboard must be created using formulas and functions.

$2\mathrm{.}2.$ The totals for each route must be formatted using Conditional Formatting, where the highest values have a green fill, lowest values have a red fill, and values in between are various shades of yellow.

$2\mathrm{.}3.$ Sparklines must be placed beneath each route total to illustrate changes in volumes month to month.

$2\mathrm{.}4.$ Add the following charts to your dashboard, with appropriate titles, labels, and formatting:

$2\mathrm{.}4\mathrm{.}1.$ A column chart shoving total passenger volumes for each route. The horizontal axis should consist of the route names, and your vertical column should represent the amount of passengers.

$2\mathrm{.}4\mathrm{.}2.$ A $2-$D regular pie chart showing the percent breakdown of passengers among the route for the month of January.

$2\mathrm{.}4\mathrm{.}3.$ A clustered column chart showing monthly passenger volumes for any two routes of your choosing. The chart should contain a data table at the bottom with a legend indicating which columns relate to which routes.

Create a scatter chart that determines a mathematical model for monthly passenger volumes:

$3\mathrm{.}1.$ Create a new worksheet called $"$R$01\_$SCATTERPLOT".

$3\mathrm{.}2.$ Base your scatter chart on the multi$-$year passenger volumes for a single route of your choosing. Copy the relevant passenger data to the R$01$ SCATTERPLOT worksheet, then create your scatter chart.

$3\mathrm{.}3.$ Configure the appropriate chart elements to clearly display the linear trend line and its equation. NONE OF THE FORMULAS WORK FOR ME HELP ME STEP BY STEP