Forecasting Dates/$ Amounts For Contracts

  • Thread starter Thread starter pw034
  • Start date Start date
P

pw034

I have an excel spreadsheet that is used for tacking contracts.

The question I have - is there a way to have excel automaticall
forecast dates and assign dollar values to the cell?

Example:

In J20 I have "signed contract date" (10/3/2003), in O20 I hav
"contract duration" in terms of months (12), in R20 I have "contrac
value" ($241,668), and in Z20 I have "average monthly income fro
contract" (R20/O20).

Since signed contract dates, and contract duration will vary, is ther
a way to have excel automatically take the signed contract date an
forecast out for the term of the contract, placing the average monthl
income in the cell under the forecasted date?

Ideally, I would like it to look like:

Customer Start Date Month Month Month ...
XYZ 10/3/2003 11/3/2003 12/3/2003 1/3/2004 ....
$20,139 $20,139 $20,139 $20,13
....


Instead of going in by hand and creating these tables, I would like t
automate the process. This way, once a date is entered in the signe
contract date, the table is created.

Thanks in advance for any help you can offer!

Pau
 
It's not clear where you want the summary to appear, so in this example
it starts in cell A22.
A B
22 Customer Start
23 XYZ =J20
24 =Z20

C23 =IF(COLUMN()-1<=$O$20,DATE(YEAR(B23),MONTH(B23)+1,DAY(B23)),"")
C24 =IF(COLUMN()-1<=$O$20,B24,"")

Copy the formulas in C23 and C24 across to accomodate the maximum number
of months.
 
Back
Top