The multimillion dollar project has been completed for six months. Now the CFO is analyzing the return and has determined that results are falling way below expectations. Your reputation as a project manager is on the line. Unfortunately, too many project managers have felt this type of pressure. Download this Excel template and use it to develop a cost/benefit analysis to help support your project. The first page of the template is completed as a sample project. This article includes instructions on how to use the template, based on the sample project.
Preparing a good cost/benefit analysis will help you to calculate data that will illustrate the impact of your project. To make this analysis easier, download this cost/benefit analysis template that I have used successfully in a number of projects.
How to use this download
The download includes two templates:
- The first page of the template is a sample and shows how the form looks when numbers are filled in. The data in this sample relates to a project described below.
- The second page of the template is blank so you may input numbers that relate to your project.
Putting it together
The sample data in the template refers to a project to implement a new online order processing system for an industrial supply company. The best way for you to learn how to use this template is to see how I’ve filled it out based on data for my sample project. The top section is where some of the specific details are entered:
Internal Labor Cost/Hour: Detail here the average cost for internal corporate labor. These costs should be loaded to include overhead, benefits, etc.
In the sample project, loaded average cost for internal labor is estimated to be $43.25 per hour.
Expected life span: Enter the number of years the system is expected to be utilized. This determines the period of amortization for implementation costs.
In the sample project, the expected life span of the system before replacement or major upgrade is expected to be four years (48 months).
Monthly Sales: Enter the number of units sold on a monthly basis. This number is obviously more complicated for businesses that do not operate a ‘unit,’ or retail sales operation (like a service company).
In the sample project, our example company sells 14,000 units per month.
Margin/Unit Sold: Enter the gross profit or margin for each item sold.
In the sample project, although the revenue and margin for our example company’s products are variable, on average, they produce $1.45 for each unit sold.
You’ll notice that sections under costs ask project managers to input all implementation costs.
Planning: Enter the number of hours estimated to complete all planning activities.
In the sample there were 120 hours of planning by the accounting department, sales and marketing required 40 hours, and the IT department put in 80 hours.
Contract Labor: This entry is for all implementation costs for work provided by outside contractors.
In the sample project, all implementation activities were provided by an IT consulting firm and totaled $15,400.
Internal Implementation Labor: Enter the number of hours estimated to complete all installation activities by internal corporate resources.
In the sample, the IT team provided 240 hours of installation labor, and the accounting team worked for four weeks total (640 hours).
Capital Costs: This entry should include all equipment and material purchased to complete the project.
In the sample, installing the system required implementing a new server at a cost of $15,500.
Maintenance Contract: Include any ongoing monthly costs paid to outside contractors, vendors, or agencies relating to the ongoing maintenance of the system.
In the sample, ongoing maintenance for the system is paid monthly at a cost of $1,250.
Operational Cost: This entry includes all expected monthly operational costs. This includes internal labor, expendables, and expected upgrades through the life of the system.
In the sample, a specialist in the accounting area will be trained in the weekly system maintenance functions and will devote four hours per week to those activities. In addition, IT has allocated $15,000 annually for support activities and upgrades.
This section details the expected benefits of the project:
Productivity Savings: Outline the monthly savings expected to result from increases in productivity. These are typically “soft” costs, and may be the number most subject to scrutiny. I am typically very conservative in estimating these numbers.
In the sample, the new system is expected to reduce the manual labor necessary to support customer purchases by one full person, for a net total annual savings of $28,600.
Expense Reduction: Detail the expected savings because of a reduction in expenses. These should be measurable "hard" dollar costs.
In the sample, no specific dollar savings are expected.
Increased Sales: If the project is expected to result in increased sales, outline the increase in number of units sold on a monthly basis. For service business, the units here will be hours or contracts.
In the sample project, the company expects to increase unit sales 25 percent based on customer demand for an online solution.
Decreased Cost of Goods Sold: Any reduction in production cost for goods to be sold is outlined here. The reduction should be the marginal difference in production cost.
In the sample project, no decreased cost of goods sold is expected.
Calculate monthly benefit
The cost/benefit results are shown as two figures:
Total Monthly Benefit (or Cost): The expected monthly benefit (or cost) over the life span of the system is shown here as a sum. This is almost never a level benefit. Many projects produce an increasing benefit over time, which is better projected using a month-to-month cost/benefit analysis. We have assumed a level benefit in this example for simplicity.
In the sample project, the company expects to receive $3,407 in monthly benefit for a total implementation cost of $78,475.
Payback Months: The payback is the number of months before the initial investment is returned (assuming a positive monthly benefit).
In the sample project, the payback for our example company, including all implementation costs amortized over the life of the system and all operational costs, is calculated at 11 months. For traditional businesses, that is a relatively short payback period.
This analysis is only as accurate as the numbers that are entered. Use this sheet as a guideline for developing your own analysis. The basic format can be tweaked for services, application systems, products, or any other project output. Download this template and let me know how it works for you.
Describe how you calculate cost/benefit. Post a comment below or send us more information.