It all depends on the data that you have available. I will make the following assumptions:
(1) you have a data dump with transactional details and data set has a field to be able to filter/group the various projects which will grow each month with new data.
(2) you have a budget set by project, not transactional
If so, you can create in excel a dashboard showing projects Year-to-date or Life-to-date spend and compare it to the Budget (Year-to-date or Life-to-date) depending on how you have the data.
A simple overlapping bar chart will do the trick visually. (one bar chart the Spend to date, one barchart the Budget, the gap between being remaining to spend or overspend.
Or just simple summary table to bring in the variance: column 1 the list of projects; Column 2 Agregate of actual spend (sumifs), Column 3 Budget (vlookup or xlookup by project) column 4 variance Act vs Budget.
You can further create some detailing views by project so the project manager has some insight by vendors spend or spend categories (again, it depends on what data you have on had or what is of interest to your user). Pivot tables reading from your transactional data.
If you want to get a bit fancier:
Most likely the data I assumed you have will live in different data sets. Project identifier will be your key between them. And you can create a third data set with the unique list of the projects. With these three tables you have a minidatabase. If you link them through PowerPivot (part of excel, no external add-ins) you can harness the power of pivot tables and filtering through slicers.
Hope this helps.
Maria
------------------------------
Maria Alaman CMA
Director/Manager
Home
Eden Prairie MN
United States
------------------------------
Original Message:
Sent: 06-21-2021 11:34 AM
From: Berardino Raimondo
Subject: Budgeting for multiple projects
Hi Everyone!
I have to set up an operating budget in Excel for a department with multiple projects. Please, may I ask how would you do that? Any recommendation?
The idea is to provide a tool so that the Manager is able to monitoring all the projects at the same time. We already know that some projects won't be profitable and he need to compensate with the remaining ones.
He also needs to implement new projects and decide their feasibility, accordingly to the goals.
Thank you for your help
BR
------------------------------
Berardino Raimondo
Other
Milano MI
Italy
------------------------------