+ Reply to Thread
Results 1 to 4 of 4

Spread Cash Flow over Different Time Periods

  1. #1
    Registered User
    Join Date
    11-23-2010
    Location
    Charlotte, nc
    MS-Off Ver
    Excel 2007
    Posts
    2

    Spread Cash Flow over Different Time Periods

    Hello this is my first post so thanks in advance for your help. I have a spreadsheet I am developing that requires the user to enter three variables (1) Project Start Date, (2) Project End Date, (3) Total Project Cost. I then need the Total Project cost to be distributed based on a specific 12 month cash flow profile (i.e. 5% in the first month, 7% in the second month, 3% in the third month, etc...). The % allocation by month is fixed over 12 periods, but I would like it to be applied to a project that may be longer or shorter than 12 months yet still maintain the same profile (cash flow curve shape). I have looked into Fourier Analysis, but it can't be applied in this case. Anyone know if a quick solution?

  2. #2
    Registered User
    Join Date
    11-22-2010
    Location
    Finland
    MS-Off Ver
    Excel 2003, 2007
    Posts
    95

    Re: Spread Cash Flow over Different Time Periods

    Do you want the new distributed cost to be monthly or cash flow divided? E.g. if your % profile would be 5,7,3,10,8,3... and you had a $100k project from Jan 1 to Jun 30: should it be distributed like a) Jan $12k, Feb $13k, Mar $11k ... or b) Jan 1st half $5k, Jan 2st half $7k, Feb 1st half $3k etc?

  3. #3
    Registered User
    Join Date
    11-23-2010
    Location
    Charlotte, nc
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Spread Cash Flow over Different Time Periods

    The cash flow %'s are monthly cash flows based on historical information. For instance if the Total Pj Cost is $100K with a start of Jan and an end date of June I would like the cash flow profile (%'s) of 12 periods to be condenced into 6. This is easy if the time periods are multiples of 12 (for 24 months just divide the 12 month % allocations in half), but if the project is say 8 months it becomes a little more tricky. - Thanks!

  4. #4
    Registered User
    Join Date
    11-22-2010
    Location
    Finland
    MS-Off Ver
    Excel 2003, 2007
    Posts
    95

    Cool Re: Spread Cash Flow over Different Time Periods

    Okay this was way more tricky than I thought it would be :D

    I tried to find a solution with the "traditional" easy understandable functions and with a matrix. After couple of hours I still couldn't fit the profile perfectly but it went close. It works only with 0-365 day projects but seems to be roughly reasonable...

    The significant problem with this try out was that I choose a month to be the basic to calculations. Maybe day-based differential functions or Fourier would've brought better results with less amount of work. The calculations also are not quite right because I used an average 30-point-something days per month: e.g. if you choose a project dates 1 Jan and 1 Aug the cost of Aug seems to be zero. Aug 2nd seems to be also very much at zero...


    Anyway here's my approach. Hope it helps. :D
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1