please read before you bid
https://www.homeworkmarket.com/questions/case-studyy-20061347
Shortguidance on how to activate ‘Solver’ in MS Excel.
Click on File → Options
Options —-→ Select ‘Excel Add-ins’ from dropdown button —-→Press ‘Go’
Check ‘Solver Add-in’ and press OK
Solver is now available under ‘Data’ ribbon
ACC700
3
– Case Study
Group work (30% of overall grade)
Submission deadline: see due date and time on Canvas
Please follow the submission guidelines (shown after the case study) carefully.
Brian Air Executive Flying Services – case study
Wayne Beckham looked out of his office window to witness the magnificent sight of snow slowly covering up the Botanic Gardens in Belfast. With a freshly brewed coffee by his side on the cold February morning, he took a blank sheet of paper and started to scribble on it some random notes in relation to his upcoming flights to various cities. A pilot by profession and with more than
1
2
years of flying experience, he considered himself fortunate to have joined Brian Air Executive Flying Services (BAEFS) some four years ago that allowed him to experience the world of executive flying service.
Prior to joining BAEFS, Beckham flew commercial planes for Fizzy Jet which was known to offer economy-class budget services to passengers. With the scourge of Covid and accompanying decline in passenger numbers, many of his former colleagues at Fizzy Jet had either been furloughed or were on unpaid leave; however, things were little different at Beckham’s current workplace. As BAEFS offered niche executive flying service to corporate executives and high net-worth individuals, Covid-19 and the resulting social distancing rules had led to higher demand in business for BAEFS. This had kept Brian, and 2
4
of his pilot colleagues, fairly busy throughout the lockdown period.
Beckham had been told the earlier day that he was to embark on an interesting flying mission to various cities and then return to Belfast within the next few days. He was scheduled to first fly to Brussels (Belgium) with the CEO and CFO of the Wimbledon Corporation, both of whom had flown with BAEFS previously and had come to know Beckham personally. The purpose of the trip, as rumour had it, was to pick up two great tennis players Rafa Fed and Andre Chang who were stranded in Brussels during the lockdown. After a short stay of just a few hours in Brussels, they would then travel together to Amsterdam to pick up four more renowned athletes, whose names were yet to be disclosed, but were understood to be good friends of both the CEO and CFO of the Wimbledon Corporation. The entire group of the renowned athletes along with the two lesser-known executives of Wimbledon Corporation were then expected to fly together to London Gatwick, England for a special photo-op session at the London eye before travelling together to Belfast for the opening ceremony of a special sports centre with state-of the art facility in the heart of Belfast.
About BAEFS
BAEFS had created a name for itself in the celebrity world for offering discreet and deluxe flying experience to the rich and famous celebrities. As the rich and famous found themselves constantly being the focus of news articles for traveling in their own private jets, despite passionately telling the world to reduce their carbon footprint, more and more celebrities started to avoid using their personal private jets in favour of more discreet private flying services in an effort to avoiding the prying eyes of paparazzis. The current directors of BAEFS, all of whom worked in the entertainment industry previously, saw a business opportunity and established BAEFS eight years ago in Belfast. Currently, it operates four aircraft to routinely offer executive flying services to mainly European and American customers to locations throughout the world.
Among the four aircraft, BAEFS’s largest and most expensive aircraft was the ATR 72-500 which had a range of 8,000 nautical miles. Purchased with long term loan in 2012, it was flown throughout the world, including the growth areas of Brazil, China, India, and Russia. It burned fuel at a rate of approximately 500 gallons per hour (one gallon of fuel weighs 6.7 pounds). It could carry up to 14 passengers, a flight attendant, and two pilots. The firm also owned and operated two Beechcraft 1900D aircraft (referred to as
BC1900
here
after), which it had purchased in 2016 and 2018. Both the Beechcraft models were smaller and could carry up to eight passengers and two pilots in each. Despite being small, the BC1900 was considered a fast plane and often flew from Belfast to as far as Australia, China, and New Zealand—a larger range than most small jets. Its
13,000
-pound-capacity tank and fuel burn rate of 310 gallons per hour meant that it required a fuel stop to reach these more distant destinations.
BAEFS acquired its newest aircraft, a Beechcraft 2100X (BC21X), in 2019. Though this aircraft’s fuel burn rate and passenger capacity is similar to that of the existing two Beechcraft models, its flying range is longer and is also considered more comfortable for longer flights.
Flying with the celebrities
As usual, BAEFS would start its initial flight from Belfast City Airport (
BHD
) before landing at Brussels airport (
BRL
) to pick up the great tennis players who were apparently staying somewhere near the city airport. The next flight then would end at Amsterdam Airport (
AMD
) and the additional mystery athletes were expected to board the plane within an hour or two of landing at AMD. The entire group would then travel together to London Gatwick (
GTW
) before traveling together to Belfast (BHD) where Beckham’s flying shift was expected to come to an end for the week.
One area of concern that pilots frequently had to deal while flying any aircraft was the take-off and landing weight of the aircraft. Pilots at BAEFS were responsible for creating and submitting their own flight plans to air traffic control and the International Aviation Administration (IAA). To calculate these important measures, the pilots started with the normal operating weight (NOW) of the craft and added the weight of the passengers and fuel. The NOW included weight of the structure of the aircraft, stocked food and drinks, emergency equipment, and the crew. The only component that differed from take-off to landing on a given flight was fuel and the only weight components that differed from flight to flight were passengers and fuel.
Fuelling dilemma
By the time Beckham finished his coffee, he had nearly filled up the whole sheet of paper with his fuelling plan and calculations for the exciting flights he was about to undertake within the following few days. Coming up with a fuel plan was a challenging task for pilots because there was no straightforward way to calculate how much fuel to take on or “upload” at the beginning of each leg. One question was whether or not to “preload” which referred to a practice in which extra fuel was uploaded initially to avoid having to purchase higher-priced fuel at destination airports. BAEFS operated its own fuel source at Belfast’s BHD, which kept its fuel costs low. Fuel at BHD at the time cost
£3.50
a gallon. In contrast, fuel purchased at BRL cost
£9.35
a gallon. As a simple example of preloading, Beckham could decide to upload enough fuel at BHD to carry him through both of the first two flights, thus avoiding buying the more expensive fuel at BRL. In essence, BAEFS would carry or preload from BHD the fuel needed to fly from BRL to AMD.
BAEFS was subject to landing fee at all airports apart from BHD (as it paid other fees for the entire year at the home airport).
Landing fee
s were fixed fees charged to each landing jet by the destination airport’s general-aviation terminal; the fees covered the costs of operation and general expenses of the terminal. The landing fee at BRL was £950 but the fee could be waived if
600
or more gallons of fuel was purchased; and similar policies were applied by the other destination airports where landing fees would be waived with purchase of minimum level of fuel specified at each airport. Hence, from cost-reduction perspective, one had to weigh the benefit of preloading with the cost of landing fees.
With no more space remaining on the sheet of paper he had pulled out earlier, Beckham now took a proper notebook from his drawer to write down the relevant figures for the trip more carefully. He had gathered enough information to make a table (which is shown in Exhibit 1). He patted himself on the back for the work done so far, and in an attempt to take a break from the work, gazed out of the window towards Botanic garden. It was snowing more heavily and he could see children throwing snowballs at each other down at the garden. He also felt like taking a short break to go out in the snow but wanted to finish one more important task before doing so.
Beckham also needed to consider the structural limitations of the BC1900 (see Table 2) in addition to the landing fees and cost of fuel. All aircraft had strict fuel tank capacity along with the departure weight and landing weight limits which were strictly specified and developed by the manufacturer and approved by the IAA. To calculate departing weight or arrival weight, Beckham added NOW to the weight of the fuel and the weight of the passengers (passenger weight calculations were based on a company-mandated figure of
300
pounds per person which includes luggage).
There were two more issues, related to the level of fuel upon landing, to be considered. The first issue to consider was that the company policy required that aircraft always land with at least 2,800 pounds of fuel. This “reserve fuel” was there to ensure jets had enough fuel to make it to an alternative airport should there be bad weather at the destination airport. Any fuel plan Beckham developed would have to be one in which the weight of fuel at arrival was at least 2,800 pounds. The second consideration was that the company required all aircraft to bring the fuel level up to 8,000 pounds immediately upon arrival back at BHD. The reason for this was that the aircraft would always be in a “ready-to-leave” position to go at a very
short notice if required to do so. This meant that Beckham’s fuel plan should begin with the BC1900 containing at least 8,000 pounds of fuel before departing BHD. For the larger aircraft (ATR 72-500 aircraft), the policy was to always land with at least 4,500 pounds of fuel and bring its fuel level up to 8,700 pounds upon arrival at BHD.
As Beckham finalized his BHD to BRL to AMD to GTW to BHD trip, he paused to look out of the window once again to check if it was still snowing. He still has some more calculations to finish before he can go out and enjoy the snow.
|
TABLE 1 |
|||||||||||
|
BRIAN AIR EXECUTIVE FLYING SERVICES |
|||||||||||
|
Flight Details |
|||||||||||
|
Leg |
Depart |
Arrive |
Miles |
Duration (hr.:min.) |
Fuel burn (pounds) |
Fuel price (£/gallon) |
Landing fee |
Minimum gallons to waive landing fee |
|||
| 1 | BHD | BRL |
890 |
02:00 |
4,800 |
£3.50 |
|
||||
| 2 | AMD |
176 |
00:40 |
2,000 |
£9.35 |
£950.00 |
600 | ||||
| 3 | GTW |
1,202 |
02:55 |
5,300 |
£7.47 |
£450.00 |
300 | ||||
| 4 |
628 |
01:35 |
3,100 |
£6.01 |
£400.00 |
350 |
|
TABLE 2 |
||
|
BRIAN AIR EXECUTIVE FLYING SERVICES |
||
|
Aircraft Limitations |
||
|
(in pounds) |
||
| Aircraft | BC1900 |
ATR-72 |
|
Maximum Departure Weight |
36,400 |
90,900 |
|
Maximum Landing Weight |
31,800 |
75,300 |
|
NOW* |
22,200 |
48,800 |
|
Fuel Tank Capacity |
13,000 |
41,300 |
*NOW = normal operating weight of the aircraft, including crew and excluding the weight of fuel and passengers.
Required:
1. Formulate a fuel plan for Beckham’s upcoming trip. Present and discuss the optimization function and the constraints from a business perspective. 7 Marks
2. Use linear programming and Solver in Excel to calculate the optimum level of fuel to be bought at various locations to minimize cost. Present all the calculations in good form.
18 Marks
3. Discuss the assumptions used in the linear programming approach above. 5 Marks
Total 30 Marks
[Important information]
Marks will be awarded on:
(i) a group basis, based upon how the Group address the tasks; and
(ii) an individual basis, based on evidence of collaboration as shown in ‘GroupLogReport’ sheet of the answer booklet provided. Unexplained absences/non-participation will attract deductions.
Submission guidelines
You have been assigned to separate groups (MA_CaseStudy 1, 2 etc.) in Canvas. Answer booklet in .xlsx format has been provided with this case study and one digital copy (in .xlsx format) of the answer booklet per group must be uploaded to Canvas by the submission deadline. The answer booklet contains five sheets as shown below in Figure 1:
Figure 1: Format of the answer booklet showing the different sheets
Ensure that the group members’ attendance or absences are logged properly in the ‘GroupLogReport’ sheet in the given format. Answers to Q1, Q2, and Q3 should be provided in sheets Q1, Q2, and Q3 respectively. Additionally, the screenshot of the Solver dialogue box (used to find the optimal solution) should be provided in “SolverScreenshot” sheet.
Plagiarism is treated very seriously; click
here for more information on plagiarism.
Any questions that may arise subsequently will be addressed in the
FAQ page
.
GroupLogReport
| MA_CaseStudy Group ____ meeting log report | Participation of group members ( | Y | N | ||||||||||||||||||||||||||||||||||||
| Date | Action | Mode of participation | Approximate Time (hours) | Firstname Lastname | John Doe | Jane Doe | David Rooney | Theresa Johnson | |||||||||||||||||||||||||||||||
| Wednesday, February 10, 2021 | Meeting | In-person * | 1.50 | ||||||||||||||||||||||||||||||||||||
| Saturday, February 16, 2019 | Discussion | online | 0.75 | ||||||||||||||||||||||||||||||||||||
| Friday, February 22, 2019 | in person | 1.00 | |||||||||||||||||||||||||||||||||||||
| Monday, March 04, 2019 | Report-writing | 2.00 | |||||||||||||||||||||||||||||||||||||
| Tuesday, March 05, 2019 | |||||||||||||||||||||||||||||||||||||||
| Friday, March 12, 2021 | |||||||||||||||||||||||||||||||||||||||
| Wednesday, March 17, 2021 | Revision | in-person | |||||||||||||||||||||||||||||||||||||
| *in person = meeting face to face but with social distancing guidelines being followed properly. |
Q1
| Answer to Q1 should be in this sheet. |
Q2
| Answer to Q2 should be in this sheet. |
Q3
| Answer to Q3 should be in this sheet. |
SolverScreenshot
| This sheet should provide a screenshot of the full dialogue box of Solver |
| used by you to solve the problem (like the example shown here; delete the |
| example screenshot after you have pasted your own screenshot) |