03132018, 07:27 AM  #1 
Novice
Join Date: Nov 2017
Posts: 11

help with forecasting/budget spreadsheet
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 
03142018, 01:22 AM  #2 
Competent Performer
Join Date: May 2017
Posts: 141

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? 
03142018, 01:32 PM  #3  
Novice
Join Date: Nov 2017
Posts: 11

Quote:
Quote:
Quote:
Quote:
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]) 

03152018, 02:29 PM  #4 
Competent Performer
Join Date: May 2017
Posts: 141

Principially yes.
With SUMIFS(), you sum a range applying conditions on another ranges. The ranges involved must be of same dimension (and I belive singlecolumn/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 RangeValue pairs, and counts records or rows mathing with all conditions. 
03162018, 11:02 AM  #5 
Novice
Join Date: Nov 2017
Posts: 11

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.

Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Budget Resources / Budget Costs / Hourly Labour  mattfromcanada  Project  0  10202017 11:57 AM 
Budget Cost  MAJID  Project  1  08102017 07:48 PM 
Mac 2011  When I close a spreadsheet, there is a blank spreadsheet left on dock.  wvinyard  Excel  0  07272017 11:32 AM 
Need Help with Advance Data Forecasting  fezzyfezz  Excel  0  06242015 10:49 AM 
Income/Budget Calendar?  Guinea  Excel  4  03112010 09:11 PM 