Please remember to follow all forum rules: Cross-posting
Hi,
I'm creating a financial model where a "Product" will be launched several times over the coming years. The Product will have a predictable financial profile but I'm trying to build in flexibility to run scenarios where we specify how many of these products are launched in a given year. I'm trying to calculate the total amount for a given account e.g. Revenues in a year, taking into account the revenue profiles of all the products launched in the previous year.
E.g. If a Product produces Revenues of 100, 200, and 400 in Years 1-3 respectively, and I launch 1 Product in each of the first 3 years, then the Revenues in Year 3 should be 600 (400 from the product launched in Year 1, 200 from the product launched in Year 2, and 100 from the product launched in Year 3).
I have tabular data which includes:
1) Annual Account profiles for each product e.g. Revenues from Year 1 through Year X
2) Profile of the number of products launch in a given year i.e. Product launches in each of Years 1 through X
I've figured out a way to do this with helper rows where I transpose the Product launches row (2) and then multiply it by the account profiles (1). The problem with this is that I'm modeling over many years and this creates an unnecessary amount of these "helper rows" for each account that I model e.g. Revenues, COGS, Opex, etc.
I'm trying to figure out a way to do the above programmatically through Power Query but I'm getting tripped up by the staggered nature of this model.
I've attached an Excel example, which is hopefully better than my explanation. Essentially, I've got the cells with blue text in data tables and I'm trying to get the cells highlighted in yellow, without using the cells with red text.
I know this is a mouthful but I'd appreciate any help on this.
Thanks in advance!
Kind regards,
Uwais
I'm creating a financial model where a "Product" will be launched several times over the coming years. The Product will have a predictable financial profile but I'm trying to build in flexibility to run scenarios where we specify how many of these products are launched in a given year. I'm trying to calculate the total amount for a given account e.g. Revenues in a year, taking into account the revenue profiles of all the products launched in the previous year.
E.g. If a Product produces Revenues of 100, 200, and 400 in Years 1-3 respectively, and I launch 1 Product in each of the first 3 years, then the Revenues in Year 3 should be 600 (400 from the product launched in Year 1, 200 from the product launched in Year 2, and 100 from the product launched in Year 3).
I have tabular data which includes:
1) Annual Account profiles for each product e.g. Revenues from Year 1 through Year X
2) Profile of the number of products launch in a given year i.e. Product launches in each of Years 1 through X
I've figured out a way to do this with helper rows where I transpose the Product launches row (2) and then multiply it by the account profiles (1). The problem with this is that I'm modeling over many years and this creates an unnecessary amount of these "helper rows" for each account that I model e.g. Revenues, COGS, Opex, etc.
I'm trying to figure out a way to do the above programmatically through Power Query but I'm getting tripped up by the staggered nature of this model.
I've attached an Excel example, which is hopefully better than my explanation. Essentially, I've got the cells with blue text in data tables and I'm trying to get the cells highlighted in yellow, without using the cells with red text.
I know this is a mouthful but I'd appreciate any help on this.
Thanks in advance!
Kind regards,
Uwais
