Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 03-13-2018, 07:27 AM
makaveli80 makaveli80 is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Nov 2017
Posts: 16
makaveli80 is on a distinguished road
Default 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
Attached Files
File Type: zip resource_utilization.zip (542.3 KB, 3 views)
Reply With Quote
  #2  
Old 03-14-2018, 01:22 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 328
ArviLaanemets is on a distinguished road
Default

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?
Reply With Quote
  #3  
Old 03-14-2018, 01:32 PM
makaveli80 makaveli80 is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Nov 2017
Posts: 16
makaveli80 is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
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 View Post
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 View Post
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 View Post
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])
Reply With Quote
  #4  
Old 03-15-2018, 02:29 PM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 328
ArviLaanemets is on a distinguished road
Default

Quote:
Originally Posted by makaveli80 View Post
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.
Reply With Quote
  #5  
Old 03-16-2018, 11:02 AM
makaveli80 makaveli80 is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Nov 2017
Posts: 16
makaveli80 is on a distinguished road
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 11:48 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft