The Activity/Capacity Based Markup WorkbookA Simple Spreadsheet App For Figuring Out "How Much Should I Charge"
How To Calculate Your Hourly Rate As A Contractor
The Activity/Capacity Based Markup Workbook is an Excel Workbook that can be used by contractors or any business person for that matter that needs to figure out accurate hourly billing rates for themselves and their personal. While the workbook is designed and distributed for SMBs (Small & Mid-sized Businesses) with only 7 employees it is unlocked and can be easily modified for companies with more employees by anyone with basic Excel skills (and if you need help with that you can always contact us too).
What is Capacity Based Markup? There are essentially only two ways markup costs to recover for Overhead (and Net Profit) that I know of. In one of those methods, the driver is what kind of dollar volume you estimate or project your company can generate in a year and then markup the combined total of the Direct Job Costs to get a Sales Price for the job.
(Labor Cost + Material Cost + SubContracting Costs) x Markup = Selling Price
But there are problems with this method that would really throw off your ability to consistently recover Overhead and Net Profit when you encounter projects that have different mixes or ratios of your company’s Labor to Material and Subcontracting costs. Using that method described above (often called a Volume Based Markup or Margin Based Markup) with a project that has a High Relative Cost of Labor to Low Cost of Materials, and SubContracting you will be underpriced. And with a project with a Low Relative Cost of Labor to High Cost of Materials, and SubContracting you will be overpriced and probably not get the job. To learn how that works out read my blog article: Comparing Markup Methodologies In Real Some World Pricing Scenarios to see how the numbers work out.
On the other hand Capacity Based Markup is based on how many billable hours your company personnel can generate in a year, a number that is much more accurately estimated than your Sales Volume. After calculating the number of billable hours you have in a year you then know you need to recover your Overhead Costs with those billable hours. Your Labor Cost times your Capacity Based Markup rate then gives you a Loaded Labor Rate and your formula for computing a Sales Price then looks like:
Estimated Number of Billable Hours the Project Will Take x Loaded Labor Rate = Selling Price
…and for any projects that require you to provide Materials and Subcontractors you extend that formula to be:
(Estimated # of Billable Hrs x Loaded Labor Rate) + (Material Costs x Net_Profit-Markup) + (SubContractor Costs x Net_Profit-Markup) = Selling Price
In his now classic book Running a Successful Construction Company author David Gerstel writes a brief but concise explanation of the CBM method on pages 167 & 168 where he also first coins the description of the method as Capacity Based Markup. And for even more reading about Capacity Based Markup checkout the blog article: Capacity Based Markup Articles & Learning Materials.
I originally created this Workbook as part of discussions in the Journal of Light Construction Online Forums back in the winter of 2004 regarding the use of what is known as a Capacity Based Based Markup also sometimes known as PROOF or Indexed or Labor Allocated Overhead markup. I gave the original file the working name of PILAO_Wksht_vx.xx an acronym for the worksheet that some JLC forum old-timers still call it.
The Capacity Based Markup Worksheet works by allowing the user to enter all the real or estimated overheads costs into the proper categories for allocation distribution (Fixed and Variable Overhead) and then using those figures to calculate an appropriate Loaded Labor Rate for each individual employee.
The Worksheets in the Workbook
The Labor Cost Worksheet
The Billable vs. Non-Billable Hours Worksheet
The Fixed Overhead Costs Worksheet
The General Summary Information Worksheet
The Owners Information Worksheet
Then…You will also be sent an email with your transaction information and the download link
After you receive your email with the download link click on the link and look in your computer’s download folder for the file named “CBMWksht_vX.XX”. The make a duplicate of it so you have a backup copy you can always revert to and then customize it based on your company’s budget numbers and personnel.
Licensed users of the Capacity Based Markup Worksheet will also receive free upgrades to any revisions whenever they become available. Just check back in from time to time to see if we have made any revisions then email us with your request.
Frequently Asked Questions
My Vehicles (Or Other Pieces Of Equipment) Are All Paid For Can I Leave Their Cost Recuperation Off The Workbook
What is the difference between a "Balanced Markup Distribution" and a "Weighted Markup Distribution"
I don’t really recall why I have that calculation in there in that I don’t support nor can I think of any reasoning why markup should necessarily be recovered that way. The only explanation for why I have it in there was it answered somebody’s request for it or came out the online discussion of workbook when I was first developing it back in 2004.
With the Weighted Mark-up Distribution, the markup is applied as a percentage of the productive billable employee’s cost and is in my mind more representative of the value that particular employee would deliver.
Can the WorkBook Be Used With Apple Numbers?
As much as I love Apple Computers (I am a lifetime Mac user) Microsoft’s Excel is a far far superior product and if you are a committed spreadsheet user I recommend switching to Excel.
Why does the workbook have a .xls extension and not a .xlsx extension?
- 3/12/2004 12:37pm First draft Version 1.1 published
- 3/12/2004 6:15pm Version 1.2 published. Added a Contingency line item to the fixed Overhead Worksheet section of the workbook and some minor spreadsheet housekeeping chores were taken care.
- 3/12/2004 6:15pm Version 1.3 published. 1) Reordered the two categories on the top of the Labor Cost Worksheet so that Employee Wage Information would be entered before the Yearly Work Schedule Information. 2) Made the separation of the the Billable vs. Non Billable Hour Computations from the basic Cost information distinct.
- 3/13/2004 3:25pm Version 1.4 published 1) Split what was the Labor Worksheet into separate worksheets now called the Labor Cost Worksheet and the Billable vs. Non-Billable Hours Worksheet 2) Added comments to the cells on those worksheets to help clarify what they are to the user and help with data entry. 3) Separated the Owners Compensation Information out as it’s own separate worksheet.
- 3/13/2004 10:42pm Version 1.5 published 1) Added comments to the cells on the Overhead Worksheet
- 3/14/2004 10:22am Version 1.6 published
- 3/13/2004 6:22pm Version 1.7 published
- 3/15/2004 7:06pm Version 1.8 published
- 3/25/2008 5:05pm Version 1.9 published
- 11/12/2011 3:38 PM Version 2.01 published
- 10/07/2015 8:18 PM Version 2.02 published Updated Links on the More Information On Capacity Based Markup sheet
- 09/13/18 1:55 AM Version 2.07 published. Updated links on the More Information On Capacity Based Markup sheet and added line items to the Fixed Overhead Worksheet for contractors that also operate a shop.