Cashflow

[Saintsman]

Can anyone help with a simpler way
Problem
Produce a set of cashflow data (cash spent) for a construction project -
which I can then benchmark against similar projects
Data (extract sample only)
A=period (days)
B=cumulative period (days)
C=spend for that period
D=cumulative spend
A B C D E
28 28 £641,727 £641,727 £22,919
34 62 £705,670 £1,347,397 £20,755
29 91 £491,425 £1,838,822 £16,946
28 119 £424,422 £2,263,244 £15,158
35 154 £773,256 £3,036,500 £22,093
28 182 £763,561 £3,800,061 £27,270

I need to end up with a set of %'s for the project. Period should be 5%;
10%; 15% etc ie
5% period; 3% cost
10% period; 5% cost
etc etc to 100% period; 100% cost

So, if 5% period = 36days the amount spent would be (28days@£22919) +
(8days@£20755)
The plan is to produce an S-curve which can be used for similar projects
Anybody able to help?

Saintsman

 

[JLatham]

This doesn't work properly until all periods/expenditures have been reported,
but will give you the numbers you want at that point in time.

Assuming first entry is in row 2, in cell F2 put this formula:
=A2/B$7
and 'fill' the formula down to row 7, format F2:F7 as % and you should get
these values using your example values:
15%, 19%, 16%, 15%, 19%, 15% for the percent of total period.

in cell G2 put this formula:
=C2/D$7
and again, fill down, and format as % and it will give these values for
percent of total expenditures:
17%, 19%, 13%, 11%, 20%, 20%

 

[Saintsman]

This wasn't the solution I'm afraid
I need to define a set % for values - I am using 5% intervals ie a 5% of
period x% of money spent. This will enable me to plot more than 1 project
(usually I need about 6 to give be a good benchmark)
The long handed method being used involves literally hundreds of rows - 1
row per day & calculating the average spend per day for that period. I can
then calculate the period %'s 5%;10%;15% etc
Sorry - it's a bit difficult to explain

Saintsman

 

[JLatham]

Download this file and see if I've come up with anything that might help you
out. I set up a couple of dummy projects on a sheet, and then set up a table
that you could use to come up with the percentages you need. Basically it
has fixed points at 5% intervals and you provide it a few pieces of data
about where/what the information for a project is on the worksheet, or in the
workbook, and it then finds the cumulative expense at that point in the
project (to nearest day) and displays that as a percentage of the total
actuals/budgeted amount, depending on which value you provide to it. That's
why I said earlier having daily entries is actually a help in this one.

It is now setup presuming all projects are on a single sheet. But if each
project occupies a single sheet, you could set up a table like this on each
sheet, then on another sheet, just have references to the different values
returned for each project into one consolidated table to do your graphing
with.

Here's link to the download (no macros, all worksheet functions, about 135KB)
http://www.jlathamsite.com\uploads\PercentExpended.xls

 

[Saintsman]

Blimey!
Thanks very much - a lot of work went into this & very much appreciated
It's not too different to the direction in which I found myself heading - ie
each day has set of data.
I will always have a set of data - eg
Month payment
Aug-01 0
Sep-01 403,000
Oct-01 955,000
Nov-01 1,464,900
Dec-01 1,678,800
Jan-02 2,097,000
Feb-02 2,532,000
Mar-02 3,055,100
Apr-02 3,563,168

Some of the projects can be very long & I was trying to reduce the number of
rows, it needs to be flexible to accept 6 month to 60 month long projects
Ideally I would simply type the data & some formula would give me my %
breakdown ie 5%; 10% etc etc.
Despite the size of a project there is a 'best fit' cashflow which we
produce prior to the project start - building types typically have similar
spend profiles which is not affected relative to their size
It seems that we will just have to live with rows & rows & rows of data
Thanks again - I will use your ideas

Saintsman