Estimating Projects

“How much will it cost? How long will it take?” asked my client.

When I was young I quickly calculated how long it took to construct similar projects and blurted out an answer. The project took longer. I ate the cost and pondered what went wrong. I realized my calculations were based on best-case scenarios, when everything goes right. That never happens. I began doubling my estimates to cover contingencies and still they fell short. I researched project estimating and learned project construction phases typically span only 25 to 50% of a project’s total time. The rest falls into these phases: Research, Analysis, Design, (Construction), Testing, and Implementation.

Research (5%-15%)

Research is gathering information. Information we need can be categorized as:

· People

· Goals and Scope

· Inputs, Processes, and Outputs

People

Not paying attention to the players can kill a project and our reputation. Our first meeting introduces us to the players. They will fall into several categories:

· Approvers – Approve the project, deliverables and tasks

· Advisers – Provide answers to our questions

· Delegates – Perform work or use the model

· Informees – Must be kept in the loop even if they don’t make any decisions or perform work.

Google RACI matrix for best practices on this task.

Goals and Scope
It is critical to know what the end game is, what is included and what is not. Document it. Larger projects may require a Project Charter and/or a Statement of Work (or SOW). Make sure all players agree on this BEFORE going any further. Invariably, someone will want to add-to scope and deliverables. This is called “scope creep” and usually happens AFTER compensation is determined. If we want to remain profitable, we must be able to quickly identify scope creep as a change request requiring compensation changes. Otherwise, clients will expect us to absorb the cost. I highly recommend drafting end user acceptance test criteria at this point.

Inputs, Processes (Calculations), Outputs

With the project’s advisers and delegates, catalog all outputs, what their purpose is, who their audience is, their structure and format, and any required interactions (such as what-if parameter changes).Then ferret out and catalog the outputs' inputs, what/who produces them, how they are validated and processed, where they end up and in what format. Then map out the processes between inputs and outputs.

Analysis (5%-15%)

Analysis is adding relationships to our research results. What belongs to what? What is missing?

Gap Analysis and Estimating

Our project will fill the gaps between where we are and where we want to be (our goal). Gaps include:

· Missing, incomplete, poorly formatted, or inconsistent inputs

· Required calculations

· New or changed outputs

· Changes in people’s responsibilities

Usually we re-estimate a project based on what we feel is required to fill the gaps. Google gap analysis for more on this.

Design (5%-15%)

Design uses analysis to determine how our model will be put together BEFORE actually entering formulas.

Functional Decomposition

In the XL world, this is usually an outline of our project’s calculations divided into smaller chunks. This identifies our model’s building blocks. For complex projects we can push this concept well beyond a simple calculation block outline so if you need to, Google Functional Decomposition for more on this.

Flow Diagram

In the XL world, this is usually nothing more than arranging our calculation blocks so every calculation’s input is to the left or above and every calculation’s output is to the right or below (for most countries). This defines our model’s structure and greatly improves our model’s transparency/auditability. Again, for complex projects we can push this concept. Google Data Flow Diagram for more information.

Construction (25%-50%)

This is typing in formulas and, possibly, VBA code. You have this covered so let’s skip this.

Testing (25%-50%)

We have all seen the headlines 88% of spreadsheets have errors. The reason is because 88% of modelers do not test properly. There are several types of testing. Some types require someone other than the modeler. Because failures in any of these types of testing cause rewrites, this can be as much as half of our project.

Unit Testing

As we construct our model, we periodically check our calculations. This testing type is part of construction. Everyone does it. But that’s just the beginning.

String Testing

This is where we test the entire model. Almost everyone does this, once. And that’s a problem. To perform this task properly we need to identify several expected input scenarios (known as test cases), have someone else run each and confirm our outputs are correct.

Usability Testing

Some models are use-once-throw-away. In such cases, this can be skipped. But for models that can be run over and over, this step is critical. After confirming our model works technically, we must also confirm our users can understand how to use it and that it is not too burdensome on them. Have a typical user try it. As much as possible, don’t help them. Record where they struggled. Fix it. Try again.

Stress Testing

Our model works and our users can understand it, but will it handle the largest expected case or will users have to wait too long for results, or, gasp, will it exceed XL’s limitation? Put the model under the worst case load and confirm it is efficient enough to put into production. If not, optimize it and try again.

Security Testing

This is an issue under only a few situations. If our model:

· Is kept on a network confirm those who should access it can and those who shouldn’t can’t.

· Uses VBA confirm each user’s XL trusts it.

· Accesses databases confirm each user has credentials

End User Acceptance Testing

At the very end someone authorized by our project’s sponsor determines that our project results conform to our project’s scope. Try to keep this simple and focused on the final deliverable.

Implementation (1% to 20%)

This is the handoff to the client. For use-once-throw-away models this is can be as simple sending a bill. But for models that are used over and over or used by multiple people, implementation involves more.

Documentation and Classroom Materials

Documenting how to use our model, and in some cases, how to install our model. I recommend using tools meant for documentation. I create documentation in MS Word and publish from MS Word to PDFs which can be displayed on just about any device.

Training

Users typically want guidance from the modeler. Though our models are intuitive to us and passed usability testing, most users struggle without us showing them at least once, perhaps more, how to use our model.

Hand Holding

In rare cases we are asked to make ourselves available as our model is first used to answer any questions. If our client wants it, bill it.

Clean Up

IF we created test data, test results, versions, etc. on our client’s equipment, we should archive it and remove it.

Summary

Modeling involves more than just putting formulas in a spreadsheet. Most of these steps are essential to successful projects and making sure our client’s expectations are met. Billing for work is essential to being profitable.