• 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.

Stock coverage with Sales Forecast consumption

C

carics

Member
Hi,


I am doing production planning and there is an issue that has haunted me for a while.

When you have a stock figure and based on a weekly sales forecast figure (that may change from week to week), how do you calc how many weeks you can cover with that initial stock?


Manually:


Stock=100

FC=10/wk


Coverage = 10 weeks = 70 days


That would be easy to calculate if the FC is the same for every week. But if you have different figures for different weeks I need to subtract from the initial stock this week's FC; then if the stock is >0 I need to subtract another week, and then another and another. When I get to the 1st week I cannot cover (the first one where stock is <0) then I need to divide the total FC for that week by the total work days (5) to check how many days of that week I can still cover. Then, multiplying per 7 the number of covered weeks and adding the number of days I can cover in the last one I get the total coverdays.


I am currently doing this either by hand or by a sheet I made with lots of figures and large IF functions; and even with this sheet I just can calculate a max number of weeks due to maximum sentences in a cell calculation. Is there an easy way - or even a function I do not know - to do this?


Thanks in advance
 
Carics,


Each week you will have a Coverage = (Initial Stock - sum(weekly stock usage))/FC

and to make sure its greater than zero


=if(Initial Stock - Sum(Weekly Stock Usage)<=0,0, +(Initial Stock - sum(weekly stock usage))/FC)

so for an example

[pre]
Code: 
A1: Initial Stock
B1: =100
A2: FC
B2: =10
C3:H3 Wk1 ..Wk6
C4:H4 Numbers    Your weekly usage
C5: =+IF(($B$1-SUM($C$4:C4))<=0,0,+($B$1-SUM($C$4:C4))/$B$2)
Copy C5 across to H5
[/pre]
 
Wow, that was fast! Thanks a lot.


Nevertheless the coverage you calculate is based on an average FC, and that I can do. What I would need is a way to calculate it with real FC (data in C4:H4) and not averages. Do you know a way?


Thanks
 
You can add a line for FC on a week by week basis and change the formula to refer to that extra row
 
That would still be an aproximated value, and not the real one.


For example, if you put "10" in the FC, you may reflect an average of the following values:

3; 5; 7; 10; 13; 15; 17


Then, if your initial stock is 25, you can REALLY cover 28 days (3+5+7+10) while for an average of 10 per week you calculate only 17,5 days.


What I would need is really a sort of "stock consumption" by the FC until it reaches zero level.
 
Can you post an example somewhere with some notes on what you are trying to achieve ?
 
Hi again,


Sorry, probably my explanation was not that effective.


I now post an example.

http://www.2shared.com/document/J9bF86jV/CD_online.html


In the first part (rows 1-23) we may see what would be the manual calculation of this coverage.

The second part (rows 30-44) have an automated calc I deveolped to do it and it works fine but it seems too much and I would like to see it simplified.


Do you know a way to do it?


thanks again
 
Hi again,


Sorry, there seems to be a problem with the upload to 2shared, so I leave you here a mediafire link:

http://www.mediafire.com/file/zwdzojjyjdj/CD.xls
 
Caricks

Try the following function which needs to be copied into a Code Module

[pre]
Code: 
Private Function HMW(Remains As Single, Wks As Variant) As Single

HMW = 0

For i = 1 To Wks.Count
If Remains - Wks(i) < 0 Then
i = i - 1
Exit For
Else
Remains = Remains - Wks(i)
End If

Next
HMW = (i * 7) + (5 * (Remains / Wks(i + 1)))

End Function
[/pre]

To use this in your example in D34 type =HMW(D33,E32:$N$32)

and copy it accross
 
Hi Hui,


Thanks a lot; just an additional question, to put it into a code mode what do I need to do? I right clicked on Sheet1 and then View Code. Then I pasted it there bt the HMW function does not work (#NAME?). I am still a noobie in VBA so there is probably something I am not doing correctly.
 
Alt F11

Find your workbook

Right Click and Insert Module

paste the code into the Empty pane on the right

Close the VBA window

now enter the formula
 
You must log in or register to reply here.
Back
Top