Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# 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

Posted on 2016-10-30
Medium Priority
105 Views
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

Thanks
my-exercise.xlsx
0
Question by:Satech Jubara
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 7
• 3

Author Comment

ID: 41866669
Dear ,

I am still waiting you response .

0

LVL 53

Expert Comment

ID: 41867848
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

Author Comment

ID: 41867980
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

Author Comment

ID: 41870123
It seems that this scenario has no ideal solution, since I have  no replies
0

LVL 23

Expert Comment

ID: 41870884
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

Author Comment

ID: 41871008
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

LVL 23

Expert Comment

ID: 41871173
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

Author Comment

ID: 41871206
Dear Ejgil HedegaardÙˆ

Thank you it's actually been solved

Thank you sir
0

Author Comment

ID: 41871212
I hope to be available for all , may there people have same case . thanks
0

LVL 23

Accepted Solution

Ejgil Hedegaard earned 2000 total points
ID: 41871217
Since it has been solved, points should be on the correct answer.
0

Author Closing Comment

ID: 41871241
Ejgil Hedegaard thank you for help
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. Weâ€™ll look at four steps to take and examine how those steps were put into action foâ€¦
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, tâ€¦
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templaâ€¦
###### Suggested Courses
Course of the Month6 days, 14 hours left to enroll