Cash flow analysis in MS-Excel

Now to something completely different:

(might be trivial for Excel Professionals, so forgive me that i am so naive)

My management asked me some days ago for the cashflow of my current project. I know basically how this needs to be calculated: You define a period of time, take all money that comes in and substract all money that goes out. You do this for every period and then you have a cashflow per period. Then you want to calculate an accummulated cash flow so you see, if you need to put money into a project or if it brings in all that it costs throughout the runtime. So far, so good. But how to do it automatically. What do i have?

Incoming invoices (those which are already there and the forecasted ones) with due dates:

10,000.-- EUR on Oct 03 from XYZ

5,000.-- EUR on Oct 23 from BlaBla

20,500.-- EUR on Nov 05 from ABC

etc.

Then the invoices we send out with their respective due dates:

100,000.-- EUR on Oct 01

50,000.-- EUR on Nov 01


... both are lists on separate pages in my excel workbook. Now i need to split the lines by month and add up the amount of a certain month. How to do this? Pivot Tables are cool for such things but not flexible enough. After some research i found a great Excel function for this called "SUMPRODUCT" (in german: PRODUKTSUMME).

Assuming, the due date of the incoming invoices is in column E of a sheet called "invoices in" and the according amounts are in column D, you would calculate the incoming amount for a given month like this:

=SUMPRODUCT((MONTH('invoices in'!$E2:$E200)=10) * (YEAR('invoices in'!$E1:$E200)=2009) * 'invoices in'!$D2:$D200)

This will give you the sum of all amounts (column D) where the month of the due date is 10 and the year is 2009. You can also replace the 10 by a reference to another date field (Oct 1 2009) (in cell C5 for example) and then make a table with date fields in the header for each month and below this you just fill this formula in a slightly modified form:

=SUMPRODUCT((MONTH('invoices in'!$E2:$E200)=MONTH(C5) * (YEAR('invoices in'!$E1:$E200)=YEAR(C5)) * 'invoices in'!$D2:$D200)

This way you can count all incoming vs. all outgoing cash. All you then need to do is to subtract and there is your dynamic cash flow. What i added then was a nice diagram of course. Works well and looks good ;-)

Auf Facebook teilen


« SVN update client performance under windows - Javascript: The good parts »