Managing Pivot-Based Forecast List

[jpalaci22]

I get a monthly data dump and do a little formatting to get into pivot-friendly format so it can display as pivots. If you see the screenshot I get pivots on the very left of the spreadsheet that occupy columns A to N with the entire year in months.

I then take that data make forecasting scenarios. I also have previous forecast scenarios in the past for historical data/tracking from previous forecasts and to update the latest forecast using actuals and previous forecast scenario`s assumptions. I'm working on implementing a pivot-based system to pull in data but now using a VLOOKUP from the raw data that's in a second sheet in the workbook.

My problems are

1) As my pivots grows (which they do and now with a new SAP system have grown dramatically) I have to add rows which make the previous forecast scenarios really messy looking. How do I control that? Im totally fine to change the model and how scenarios are displayed but my look will change over the year as new costs hit new new lines.

2) Is my pivot solution the best? It solves my issue with growing list and using VLOOKUPs, which are tedious to manage. Is there a better way with a pivot that will grow and shift the rows? Even when getting the raw data to a pivot-friendly format, it takes not much time but can be easier to do.

3) I think this goes to the second point and format, but how would I manage older data?

I'm playing around with the format and hoping to take large data dump and put out into a particular output/view, but I wonder if I can do some behind-the-scene formatting and magic so the final view is what's needed.

[#2]

@jpalaci22 

I'll be curious to see what others might come up with to recommend.

I worked with a friend who was summarizing product sales by SKU, by month. The Pivot Table method worked up to a point, as you've found. When Excel launched its newest version, it included some Dynamic Array functions that seemed to me to enable "self-designed and therefore nuanced" summaries that could resemble the Pivot Table but be more flexible.

I'm thinking of FILTER in particular, but you can tailor which rows appear, etc., by judicious use of UNIQUE and SORT.

Microsoft very helpfully produced a YouTube video that goes into some depth on how these functions work. Take a look and see if you get inspired.

https://www.youtube.com/watch?v=9I9DtFOVPIg

[#3]

Thank you! I started to watch the video and I’m going to work it. I’ll post my results later to how this turns out for me.