Tracking hires and costs

[ecanestrari]

Hi,

I'm new here and was hoping you can help me deciding the best way to design a template in Excel.

I need to track resources and spent by department and by project. The idea is that in this table I register every resource, when they start, when they finish, their salary and what project they have been working on. I'm keen to keep it simple so a number of us can use it on a daily basis.

The objective is to be able to have 2 charts where I can see how resources have decreased or increased in the last few months and how do we expect it to move in the future (as I'll have in the table future hires). The second table would be quite similar and but instead of number of resources it would be the monthly cost.

I'm not sure where to start, I thought of a pivot chart as it should allow me to filter by project or department but struggling to have the number of resources increasing based on the month of the starting date and the same for when they finish. Any guidance?

I have attached the first draft of the workbook, it's very basic but hopefully it helps with what I'm trying to do. The second tab is just for you to have an idea of how the graph would look like, I would prefer not having any additional tables, keen to keep everything in a single table so it's easy for people to update it.

At the moment it's all quite manual for the reporting which is the biggest problem. I thought of using some IFCOUNTS but felt it was just too complex for something that should be simple enough (I think!) so maybe I'm doing something wrong or there's a better way of doing it.

Thanks!
Ezequiel