target numbers change based on actual numbers excel formula

Copper Contributor

hi everyone i need help:( 

for example my forecast for year 2022 is 100 products

jan 2022 -> forecast: 10, actual: 8

feb 2022 -> forecast:18, actual: x (gross add is 8)

mar 2022 -> forecast: 25, actual: x (gross add is 7)

 

so if my jan never hit the target of 10 products, feb march apr onwards must help to make up for the short fall of products, means instead of bringing new 8 products for feb, i must bring in 10 since jan we short of 2 products.

 

if feb never hit 8 new products but only 6, the shortfall of 2 products from jan and 2 products from feb will be carried to mar, where mar need to bring in 7 + 2(from jan) + 2 (from feb) = 11

 

is there a formula where the forecast number will change based on the actual number? and a formula where the shortfall will be divided into x remaining months of 2022? for etc, if 2022 left 6 months and short fall is 2 products, it will be added to the first 2 months of 1 product each

1 Reply
Your description is not consistent to me.
Could you provide a sample workbook with the desired result?