Open SoSi: The Concept Jam Part 7How to create budget estimates for proposals and project management

Share |
Spreadsheet

Today we're releasing the budget estimating template we use to generate cost estimates and project plans for The Concept Jam. We use similar templates for all our budgeting processes, so we expect that the attached template will be relevant to a wide range of budget, estimating and project planning needs. Whether you're a social media agency (like us) or in an entirely different sector and service industry, these files and notes can help you define or refine your estimating and project management system.

To help you understand our process we are releasing:

  1. The Concept Jam: Internal Budget Estimate spreadsheet. This is an annotated version of the spreadsheet we use ourselves. It includes four worksheets: A title page, our internal estimating spreadsheet, the client version and our project timeline (generated once the proposal is accepted.) Be sure to click the cells with red corners to view the annotations. You can view this spreadsheet as a Google Doc or you can download the XLS version. If you plan to work with it we highly recommend downloading the file.
  2. Screencast: Open SoSi Project Estimating Worksheet. This 17-minute screencast explains our worksheet -- the information it contains parallels what's in the spreadsheet annotations but you may find the walkthrough helpful.

Task-by-task budgeting makes for accurate estimates and proposals

Here's how we use the attached budget estimating worksheet to generate project proposals for clients:

  1. We break the project down into phases, which correspond roughly to deliverables, though some phases include multiple deliverables.
  2. We break each phase into individual tasks, estimating the hours each staff member or contractor will spend on that task.
  3. We add an additional line item to each phase to cover project management hours -- all the e-mails, phone calls etc. that inevitably pop up during a project. We take the total hours each staff member is expected to spend on that phase and add 15% more time to cover their project management tasks. You'll note that our estimates do not include a contingency. We've found that building those project management hours into our budget means we don't need a contingency.
  4. We add a section to the spreadsheet tabulating the business development costs of the project. Typically when we do the estimate we've already logged some hours talking through the project with the client -- often, a lot of the strategy work happens before we've even delivered a proposal. So we add up the costs of all that time up to the moment of proposal delivery, plus whatever time we anticipate we'll log before the proposal is approved (typically, another client call and internal strategy meeting) and come up with a dollar figure representing our total up-front cost on the project. We then take this number and distribute it proportionally across all the other phases of the project, adding the appropriate portion to the total for each phase. This allows us to deliver projects at a lower cost to clients who come to us with a clear sense of what they need, and to pay for the time it takes to work with a client to develop a clear set of requirements or project strategy if they need to do that before proceeding to proposal.
  5. We take the total budget and copy-paste as values (so that we can delete rows and columns) into another worksheet, where we delete the task-by-task breakdown and the person-by-person breakdown. That leaves us with column A (phase) and column O (total cost). We then round the total cost for each phase to the nearest $250, taking care that the final total isn't more than a few dollars away from the original total (before rounding).
  6. We copy & paste the cost for each phase, and the final total, into the Word doc we give to the client as a proposal.

A detailed estimate drives tight project management

Once the client approves the proposal, we continue to use the spreadsheet to guide our work. Here's how:

  1. At our kick-off meeting, we review the proposal and the spreadsheet as a team, making sure everyone knows who is doing what and how it all fits together. We take the tasks outlined in the estimate and turn them into a project timeline (see the "Timeline" worksheet), specifying which tasks will happen during which week, and when we have to meet key milestones.
  2. We give the client a distilled version of the timeline (focusing on meetings, deliverables, and anything they need to get to us at key points along the way) and schedule all internal and external meetings that are called for in the timeline, putting them in the calendars of anyone on our team who needs to participate (so we don't get 3 weeks in and then stall because we can't find a time when everyone is available for a key meeting).
  3. We enter each phase of the project in Harvest, our time tracking system, as a separate task. We find this is much more useful than using tasks to cover types of work -- meetings, writing, etc -- because it helps us track our time by phase.
  4. When we check in as a team during docket or project meetings, we check the total hours logged on the project in Harvest against the running total of hours in the spreadsheet. If we're running much over or under our anticipated hours -- where we thought we'd be on the project by the time we completed a task in the estimate -- we can see it right away, and drill down within Harvest to see whether the whole team is off our projections or whether there are just one or two people who need to adjust how they're spending their time.
  5. At the end of a project, we use Harvest to generate the total cost of our project in billable hours. We add that to our up-front business development costs, and assess whether we met, exceeded or beat our original cost estimate.

Reflections

How well does this estimating process work in practice? The very first time we used it to generate an estimate, I was flabbergasted to check in on our costs at the end of the project, and see that our original estimate was within a few hundred dollars of our final billable hours. There was just one problem: the estimate had come in way over the client's budget, so I trimmed it down by about 40%. The difference came out of our pocket.

But that experience forced me to make a resolution: I was going to stop delivering quotes that were below the actual cost to deliver a project. The next week, I submitted a proposal to a client based on our line-by-line spreadsheet estimate. It scared the heck out of me, but the client accepted our proposal. We delivered the whole project on time, to the client's whole-hearted satisfaction, and beat our estimate by a few hundred dollars.

We're now religious about delivering proposals that are always based on line-by-line estimates. And while it looks like a lot of work, it gets easier over time: once you've done a line-by-line estimate for each of your major types of projects, it only takes a few hours of tweaking to adapt your estimate to each future project. But it is worth tweaking each time, so that you're forced to think through any variables that might have changed (for example, increases in your contractors' rates), learn from past engagements (maybe you can offer to meet with your client once every two weeks instead of weekly) and adapt to this particular client's needs (for example with one more deliverable).

Best of all, we find that we are more flexible with pricing, instead of less. Because we know exactly how our estimates break down, we are able to scale a project to a client's budget by trimming scope. We can think through the implications of each item we trim ("if we eliminate this deliverable, is the client still going to understand our recommendations?") and be clear with the client about the implications of different budget scenarios. Wherever possible, we identify phases that are not mutually dependent, so that we can suggest options like contracting for one deliverable but not its successor.

Today, our estimating system is a core part of how we manage our projects, plan our business, and select which projects to bid on. But it's the result of a lot of painful trial and error. We hope that what we've learned can help other companies and consultants refine their own estimating and project management approach.

Learn more about our experience developing this budget process and our decision to release it.

Comments

Alexandre Pieske says

March 30, 2010 - 8:07am

Dear Alexandra

Your blog and now Open SoSi have been a very nice experience to follow. Many insights after each reading to manage a better business in our field. I am a freelance copywriter in Brazil.

But how about your project management web based software saga?

Did you drop ManyMoon + Google Apps solution in order to move to your own spreadsheet and Harvest?

Best regards

Mia Khurram Ijaz says

September 7, 2010 - 6:48am

Thankx a lot for sharing it.

 

Dee Kumar says

January 4, 2011 - 4:30pm

Really great post and thanks for the resources. As a project manager I find it is always good to remind myself of best practices and continually improve my PM skills.

Khyne says

January 5, 2011 - 7:29am

Super useful post, and thanks for the excel sheet, this will really help me in my calculation.

Okan says

March 15, 2011 - 11:04am

Great post. But Admin deleted my comment Why?

Anonymous says

August 4, 2011 - 6:21am

i have just been appointed the budget officer of my division in my establishment but i know nothing about budgeting.i really find your posting interesting but im still confused about alot of things.i will continue to read your postings and im sure i will get to understand it gradually.thanks.Victor

Ava Naves says

December 9, 2012 - 8:55pm
You've managed to write a post that actually presents project management in an exciting and approachable light! This is just another reason why you and Rob deserve your share of success.

Rob Cottingham says

December 10, 2012 - 11:47pm
Aw, thank you!

Leave a comment

The content of this field is kept private and will not be shown publicly.

More information about formatting options

Social Signal on...

RSS feedTwitterFacebookGoogle+

Work Smarter with Evernote

Get more out of Evernote with Alexandra Samuel's great new ebook, the first in the Harvard Business Press Work Smarter with Social Media series!

Available on Amazon, iTunes and HBR.

Join Newsletter

Alex on Twitter