AS Status Calculator Tool

Old Jul 17, 17, 9:51 pm
  #1  
Original Poster
 
Join Date: Dec 2016
Posts: 179
AS Status Calculator Tool

Hi Everyone,

I was trying to calculate whether or not my existing flights for 2017 would get me to MVP Gold 75K status this year and made an excel spreadsheet I thought I would share with everyone. Here is a Dropbox link to the file:

https://www.dropbox.com/s/0x2o4i4i52icscg/Voldoo_AS_StatusCalculator%20-%202018.xlsx?dl=0

The spreadsheet is pretty self-explanatory.........basically you enter your starting mileage and status levels. Afterwards, use the table to enter your individual flight segments be completing the following required fields:
  • Airline
  • Ticket Class
  • Miles Flown
The spreadsheet will automatically calculate your EQMs, and MileagePlan miles based on the mileage flown and your status.



Please keep in mind this sheet calculates this based on CURRENT partner mileage redemption rates. I do not plan on updating the spreadsheet myself, but intermediate to advanced users should be able to make necessary adjustments to mileage rates and partners going forward.



I found this quite helpful in figuring which mileage runs would be most helpful so I figured I would share it in hopes some of you are able to use it as well.


Best,


Voldoo
SanDiego1K, rune87 and captobie like this.

Last edited by Voldoo; Sep 6, 18 at 8:48 am
Voldoo is offline  
Old Jul 18, 17, 9:28 am
  #2  
 
Join Date: Mar 2016
Location: LAX
Programs: AS MVPG, IHG Spire Elite
Posts: 660
This is helpful- thanks! I've been trying to track/plan on my own, but somehow my calculations seem to vary a bit from what AS's tier status tracker shows after my flights.

One of my biggest pet peeves with the AS website is that they do not separately show the EQM-earnings on the mileage plan activity page, lumping together elite status/other non EQM bonuses w/ class of service, etc that do.
be_rettSEA is offline  
Old Jul 18, 17, 9:40 am
  #3  
 
Join Date: Mar 2016
Location: LAX
Programs: AS MVPG, IHG Spire Elite
Posts: 660
Just a heads up after putting in my first entry, the class of service bonuses for AS are in the wrong category for EQM earning (e.g. F or P should earn 175% EQM, not just RDM). I moved it in my own copy, but others may want to also update.
be_rettSEA is offline  
Old Jul 18, 17, 10:31 am
  #4  
Original Poster
 
Join Date: Dec 2016
Posts: 179
Originally Posted by be_rettSEA View Post
Just a heads up after putting in my first entry, the class of service bonuses for AS are in the wrong category for EQM earning (e.g. F or P should earn 175% EQM, not just RDM). I moved it in my own copy, but others may want to also update.
Thanks for pointing that out, be_rettSEA!

I made the correction so the link is updated with the new version.
Voldoo is offline  
Old Jul 18, 17, 12:27 pm
  #5  
 
Join Date: Nov 2014
Programs: AS MVP Gold 75K
Posts: 102
I had a minor issue with entering an AA flight - my booking class is "O" and when I select that the EQM/RDM values get fubared...I think the issue is the '-' character in the class bonus lookup table(s)

Very useful tool!
Rich Boykin is offline  
Old Jul 18, 17, 1:02 pm
  #6  
Original Poster
 
Join Date: Dec 2016
Posts: 179
Originally Posted by Rich Boykin View Post
I had a minor issue with entering an AA flight - my booking class is "O" and when I select that the EQM/RDM values get fubared...I think the issue is the '-' character in the class bonus lookup table(s)

Very useful tool!
Thanks for pointing that out........you are correct is was the "-" that was causing the lookup issue. That has been fixed (for all airlines) as well as the fix for the status levels (25K, 50K, 90K)

I'll start tracking corrections in a version tab if people find more. Cheers!
Voldoo is offline  
Old Jul 18, 17, 2:05 pm
  #7  
 
Join Date: Jul 2017
Posts: 105
Pardon my ignorance as I'm new to FT (and Mileage Plan)...

I DL'd the spreadsheet and entered my flights for the year. I started with 0 miles, and entered only flights on Alaska up to 20k. A flight in Alaska first (F class, I'm assuming, despite my booking saying A) takes me over 20k EQM (22,671 to be exact) but the next-year status does not reflect a change in status to MVP. A first class flight (U class??) 3 days later on AA bumps me to 27.7k, and get an acknowledgement of MVP from the chart. Any reason why it takes the second flight?
91foxbody is offline  
Old Jul 18, 17, 2:29 pm
  #8  
Original Poster
 
Join Date: Dec 2016
Posts: 179
Originally Posted by 91foxbody View Post
Pardon my ignorance as I'm new to FT (and Mileage Plan)...

I DL'd the spreadsheet and entered my flights for the year. I started with 0 miles, and entered only flights on Alaska up to 20k. A flight in Alaska first (F class, I'm assuming, despite my booking saying A) takes me over 20k EQM (22,671 to be exact) but the next-year status does not reflect a change in status to MVP. A first class flight (U class??) 3 days later on AA bumps me to 27.7k, and get an acknowledgement of MVP from the chart. Any reason why it takes the second flight?

Hello 91foxbody,

There are two ways to earn status on AS; one by flying only AS-operated flights and the other flying any combination of AS flights and their partners. The spreadsheet assumes ONLY that the user earns status via the route flying AS and their partners (which is why the status level does not change after you are passing the 20K threshold on Alaska flights). The status levels can be reached by achieving the following EQM levels within a single year:
  • MVP: 25,000 EQMs
  • MVP Gold: 50,000 EQMs
  • MVP Gold 75K: 90,000 EQMs

EQMs are calculated by multiplying the actual miles flown multiplied by any fare class bonuses (or reductions). So, for your particular example, any flights you fly in Alaska F class will yield an EQM bonus of 75%. Example: a 1,000 mile flight would yield (1,000 miles flown + 750 bonus) = 1,750 EQMs. A full explanation can be found here:

https://www.alaskaair.com/content/mi...-benefits.aspx

Earning miles on Alaska partners depends on ticket class and airline. Here is a link to the partner earning charts:

https://www.alaskaair.com/content/mi...tners-overview

Hope that helps!
Voldoo is offline  
Old Jul 18, 17, 2:56 pm
  #9  
 
Join Date: Apr 2010
Location: SEA
Programs: AS G75K, Hilton Diamond
Posts: 275
Is there a way to edit the sheet to change the EQM assumption? Right now it assumes you meet based on partners and Alaska. What if all you fly is Alaska metal and no partners? Otherwise very nice work.
rune87 is offline  
Old Jul 18, 17, 3:18 pm
  #10  
 
Join Date: Nov 2014
Programs: AS MVP Gold 75K
Posts: 102
Originally Posted by rune87 View Post
Is there a way to edit the sheet to change the EQM assumption? Right now it assumes you meet based on partners and Alaska. What if all you fly is Alaska metal and no partners? Otherwise very nice work.
Edit the level values in the calculated field in cell I10 - the value for each status after the ">" character. This should cascade down the rest of the table and update the rest of the fields in column I.

20k, 40k, 75k for AS only
25k, 50k, 90k for AS + partners
Rich Boykin is offline  
Old Jul 18, 17, 3:24 pm
  #11  
 
Join Date: Jul 2017
Posts: 105
Originally Posted by EbonyTatas View Post
Hello 91foxbody,

There are two ways to earn status on AS; one by flying only AS-operated flights and the other flying any combination of AS flights and their partners. The spreadsheet assumes ONLY that the user earns status via the route flying AS and their partners (which is why the status level does not change after you are passing the 20K threshold on Alaska flights). The status levels can be reached by achieving the following EQM levels within a single year:
  • MVP: 25,000 EQMs
  • MVP Gold: 50,000 EQMs
  • MVP Gold 75K: 90,000 EQMs

EQMs are calculated by multiplying the actual miles flown multiplied by any fare class bonuses (or reductions). So, for your particular example, any flights you fly in Alaska F class will yield an EQM bonus of 75%. Example: a 1,000 mile flight would yield (1,000 miles flown + 750 bonus) = 1,750 EQMs. A full explanation can be found here:

https://www.alaskaair.com/content/mi...-benefits.aspx

Earning miles on Alaska partners depends on ticket class and airline. Here is a link to the partner earning charts:

https://www.alaskaair.com/content/mi...tners-overview

Hope that helps!
That helps a bit, but I think I'm still missing something. All of my flights will be on Alaska metal, so why would I need to meet the 25k instead of 20k threshold?

Here's my chart:
Attached Images  
91foxbody is offline  
Old Jul 18, 17, 3:50 pm
  #12  
 
Join Date: Nov 2014
Programs: AS MVP Gold 75K
Posts: 102
Originally Posted by 91foxbody View Post
That helps a bit, but I think I'm still missing something. All of my flights will be on Alaska metal, so why would I need to meet the 25k instead of 20k threshold?

Here's my chart:
see my post above about changing the assumption the spreadsheet makes - it's a calculated field that has the earning levels hardcoded to the AS+partner levels. Re-set the values in cell I10 to those of just AS and it will work for you.

It would be somewhat tough to program that logic to check for 'all AS' vs 'AS + partner' without some VBA code to do it....
Rich Boykin is offline  
Old Jul 18, 17, 3:55 pm
  #13  
Original Poster
 
Join Date: Dec 2016
Posts: 179
Originally Posted by rune87 View Post
Is there a way to edit the sheet to change the EQM assumption? Right now it assumes you meet based on partners and Alaska. What if all you fly is Alaska metal and no partners? Otherwise very nice work.

Hi Rune.

Great idea.....this was not a feature I thought to include since I wouldn't even think of qualifying with only AS flights. I've updated the spreadsheet to allow for tracking of status qualifications using either earning track (AS or AS + Partners)

Last edited by Voldoo; Jul 18, 17 at 4:01 pm
Voldoo is offline  
Old Jul 18, 17, 4:01 pm
  #14  
Original Poster
 
Join Date: Dec 2016
Posts: 179
Originally Posted by 91foxbody View Post
That helps a bit, but I think I'm still missing something. All of my flights will be on Alaska metal, so why would I need to meet the 25k instead of 20k threshold?
HI FoxBody,

You weren't doing anything wrong, I just hadn't created a way to calculate status based on AS flights only. The new version allows you to track your qualifications using either method now (AS vs. AS + partner flights).

Column I (Next Year Status) now calculates by calculating total EQM miles vs. total EQM AS-only miles and updates status level based on which threshold-level is reached first.

Please note Row 3 (Starting EQM Balances). Use Cells B3 & E3 to differentiate betwen starting EQM miles and starting AS EQM miles. Hope that makes sense!


EDIT: Thanks Rich!
Attached Images  

Last edited by Voldoo; Jul 18, 17 at 4:32 pm Reason: Forget to add info
Voldoo is offline  
Old Jul 18, 17, 4:08 pm
  #15  
 
Join Date: Jul 2017
Posts: 105
Rich/Ebony, thanks very much I dug through the code afterwards and figured out the limit issue. Much appreciated!
91foxbody is offline  

Thread Tools
Search this Thread