Minimum mileage guarantee

[simongr]

Hi all
I am just tweaking my spreadsheet for tracking QF and AA miles this (given I might be crediting to two programs ). Pretty much all of my travel is in J (even domestic as I bolt it onto DONE4 travel) and I am just trying to work out how I account for the minimum miles guarantees.

I know QF has a 1,000 mile minimum and AA 500 mile. I am just trying to work out what the status and cabin bonus are based on - is it the base miles or the mnimum miles.

E.g. SYD-BNE in J(D) is 468 miles. So do I in total under AA earn 1053 (being 468 base, 117 class of service 468 status bonus) or 1125 (463 base, 32 minimum, 125 COS, 500 status)?

Do QF and AA work the same way?

This wont affect me a lot and wont colour decisions on which program as these miles dont make much difference - decisions on the program will be on the minimum miles to credit to QF for maximum SCs (to get WP in addition to AA EXP).

Cheers

S

S

 

[oz_mark]

The minimum applies after the status and cabin bonuses have been applied.

9.3.6 The Minimum Points Guarantee applies only if the number of Points earned after the application of any applicable Cabin Class Bonus, any promotional bonus and any Status Bonus, is less than the applicable Minimum Points Guarantee specified in clause 9.3.5.

Also, in business class the minimum is 1,250 per segment

 

[simongr]

Thanks - should have looked their first - bad sim

 

[NM]

My spreadsheet has lots of IFs and lots if lookup tables to account for all the options of fare class and airline. Its evolved over quite a few years. In fact it still has the lookup table for km flown .

 

[Kiwi Flyer]

QF & AA work differently.

With QF calculate miles, cabin bonus, status bonus and then apply minimum mileage guarantee if applicable.

With AA the minimum mileage gets applied first then added to by cabin bonus, status bonus.

 

[simongr]

Thanks both.

I spent a few mins this morning on mileage monkey getting "destination" pairs - about 2000 potential combinations now.

Once I have a bit more direction on the travel this year (after my conferences in the US in March that will be alot clearer) - I can then work out the absolute minimum flights I can credit to QF to get WP - and maybe even get WP for 2007-2008 qualifying year (anniversary is September).

S

 

[Dave Noble]

With AA the minimum mileage gets applied first then added to by cabin bonus, status bonus.

No. With AA, calculate the miles, apply the cabin bonus and then apply min miles and then apply status

e.g. for MEL-SYD for a Platinum member

travel in super saver
base miles 439 .. with 50% earning gives 220
min miles guarantee of 500 applies bringing earning to 500
plat bonus of 100% gives 500 miles totalling 1000

Appears on statement as 220 base 780 bonus


travel in K
base miles 439 ... at 100% earning gives 439
min miles guarantee of 500 applies bringing earning to 500
plat bonus of 100% gives 500 miles totalling 1000

Appears on statement as 439 base 561 bonus


Travel in J
base miles 439 plus
cabin bonus of 25% is 110
gives 549 .
Plat bonus of 500 on the base min mileage earning of 500
total earning 1049

Appears on statement as 439 base 610 bonus

Dave

 

[Kiwi Flyer]

Thanks Dave Noble - not quite as generous as I thought. (Not that it makes much difference for NZ domestic.)

 

[NM]

Thanks Dave Noble - not quite as generous as I thought. (Not that it makes much difference for NZ domestic.)

And not all the simple to model in Excel - though achievable with a combination of IFs and VLOOKUPs.

 

[simongr]

I would hate to try this with multiple Airline programs - its hard enough with just QF and AA...

 

[Kiwi Flyer]

And not all the simple to model in Excel - though achievable with a combination of IFs and VLOOKUPs.

Not hard at all. Digs out formula in my spreadsheet

=MAX(Index(AA_Earning, match(AA_Earn_Desc, AA_Earn_Index, 0), Min_Column),round(index(AA_Earning, match(AA_Earn_Desc, AA_Earn_Index, 0), Multiple_Column) * Distance, 0))

where AA_Earn_Desc is AA_Status & AA_Airline & AA_Fare_Class

similarly for QF (replace AA for QF).

 

[Kiwi Flyer]

I would hate to try this with multiple Airline programs - its hard enough with just QF and AA...

I have a spreadsheet set up with earning/burning calculations for 6 different programs. Needs updating though, and I'd like to add a few more.

 

[NM]

Not hard at all. Digs out formula in my spreadsheet

=MAX(Index(AA_Earning, match(AA_Earn_Desc, AA_Earn_Index, 0), Min_Column),round(index(AA_Earning, match(AA_Earn_Desc, AA_Earn_Index, 0), Multiple_Column) * Distance, 0))

where AA_Earn_Desc is AA_Status & AA_Airline & AA_Fare_Class

similarly for QF (replace AA for QF).

Your is a little more elegant than mine which looks something like this for AA:

=INT((IF(M7>0,1,0)*VLOOKUP(K7,Tables!$L$40:$P$64,3)+IF(N7>0,1,0)*VLOOKUP(K7,Tables!$L$40:$P$64,5)+IF(O7>0,1,0)*VLOOKUP(K7,Tables!$L$40:$P$64,2)+IF(P7>0,1,0)*VLOOKUP(K7,Tables!$L$40:$P$64,4))*SUM(M77))+SUM(M77)

and this for QF:

=IF(M7>0,1,0)*(IF((INT(M7*1.25+0.5)+M7)<1250,1250,INT(M7*1.25+0.5)+M7))+INT(N7*1.25+0.5)+INT(O7*VLOOKUP(K7,Tables!$H$12:$J$35,3)+0.5)+O7+INT(P7*1.25+0.5)+P7

 

[Altair]

Not hard at all. Digs out formula in my spreadsheet

=MAX(Index(AA_Earning, match(AA_Earn_Desc, AA_Earn_Index, 0), Min_Column),round(index(AA_Earning, match(AA_Earn_Desc, AA_Earn_Index, 0), Multiple_Column) * Distance, 0))

where AA_Earn_Desc is AA_Status & AA_Airline & AA_Fare_Class

similarly for QF (replace AA for QF).

I used to think that I was good at Excel after years of working with external consultants and financial business cases. That is until I met an actuary :shock: and now I hold no pretense of any skill at Excel....
I heard all about Kiwi Flyer's spreadsheets and I am keen to have a look at them for professional interest.

 

[Kiwi Flyer]

I set mine up that way when I got sick of editing hard-coded formulae whenever the rules/calculation method changed. Now I only need to update the earning table (and make a copy to retain past calculations if needed).

I need some time to update for the latest changes though. A never ending job, but keeping the routing/mileage table up to date is harder than the earning/burning changes.

Yes, distance = index( Routing, match(From & "-" & To, Routing_Index,0)).

 

[Kiwi Flyer]

I heard all about Kiwi Flyer's spreadsheets and I am keen to have a look at them for professional interest.

I'll try to remember to bring them on Sunday so you can see in person.

Which ones do you want to see? I have Hilton HHonors earning, routing database, earn/burn template, plus another completely different version which is still in development.

 

[Altair]

I'll try to remember to bring them on Sunday so you can see in person.

Which ones do you want to see? I have Hilton HHonors earning, routing database, earn/burn template, plus another completely different version which is still in development.

Lets try the routing database and your earn/burn template. You also mentioned that you kept one with all your flights etc on is that your routing one? I am going to bring my own laptop along as I can use WLG free wireless for as while after you leave so you can avoid bringing your laptop just to show me the spreadsheets.

 

[Kiwi Flyer]

Okay. I couldn't remember if I'd already emailed them to you.

Flights are currently separate (but linked), but the development version is/will be better integrated.