Hi, Thankyou in advance to anyone that can help.

I can explain in words what i am trying to do and understand certain parts of the theory, hower creating a suitable solution for my problem is casuing me difficulty.

Scenario - ( this is not real, just hypothetical problem i am trying to solve for future use)

So.. I rent / lease out computer equipment for periods of time of 1 week to 156+ weeks.

there are 5 different products i lease, indexed from 1 ( least income generating to 5 ( most income generating), i call these "points". ie a modem is 1 point and may generate $2 - $4 per week depending on negotions and the hire period. A pc is 3 points generating between $20 - $50 per week, and a server possibly indexed as 5.

The minimum hire period (MHP) is contractual so i always get the full value and i assume no early termination) So all contract run at least to the end of their MHP, and some may go into run on.

A typical contract would be 1 modem / router and 3 PC's on a MHP of 26 Weeks. Points = (10)

or 2 pcs for 4 weeks (6 points)

-----
Through past data i can see that 98% contracts on relativley small MHP 0 - 6 weeks terminate at the end of their MHP. This is usually because they were used for an event where there was little possiblity of it lasting longer.

The other % terminate rapidly over the next 2 weeks, and the odd contract runs on for a long time.
------

on the flip of this, contracts on a hire period 156+ weeks, only 40% terminate at the end of the MHP, the other 60% come in over the next 6 months ( again witht he odd exception of 1 or 2 which stay out for a long time). This is usually because they were hired for for a big project which are prone to delays and such.

there are other common mhp's 26 52 104 ( although not necessarily linear relationship between run on and mhp)
--------

looking into a particular month say May i can see that there are 6 contracts in the band MHP 0 -6 Weeks which the MHp finishes with a toal points value of 100.

and 2 contracts on 156week MHP with a points value of 1000 which are also at the end of their MHP.

a simple estimate would be 98% of 100 wil terminate thus 98 points coming back + 60% of 1000 thus 600 points coming back, so in total 698 points coming back.
-----

I would like to turn this into a usable model with groups of MHP
( 0-5) (5-10).... (156+) allowing me to forcast the amount of points that will be coming back into the warehouse.

----- So i know contacts with certain MHP have these charactaristics ( there are other variables such as time of year but ignoring them for now)

example figures

MHP of Contract %term on MHP end MHP+2 weeks MHP+4 etc.
(0 - 5) 98% 1.5% 0.49%
(6 - 10) 96% 3% 0.9%

up to

(156+) 60% 6% 7%

so 99.99% of contracts on MHp ( 0-5) will have terminated 4 weeks after then end of the MHP period. Longer MHP ie 156+ will terminate at a steady rate up to a year past the end of the MHP and the odd one till the end of the forseable future.

------

Data i would get from sales sheets show for May live contracts

Contract MHP Due to end Points Value

3 03/05/2010 10
5 05/05/2010 40
5 15/05/2010 20
156 04/05/2010 60
156 17/05/2010 40
156 18/05/2010 30

---------------

My Aim is to create a model possibly in excel that could forcast with some degree of certainty how many points wil be coming back in. However it i think it has to be live as there may also be contracts in the list that should have terminated 2 3 4 5 weeks ago thus the probability of them terminating increases as time passes to some degree.

So at the begning of the month ceterus paribus i can predict what will come back.



Apologies if this does not make any sense at all , however any help would be greatly appreciated. Please let me know if there is any other info or assumptions needed.

Regards

Chris