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
12
70 Views
Last Modified: 2016-11-03
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
0
Comment
Question by:Satech Jubara
  • 7
  • 3
12 Comments
 

Author Comment

by:Satech Jubara
ID: 41866669
Dear ,

I am still waiting you response .

Thank you for your support
0
 
LVL 49

Expert Comment

by:Ryan Chong
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

by:Satech Jubara
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

by:Satech Jubara
ID: 41870123
It seems that this scenario has no ideal solution, since I have  no replies
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:Satech Jubara
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 21

Expert Comment

by:Ejgil Hedegaard
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

by:Satech Jubara
ID: 41871206
Dear Ejgil Hedegaardو

Thank you it's actually been solved

Thank you sir
0
 

Author Comment

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

Accepted Solution

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

Author Closing Comment

by:Satech Jubara
ID: 41871241
Ejgil Hedegaard thank you for help
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Steve Terp was featured in a video created by CRN about how "Channel Is Crucial To Market Disruption". Click on View source to see the video and article
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now