Way to spread budget cost across quarters/years?
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
Answers
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!