I'm working on this financial analaysi but i don't understand how to do a sensitivity analysis projected fuel prices and discount rates in a single table. A 3 by 3 table with 9 combinations of prices and discount rates is one method. See below excel sheet with data. and also the full assigment.

This assignment has three objectives, to: 1) become familiar with the type and magnitude of mainline aircraft operating costs; 2) understand the operating economics of new versus older aircraft; and, 3) learn how net present value analysis is used in capital acquisition decision-making. InselAir, a newer Caribbean airline based in Curacao, has engaged the aviation consulting firm SH&E to evaluate whether it should continue its fleet expansion with used McDonnell Douglas MD-80 aircraft or purchase new Airbus A-320s. You are the senior financial analyst with SH&E assigned to this project and will prepare a memorandum with your analysis and recommendations to InselAirs Chief Executive Officer Albert Kluyver. Note: The assignment has detailed requirements similar to those that would be given to a financial analyst. Be certain to read carefully before beginning work. Background InselAir is the national airline of Curacao and in 2015 staked its claim to be one of the best airlines in providing international flexible connectivity to North and South America as well as the Caribbean region (fly InselAir.com). From hubs in Aruba, Bonaire and Curacao, InselAir serves 27 destinations ranging from Charlotte in the US to Manaus and Quito in South America. The fleet comprises 19 aircraft: 8 McDonnell Douglas MD-80s, 3 Fokker 70, 6 Fokker 50 and 2 Embraer Bandeirante. InselAir plans continued fleet expansion. Used MD-80 aircraft are available from several sources including the large MD-80 fleets at American and Delta which are gradually being replaced with new aircraft. US-based Allegiant Air has made used MD-80s the backbone of its fleet having acquired 18 of its early MD-80s from Scandinavian Airways System paying roughly $4 million dollars per plane in an all cash transaction. InselAir believes it could also acquire MD-80s for its expansion at similar prices. Though these aircraft are well used, service life is still 15 or more years. As with all older aircraft, the MD-80 burns more fuel and requires more maintenance than new generation aircraft of equal mission capability. Volatile fuel prices have added to senior managements interest in evaluating new aircraft. As an alternative to the MD-80, Mr. Kluyver is considering purchasing new Airbus A-320s. The Analysis SH&E has developed an Excel template which is provided as an attachment for conducting your net present value analysis. You will need to insert costs and performance figures into the template. You may wish to review the template before reading further. In order to complete your analysis, you will need to obtain current aircraft operating data and prices from authoritative sources. The sources listed below are sufficient and adequate for your project: Aircraft prices: Airbus publishes its aircraft list prices periodically. Search the Airbus Industries website or simply do a Google search for Airbus aircraft list prices. Aircraft performance data and operating costs: The Airline Monitor publishes extensive airline data needed for your analysis. The Airline Monitor is available through the Hunt Library Aerospace/Aviation electronic databases. When youve accessed The Airline Monitor, select Online Edition, then Block Hour Operating Costs (pdf). This is a large document. SH&E would have access to InselAirs MD-80 operating data, but for this assignment, assume costs are very similar to Allegiant Air. For your analysis, use Allegiants reported data for the MD-80 and US industry data for the A-320 (use the data for the A-320, not the A0320neo). The data you need looks like this example: Fuel Prices: Your estimate of fuel prices over the next fifteen years is critical. Historical data on jet fuel prices are available from several sources. The Airline Monitor includes this data in the Block Hour Operating Cost document (above). Data are also available from the industry association Airlines for America. Select Economics & Analysis, then Traffic & Financial Results. Scroll down to select the appropriate reports. You may also find long-range forecast energy prices from the American Energy Information Administration useful. Note that fuel prices increased dramatically during the global economic expansion of the mid-2000s peaking at nearly $4 per gallon in June 2008, but plummeted during the subsequent recession. Fuel costs seem likely to increase again when world demand recovers. You will need to estimate future fuel costs for the analysis. Be certain that the fuel price for the first year is the current jet fuel spot price (available from several sources via a web search). Because fuel prices are difficult to predict, develop estimates for a range of projected fuel prices. This is often done using optimistic, pessimistic, and most likely scenarios. Return on Invested Capital: The appropriate return on invested capital (the discount rate) varies by airlines; however, the following extracts from the financial press are illustrative. Alaska Air Group CEO Bill Ayer pointed to its target of 10% return on invested capital (ROIC). According to President Ed Bastian, Delta Air Lines is also targeting a 10% sustainable return on invested capital. Southwest Airlines is looking for a 15% ROIC. Your fleet replacement decision will depend on what rate you choose. Airlines with the best credit can borrow at the lower rates which also decreases the discount rate. You should perform a sensitivity analysis (work the problem with several discount rates) to better understand and defend your recommendation. SH&E staff have surveyed InselAirs management to arrive at several critical assumptions about aircraft costs and performance. 1. Airlines with solid balance sheets, such as InselAir, can normally obtain new aircraft for about two-thirds (2/3) of list price. After 15 years, an A-320 is estimated to be worth about half of the original purchase price (not list price) in the used market whereas an MD-80 will have only $100,000 in scrap value 15 years hence. Even if InselAir should continue to operate the new planes beyond 15 years, these values still represent an opportunity cost. 2. Because InselAirs segment lengths are relatively long, it believes fuel burns (gallons per block hour) on a new A-320 will meet the lowest of any airline and that speed in miles per block will equal the highest of any airline. 3. Although InselAirs business model does not provide for high aircraft utilization, because of the A-320 greater range capability, annual utilization (block hours per year) for the A-320 will be 25% higher than for the MD-80. Note: This annual utilization is for one aircraft, not the entire fleet. 4. InselAir plans to outsource its heavy maintenance, so it will pay another airline or maintenance repair facility for both direct and burden (overhead) costs. If it decides to purchase a new fleet type, it believes that its first year maintenance expense will equal the lowest for any airline operating the Airbus 320 but that these costs will increase by 2% per year. However, as the fleet of MD-80s age, InselAir believes that maintenance costs for this fleet type will increase by 5% per year. 5. InselAir configures its aircraft in high density, all-coach configuration (this assumption takes some liberty on InselAirs actual configuration). It plans a seating capacity equal to the highest of any airline. 6. InselAir does not expect crew expenses to change with the choice of aircraft, so this and other immaterial costs are not included in the analysis (an extra flight attendant will be required but this cost is ignored here). Task Enter data into the Excel template. As you will see, this decision is critically dependent on your projection for future fuel costs and the discount rate employed. Run a few sensitivity analyses with varying fuel and discount rates to see how the fleet replacement decision changes. Remember that the net present value obtained is a total cost of operation. The spreadsheet computes the cost per available seat mile (CASM). The aircraft with the lowest net present value CASM is the best financial choice. Prepare a memorandum (not more than 2 pages not including appendices) to Mr. Kluyver summarizing your analysis and making a recommendation. Remember that executive management will need to understand how the analysis was conducted. Explain your assumptions and methodology concisely. Insert (copy and paste) and reference Excel worksheets as appendices to support your fleet replacement recommendation. Use other tables and graphs as appropriate to support your recommendation. Notes Check carefully to ensure the inputs and results of the discounted cost analysis are reasonable. The NP CASM should be between 3 and 6 cents depending on the input variables. This is lower than reported CASM for US airlines because many costs that do not affect the choice of aircraft type are not included. Total annual operating costs should be in the millions of dollars. The Airline Monitor report can be used to check for reasonableness. You may wish to present your sensitivity analysis of projected fuel prices and discount rates in a single table. A 3 by 3 table with 9 combinations of prices and discount rates is one method. Excel spreadsheets should not be submitted separately; Mr. Kluyver wants the entire report in a single document. The assignment is not in APA style. Its a report for Mr. Kluyver in a business memorandum format. See Purdue OWL for guidance on writing business memoranda and memo format. Search OWL for memorandum. Finally, remember that you are writing for Mr. Kluyver. You may assume that he understands aircraft operating costs and financial analysis including net present value, but do not assume that he is intimately aware of all the assumptions underlying the analysis or how the comparison between the aircraft was developed. Although he has not been directly involved in your analysis, he will need to explain the details to his senior management team. SH&E is a consultant hired to provide a carefully researched recommendation. Include sufficient detail in the memorandum and appendices so that he will not have to ask for additional information.

Parameters MD-80 operating Costs & Statistics A-320 Operating Costs & Statistics 20 Block Hoursiyear 196 012 Bock Hourdyear 131067 22 24 Purchase Price 000 Purchase Price sss 000 000 25 Salle Price in year 400,000 Sale Price in year 16 $49.000,000 MD-80 capital and direct operating cost A-320 capital and direct operating cost 20 Year Discount Fuel wgo MD-80 Fuel Maint Total Annual NPV A320 Fuel Maint Total Annua NPv 29 Factor Purchase cost costs operating Purchase cost costs Qber ating 30 98,000,000 98,000,000 4,000,000 4,000,000 32 1 0962 1152 47,549,286 160 1.26 36 0822 433 13184 e2 56175595 46172244 0760 39 8 071 2.14 40 9 0703 22732615 77.061888 29794504 20.631642 10 0676 212,656,946 42 11 0850 2.48 57612.632 13 0625 60,493.263 15.44458 75.637722 47243098 44 0601 45 0577 2.87 22426953 2667 382.620 198 220,954 786 66693823 6,756,294 82450117 47612888 46 0555 288 A83008 13270300 401753308 223.079.351 2002854 16071.420 86099934 A7808237 3.0 0534 3.16 0,0001 -26.16150 48 Net Present Value Cost 3,136,006.354 MD-80 NPCASM A-320 NP CASM Parameters MD-80 operating Costs & Statistics A-320 Operating Costs & Statistics 20 Block Hoursiyear 196 012 Bock Hourdyear 131067 22 24 Purchase Price 000 Purchase Price sss 000 000 25 Salle Price in year 400,000 Sale Price in year 16 $49.000,000 MD-80 capital and direct operating cost A-320 capital and direct operating cost 20 Year Discount Fuel wgo MD-80 Fuel Maint Total Annual NPV A320 Fuel Maint Total Annua NPv 29 Factor Purchase cost costs operating Purchase cost costs Qber ating 30 98,000,000 98,000,000 4,000,000 4,000,000 32 1 0962 1152 47,549,286 160 1.26 36 0822 433 13184 e2 56175595 46172244 0760 39 8 071 2.14 40 9 0703 22732615 77.061888 29794504 20.631642 10 0676 212,656,946 42 11 0850 2.48 57612.632 13 0625 60,493.263 15.44458 75.637722 47243098 44 0601 45 0577 2.87 22426953 2667 382.620 198 220,954 786 66693823 6,756,294 82450117 47612888 46 0555 288 A83008 13270300 401753308 223.079.351 2002854 16071.420 86099934 A7808237 3.0 0534 3.16 0,0001 -26.16150 48 Net Present Value Cost 3,136,006.354 MD-80 NPCASM A-320 NP CASM