41 replies to this topic

[CS_Pon]

Greetings,

 

As much as I love playing AE, I always wanted to predict/analyze how much will it cost to purchase/lease new/used aircraft.

So I gathered as much knowledge and information as possible to design a spreadsheet, designed to analyze estimated aircraft yield based on cost.

 

Please note, there will be an error on estimation since I cannot get exact value for aircraft over period of times. I get value close to what AE gives, but never the same.

Also, result may be incorrect since it is difficult to predict what would actually happen in the actual game. Choice is up to you.

I only added three options since adding more option means I would have to make tables for possible combinations(I'm trying to make this macro-free spreadsheet).

 

Instructions are inside the spreadsheet, but I'll write them on the post as well. Screenshot is at the bottom if you want to skip to the screenshot.

 

UPDATE I:

1. Usage for each different spreadsheet is now inside the file, in Front Page sheet.
2. Created a new spreadsheet which now includes maintenance cost. For maintenance cost, I used 3.6% annual increase in maintenance cost since that is the closest I could get by sampling.
3. I kept original yield analysis for those who wants to estimate value only by using aircraft value.
4. New spreadsheet is added; new spreadsheet will allow you to estimate annual expenses for each aircraft leased, cycling through fixed lease term infinitely. You can use results to estimate how much revenue to generate in order to break even or make profit.

UPDATE II:

1. Combined Annual Worth Analysis for three different situation into a single spreadsheet.
2. Annual Worth Analysis for Lease-Purchase situation is added.
3. Annual Worth Analysis for Purchase only situation is added.
4. Graphical output on Annual Worth Analysis.

UPDATE III:

1. Present Worth Analysis for Lease/Lease-Purchase/Purchase situation is added. Present Worth Analysis includes single aircraft analysis.
2. Annual Worth Analysis is fixed to apply annual maintenance cost increase.

UPDATE IV:

1. Temporarily disabled Yield Analysis. It will be back up once I get better calculation model done.
2. Future Worth Analysis for Lease/Lease-Purchase/Purchase situation is added. Future Worth Analysis includes single aircraft analysis.
3. Present Worth Analysis and Future Worth Analysis now applies airline's MARR into calculation. Applying MARR adds airline's expected revenue return into calculation, giving more realistic result.  This will be added to Annual Worth Analysis later on (Annual Worth Analysis doesn't take MARR into account yet).
4. Both Present/Future Worth Analysis now can take different study period. That means, three choices can have three different cycle period. Spreadsheet will use Lease Common Multiples(LCM) as equal study period and will give you a result based on LCM period.

Future Expansion Plan:

1. Graphical guideline on how to use these spreadsheets.
2. Annual expense analysis over infinite period, plus lessor purchasing leased aircraft to sell at the end of each cyclic period. (How much will it cost to operate annually?)
3. Calculation of equivalent present aircraft value over fixed lease term. (How much is my aircraft worth now?)
4. Calculation of equivalent present aircraft value over fixed lease term, plus lessor purchasing leased aircraft to sell at the end of study period. (How much is my aircraft worth now?)
5. Calculation of equivalent future aircraft value over fixed lease term. (How much will my aircraft worth at the end of lease term?)
6. Calculation of equivalent future aircraft value over fixed lease term, plus lessor purchasing leased aircraft to sell at the end of study period. (How much will my aircraft worth at the end of lease term?)
7. Yield Analysis for different situation. (How fast will my aircraft recover invested capital?)
8. Annual expense analysis over infinite period for purchased aircraft (How much will it cost to operate annually?)
9. Calculation of equivalent present aircraft value over fixed study period. (How much is my aircraft worth now?)
10. Calculation of equivalent future aircraft value over fixed study period. (How much will my aircraft worth at the end of expected cycle?)
11. Yield analysis over a single, round-trip route, studied over infinite study period. (How fast is my route recovering invested capital over time?)
12. Yield analysis over a single, round-trip route, studied over fixed study period. (How fast is my route recovering invested capital over fixed time?)
13. Estimated total bond payment, studied over bond maturity date. (How much am I actually paying for issued bond?)
14. Capitalized Cost Analysis (Present Worth in infinite period. Not implemented in AE, but still a neat feature!)
15. Fix overall spreadsheet for better rate of return analysis.

 

That's it for now. I'll be updating as soon and as fast as possible. Questions, comments, suggestions, or bugs, I'm glad to hear from you guys.

Thanks!

-CS

 

Here are some screenshots;

 =Annual Worth

 

 =Present Worth Analysis

 

=Future Worth Analysis

 

Attached Files

•  AE_Aircraft_Analysis.xlsx   70.84KB   122 downloads

[highland]

god-dang man, we just want to play a game. not work in an accounting office..... your killing me...

[Denver.]

omg we just want to play this game and wait for ae4, and you started the math. 

 

appreciate but wonder if it would be even 0.69 % helpful

[Michael.]

god-dang man, we just want to play a game. not work in an accounting office..... your killing me...

While I certainly understand your statement, I do like this spreadsheet and would use it. I have a few bits and pieces of my own. Some people enjoy cracking the game into small parts and working with the numbers and I guess I'm one of them!

[Conor]

Well done that's some really nice work. 

[NNR]

omg we just want to play this game and wait for ae4, and you started the math.

appreciate but wonder if it would be even 0.69 % helpful

Stop being a dick. This guy's put in a lot of work, and you're trying to troll him.

Fair play, OP. You've worked really hard on this I can see.

[highland]

even after a couple of hours of thinking about it. it's still just to much. i mean, the left side there with all those numbers. i can picture that

being some kind of filter. but the right side. there is so much writing there. it i think it would end up in the guides and reference

pages of the game. and i really don't think people bother with it anymore. which in turn would make the learning curve just that much more

tedious.....  

 

i'm thinking about it, and it just don't seem to me to be a perk that will help make my game better.

 

a plane filter that lets me compare range and runway lengths. that would be the hour on the side routine I could handle to

pick out the best planes for my airline..... yes, that would be wonders.

 

a plane filter that would take two hours of side routine just to see if I can save 50 thousand dollars a day... that ! having to

live through that added routine would only put a wrinkly dent between my eyebrows. ..   nooo, this filter would kill me..

 

haa haa haa, i'm sorry. I just can't side with the spread sheet thing........

[bAnderson]

even after a couple of hours of thinking about it. it's still just to much. i mean, the left side there with all those numbers. i can picture that

being some kind of filter. but the right side. there is so much writing there. it i think it would end up in the guides and reference

pages of the game. and i really don't think people bother with it anymore. which in turn would make the learning curve just that much more

tedious.....  

 

i'm thinking about it, and it just don't seem to me to be a perk that will help make my game better.

 

a plane filter that lets me compare range and runway lengths. that would be the hour on the side routine I could handle to

pick out the best planes for my airline..... yes, that would be wonders.

 

a plane filter that would take two hours of side routine just to see if I can save 50 thousand dollars a day... that ! having to

live through that added routine would only put a wrinkly dent between my eyebrows. ..   nooo, this filter would kill me..

 

haa haa haa, i'm sorry. I just can't side with the spread sheet thing........

What's wrong with you!? This guy probably spent weeks on this, and all-in-all this is a great resource! I can't believe that you can't wrap your mind around looking just a little at the actual aspects of aircraft management!

[Denver.]

What's wrong with you!? This guy probably spent weeks on this, and all-in-all this is a great resource! I can't believe that you can't wrap your mind around looking just a little at the actual aspects of aircraft management!

You just need to know that not everyone think the same way. It's simple as that. Yea this does look like a lot of effort taken but I would rather suggest considering this to be used in any non AE type services. Simply cause it makes no relevance here at,

[NNR]

You just need to know that not everyone think the same way. It's simple as that. Yea this does look like a lot of effort taken but I would rather suggest considering this to be used in any non AE type services. Simply cause it makes no relevance here at,

bAnderson's right, you know. Someone puts in work and you just make unnecessary comments. 

[CS_Pon]

Whoa, what is going on? 

 

Well, we can just throw in bunch of flights to a route and get revenue, expenses, and profit calculation done. Even I would just go straight and add flights and see how much I'm actually making.

 

But there are always people who would like to know how much will it cost or how much revenue one should make to break even or make profit.

Some would play with this to see how one person could do such crazy thing, some for curiosity, and some would use it for actual estimation. Even I use it for myself everytime to see if this spreadsheet works and to see which aircraft would actually benefit me more based on estimated calculation rather than searching around asking which is good which is bad. Personally, I believe it is up to you at the end which aircraft you purchase to operate.

 

BUT, all things aside, if you think this is unnecessary, that is fine, I would still try to tidy it up to make it more straight forward, but as far as my curiosity goes, I like to know and figure out what to pick and what to drop before I actually execute my plan for expansion/replacement/closure.

 

Oh and if possible...let us not actually argue over a small spreadsheet with bunch of numbers and text. If you think this is a job well done, thank you. If you think this is unnecessary, thanks for your feedback. I know I can't always satisfy everyone.

 

EDIT: Oh and if anyone curious, I'm still adding more stuff.

[NNR]

Whoa, what is going on? 

 

Well, we can just throw in bunch of flights to a route and get revenue, expenses, and profit calculation done. Even I would just go straight and add flights and see how much I'm actually making.

 

But there are always people who would like to know how much will it cost or how much revenue one should make to break even or make profit.

Some would play with this to see how one person could do such crazy thing, some for curiosity, and some would use it for actual estimation. Even I use it for myself everytime to see if this spreadsheet works and to see which aircraft would actually benefit me more based on estimated calculation rather than searching around asking which is good which is bad. Personally, I believe it is up to you at the end which aircraft you purchase to operate.

 

BUT, all things aside, if you think this is unnecessary, that is fine, I would still try to tidy it up to make it more straight forward, but as far as my curiosity goes, I like to know and figure out what to pick and what to drop before I actually execute my plan for expansion/replacement/closure.

 

Oh and if possible...let us not actually argue over a small spreadsheet with bunch of numbers and text. If you think this is a job well done, thank you. If you think this is unnecessary, thanks for your feedback. I know I can't always satisfy everyone.

 

EDIT: Oh and if anyone curious, I'm still adding more stuff.

Good to hear. Keep the good work up.

[tom.]

This is a great resource CS

I can't wait until you add the rest of the items you just talked about

Thanks so much for posting this and letting us use it. Some users (like me) want to play as realistically as we can (and sadly that means full realism will only be met on AE4 because of the current features) but this helps plan out even more like real airlines, not just clicking the order button on whichever looks best.

Thanks again

[n.x.w.m]

You just need to know that not everyone think the same way. It's simple as that. Yea this does look like a lot of effort taken but I would rather suggest considering this to be used in any non AE type services. Simply cause it makes no relevance here at,

 

You should really listen to yourself sometimes. Not everyone thinks the same way, its as simple as that, so if you're just going to make unnecessary, demeaning comments, keep them to yourself.

[CS_Pon]

First update! I added new cyclic analysis sheet and fixed existing sheets to tidy up.

 

Original post has been updated.

[Michael.]

not everyone think the same way

You certainly ensure that there's an alternative opinion. 

 

Simply cause it makes no relevance here at,

Wrong. Maybe you won't use it but you don't speak for me thanks. You speak for yourself,. I'll certainly use this spreadsheet. I had similar bits and pieces myself but I'm not 100% sure where they are. However they were very basic by comparison to this. 

 

I think the general consensus here - apart from those unnecessarily bashing it - is that this is an excellent piece of work and while some may not use it, it is certainly insightful and will help many should they choose to look at it. Thank you CS.

[bAnderson]

I have a few suggestions..

 

1: Why compare the A319 to the A321-200 and A321NEO? I feel as if it is a weird comparison.

2: More aircraft types: Why not compare the 737-800, 737-900, and 737-900ER? It looks like a good comparison and is very useful in the most recent years. Or something important in the real world right now, the CS100, 737-700 (or MAX 7), and A318? These are all aircraft that a modern company could need to decide between. 

 

Great work, but I'd like to see more! Actually, I'm about to PM you about this.

[CS_Pon]

I have a few suggestions..

 

1: Why compare the A319 to the A321-200 and A321NEO? I feel as if it is a weird comparison.

2: More aircraft types: Why not compare the 737-800, 737-900, and 737-900ER? It looks like a good comparison and is very useful in the most recent years. Or something important in the real world right now, the CS100, 737-700 (or MAX 7), and A318? These are all aircraft that a modern company could need to decide between. 

 

Great work, but I'd like to see more! Actually, I'm about to PM you about this.

 

Q. Why compare the A321 to the A321 and A321NEO?

A. Well, first and foremost reason is because I have all three of them in my current airline allowing me to look into more detailed info, such as maintenance base fee and other details. 

But its not just I have all three of them. I also assumed a situation where you have two different options; replacing aircraft within same family group and replacing aircraft from different family group.

 

Q. Why not compare other aircraft such as Boeing or Boeing vs Airbus?
A. Value showed in the spreadsheet is just an example so airlines know what to enter on the table. Really, you can match any two different aircraft to see what yields more. If you want to, you can compare 440 seat B777-200ER to 2 of A321NEO and see which option will yield more. I tried to build up the spreadsheet as much flexible as possible. So if you have data, you CAN compare A318 to B737-700.

[CS_Pon]

Reading through documents again, and realized MARR is suppose to be positive (MARR >= 0%) on the yield analysis.

 

Which makes sense, since airline wants to make profit rather than loss. Spreadsheet still works since yield analysis only accounts expenses only, hence why IRR is negative.

Maybe I could add projected revenue and give more realistic value... such as ticket price * number of seat.

 

Also user input should have all things accounted for maybe, like fuel price and operating cost in annual worth analysis.

[CS_Pon]

Second update is coming soon, with new graphical output. I'll be editing the original post later.

 

This update brings you the entire set of Annual Worth Analysis. I actually managed to have three possibilities (Lease only/Lease-then-purchase/Purchase only) into a single spreadsheet.

 

Annual Worth Spreadsheet allows airline to see how much revenue should their aircraft generate. Here is a little handwriting I did to explain how these are calculated (Sorry for bad handwriting. Its awkward to write on a glass with plastic pen tip )

 

So, lets do a little example.

An airline is investing on a new route and is deciding which aircraft to choose for their route operation based on an aircraft's annual expenses. Airline is deciding between two A319-100s, one A321-200, and one A321NEO. Airline is expecting to replace old aircraft every ten year, but they decided to purchase this aircraft after leasing for five years then buy the aircraft from lessor to sell it at the end of 10 year. Airline already gathered all needed data to estimate annual expenses.

 

To start, airline will enter all necessary values. In this case, aircraft value of a new aircraft, monthly lease, depreciation rate, cyclic years, purchase year, fleet size, and fuel flow of an aircraft. Airline will also input some estimated value, such as maintenance base cost, maintenance cost, operating cost, and fuel cost.

 

Now, airline will get both graphical and tabulated output. (Showing graphical output only here)

 

Looking at the output, airline sees a single A319-100 will spend less compare to other two options. However, looking at the overall annual expenses, A321NEO spends less compare to other two options, so airline will go with one A321NEO for their new route.

 

New spreadsheet will be uploaded when I edit the original post!