Help Needed - Calculation on Week Forward Coverage of Inventory
Wan Josephine
ending inventory against sales forecast. Example: Ending
inventory is 350, forecast for month 1 - 200, month 2 -
300; how many forward weeks will the inventory covers and
what if there is no forecast, can I use a fixed number?
Norman Harker
I'm having difficulty understanding what you want.
But first you need to set up your date matrix making sure that you enter
dates in a form recognized by Excel as dates. Best and easiest is probably
to use an entry and format of dd-mmm-yyyy (e.g.. 12-Jan-2003).
You may need to address the problem of setting up a sequence of dates one
month apart. You can do that manually with the help of Edit > Fill > Series
or you can use formulas to do it automatically from a user input base date.
You decide!
And then you have a problem that all months are not of equal length and that
weeks do not fit exactly into months. Week numbers are usually ascribed on a
year (rather than month) basis but there are at least four different week
numbering methods:
Absolute where Day 1 of Week 1 is 1-Jan, and Day 1 of Week 2 is 8-Jan. This
gives a week 53 of 8 or 9 days.
WEEKNUM option 1 where Day 1 of Week 1 is 1-Jan but Day 1 of Week 2 is the
following Sunday. This gives odd numbers of days in weeks 1 and 53.
WEEKNUM option 2 where Day 1 of Week 1 is 1-Jan but Day 1 of Week 2 is the
following Monday. This gives an odd number of days in weeks 1 and 53.
ISO8601:2000 where Day 1 of Week 1 is the Monday of the week that Jan-4 is
in (equivalent also to saying it's the Monday of the first week of the
calendar year which has a Thursday in it) This gives exactly 7 days to every
week but some years day 1 of week 1 will fall in the last few days of
December.
There are formulas that exist to help on all of these options.
But if you are looking at dividing your months into weeks, you must clearly
define how you are going to do it. There are a number of different ways.
HTH
--
Norman Harker
Sydney, Australia
If you missed Christmas there's another on 7 January!
Happy New Year
(Whenever you celebrate it and whatever number you give it)
njha...@optusnet.com.au
"Wan Josephine" <wa...@dow.com> wrote in message
news:01ff01c2ae0f$743a5e80$d7f82ecf@TK2MSFTNGXA14...
wan
Thanks for your response. I have inherited a file which I
need to continue maintaining and find the formula very
complicated and difficult.
This is how the spreadsheet is structured:
Description Oct Nov Dec Jan
Lentrek 400EC .25L Inv 1683 1593 #N/A #N/A R1
WFC 16 16 #N/A #N/A #N/A R2
Lentrek 400EC 20L Inv 10500 15420 #N/A #N/A R3
WFC 7 10 #N/A #N/A #N/A R4
Lentrek 400EC .25LF'cast 0 0 0 0 R5
Lentrek 400EC 20L F'cast 6000 6000 6000 5635 R6
R1,3 - is the monthly ending inventory
R2,4 - is the calculated week forward coverage based on
this formula =IF(WFC!AC33=0,0,IF(WFC!AC33<=FAccy!AC34,WFC!
AC33/FAccy!AC34*4,IF(WFC!AC33<=(FAccy!AC34+FAccy!AD34),
(((WFC!AC33-FAccy!AC34)/FAccy!AD34*4)+4),IF(WFC!AC33<=
(FAccy!AC34+FAccy!AD34+FAccy!AE34),(((WFC!AC33-(FAccy!
AC34+FAccy!AD34))/FAccy!AE34*4)+8),IF(WFC!AC33<=(FAccy!
AC34+FAccy!AD34+FAccy!AE34+FAccy!AF34),((WFC!AC33-FAccy!
AC34-FAccy!AD34-FAccy!AE34-FAccy!AF34)/FAccy!AF34*4)
+12,16)))))
R5,6 - is the reference on FAccy
You will note that the formula is capped off at 16 meaning
another number greater than 16 will show as 16.
What I wanted to do is use a formula that is simple and
easy for a new person to understand and continue with the
updating.
Regards
Jo
>.
>
Norman Harker
I need to see this one as it involves variables on other sheets. I've
updated my virus definitions this morning and the Norton "weather forecast"
isn't bad so you can send me a copy direct. (Don't post to newsgroup though
as that is actively discouraged for virus and download time / cost
considerations).
No promises but I'll take a look and see if I can help or ask direct
questions to others who can.
Regards
--
Norman Harker
Sydney, Australia
If you missed Christmas there's another on 7 January!
Happy New Year
(Whenever you celebrate it and whatever number you give it)
njha...@optusnet.com.au
"wan" <wa...@dow.com> wrote in message
news:08c401c2ae20$ea3e8eb0$8af82ecf@TK2MSFTNGXA03...
Norman Harker
PS. Sorry for getting your name wrong before. Many of my Asian students have
told me the correct order but I still get it wrong. Mind you, it doesn't
help when they come here and swap their names round themselves!
Regards
--
Norman Harker
Sydney, Australia
If you missed Christmas there's another on 7 January!
Happy New Year
(Whenever you celebrate it and whatever number you give it)
njha...@optusnet.com.au
"wan" <wa...@dow.com> wrote in message
news:08c401c2ae20$ea3e8eb0$8af82ecf@TK2MSFTNGXA03...