Waterfall Table

Copper Contributor

Hi! I'm having trouble figuring out how to create a waterfall table in excel. I kind of have the logic down but I'm not sure how to incorporate the data. In the attached file, I have the tab Formula, which basically shows what the table should look like. For the forecast months before the actual month AND the forecast month before the current month, the actual data should be provided, or give the forecasted value. So since we are in April, we should have the actual values for cells D5:D15,E6:E15,F7:F15.

 

To explain how to read the table, in January 2023, I have no actuals so everything I see for the actual January row are forecasts. in Feb 2023, I now have the actuals for Jan but the rest of the months have the forecasts values. Note that the forecasts listed from when we pulled the data in january can be different from those that were pulled in feb.

 

My current issue is having a static snapshot of the data each month so I can see what the forecast was depending on what month I was in. On tab Formula with data, I created a dummy version of what I think could work but I feel like there could be a better/advanced version. In a perfect world, I'd like to pull the data, paste it in a tab and only the formulas in the row of the current actual month get updated (meaning, if I pulled the data in april, I only want the row april to be updated while rows jan-mar remain as is). at the same time, if there are actuals then the rest of the months should have those actuals too (I have the actuals for Jan as of Feb, so for the col D, after row 4, all the january actuals should be listed)

 

Any suggestions would be really appreciated! I've been playing around with this for a long time now and I'm ready to throw my laptop.

 

Thanks!

 

link to the file: excel waterfall test.xlsx

1 Reply

If you can't attach a file the suggestion is to upload the file to a cloud account like OneDrive or DropBox and share it. If that fails you can PM it to some that can post and they can post it here (e.g. me).
I don't know the format you have but would suggest 2 'master' tables/tabs. 1 tab for all the forecasted numbers and 1 tab for all the actual numbers. Then you waterfall would have a formula like (in pseudo code): If the count of lines on actual tab that have date > date at top of this column then 'waterfall' based on numbers on actual, otherwise use projected tab.

BTW please also include what version of Excel you are using as if you are using 365 we can give better options including newer functions like LET, FILTER, LAMBDA, etc...