• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating days (end date) based on multiple input.

Kasper Devald

New Member
Hello,


I have for quite some time tried to make an excel solution that should provide an overview for a certain component, on hove many days we have on stock and what date the stock run out! This should be based on multiple products where the component is a part of and the obsolete date of the specific product.

My challenge is the obsolete date on the products and that the usage will drop over time and therefore making it very difficult for me to calculate an exact date when we have no more on stock. If we have enough on stock the cell should show “OK”.

I have attached a excel sheet that shows my question.

I hope that I have explained my challenge well enough but please write if you need additional information.

Thank you very much for your help in advance. :)
 

Attachments

Luke M

Excel Ninja
Hi Kasper,

First, let's define the inputs...
There are multiple products listed. Does each product use 1 pc of component, or do these products get used to make 1 component? I.e., if I have 8 wheels and 3 car bodies, I can only make 2 cars because I run out of wheels.

What does "EAU" stand for?

You use the DAYS360 function. Do you really want to use just 360/yr, or should we use actual dates?

The final question to answer is "When will we run out of stock?", correct?

I have a feeling we can borrow a function from the Finance group, the NPER. (Number of periods). If we think about it, the "stock" is like an investment, where we have an initial amount (pv), how much we're adding (pmt), and we want to know when we run out (fv=0). the function would then tell us how many periods (days) until we run out. But first, need to make sure I've got all the inputs sorted out.
 

Kasper Devald

New Member
Hi Luke,


Thank you for your reply and to answer your questions;


A) In this sample file each product use 1 pc of component.

B) EAU stands for "Estimated Annual Use", e.g. could also be the last 12 mth sales of each product listed.

C) I used the DAYS360 function because this was the only option I could think off, and I have to be honest that I don't really know what you mean by "actual dates":oops:

D) and yes "when will we run out of stock" on the component we use in the products listed.


This is a part of a large excel workbook we use to look at multiple "key" components that are used in manufacturing a variety of our products. When a component is obsolete we need to know when we are out stock in order for us to find an alternative component in due time.


I hope that you could use the information?
 

Luke M

Excel Ninja
Hi Kasper,

Yes, I think I was able to solve it. :DD

The DAYS360 function compares dates as if there were only 360 days in a year. This is commonly used in accounting/finance when it's nice to have even numbers, such as 30 days/month. For your planning purposes though, I think we want to know actual dates. In which case, the number of days between a date in A2 and A3 is:
=A3-A2
ie, straight subtraction.

Now, for the solution.
We'll refer to the number of parts being used per day as our "burndown". From the file, it looks like we're not buying/adding more components, so it's just a simple "what do we have currently - burndown". There *might* be a way to do this all in one formula, but it would be horribly complex I feel and hard to audit. Hopefully my way will suffice, or at least show the steps to take. Each time there is a product obsoletion, our "burndown" rate changes.

So, in the blue table, I pick out each date and put them in order. The 2nd column in table calculates how many pcs of component are left. Since the burndown rate between each listed date is constant, we can calculate this fairly easily by seeing how many pcs are getting used per day in this time frame and multiply that by number of days in this time frame. The last column gives us the estimate of how many days at current rate we have till we run out of stock. For the purpose of our file, we only care about the last number which is > 0, as this row tells us what time period we'll actually run out of stock.

When we know what time frame the run out occurs, we can take the last date we know there was stock + # of days till we run out of stock at that burndown rate, and voila! We have our answer. :)

In columns O:P, I manually checked the formulas by figuring out how many pcs we would have on each day, and the last date with postive stock matchs our formula solution, so I think we're good.
 

Attachments

Kasper Devald

New Member
HI Luke,

I never thanked you for your help back in 2015 :) The solution you provided really helped me but now I'm looking to put it down in one formula even as you write back in 2015 would be "horribly" complex.

The reason is that this calculation is only part of a larger and more complex excel solution. We used it to calculate the impact of obsolete components ( each component is one project) in our calibration systems. We have seen a dramatically increase in obsolete projects over the years 100+ incl. the need to save all those that have been solved in the same workbook. For each project I need to create a table based on you solution back from 2015 but its's to time consuming adding new project.

I have attached a test workbook showing the calculation I use today. I hope you could help perhaps not with the solution but e.g. another approach to it?
 

Attachments

Top