WOS

[tracy]

Is there a way in Excel to calculate out the weeks of
supply? For example: product xyz has a wos of 8, how can
I find out how many units I need to have on hand today to
cover the next 8 weeks of demand? Note, the wos can be
different for every product. Thank you!

[EZ Money]

This seems so straightforward I must be missing something. <g> If the WOS is
8, it appears you would need 8*8 for that period of time.

=WOS*Number of weeks

--
Greeting from the Gulf Coast!
http://myweb.cableone.net/twodays
"tracy" <tracy....@wearguard-crest.com> wrote in message
news:00eb01c35b75$8983bfc0$a401...@phx.gbl...

[tracy]

Thanks for replying. That would work if each week's
forecast was an equal value, but in this case it's not.
Wk1 could = 2units, wk2 could = 20units and wk3 could =
50units. If the WOS = 2 wks, than on wk1, I would need to
have 72 units on hand to cover the current week as well as
the future two weeks forecast. Is this still unclear?
Thank you!

>.
>

[Glenn Schwandt]

Assuming WOS in A1 and wk1...wk26 forecast in A2:Z2 (or however far you go):

A3=SUM(INDIRECT(ADDRESS(ROW()-1,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN()+$A$1)
))

"tracy" <tracy....@wearguard-crest.com> wrote in message

news:04e501c35b89$cf700ca0$a101...@phx.gbl...

[Glenn Schwandt]

In case you are interested in the reverse process (you know how much
inventory you have, your weekly sales forecast and your weekly production
schedule and you need to determine WOS), put the beginning inventory
quantity in A1, forecast again in A2:Z2, your weekly production in A3:Z3,
and then the following:

B3=A1+A3-A2

Fill this across to Z3. This is your weekly beginning inventory.

A4{=SUM((A1>MMULT(A2:$Z2,(COLUMN(A2:$Z2)>=TRANSPOSE(COLUMN(A2:$Z2)))+0))+0)}

This is an array formula. Hold down Shift and Ctrl when you hit Enter and
Excel will add the {brackets} for you. Fill this across to Z4. This is the
number of full weeks that the beginning inventory of that week will cover.

The formula "breaks" when the inventory is greater than the combined
remaining forecast, counting only the remaining number of weeks on the
forecast. It also requires a number in every cell from A2:Z2, so use a zero
instead of a blank.

One more...

A5=A4+((A1-SUM(A2:INDIRECT("R"&ROW()-3&"C"&A4+COLUMN()-1,FALSE)))/(INDIRECT(
"R"&ROW()-3&"C"&A4+COLUMN(),FALSE)))

Fill this across to Z5. This will add the portion of first "uncovered" week
that the beginning inventory will cover.

Finally, thanks to whomever it was who helped me with this when I was in
need...can't remember for sure, but I think it was Harlan Grove.

"Glenn Schwandt" <schwa...@aoldot.com> wrote in message
news:vj072ef...@corp.supernews.com...

[tracy]

THANK YOU GLENN!! I really appreciate your help with
this. With a little adjustment to the formula, it worked
perfectly and you also introduced me to the world of
arrays that I know will make my job much easier. Thank
you again!

>.
>

[Glenn Schwandt]

Your welcome! Just trying to give a little back...

"tracy" <tracy....@wearguard-crest.com> wrote in message

news:0c2301c35c2b$eceeadc0$a601...@phx.gbl...

> THANK YOU GLENN!! I really appreciate your help with
> this. With a little adjustment to the formula, it worked
> perfectly and you also introduced me to the world of
> arrays that I know will make my job much easier. Thank
> you again!

<SNIP>