Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cashflow
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cashflow
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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cashflow
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cashflow
Can we agree that we cannot perform these calculations until we either have
firm values for the total duration and expenses - either planned or actuals? What I'm hearing you say now is that you need to take a long bunch of entries relating to any given project and determine where the 5%, 10%...95%,100% points are either in time or $ and obtain the values at that point. Is that correct? The fact that you DO have many rows actually helps - one row for each day should make it easy to determine the %-time passed and toss in the numbers. "Saintsman" wrote: 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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cashflow
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" wrote: 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" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cashflow
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 "JLatham" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Waterfall cashflow distribution | Excel Discussion (Misc queries) | |||
IRR weekly basis cashflow | Excel Worksheet Functions | |||
Annual From Monthly Cashflow | Excel Worksheet Functions | |||
Help: Contingency based fee formula / cashflow analysis | Excel Discussion (Misc queries) | |||
how do I set up cashflow budgets for different projects | New Users to Excel |