Figure 1: Managing Means Estimating and Budgeting.
In the fiber optic business, there are always more potential projects than we can execute, and we choose those projects we staff based on their Return On Investment (ROI). To generate these ROI analyses, I have to generate preliminary schedules and cost estimates, often for projects that will never happen because their ROI is not sufficient. I spend a lot of generating and updating budget spreadsheets (Figure 1).
When I plan, I used the concept of a "man-month", which is an average amount of work that an individual can get done in a month assuming typical overhead times: vacation, illness, and other non-project related time. A man-month has been referred to as a mythical concept, but it is useful for rough planning.
Unfortunately, my company's finance group has now requested that I separate out vacation time from all the other times. They want me to allocate 8% of each month to vacation time. The logic behind their 8% number is as follows:
- There are 260 weekdays in a year (52 weeks · 5 days/week).
- There are 10 paid holidays a year (roughly)
- The average employee has 20 days (4 weeks) of vacation a year.
Over my career, I have created all sorts of Excel-based planning tools that use the man-month approach using aggregated work and overheads. I now need to separate out the vacation time for our Finance group, but I do not want to spend the time updating my tools. Fortunately, I always keep my data and analysis worksheets separate – I use the same analysis worksheet for all my planning. This means that I only have to update my analysis worksheet. I decided to use a little math to resolve the issue – the problem is a simple one, but one that saved me a bunch of time.
Here are my assumptions:
- I am delivering my labor summaries to our Finance people in the form of a pivot table that shows labor allocation as a percentage of a man-month every month.
They love Excel and I love pivot tables, so no argument here.
- I do not want to have to modify my data tables.
I have quite a bit of data already in place for my current programs. In addition, I literally have dozens of program plans in Excel templates and I reuse them all the time. I do not want to have to rework any of this data.
- All my tasks are assigned resources as percentages of a man-month
Usually, I work in terms of whole man-months (i.e. 100%), but I use 25%, 50%, and 75% as well. I don't find rough planning at a finer level of detail very useful.
This seems like an ideal task for an Excel Pivot Table Calculated Item (CI). Here is why:
- I do not have to modify my data.
That is why Excel has CI – they all you add an item to a field without changing the data table. My approach will be to add a "Vacation" CI to my pivot table. Since CIs do not require any modification of the my data tables, this should work out great.
- It is easy to create a constant value (called "Vacation") that will be 8% of a man-month.
The subject of this post is on how to compute the constant value. I cannot simply set my Vacation variable to 8% because my time estimates include vacations and currently sum to 100%. I am now going to add a task with a value that will result in vacation being 8% of the total and that will adjusted all other results automatically.
Figure 2 shows the simple math equation that I needed to solve and provides a quick example.
Figure 2: Solving for the Amount of Vacation I Need To Assign Everyone Every Month.
Figure 3 shows the pivot table format I send to Finance and how I defined the CI. The pivot table show all data as a percentage of the column total. This is exactly what Finance wanted for their purposes. They can select each person (Resource selector) and will see their planned project allocation.
Figure 3: Filtered Pivot Table Showing Final Percentages.