Building to a target number

U

uship

Hello,

First time poster here.

Here is the situation. I am working on the finishing touches of m
companies' financial model which projects future revenue, income, etc.
Anyways, one of the inputs for the model is a collections rate. W
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 tha
rate.

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

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

Any thoughts
 
P

Pete_UK

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
 
Top