• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Forecasting with Pivottables is it possible?

AMG

New Member
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
 
Top