hi all,



I am wondering if there is an easier way for me to update a forecasting spreadsheet or even a better way to this overall? I have one spreadsheet called Timesheets where I capture the weekly timesheets that all employees send to me. Each project has it's own budget and expected effort in days.

Right now I'm filling out the "actual utilization" which is basically the hours worked in a week divided by 40. Then I insert that into the Forecast/Budget spreadsheet manually. This enables me to see the "forecasted spend based on burn chart" and lets me know how much is left on the budget if people worked x amount of days/hours on a project.

So I' wondering if there is a good way to fill out the Forecast_Budget tab with the actual utilization (i.e. 1, .5, ) based on the timesheet tab?

I thought I might create a utilization report and maybe do a lookup based on name of person and the week and project but i don't know how complicated that might get.

Any help would be appreciated. Thanks in advance

Attached Files

resource_utilization.zip (542.3 KB, 10 views)

Again you think of your workbook as a lot of tables to print out The sheet Forecast_Budget is OK to show to boss, but a disaster for calculations. P.e.
1. You need to add jan.20. You have to redesign ALL your formulas which refer to range G : CO;
2. You decided to employ a new analyst/designer/whatever. You have to edit formulas for 'Forecasted Spend based on Burn Chart' in all 3 tables, and the whole Total table;

It looks like all values you calculate on sheet Forecast_Budget, you can calculate directly from TimeSheet. The only exceptions are:
1. Rate, which is best to enter into Employees table, or into separate Rates sheet, when an employee can have different rates in different situations;
2. Total Budget, for which you need also a separate sheet, p.e. Projects.

And how are those analysts, designers, etc. connected with timesheet? Must there be a column Employee instead of Analyst, and every employee has an appointment/role?

At last, which records from Timesheets are connected to which table on Forecast_Budget? The total one must be obviously the sum of other 2, but how aren't rates there neither same as in other tables, neither sums from other tables?

Yes you are right, it is a mess to calculate and update when a new analyst and a new project comes on board. I have like 10+ projects I am trying to track - if I could find a better way to do this I would be so happy. Nonetheless, this works for us right now

This is correct, I know the rates by heart don't have them listed really, different employees can have different rates in different projects. Like John Smith could be on Project A and have a bill rate of 25 but then on another project maybe a bill rate of 30 etc. So your assertion is correct. Do I need this?

Correct, every employee/analyst has a role, they could be a designer an analyst, a PM etc. and each person has their own rate. Maybe I need a table or expand the Employee one to include their role? I usually know this off the top of my head so i never put it down, so maybe that needs to be added? I just use Employee/Name/Analyst interchangeably they all mean the same thing

Yes right now its mostly entered manually. To be honest, all I need is for the items in the actual main section to be populated as data is entered in the Timesheet tab. The rest of the stuff I can manually figured out I think

https://imgur.com/a/7AwC9
Can I use the same code you gave me before in this table?

Principially yes.

With SUMIFS(), you sum a range applying conditions on another ranges. The ranges involved must be of same dimension (and I belive single-column/row ranges), and the sum range must be numeric/date/boolean (condition ranges and values can be of any type). Condition values can be values in some cell in table or elsewhere in workbook, names, values returned by formulas (p.e. another SUMIFS formula), or constants. The number of condition pairs (range + value) is limited only with max number of characters for Excel formulas. Originally the formula is meant to calculate sum, but by using right conditions is it possible to use it like lookup for numeric value from table.

Another useful function is COUNTIFS(). Works similarily, but contains only Range-Value pairs, and counts records or rows mathing with all conditions.

Ok how do I adjust the table and formula to use that formula? I was trying to do it on my own and it is not working well.