Building to a target number

Hello,

First time poster here.

Here is the situation. I am working on the finishing touches of my companies' financial model which projects future revenue, income, etc. Anyways, one of the inputs for the model is a collections rate. We foresee ourselves increasing our outstanding collectibles over time. I'd like to be able to have an input of a collections rate, i.e., 90% and an input of a date (a month and year) where we'd actually hit that rate.

I'd like the model to increase our average monthly collections rate evenly from the past actual month to the date inputted where we'd hit our target collections rate (the columns are arranged by date in the model).

For example. If July's collection rate was 80%, and I'd like to hit a collections rate of 90% in July of 2007 and to remain at 90% going forward. How would I create a flexible formula to do this, where I can easily change the date and collections target assumptions?

Any thoughts?

I set up the following headings in A1:A6 respectively:

Current Date
Current Rate
Target Date
Targ Rate
Months
Monthly rate increase

I entered the following data into B1 to B4:

01/07/2006
80%
01/07/2007
90%

I entered the following formulae in B5 and B6:

B5: =ROUND((B3-B1)*12/365,0) formatted as number with 0 dp
B6: =(B4-B2)/B5 formatted as % with 2 dp

giving 12 and 0.83% respectively.

Change the values in B1 to B4 to see the monthly rate increase vary. Is
this the kind of thing you wanted?

Hope this helps.

Pete

uship wrote:
> Hello,
>
> First time poster here.
>
> Here is the situation. I am working on the finishing touches of my
> companies' financial model which projects future revenue, income, etc.
> Anyways, one of the inputs for the model is a collections rate. We
> foresee ourselves increasing our outstanding collectibles over time.
> I'd like to be able to have an input of a collections rate, i.e., 90%
> and an input of a date (a month and year) where we'd actually hit that
> rate.
>
> I'd like the model to increase our average monthly collections rate
> evenly from the past actual month to the date inputted where we'd hit
> our target collections rate (the columns are arranged by date in the
> model).
>
> For example. If July's collection rate was 80%, and I'd like to hit a
> collections rate of 90% in July of 2007 and to remain at 90% going
> forward. How would I create a flexible formula to do this, where I can
> easily change the date and collections target assumptions?
>
> Any thoughts?
>
>
> --
> uship
> ------------------------------------------------------------------------
> uship's Profile: http://www.excelforum.com/member.php...o&userid=37206
> View this thread: http://www.excelforum.com/showthread...hreadid=569214