Whoa! Predictive status modeling?
@brp you are definitely playing this game well beyond my travel pay grade.
Ok, I think I made this sound a little more high highfalutin' than it really is. I just look at possible flights on a separate tab and look at the impact of those flights on the overall status year to see how we're doing relative to where we need to be with what is already booked. And, because Hyatt status is based both on stays and spend, I do make some assumptions about spend amounts for status. Speculative, but straightforward.
Here are a couple of examples:
Above is for hotel awards. I have separate sections for each year. I enter things as soon as we get them. The sheet uses Conditional Formatting to color code: dark grey is used. Light grey is assigned, but the date has not yet passed. Yellow s unused and expires this year, so better use it. Uncolored expires in the future.
For hotels, we have to earn Hyatt status by nights + spend (not just by owning a credit card, like Hilton). I have per-year sheets where I enter every trip we have planned, I include all flights, hotels car rentals and any parking rentals we need to do. anything unfilled colors yellow, meaning I have to do something about it. Then, the above has formulae to work through the sheet and look for the word "Hyatt" in a particular column. For all such lines, it computes the difference between the end and start dates to get total nights. Since some trips may have only a few Hyatt nights in a longer trip (maybe at the airport the night before), I have the offsets to adjust. And I have the spend nights where I can do some modeling. Finally, I have the date field (usually =today()) as shown, But I can change that to the future and the formulae are keyed to this field, so I can see how things will look on a certain date.
A fair bit of work to set up. But once constructed, very easy for upkeep. Just add al trips. Since 2025 currently has 26 trips, there is some work. But it's fun to do.
Cheers.