41 replies to this topic

[bAnderson]

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!

You are an outstanding person. Are you studying Aviation Business?

[CS_Pon]

You are an outstanding person. Are you studying Aviation Business?

 

No, not really   I'm a computer science student but I have to take at least one business class so I'm just taking one for this semester. (Which gave me an idea to make a spreadsheet)

[CS_Pon]

So, thinking about adding a situation where airline terminates lease in the middle of term.

 

Rolling numbers around, maybe I could 'assume' termination fee is equal to (number of unpaid term * lease payment) * 50%. This means if airline extends lease term it also increases termination fee.

Though in my head, it might not be much profitable compare to other three situations, but who knows, it might actually yield more.

[CS_Pon]

Third Update is coming soon, this one will come with Present Worth Analysis.

 

Present Worth is basically airline asking "How much is our aircraft worth with all the capitals we invested if we were to pay it now?"

 

Just like Annual Analysis, it will have both tabulated output as well as graphical output.

 

Also updated Annual Analysis as I realized I didn't include annual maintenance cost increase in overall cash flow part.

 

Here's the screenshot of Present Worth Analysis

 

I'll be updating the original post soon!

[Kwells]

This is great work, but I fail to understand the spreadsheet. Maybe I am not yet advanced enough to understand it, or maybe I'm just a different learner. If you could make a visual kinda tutorial to show depreciation with fuel costs and such, that would help me, and many other visual people.
Again, keep up the amazing work; and don't let anyone get you down.

[CS_Pon]

This is great work, but I fail to understand the spreadsheet. Maybe I am not yet advanced enough to understand it, or maybe I'm just a different learner. If you could make a visual kinda tutorial to show depreciation with fuel costs and such, that would help me, and many other visual people.
Again, keep up the amazing work; and don't let anyone get you down.

 

Actually, was thinking the exact same too! Sometimes even myself gets confused what to use

Showing a graphical output was one way of giving more straight forward type output for result...Maybe it would be useful to add maintenance cost increase as graph as well.

[CS_Pon]

Present Worth Analysis is added to the spreadsheet!

 

Like Kwells mentioned above, once I add Future Worth Analysis to the spreadsheet, I'll be working on something to do better explanation on each analysis spreadsheet.

 

As far as Present Worth Analysis goes, it shows airline how much an aircraft is worth at the time it is being purchased. BUT, its not how much airline pays actually. It only represents its value at time 0.

[CS_Pon]

Future Worth Analysis Spreadsheet is added!

 

Future Worth Analysis will give airline estimated value of aircraft/fleet starting from the year aircraft/fleet was leased/purchased to the end of study/cycle period.

Note that the result will be HUGE, as in, we are talking a billion dollar even. This is because MARR is being taken into account for calculation.

Just like Present Worth Analysis, this is NOT what the airline actually pays for. This only shows aircraft/fleets value.

 

So from here on, I'll be working on something to give more clear understanding of how to use these spreadsheets, starting with terminologies. Hopefully this will clear few things up and make these spreadsheets useful!

[Chubby Bear]

Future Worth Analysis Spreadsheet is added!

 

Future Worth Analysis will give airline estimated value of aircraft/fleet starting from the year aircraft/fleet was leased/purchased to the end of study/cycle period.

Note that the result will be HUGE, as in, we are talking a billion dollar even. This is because MARR is being taken into account for calculation.

Just like Present Worth Analysis, this is NOT what the airline actually pays for. This only shows aircraft/fleets value.

 

So from here on, I'll be working on something to give more clear understanding of how to use these spreadsheets, starting with terminologies. Hopefully this will clear few things up and make these spreadsheets useful!

I will just quote this as its easier than quoting your whole spreadsheet,

 

I only discovered it this afternoon and have been working through it for a couple of hours now as well as your updates. You have taken the complex aviation accounting methodologies to new levels, As a fellow student having also done Finance and Commerce, this actually makes perfect sense. Your explanations with examples are examplanary, are you doing this for an assignment or just because you can(for the fun)? I will most definitely use it ingame but it also gave me another insight into aviation accounting and data use, even after I have studied it. You should persue something aviation related one day, you would do well! You have an understanding that is well above average of the wonderful industry with all its intriguing complexity.

 

Keep up the good work, I look forward to reading about your updates!

[jmfsam]

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

you could really say that about the national awards as well 

[bAnderson]

you could really say that about the national awards as well 

LMAO Savage

[Denver.]

you could really say that about the national awards as well

LMAO Savage

There's a topic for a reason


Also, Regarding the spread sheet could we have a average comparison on the A350 and the 787. It would sound cool.

[bAnderson]

I think that the point of the spreadsheet is not to compare aircraft for the population, but to give you a guideline on how to compare them for yourself. I've had a lot of fun with this.

[CS_Pon]

Here is a one big image I drew about how to understand/use the spreadsheet. I'll be adding things little by little. Perhaps I should add this image on the original post, but for now, this will suffice.

 

This one will explain cash flow diagram that will be used within this series, CoC, MARR, ROR, and basic assumptions made for spreadsheets. Again, sorry for my bad handwriting!

 

 

 

[CS_Pon]

Aaand here is a in-depth explanation of Annual Worth Analysis and quick spreadsheet usage.

 

Still working on yield analysis, the more I look into yield analysis, more mistake found in original spreadsheet

 

[highland]

 doesn't the 365 day thing screw up all the math ???

[CS_Pon]

 doesn't the 365 day thing screw up all the math ???

 

No, since we are doing calculation based on yearly basis. So if we assume 20 hours per day, annual operating hour becomes 7,300 hours per year.

You can see the "Assumptions Made for Spreadsheet" part has assumption about yearly basis calculation (all cash flow happens at the end of each year)

[highland]

but the game doesn't play a 365 day year...... just a 288 day year because of only 24 days in a month...

 

so I think all that stuff up there is all gonna come out wrong....

 

and seeing that..... I would be pissed to have put all that energy into trying to "my hands flinging through the air" figure

out what plane I would want, but only to find out that the math would be like a hundred decimals off, and end up

watching my airline fludder away like a bad ticker tape parade......

 

 

I would be pissed.........

[CS_Pon]

but the game doesn't play a 365 day year...... just a 288 day year because of only 24 days in a month...

 

so I think all that stuff up there is all gonna come out wrong....

 

and seeing that..... I would be pissed to have put all that energy into trying to "my hands flinging through the air" figure

out what plane I would want, but only to find out that the math would be like a hundred decimals off, and end up

watching my airline fludder away like a bad ticker tape parade......

 

 

I would be pissed.........

 

Regardless of how many days in an year, calculation remains same. In fact, if there are 288 days in an year, simply change to (daily operating hours) * 288 days.

Still gives you yearly operating hours, calculation will use those values and give you result based on that numbers.

If you want me to do the math, 20 hours * 288 days/year = 5,760 hours/year. You can change the yearly operating hours in the spreadsheet anyways.

Hence why I set all calculation to yearly basis instead of month or days.

 

Plus, regardless of how many days in an year, I see solid 12 months every year. This means ANY monthly cost can be converted into yearly cost by multiplying 12 to monthly cost (ex. monthly maintenance cost converted to yearly)

 

About calculation being off, I already mentioned in the original post that result may be off due to limited data. I have to assume on things that is not provided from the game itself, like maintenance cost increase and discount rate for aircraft lease/purchase. Here's a little quote from original post.

 

I get value close to what AE gives, but never the same.

 

 

AE has its own formula running behind to give you adjusted down payment, maintenance cost increase, and my spreadsheet tries to imitate the formula in a linear behaviour.

Sure, I could use polynomial trend line and get a formula close to what AE has, but I wouldn't do it unless I really feel like to. Personally, linear trend line gives value close to result so I'll be keeping it.

[bAnderson]

CS_Pon, you are the greatest player on AE.