30 Day Forecast Report---How do I go about ti

[Danglez]

Here is the type of data my database contains, I am a newbie so I don't really know how to approach this problem

Counterparty ValueDate Maturity Date Amount Cashflow
BankA 8/9/04 8/10/04 $100 -$110
BankB 8/9/04 8/10/04 -$200 $220
BankC 8/10/04 8/11/04 $300 -$330
BankD 8/10/04 8/11/04 -$400 $440

What I need is a report that outlines the positive and negative cashflow for the next 30days.

On 8/10/04:
Based on the maturity dates of deals with Banks A & B I have negative cashflow of $110 and positive of $220 (Using the Cashflow field)

Based on the Value Dates of deals with Banks C & D I have negative cashflow of $400 and Positive of $300 (Using the Amount field)

In total my positive cashflow is: 300+220= $520
my negative cashflow is: 110+400= $510
my net cashflow is hence: 520-510= $10

On 8/11/04 Based on the Maturity Dates of deals with Banks C & D my positive cashflow is 440 and negative is 330 (using Cashflow field). SO my net on that day would be $110.


The basic format of the report/Query I want is this:

Date PositiveCashflow NegativeCashflow NetCashflow
8/10/04 520 510 10
8/11/04 440 330 110
etc...

I must warn that I don't know too much about VBA coding, I'm just doing this as a project for a summer internship, but I am willing to try and figure things out.

 

[Xoanon123]

Where are you getting the 'Date Amount' & 'Cashflow' info from? You can easily setup a report to formulate your data, but the initial data or formula needs to be in the table (not really in the report, since you can show only what you want in the report).

Do you have some form of initial amount you are working with for each bank?

thanks

 

[Danglez]

This is all in a query called 'cashflow calculations' which uses the interest and the amount to calculate the cashflow. And just a note, you misread the field due to formatting. It is 'Maturity Date' and 'Amount.' I have all this information on a table, I just don't know how to make a report based on the rules I outlined in the example above.