R
refresh
I'm throwing myself on the accumulated power of the forum and hope there are
people out there that can help me with this please?!?
I am trying to predict the age of outstanding work to the month end. I have
set up a table that has the population of cases in house at start of month.
In columns to right of this I have end of month date for each month I am
projecting to. Following this is a calc field for each date to work out age
of case at each month end.
This data is used through the indirect function to calculate the average age
of cases at the end of each month. To get it to work though I need to assume
a certain amount of work comes in each month. The projected cases need to be
added to the main list of o/s work. All of which I can do manually and this
is just about passable as method to provide the information based on one set
of projected volumes.
However, projected volumes change and when they do I need to manually go
through the cases added to ensure the right number for each is there. With
over 1800 cases in a month and that data spread across week days in the month
I get asked to provide a projected based on getting x in or y in. And off I
trot to cull or add cases ad infinitum.
What I would like is to be able to get a worksheet that asks the user how
far they want to project and what the monthly volume in is. From there I'd
like excel either to create the new population based on those values or to be
able to calculate what the new values are.
If creating a new population, it would also have to be create new end of
month dates if the projection exceeded current values and populate them.
I've looked at various averaging techniques and provided I use original
population as starting point I think it ought to be possible to use these but
I don't know how or where to start. Another part of the projection looks up
the oldest case worked on and most recent worked on per month and I think
this may be lost if I went to a formula?
I'd appreciate any pointers available. If clarification needed let me know,
these things become very focussed and whilst I can just about follow what I
write I appreciate what goes on in my head is not always as clear to others.
fingers crossed!
people out there that can help me with this please?!?
I am trying to predict the age of outstanding work to the month end. I have
set up a table that has the population of cases in house at start of month.
In columns to right of this I have end of month date for each month I am
projecting to. Following this is a calc field for each date to work out age
of case at each month end.
This data is used through the indirect function to calculate the average age
of cases at the end of each month. To get it to work though I need to assume
a certain amount of work comes in each month. The projected cases need to be
added to the main list of o/s work. All of which I can do manually and this
is just about passable as method to provide the information based on one set
of projected volumes.
However, projected volumes change and when they do I need to manually go
through the cases added to ensure the right number for each is there. With
over 1800 cases in a month and that data spread across week days in the month
I get asked to provide a projected based on getting x in or y in. And off I
trot to cull or add cases ad infinitum.
What I would like is to be able to get a worksheet that asks the user how
far they want to project and what the monthly volume in is. From there I'd
like excel either to create the new population based on those values or to be
able to calculate what the new values are.
If creating a new population, it would also have to be create new end of
month dates if the projection exceeded current values and populate them.
I've looked at various averaging techniques and provided I use original
population as starting point I think it ought to be possible to use these but
I don't know how or where to start. Another part of the projection looks up
the oldest case worked on and most recent worked on per month and I think
this may be lost if I went to a formula?
I'd appreciate any pointers available. If clarification needed let me know,
these things become very focussed and whilst I can just about follow what I
write I appreciate what goes on in my head is not always as clear to others.
fingers crossed!