Formula for Calculating Forward Weeks Cover....

[ukgthor]

I need to calculate inventory forward weeks cover for the coming months. Based off of recent forecasts, I have the month end inventory positions and the Cost of Goods (cogs) by week also.

Can someone guide me on how to use a formula to calculate the forward weeks cover (i.e. how many forward weeks cogs can fit into the month end inventory position).

Hopefully I've explained myself correctly.

Thanks in advance.

[arthurbr]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[ukgthor]

Thanks for the advice.

See attached spreadsheet for the an example of what I am trying to acheive.

The 'Forward Weeks Cover' highlighted in yellow have been calculated by me manually, hence the requirement for a formula. Cell C23 has been calculated by taking the P1 Inventory position (Cell C3) and working out how many weeks (from Week 5 the first week after the period end) COGS fit into this Inventory number.

Ideally, I would also want to be in a position to derive what the the Inventory value should be by using the COGS and Forward weeks cover.

Hopefully this is clear.

[ukgthor]

-------------------------------------------------------------------------------------------------------------------------------
Now with attachments...

[ukgthor]

Anyone had any ideas on how to solve my issue ?.... let me know if you require any further clarification over and above that already provided.

Thanks.

[hilbily]

Hi
How did you work out COGS for a future date?
I would use the average COGS for an overall position.
Would this suit your needs?
eg: current SOH / (( prev 3 months COGS / ( prev 3 months workdays / 5 )) = weeks cover.

[hilbily]

Hi
I have uploaded a file with the first year done as per your COGs.
This should give you the number of full weeks cover.

You will need to add the year to all your P1...Px entries to do extra years.
eg: P1 = P1 2010, P2 = P2 2010.
I would actually use the dates myself.

The formula in the attached will get you started anyways.
from cell C23:
=MATCH(C3+INDEX($31:$36,MATCH($B3,$B$31:$B$36,),MATCH(D$2,$C$11:$BB$11,)),31:31,1)-(MATCH(D$2,$C$11:$BB$11,))
Good luck.