• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with Formula to Calc Funding Spend Down

aklewe

New Member
Hi there,

I have a problem at work I am trying to solve, trying to model how we will spend down available funds in a pivotable table.

Basically, we have a complex program where we get incremental funding on a regular basis and each funding document will provide an increment for multiple programs. The funds expire at different times, so there is a priority order in which we want to apply our costs as they are incurred against the available funds.

I have created a very simplified version of the problem in the attached file. The real-world version has dozens of programs and hundreds of funding lines. Basically, I have one data table with the funding information, and another with our forecast by program information across the months of the contracts period of performance. In the third tab, I am trying to create a combined table (and it must be pivotable) that will calculate across the months how funding will be expended against each funding line. So once we 100% exhaust funds on one line, the balance will be applied to the next available funding line in priority order. And then we can also see when each funding line is 100% expended.

Preferably, this will be done without macros, and with a formula that could just be dragged down across any new rows to the table... but if a macro is what it takes, it could be acceptable.

Hopefully my description of the problem makes sense. Attached is my demo file. I appreciate any help!!
 

Attachments

  • Funding Tracker Test.xlsx
    14.5 KB · Views: 2
Back
Top