Appreciate any advice on the following challenge...
Our forecast is based on templates of how cash flows on products will emerge over a number of years. All items in the template can be scaled up and down by the sales related to that period but it then needs to be added to the scaled template from other time periods. For example, 2024 forecast would be 2023 sales assumption times year 2 template column plus 2024 sales times year 1 template column. Easy at small volume but with hundreds of accounts and time periods it gets complicated. Below is a simplified example.
Ideally we would be able to load templates by product into power query and load a sales table but then need a way to write a calculation to get the aggregated, stacked projection.
We can do this with a macro but it takes too long to run because of the complexity of the model that relies on this step. Hence why we're trying to use power pivot to solve.
Thanks in advance.
Our forecast is based on templates of how cash flows on products will emerge over a number of years. All items in the template can be scaled up and down by the sales related to that period but it then needs to be added to the scaled template from other time periods. For example, 2024 forecast would be 2023 sales assumption times year 2 template column plus 2024 sales times year 1 template column. Easy at small volume but with hundreds of accounts and time periods it gets complicated. Below is a simplified example.
Ideally we would be able to load templates by product into power query and load a sales table but then need a way to write a calculation to get the aggregated, stacked projection.
We can do this with a macro but it takes too long to run because of the complexity of the model that relies on this step. Hence why we're trying to use power pivot to solve.
Thanks in advance.
