A step-by-step process and model-driven approach for creating a project estimate: imagining a report development project for a product-based company.
Overview scenario
Your company has an aging reporting environment containing thousands of reports developed over the previous 20+ years. The current reporting approach has failed to keep pace with the advancements in the industry and there are better available alternatives that will:
- Save the firm real dollars - has lower licensing costs
- Provide better service to the end-users – easier and more intuitive to use
- Provides for easier development and maintenance
Your company has decided to move in this new direction and has asked for a plan identifying needed resources to get the job done in 18 to 24 months. What do you do?
What follows is a practical step-by-step guide and model to help you estimate the people resources, costs, and time needed to successfully get the job done.
Step 1 – Conduct an inventory of work that needs to be done
Inventory the reports from the old system to be migrated to the new platform. Some of the things you want to learn about the existing reports are:
Determine the "who" - how the users are using the reports
- How often is each report accessed or run? - helps establish priority, business & user impact, etc.
- Who are the power users of the reports? - helps identify who will review and sign off that the new reports meet the business need.
- How many unique users are there for each report? - speaks to per seat licenses
- Identify reports by the business groups that sponsored the original development
- What things are missing from the existing reports- what features/functions are no longer needed?
- What reports are only used to export data to spreadsheets for users to do their own reporting?
Identify the set of reports that must be migrated
- How many abandoned reports are there? Certainly, over the course of 20 years of development, there must be a fair amount of reports that have fallen into disuse or reports that may not have been run in years.
- How many duplicate or near duplicate reports are there? Perhaps some reports are just different orientations of the same underlying data.
- How many reports could be combined with just a few tweaks?
Determine the relative complexity of each report, identify:
- Number of report drill downs & click-throughs
- Number of objects within the report
- Pick list selections, sources, and selection behaviors
- Number of data sources. access, locations, and update schedules
- Data volumes presented in the report
- Data transformations within the report itself
- Performance expectations
- Report exports and integration with Excel
- Number of in-report metrics (measures) calculations
- Filtering within report (Day, Month, Qtr, Year)
- Roles & Permissions (Report & Data)
- Etc.
NOTE: whatever set of criteria you use to evaluate the complexity of the reports – be consistent. In the next step you will use this data as input into the calculations used to determine needed resources.
Step 2 – Define a limited set of report complexities and identify roles needed to do the work
In this blog, let's limit the number of complexity levels to 4 (Simple, Medium, Complex, Very Complex). Each level being a multiple of simple (Simple being 1, Medium = 2x Simple, Complex = 3x Simple, Very Complex = 4x Simple).
Next, let's define the resource talent pool you will need to perform the work. In this example we will assume the following roles:
- Solution Architect – Determines overall system and application architecture direction
- Business Analyst – Works with the business to determine requirements
- UX Developer– Designs the User Interface according to corporate standards
- Data Developer – Identifies the data sources, target repository, and transforms necessary to create the report.
- Report Developer – Develops the reports using:
- Architect direction
- BA requirements
- UX Design
- Data design
- QA Tester – Tests the reports using the above criteria
Lastly, for step 2, create a table of values estimating the time needed, by resource, for the different levels of complexity. Below is an example of such a table.
NOTE: Try to avoid assuming individual resources into multiple, different, roles in your model. Successful projects respect that a business analyst, though their individual intentions may be pure, is not a good substitute for a Solution Architect. The roles are different. Similarly, just because a developer may be able to design the user interface and could potentially do a satisfactory job, they are not specialists in the discipline of designing intuitive and keenly efficient user interfaces that comply with corporate standards. Developers are focused on getting the job done using a defined design and set of requirements.
Of course, this "days" table translates into an "hours" table, as follows.
Step 3 – Estimate complexity for each report to be developed
In this step, we begin to use the criteria we have developed (resource roles, report complexities, days and hours needed by role and by complexity) to create an "all-in" estimate of work hours required to complete the project.
The following example imagines reports for a Product Manufacturing firm. We have listed the reports associated with each department and, after using the complexity criteria listed in step 1, have been able to estimate the complexity of each report. Using the table of values we created in step 2, we are able to calculate the total level of effort (hours) required for each report as well as the cumulative total of hours by role for the entire project. The table below depicts the results of our exercise.
Step 4 – Create a Resource Planning Timeline
Here we use the data created in steps 1-3 to compose a real-world timeline that realistically consumes the resource hours calculated for each role. This exercise applies the assumptions of resource availability, available days and hours per month, and holiday impacts. This step makes you keenly aware of the timeline needed to consume the required hours for each resource while allowing you to better understand when resources are needed to come on or leave the project. The following assumptions are also worth noting.
Assumptions
- Resource Availability = Maximum of 80%. Many firms are warming to the understanding that people resources are rarely available 100% of the workday. Team members are often pulled into meetings, other project collaborations, and support responsibilities, all of which all translates into time away from your project. In our exercise we use 80% as the max percent available for any given role.
- Blended Average Rate for project team resources– It is increasingly common for firms to use a blended rate (salary + benefits) for calculating spend on major projects. In our example we use 88.25 as our blended rate. The model works just as well if we set the rate to .0001 avoiding the rate discussion entirely.
- Contract rate– The per hour rate for contract resources is often higher than the internal blended rate set up by the company, In our example we allow the PM to enter an alternative rate to calculate forecasted cost. When entered, the alternative rate overrides the blended rate for that resource.
Set up the timeline calendar (see graphic below)
- Create individual columns for each month in your model, take the model out 18-24 months
- Use the NETWORKDAYS function to calculate the number of work days in each month - NETWORKDAYS(start_date, end_date, [holidays])
- Multiply NETWORKDAYS by the number of hours per day to get the available number of hours per month
NOTE: Work Days/Month - The "holidays" clause refers to a range of dates that you may create for the function to use to reduce the number of available workdays for that month.
Enter the resource utilization percentages by role
- Lay out the expected percentages each role is expected to be available for each month
- Use real world expectations of when each resource is needed on your project. (Rows 15, 18, 21, 24, 27, …)
- Calculate the number of hours your allocation nets out to be. 10% of 176 possible hours = 17.6 hours. (Rows 17, 20, 23, 26, 29, …)
- Calculate the monthly hours by the blended rate (or alternative rate) and you will have the cost for that resource for the month. (Rows 16, 19, 22, 25, 28, …)
- The example below carries over the total hours needed for each role (Column B) from step 3 so you can compare your total forecasted allocations against the hours available for that role.
- As you can see below, each role consists of three rows of information (% allocation, cost in dollars, and forecasted hours). Given the target date expectations by the business and the amount of hours needed by resource to get the job done, you may need to add additional resources into your model while respecting the theory of the mythical man-month.
When you have completed entering your data, understand what the Resource Estimation model is telling you
- The model allows you to enter the names of available resources, It will be easy to see what roles are unaccounted for from your current staff (A16, A19, …)
- In Column D, sum the Forecasted hours for each resource (=SUM(E17:AB17)) , (Rows 17, 20, 23, 26, 29, …) The forecasted hours you enter should approximate the hours carried over for each role in the Complexity and Resource estimates we did in step 3. (Columns B & D). This will show the time duration that each resource needs to complete the work.
- Given our earlier agreement on the max available percentage someone is capable of working on the project (80%) we can learn how long it will really take to do the work.
- Based on the blended rate of resources and the allocation of those resources over the months in your model, you will be able to see the total cost estimate by month as well as the total resource costs for the project.
Step 5 Report the Resource Gaps and lobby the decision makers for the team you need to be successful.
Undoubtedly, prior to you being asked to develop an 18-24 month plan, senior management had a "gut" feeling of what it would cost and how long it would take to do the work. This model will allow you to have a more detailed and analysis-based conversation about needed resources and time. In this step, take a look at the resources the model is saying you need and the resources you currently have available. Compose a simple "Gap" table ahead of your conversation with the decision makers (example below).
In conclusion
This blog took us through a reasoned, model-based, discussion about understanding the work complexities, required resources, and time needed to satisfy a work request.
Following the steps to create the model allowed us to understand the hours needed by each role. The model also exposed the possible need for multiple resources for a given role to satisfy the work being completed in a given timeframe. Using the per-month cost calculations, we allow the company to better plan and manage the resource expenses over the duration of the project.
Lastly, the model brought us to a place where we could have the discussion with decision makers about what resources are needed to deliver the solution, on time, on budget, and on quality. If you have questions or want to discuss this topic further, please contact us today!