Weeks of Supply Function

[jeffbert]

I need to come up with a more efficient way to calculate my weeks of supply.

The way that they want the calculation to be done is as follows.

Week # Sales$ Inventory$ WOS
46 $31,567 $270,114 6
45 $36,686 $269,682 6
44 $60,120 $264,507 6
43 $44,616 $242,953 6
42 $32,092 $260,974 6
41 $39,601 $244,415 5
40 $36,735 $220,009 5
39 $34,802 $229,787 4
38 $50,207 $257,078 5
etc.

The WOS for week 46 is the Inventory$ less week 46 sales, less week 45
sales, less week 44 sales, etc. Continue subtracting out weekly sales until
your inventory is less than 0. Count how many weeks that you subtracted out
sales and your number was positive. This is the WOS.

I have "helper" columns set up to do this, and am getting the correct
answers. However, this clogs up a large spreadsheet, and thought that there
might be a built in function to accomplish this.

thanks

Jeff

 

[Jim Cone]

Jeff,
I can do it with one helper row...
Your data (and header rows) from below was entered into B5:E14

This formula was entered into A1 and filled across...
=SUM($C$5:OFFSET($C$5,COLUMN(),0))

This formula was entered into E6 and filled down...
=MATCH(D6,$A$1:$Z$1,TRUE)
(assumes after 26 weeks inventory that nobody cares)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"jeffbert"
(e-mail address removed)
wrote in message
I need to come up with a more efficient way to calculate my weeks of supply.
The way that they want the calculation to be done is as follows.

Week # Sales$ Inventory$ WOS
46 $31,567 $270,114 6
45 $36,686 $269,682 6
44 $60,120 $264,507 6
43 $44,616 $242,953 6
42 $32,092 $260,974 6
41 $39,601 $244,415 5
40 $36,735 $220,009 5
39 $34,802 $229,787 4
38 $50,207 $257,078 5
etc.

The WOS for week 46 is the Inventory$ less week 46 sales, less week 45
sales, less week 44 sales, etc. Continue subtracting out weekly sales until
your inventory is less than 0. Count how many weeks that you subtracted out
sales and your number was positive. This is the WOS.

I have "helper" columns set up to do this, and am getting the correct
answers. However, this clogs up a large spreadsheet, and thought that there
might be a built in function to accomplish this.
thanks
Jeff