Tracking Progress and Profit on Projects
These worksheets will help the owners of project-based companies calculate gross profit earned on individual projects that are underway in their companies.
Tracking the progress of individual projects and monitoring costs associated with each enables a company to keep on top of its financial picture. This tool illustrates how Setpoint, an engineering firm based in Ogden, Utah, does just that. By tracking projects in terms of progress and their weekly costs and profits earned, the company sees where each project is financially, allowing it to change course quickly, allocating more resources to more profitable projects.
The cornerstone of these worksheets is Setpoint's unique way of looking at gross profit (GP). Gross profit typically equals sales minus cost of goods sold (materials, labor, and overhead). However, Setpoint uses only material costs in cost of goods sold (COGS). This way, Setpoint views GP as an aggregate income from which to pay ongoing operating expenses, like salaries and wages.
The company replicates the spreadsheet on a whiteboard and updates it weekly for everyone to see, helping employees and management monitor the efficiency of their production system. For more details on Setpoint's approach to project management, see "What's Your Culture Worth?" and "By the Numbers: How Setpoint Stays in Control" in the September 2001 issue of Inc.
How to use these worksheets:
There are six worksheets attached: Explanation, which explains each worksheet cell and how the corresponding number is calculated; Prior Month-end; Week 1; Week 2; Week 3; and Week 4 Month-end. All numbers that must be entered on the worksheets are highlighted in blue. Sample numbers for a fictional company have also been provided.
To begin, create a baseline by filling out the Prior Month-end Worksheet with any current numbers you have for projects in progress. Then continue with the four weekly worksheets to track the profitability of your projects on a weekly basis. Information you'll enter into the worksheets include:
- Project Name (Carry this through all worksheets, taking care to use the same row for each project's name and numbers since subsequent worksheets automatically carry some numbers over.)
- Current Percent Progress (Current percent achieved on a given project. Note: For the system to work correctly, you need to be able to estimate where you are in terms of completion of a project on a weekly basis.)
- Project Revenue (Total billable revenue in the project.)
- Project COGS (Project's direct materials costs. Do not include labor.)
- Month Hours (Hours spent on project during prior month or to date if just starting out. Month Hours equal Week Hours during first week. For Week 2 through Week 4 Month-end, Month Hours is cumulative.)
- Other GP/COGS WARRANTY (Adjustments to Month GP and Week GP and any warranty expenses.)
- Material/Contract/Other Hours (Weekly and month-to-date contract labor entered into the Month GP and Week GP columns.)
- OE (Operating Expenses) Estimate (Total OE for week and month-to-date, calculated by working days, entered into the Month GP and Week GP columns. Operating expenses as defined by Setpoint include all non-variable expenses involved in running the business-excluding interest and taxes.)
The worksheets will tell you:
A project's gross profit as defined by the rate at which raw materials are being put through your business's production system. The more efficient a production system, the more net income you'll reap from the project.
Please Note: Setpoint uses its definitions of GP and OE for management purposes only. Generally accepted accounting principles are used to create financial reports for its bank and CPA. Setpoint uses this system as a simple way to open its books to its employees and show them how productivity affects the bottom line.
Joe Knight welcomes your comments and questions at email@example.com.
File Description: The attached file is a Microsoft Excel spreadsheet. You much have Excel, or compatible software, to use it.
Copyright © 2001 inc.com LLC