Formula which adjusts current cell to "catchup' for shortfall in rows/cells above.

[altwood]

Hi,

I am having a lot of difficulty trying to come up with a formula for the following table that will look at the cumulative shortfall for prior months and if available add the shortfall to the Sales in the current month. For example, the cumulative shortfall in Aug is 41 ( 6 in May, 17 in June & 18 in July) and there is 11 extra units that can be sold (August amount in Stock-Sales column). I would like the formula to add the 11 units to sales in August to arrive at 20 units (currently at 9). Next month, there should only be 30 units available in the shortfall as 11 were used in August. In September, the formula needs to know that there are only 30 units of shortfall remaining this month (11 of the 41 were used in August) and as result only 30 can be added to the Expected Sales column in September even though there is stock of 36 in the Stock-Sales (Max)Column. Obviously, the remainder of the year has zero shortfall remaining so if the formula is correct the Expected Sales should stay the same as is for Oct-Dec.

excel problem.JPG

Thanks

[Glenn Kennedy]

You talk about "add to revenue", but there is nothing labelled revenue. What are your expected answers (calculated manually) & where do you expect to see them?

[altwood]

You talk about "add to revenue", but there is nothing labelled revenue. What are your expected answers (calculated manually) & where do you expect to see them?

Sorry, I should have clarified that I meant "Expected Sales". Revenue/Sales are interchangeable where I work which is why I overlooked that. I updated the wording in the original post.

[Glenn Kennedy]

Hahahaha. And I'm an analytical chemist and such interchangeabilities are totally foreign to me....

[Glenn Kennedy]

Did you add manually calculated expected results as requested?? Refer to the second sentence of Post 2.

[altwood]

Did you add manually calculated expected results as requested?? Refer to the second sentence of Post 2.

I just added a new file to the original post which shows the "correct" sales amounts that I would expect to see and the cumulative shortfall balance per month and amounts used each month. You can put them in new columns like I have. Hope this helps.

[altwood]

Hahahaha. And I'm an analytical chemist and such interchangeabilities are totally foreign to me....

Haha, Yeah that was my fault I should of made it consistent.

[JohnTopley]

Why is the calculation what not I consider to be the "norm":

Opening Stock + Stock Received - Sales (actual)

Why are you adding the 11 to "Expected Sales" ?

or more ... curious !

[altwood]

Why is the calculation what not I consider to be the "norm":

Opening Stock + Stock Received - Sales (actual)

Why are you adding the 11 to "Expected Sales" ?

or more ... curious !

"Expected Sales" cannot exceed "Expected Stock" in the month. If the Forecast is higher than the Expected Sales (due to Expected sales being limited by lower "Expected Stock" than forecasted) then there is a shortfall in the forecast for that month (revenue did not meet forecast). Currently, Sales cannot exceed the forecast so they will max out at the Forecast amount in the month even if there is more stock available in the month than forecast. The idea of the formula I am looking for is to correct this and look at the available stock in the given month and the cumulative forecast shortfall for prior months and max out any cumulative forecast shortfall up to the available stock in the month or the cumulative shortfall which ever is less. For example, in a month if Forecast is 8, Sales are 8, Stock is 20 and Cumulative forecast shortfall for prior month is 7 then I would expect sales to be 15. The cumulative balance should reset to zero in the following month as the shortfall was used up unless of course there is new shortfall. I hope that makes sense.

[JeteMc]

This proposal employs two helper columns which may be moved and/or hidden for aesthetic purposes.
The first helper column (L) is populated using: =SUM(L1,-MIN(0,B2-C2))
The second helper column (M) is populated using: =IF(L2=0,0,MIN(L1-M1,MAX(0,B2-C2)))
The Expected Sales column is now populated using: =MIN(B2,SUM(C2,F1))
The Forecast - Sales (Cumulative) column is now populated using: =MAX(0,L2-SUM(M$2:M2))
Let us know if you have any questions.

[altwood]

This proposal employs two helper columns which may be moved and/or hidden for aesthetic purposes.
The first helper column (L) is populated using: =SUM(L1,-MIN(0,B2-C2))
The second helper column (M) is populated using: =IF(L2=0,0,MIN(L1-M1,MAX(0,B2-C2)))
The Expected Sales column is now populated using: =MIN(B2,SUM(C2,F1))
The Forecast - Sales (Cumulative) column is now populated using: =MAX(0,L2-SUM(M$2:M2))
Let us know if you have any questions.

Great job, this is exactly what I am looking for. I figured it would be something out of the box like this. I was trying to solve it with sum ifs, etc but it did not come across my mind to use max/min.

Thank you very much.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

[altwood]

Done. Thanks and you too!