Hi Pjwhitfield,

I should have explained a bit more, apologies. In the excel sheet I had attached earlier there are colored tables in the April tab. So I have a collection of these by month, i.e. July, August, Sept, etc.. and I will collect and aggregate these tables so I can do the simple sum and transpose functions to make the data more manageable.

Each week I will get a new set of tables that shows what our current bookings are, how much revenue we have collected, what our load factor is and what is the week on week changes in these figures. Since I get these tables every week I can compare these to our month end closing figures. For example if at month end of April Destination 1 had a total of 100 in revenue, I can compare this to what we had in week 1 of April, week 2 of April, week 3 and week 4. If in week 1 we had 90 in revenue then I know in 3 weeks we generated an extra 10 in revenue. If in week 2 it had grown to 110 then I know by the end of month the variance here is -10 in revenue (refunds, cancellations etc).

My purpose is to aggregate this data so I can forecast what we can expect to get in the future. We get bookings usually about 4 months in advance so historically I can see in January we had bookings for January, February, March, and April. How have these bookings changed over time compared to their month end numbers. Once I know how these numbers have changed then I can extrapolate that into the future.

In the Dest 1- Revenue tab I have created a table for how many days until the end of the month compared to when I received each table. So if I got numbers as of April 18th there will be 12 additional days until the end of the month. So I have an idea that we can still collect and try to sell for 12 more days.

The line below that "End of Period Pax Rev AC 400105" is what we actually close at the end of the month. This is the final amount that I can compare our previous forward bookings to.

The next table "Pax Rev from Commercial" these figures are linked into the tables I get from the other department on a weekly basis. You can see that lines 94 and 95 link into the April tab. I have tabs ranging back to July of last year but have not included it here.

Daily % is finding what is the percentage change on a daily basis when comparing our forward numbers against the actual final number at the end of the month. And then finally I average these numbers out so I know for month 0 we can expect what change for the remaining however many days, for month 1 we can expect what percent on a daily basis we can continue to collect (or refund) until the end of month 1 same for month 2 and 3. Month 0 is the current month, month 1 is next month, etc.

Right now every time I get a new table from the other department I would copy and paste it into their respective "month" tab, i.e. April and then sum up the numbers. Then I will go into the Dest 1 - Revenue tab and populate this by manually linking these to the month tab and then finding the new daily percentages to help make the forecast a bit more reliable. I'm sure there must be some seasonality in these numbers but I am just taking an average across the board and applying it to my financial model.

I hope this explanation was ok, if I missed anything please let me know.

Thank you Pjwhitfield