Way to spread budget cost across quarters/years?

Antonio de Sousa

Hi Community,

I have a row of projects and I am tracking budget cost, duration (months), expected start date and expected end date for each project.

I want to divide the budget cost evenly for each quarter where the project is expected to be running.

I have a sheet with quarters/years as columns and one row with the budget costs. Any idea on how to populate each quarter with the total budget costs of all the projects expected to be running on those dates?

I am currently trying a SUMIFS function but I know it does not work.

=SUMIFS({Budget Cost}; {Planned Start}; >=DATE(2022; 2; 2); {Planned Start}; <=DATE(2022; 9; 30); {Planned End}; >=DATE(2022; 9; 30); {Planned End}; <=DATE(2028; 9; 28))

I would greatly appreciate any ideas/input.

Regards

Antonio

Julio S.

Hi @Antonio de Sousa ,

A possible solution to what you propose could be dividing the total project allocation evenly by quarter which you should be able to achieve with a formula in line with as in the example below:

=[Budget cost]@row / NETDAYS([Planned start]@row, [Planned end]@row) * NETDAYS(DATE(2022, 1, 1), DATE(2022, 3, 31)) *Note that in my example all calculations are made in the same sheet, if your quarterly columns are in a separate sheet, you'll need to replace the cell references with cross-sheet references. Also note that the quarterly distribution in my formula assumes that quarters run from Jan-Mar, Apr-Jun, Jul-Sept and Oct-Dec which might be different in your company and you may need to adjust accordingly.

Once you have the equal allocation by project and quarter, you can easily add all of them by quarter with a SUM formula in line with:

=SUM([Q1/22]:[Q1/22]).

I hope that this can be of help.

Cheers! 

Julio