Calculations by details -formula/VBA help needed

Sorry if my title is kind of cryptic. I need formulas, which I'll then put into a macro I'm writing. But I need to figure the formulas first.

Here's the overview of the need and the problem:
We have contracts in place. Some contracts cross regions, some cross PACs (program areas), some cross LBB Accts (specific expenditure accounts). In creating projections (which is done by region, and within region by PAC and LBB Account) we base our projections on past expenses. We want to limit our projections by any limits on our contracts. So, we first figure the expense by region in dollars and % of the total contract expense. We multiply the expense % by the contract amount to determine the max contract amount for the region (IE If Region has spent 10% of the expense, their "Limit" is 10% of the max contract amount). We then narrow it down to the PAC level, and will need to narrow it down to the LBB Acct level.

I can't change that methodology, but I need. to figure what kinds of formulas I could put in place to determine these amounts, knowing that every month new contracts are going to come in, and expenses against new LBB Accts and new PACs. Any help is greatly appreciated, and if I more explanation is needed please let me know.

Thanks,
John

The attachment shows information I have (in green), and information I need (in red). The formulas I have in place work for the old data I have in place, but each month I'll be adding more (see the bottom part of the "Spread of Max" sheet). When I add new lines in, some of the formulas will have to be recreated/changed to incorporate the new details.