I need a formula to help in the distribution of the quantities by months according forecast sales and When the quantities of batch ends jumping to the quantities from the next batch to make up

Dear ,

I hope you are doing great

I have a small problem I hope to find a solution for the purpose of facilitating the work, I have two product A&B and there quantities available  for sales in the store , the quantities categorized according batch no .

As well,  I have information about what are the expected quantities of sales on monthly basis  of each product.

Now I need a formula to help in the distribution of the quantities  by months according  forecast sales and  When the quantities of  batch  ends  jumping  to the quantities from the next batch  to make up the shortfall.

For more clarification i have enclosed my exercise , Please see the attached

Your  cooperation is highly appreciated

Thanks
my-exercise.xlsx
Satech JubaraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Satech JubaraAuthor Commented:
Dear ,

I am still waiting you response .

Thank you for your support
0
Ryan ChongCommented:
I try to come out with something to handle all the conditions but it may still need to touch up a bit.

as a start, you probably can refer to attached and amend accordingly.

hope this give you some idea how to do it.
my-exercise_b.xlsx
0
Satech JubaraAuthor Commented:
Dear Ryan Chongو

Thank you for your interest and help, Unfortunately not solved properly ,It will seem to you if you change the qty available for product A batch no K120 , if you change the qty to be 3,000 Instead 5,000 you will notice that the distribution for product A batch no K121 not correct ,All quantitative"4000" putted  in the month of February 2016.

Try and tell me if there is an update

Thanks , once again
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Start This Course
Satech JubaraAuthor Commented:
It seems that this scenario has no ideal solution, since I have  no replies
0
Ejgil HedegaardCommented:
Try attached.
Column D, row 11 and row 18 are hidden with no values, to be able to make a single formula to copy to all cells.
my-exercise-1.xlsx
0
Satech JubaraAuthor Commented:
Dear Ejgil Hedegaard,

Thanks lot for your response , the formula that  used not help , you will discover that if you change the qty  for product A batch no K120 to be 1,000TINS  

Thanks
0
Ejgil HedegaardCommented:
I did not expect the batch qty to be lower than the monthly qty, so the initial value used the monthly, not the available batch.
Another adjustment made to handle that all batch qty can be below monthly qty.
Here is a revised version.
my-exercise-1.xlsx
0
Satech JubaraAuthor Commented:
Dear Ejgil Hedegaardو

Thank you it's actually been solved

Thank you sir
0
Satech JubaraAuthor Commented:
I hope to be available for all , may there people have same case . thanks
0
Ejgil HedegaardCommented:
Since it has been solved, points should be on the correct answer.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Satech JubaraAuthor Commented:
Ejgil Hedegaard thank you for help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.