This article first appeared in The Zweig Letter (ISSN 1068-1310) Issue # 842
Originally published 12/14/2009
An effective backlog forecasting process is a direct road to improved profitability.
At MacKay & Sposito, Inc. (M&S), we recently prepared several forecasts to plan the final quarter of 2009. During that process, I contemplated the value of backlog forecasting, which is key to our planning process. An AEC firm’s backlog (the difference between contracted work and the value of previous invoices) reflects past marketing success and current project management effectiveness. Our ability to balance resources against project workload determines whether profits are either realized or squandered. A solid backlog forecast helps manage this balance in the following ways:
1) It gives your project managers an important tool for resource planning, scheduling, and deliverable management.
2) It provides a solid basis for marketing decisions. If your backlog forecasts lower-than-normal workload levels, turn up the marketing engine. If your backlog forecasts higher-than-normal workload levels, add resources or become more selective about the work you pursue.
3) It validates team, office, division, or corporate revenue projections. Use backlog forecasts to inform the accuracy of your corporate financial planning efforts.
Workload projections can be divided into different forecasts depending on the likelihood of identified projects. At M&S, we have two different processes for forecasting work. First, project managers forecast our work-under-contract, as part of our backlog forecasting process. This contracted work is the most accurate indicator of short-term revenue and is the focus of this article. Second, our business development group forecasts less-probable opportunities as part of our work mass analysis. Unlike our backlog forecast, the work mass analysis discounts project opportunities to reflect uncertainty the project will be put under contract. When reviewed together, these two forecasts give a complete picture of upcoming workload.
Your backlog forecasting process need not be complicated, it needs only to provide a standard tool for company-wide project planning and review. Use the following three steps to initiate a backlog forecasting process.
Step #1— Prepare a weekly ‘bottom-up’ forecast by project manager. At the end of each week, have every project manager compile a backlog forecast that identifies active projects, assigns team member resources, and then resource loads (using an hours forecast) each project for the next six-to-eight weeks. The forecast provides the project manager an opportunity to balance resource needs, identify short-term gaps in workload, and plan for downtime (i.e. vacation, holiday, training, etc.). We use these backlog forecasts as the agenda for weekly project meetings, and as a consistent structure for reviewing project milestones, progress, and budgets. Spreadsheet formulas convert the forecasted hours to weekly revenue by pairing projected hours with billing rates. They also project the consumption of budgets during the forecast time period.
Don’t get hung-up by the tools used to compile these forecasts. Instead, start with simple spreadsheets and focus on developing the consistency and discipline necessary to prepare meaningful projections. Good backlog forecasts are more the product of good team (and inter-team) communication than fancy software tools. Although we have experimented with several sophisticated forecasting programs, we’ve found that simple spreadsheet templates and macros can provide a scalable platform for team/organizational backlog forecasting. (Please refer to the bottom of this article for directions on how to receive a free example backlog forecast spreadsheet).
Step #2— Management review, testing, and coordination. Once weekly backlog forecasts are completed, management then compiles and reviews division, office, or company-wide forecasts. At M&S, we review these aggregated forecasts in an end-of-the-week meeting where we discuss resource sharing between teams or service areas, question weekly/monthly revenue projections, and identify potential budget problems. Our accounting department also attends and compares projected backlog revenues against corporate revenue goals to ensure we meet targets.
Step #3— Comparison to actual utilization. Finally, we review weekly utilization (as computed from timesheets) to ensure that weekly forecasting matches reality. These reviews provide a critical feedback loop to improve your forecasting process.
By implementing a backlog forecasting process your firm can increase project management effectiveness, make better decisions about marketing expenditures, and improve and validate your corporate revenue planning efforts. These benefits will drastically improve profitability, which is especially important given the current economic environment.
DERRICK SMITH is a senior vice president at MacKay & Sposito, Inc. (M&S) (Vancouver, WA). Contact him atdsmith@mackaysposito.com.
Please fill out the following form to receive a free example Excel backlog forecasting spreadsheet!
An email will be sent with a link to the download!
RSS Feed
Subscribe
Twitter
Follow
LinkedIn
Connect
{ 1 comment… read it below or add one }
A couple of quick comments about the attached example forecast:
1.) Our version of this spreadsheet pulls project information, remaining project budgets, employee information, and rate sheets from our accounting software. As a result, I had to “dumb-down” the spreadsheet so it would work outside of our system. You’ll see there are two tabs labeled “Resources” and “Projects” which have information in them to populate the various pulldowns and lookups. To use the spreadsheet, fill out the information required in the yellow cells (hint: start by entering the project number “10001”). I’ve also included a pdf of a completed spreadsheet for an example. Don’t forget to enable the macros.
2.) I original thought I’d just send a pdf of our forecast form to those who requested it. However, I’ve sent the actual spreadsheet so you’d get an idea of how the tool works. My intent in sharing this spreadsheet is not to provide you with a solution, but rather to give you ideas on how you might implement something similar. In our firm, each PM fills out a separate team form every week. We then use another spreadsheet/macro to quickly aggregate all the teams’ forecasts into a pivot table and sort the information by employee. This report is used in weekly department meetings to discuss individual utilization. It actually works quite well!
3.) I received several comments from readers who said they were already using an AEC specific commercial product to forecast their backlog. This is great! We’ve seen demonstrations of several great products that fulfill this purpose. Our accounting / project management system also offers a resource planning module, however, we’ve opted for a lower-tech approach for a couple of reasons:
a. The big challenges to implementing backlog forecasting is getting buy-in, changing behavior, and developing discipline among your PMs. As a result, I suggest you first try a low-tech solution, develop the habit of forecasting, THEN go spend money on an expensive software product. You’ll be better educated on what software features you need and don’t need. Better yet; you can tell the vendor; “Here’s what we do now … we like 90% of it … can you improve on our existing process?” Yes, I know your low-tech solution might be more labor intensive than a sexy project management system. However, a low-tech approach will allow you to prototype what works best for your firm prior to spending money on new software.
b. We’ve struggled to find a commercial software solution that isn’t too complicated for the simple resource / project planning we desire. Most of the products I’ve reviewed require a lot of maintenance. It’s hard to get your busy PMs to provide quality forecasts if your tool is too complicated and the process too time-consuming.
c. There are limitations to what a low-tech solution can give you. As one example; we’ve talked internally about how nice it would be to put together a forecast and see instantly that John has already committed Sally to Project X … and then make the necessary adjustments. However, we’ve determined that these dynamic features are impractical for our firm. Instead, we get PMs in a room (or on the phone), with the aggregated department/division/firm backlog reports and hash out workload assignments and conflicts. I find it hard to believe that a dynamic resource-forecasting product would eliminate the need for PMs to meet to coordinate resource sharing.
Again, the value of this effort is largely in the process, and not the final product. We really use this tool to drive communication about resource needs, project budgets, progress, etc.
Again, I hope you find this useful. Let me know what you think … or alternatively, drop me an email and let me know what solutions you are using for backlog forecasting.