Incremental Increases based on criteria

Hi

I am working on a model looking at the launch of a new product. On the attached file I am using the sales inputs tab to say how many distribution points (DPs) we will start with, which month a product will be launched into a particular outlet type and how those DPs will increase during 2020 - 2023. Lastly the frequency column will say how often the increase will take place. So for example the product will be launched into outlet type 1 in Jan 2020 starting in 20 DPs. It will increase by 10 DPs every month in 2020 then increase by 20 DPs every month in 2021.

The formula I have come up with, following much google time, works a dream but goes wrong when the year ticks over and I cannot work out why. The increase of 10 is fine in 2020 but in Jan 2021 it jumps by 130.

Anyone have a suggestion?

Many thanks

Chris

Thank you so much! So much cleaner than mine, I knew there had to be a better way.

Hi

Just realised that your version doesn't account for the frequency. I.e. it applies an increase every month rather than the criteria specified in column J on the sales input tab. Any thoughts?

Chris

Thank you again, works perfectly

Hi again and thanks again

One more query for you. I am now looking at promotional activity. In this scenario, we will have certain outlets that will have regular promotional activity which is defined in the sales inputs tab. So I have set this up for Outlet 1 who will have their first promotion in April 2020 and every 6 months thereafter in 2020, 7 months in 2021 and 8 months in 2022. The assumption here is that volumes will be 2 times a normal month. Thoughts on how this will work?

Chris