I am responsible for maintaining a spreadsheet with all our employees. I record name, position, start and enddate in position amongst other things.
Row 4 Name (A), dept (B), position (C), headcount (D), start (E), end (F), reportmonth (G)
Row 5 Tom, dept a, manager, 1, 1-1-2008,31-12-9999,true
Row 6 Dick, dept b, analyst, 1, 1-1-2008,31-12-9999,true
Etc....
Row 999 Harry, dept c, adviser, 1, 1-1-2008,31-12-9999,true
On a monthly basis I need to report how many active employees we have.
All active employees will have their enddate set to 31-12-9999
If Harry leaves at end of march I will amend the enddate to 31-3-2010
Report date (in march) in cell A1 is a named cell and will contain 01-Mar-2010.
Column G (report month) contains a formula
IF(AND($E2<=ReportDate,$F2>ReportDate),TRUE,FALSE)
The result of this formula will be true if the employee is still in his position in the reporting month.
I use a pivot table to generate results
Filter: Reportmonth = True
Rows: Departments
Sum: Headcount (HC)
The result of total will be 3 (Tom, Dick and Harry)
If I change the report date to 1 Apr 2010 the result will be 2 (harry left)
So far so good.
Now I also need to do forecasting:
I’d like to be able to produce what the headcounts would be for future months (e.g. till end of 2010) by setting up 12 pivot tables, one for each reporting month and by amending start and enddates of employees for those where Ive been given the correct dates.
E.g. I know now that Dick will leave in June and I know that we will hire 2 resources in May (Joe and Jane)
So the totals of my pivot tables should show
Jan 2010 3 HC
Feb 2010 3 HC
Mar 2010 3 HC
Apr 2010 2 (harry left)
May 2010 2 + 2 = 4HC (2 hires Joe and Jane)
Jun 2010 4 HC
Jul 2010 4 - 1 = 3 HC (Dick left)
Aug 2010 3 HC
Sep 2010 3 HC
Etc
Is it possible to create these forecasting pivots based on the above data? If so how...
I hope I've explained this well enough
Row 4 Name (A), dept (B), position (C), headcount (D), start (E), end (F), reportmonth (G)
Row 5 Tom, dept a, manager, 1, 1-1-2008,31-12-9999,true
Row 6 Dick, dept b, analyst, 1, 1-1-2008,31-12-9999,true
Etc....
Row 999 Harry, dept c, adviser, 1, 1-1-2008,31-12-9999,true
On a monthly basis I need to report how many active employees we have.
All active employees will have their enddate set to 31-12-9999
If Harry leaves at end of march I will amend the enddate to 31-3-2010
Report date (in march) in cell A1 is a named cell and will contain 01-Mar-2010.
Column G (report month) contains a formula
IF(AND($E2<=ReportDate,$F2>ReportDate),TRUE,FALSE)
The result of this formula will be true if the employee is still in his position in the reporting month.
I use a pivot table to generate results
Filter: Reportmonth = True
Rows: Departments
Sum: Headcount (HC)
The result of total will be 3 (Tom, Dick and Harry)
If I change the report date to 1 Apr 2010 the result will be 2 (harry left)
So far so good.
Now I also need to do forecasting:
I’d like to be able to produce what the headcounts would be for future months (e.g. till end of 2010) by setting up 12 pivot tables, one for each reporting month and by amending start and enddates of employees for those where Ive been given the correct dates.
E.g. I know now that Dick will leave in June and I know that we will hire 2 resources in May (Joe and Jane)
So the totals of my pivot tables should show
Jan 2010 3 HC
Feb 2010 3 HC
Mar 2010 3 HC
Apr 2010 2 (harry left)
May 2010 2 + 2 = 4HC (2 hires Joe and Jane)
Jun 2010 4 HC
Jul 2010 4 - 1 = 3 HC (Dick left)
Aug 2010 3 HC
Sep 2010 3 HC
Etc
Is it possible to create these forecasting pivots based on the above data? If so how...
I hope I've explained this well enough