ASSIGNMENT OBJECTIVES

The objective of this assignment is to get you familiarized with functions and features in Microsoft Excel that can help in a variety of data management, problem solving and decision analysis contexts. Toward this, each

assignment exercise requires you to fulfill specific requirements. In addition to advancing your knowledge of

Microsoft Excel as atool, a complementary objective of this assignment is to foster your analytical thinking and logical reasoning skills.

The requirements have been provided with varying degrees for which specific features in Excel must be used. If a requirement does not have a specific technical requirement (e.g. using a specific type of chart), you are free to

use any approach you are comfortable with. However, your work should take advantage of Excel’sability to

automatedata processing and analysis, meaning that any manually performed steps (i.e. typing in values instead of using formulas and functions) will not receive a mark.

Note that Microsoft Excel must be used to complete this assignment, other spreadsheet applications and file formats will not be accepted.

Read the disclosure below following the completion of your assignment. Once you have verified these points, hand in this signed disclosure with your assignment.

1.           I acknowledge to have read and understood my responsibility for maintaining academic integrity, as defined by the University of Ottawa’s policies and regulations. Furthermore, I understand that  any violation of academic integrity may result in strict disciplinary action as outlined in the regulations.

2.           If applicable, I have referenced and/or footnoted all ideas, words, or other intellectual property from other sources used in completing this assignment.

3.           A proper bibliography is included, which includes acknowledgement of all sources used to complete this assignment.

4.           This is the first time that I have submitted this assignment or essay (either partially or entirely) for academic evaluation.

5.           I have not utilized unauthorized assistance or aids including but not limited to outsourcing

assignment solutions, and unethical use of online services such as artificial intelligence tools and course-sharing websites.

ASSIGNMENT SCENARIO

Icarus Flights (IF) is a discount regional airline that offers flights between several cities across Canada. As a

discount airline, IF offers economy seats to all its passengers at low prices to encourage high volumes, as well as focusing on keeping costs aslow as possible.

You have recently joined as a Financial Analyst to help management assess and report on the profitability of its current and future operations. You are going to use your knowledge of Microsoft Excel and spreadsheets to

develop an analysis that will inform management whether specific routes are performing well, which potential routes the airline should start flying, and what the right mix of plane types is to maximize profitability.

REQUIREMENT 0: SETUP

Tasks

1.   Download the Excel workbook titled “ADM1370_W24_A02_Workbook” from Brightspace and rename it to

“ADM1370_W24_A02_StudentID_Excel  replacing StudentID with your student number. This file will contain all your worksheets for Requirements 1 to 3.

2.   In the “ID” worksheet, write your Full Name, Student ID, and Course Section Letter.

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 way to 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

1.   In the “R01_SOURCE” worksheet, convert the range of data to an Excel table renamed as “ PAX” .

2.   Create a new worksheet called “R01_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.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.1.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.1.2.     All amounts must be formatted with commas and no decimal places.

2.1.3.     The dashboard must be created using formulas and functions.

2.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.3. Sparklines must be placed beneath each route total to illustrate changes in volumes month to month.

2.4. Add the following charts to your dashboard, with appropriate titles, labels, and formatting:

2.4.1.  A column chart showing 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.4.2.  A 2-D regular pie chart showing the percent breakdown of passengers among the route for the month of January.

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

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

3.1. Create a new worksheet called “R01_SCATTERPLOT” .

3.2. Base your scatter chart on the multi-year passenger volumes for a single route of your choosing.

Copy the relevant passenger data tothe R01_SCATTERPLOT worksheet, then create your scatter chart.

3.3. Configure the appropriate chart elements to clearly display the linear trend line and its equation.

Appendix A - Dashboard Screenshot

REQUIREMENT 2: EVALUATING EXPANSION ALTERNATIVES

Background

IF is considering flying a new route from itshubin Ottawa. The management team had a market study done to determine which cities people are flying to the most, and they found that there four cities close to Ottawa that would make suitable routes.

You have been tasked to develop a model that can be used to evaluate these four new routes.

Tasks

1.   In the “R02_EXPANSION” worksheet, create a model that utilizes the Maximax, Maximin, Averaging, and Expected Monetary Value heuristics.

2.   Apply conditional formatting that highlights the optimal value for each heuristic model with a green fill.

REQUIREMENT 3: OPTIMIZING MIX OF AIRCRAFTS

Background

IF has a mixed fleet of aircrafts that it uses on its routes: (1) turboprop propeller planes, (2) narrow-body jet engine planes, and (3) wide-body jet engine planes.

Each type of plane has different resource requirements, as well seats to hold passengers. For example, the

turboprop planes are smaller and slower, resulting in less fuel being consumed each flight, but it can only carry 74 passengers. In contrast, the largest of planes, the wide-body jets, travel almost 70% faster than turboprops while in the air and carry 174 passengers, but consume four times as much fuel per flight!

IF tracks the following items when analyzing the performance of a route: (1) time for each flight (which includes  time to takeoff, fly, and land); (2) the amount of fuel used during the flight; (3) the number of personnel involved in the flight (including pilots, flight attendants (FA), security guards, and baggage handlers); (4) the maximum

number of passengers per flight; and (5) the ticket price for the flight.

Given the resource requirements and revenue potential for each type of plane, along with constraints on specific resources, your task is to determine the optimal mix of planes to cover the demand for a new route that IF’s

management team is considering.

Tasks

1.   In the “ R03_PLANE_MIX” worksheet, enter formulas to calculate the following:

1.1. Total Revenue Per Flight, which is Passenger Per Flight multiplied by Average Ticket Price, for each plane type.

1.2. Individual and Total Costs for each type of resource (Pilots, FAs, Security, Baggage, Fuel) for a month. Each of these will be a combination of (1) the number of flights, multiplied by (2) the amount of each   resource used per flight, then multiplied by (3) the dollar cost rate per unit of each resource. Note that the cost of aresource depends on its cost behaviour; for example, the cost of a pilot is based on how  many hours they have flown, while the cost of baggage handlers is based on how many of them were   needed for that flight.

1.3. Number of Passengers flown, the Total Revenue earned, and Gross Margin (i.e. Total Revenue less Total Costs), for each plane type and in total.

1.4. Number of Turboprop Pilot, Jet Pilot, and FA hours used in the month. Each of these are combination of (1) the number of each personnel type used on a given flight, multiplied by (2) the number of hours

flown, with consideration to plane type.

2.   Using the Solver Add-in provided by Microsoft, configure Solver to maximize Total Gross Margin by adjusting the number of flights per plane type in a month, respecting the following constraints: (1) there cannot be any partial flights, and (2) flight hours for pilots and FAs must not exceed the stated capacities.