Quote:
Originally Posted by ArviLaanemets
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; |
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
Quote:
Originally Posted by ArviLaanemets
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.
|
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?
Quote:
Originally Posted by ArviLaanemets
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?
|
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
Quote:
Originally Posted by ArviLaanemets
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 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?
Code:
=SUMIFS(Table1[Hours],Table1[WeekNo],Table2[[#Headers],[201809]],Table1[Analyst],[@Name],Table1[Stream],[@Stream])/SUMIF(tCalendary[WeekNo],Table2[[#Headers],[201809]],tCalendary[WorkHrs])
03-14-2018, 01:32 PM
Similar Threads
Windows 7 64bit 
Office 2010 64bit
Threaded Mode