Revenue Forecast

[tryingtoexcelatexcel]

Hi everyone,

So I have a file that I use for revenue forecasting but it is very tedious because every time I get a new file with inputs I need to update this file manually. Right now I only have to update for 5 destinations but as we add more I can see this will become very cumbersome.

I'm attaching an altered version of the file I use and some notes of what I am currently doing, if anyone has any suggestions on how to make this less manual I would really really appreciate it. Thank you guys.

[pjwhitfield]

You talk about the formulas having to change but there appear to be very few formulas in the sheet? mainly just some SUM values. This is one of those sheets where I think you're going to have to go back to the start and describe what exactly you're looking to get out of the sheet, what data you receive (and how) ie is it a file/ another spreadsheet etc? Does that data include all the fiigures in your tables or are they calculated in your sheet (if they are I cant see them).

Personally Im not a fan of any sheet that has multiple rows to display a single item of data in blocks, it makes it much harder to work with however a tiny change to that data can make a massive difference and often its a case of having to store the data in a boring long table format elsewhere in the workbook and have your displayed sheet generated from that rather than trying to do both in one. Doing that could automate the whole new Destination bit.

Give us some more info on the process itself, the data you use etc.

[tryingtoexcelatexcel]

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