The Capacity Based Markup WorksheetA Simple Spreadsheet App For Figuring Out "How Much Should I Charge"
How To Calculate Your Hourly Rate As A Contractor
The 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 Introduction Worksheet provides a basic overview of the spreadsheet’s framework and navigation links to the individual worksheets.
The Labor Cost Worksheet
With the Labor Cost Worksheet in the yellow shaded data entry cells you set the wage rates, and then the costs that make up your Labor Burden i.e. the employer costs such as benefit costs, and other miscellaneous Variable Overhead expenses you want to cover such as car and/or truck payments, vehicle maintenance, small tools, etc.
The Billable vs. Non-Billable Hours Worksheet
On the Billable vs. Non-Billable Worksheet you account for items such as paid holidays vacation time, training, downtime and other non-billable but paid hours that you and your employees will generate.
The Fixed Overhead Costs Worksheet
With the fixed Overhead Worksheet you enter you cost or budgets for all of your Fixed Overhead items such as Capital Tools, General Office Expenses, Marketing, Insurance , Professional Fees, Local & Regional Business Taxes., Loan Payments, Contingency, Owners Compensation, Sales Cost and the Net Profit you want your business to generate.
The General Summary Information Worksheet
The General Summary Information Worksheet displays the calculated Labor Billing Rates for you and your employees along with a breakdown based on all of the entered data as to what your costs and projected Net Profit are per employee and/or per hour, per day, per week, per month or per year.
The Owners Information Worksheet
And after setting up and answering the questions in the yellow shaded cells the Owners Information Worksheet displays the Owner’s Compensation package based on all the data entered.
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
Well, you could but you really shouldn’t. You will still have to replace the item someday so you need to plan for that recoupment cost now and not when it comes up which might very well me and ambush or emergency situation. For more on the thinking behind the subject:
What is the difference between a "Balanced Markup Distribution" and a "Weighted Markup Distribution"
In a Balanced Markup Distribution, each production employee is marked up the same uniform amount of dollars on top of their cost. In other words is I have three productive billable employees and one costs $20 per hour, one $30 per hour, and one $40 per hour I would add let’s say $28 per hour to their cost to come up with their loaded rate. The $20 per hour employee would bill at $20 + $28 = $48 per hour. The $30 per hour employee would bill at $30 + $28 = $58 per hour and the $40 per hour employee would bill at $40 + $28 = $68 per hour.
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?
Yes, the Workbook can be used with Apple’s Numbers but when you convert the file you lose the tab-based sheet navigation but perhaps even more importantly Apple’s Numbers doesn’t support cell or sheet protection so it then possible to damage or break the functions if you’re not carefully using it.
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?
The default filename extension Microsoft Excel gave to new spreadsheets prior to Excel 2007 was .xls and we keep it that way for the sake of backward compatibility for users with older versions of Excel and users with Apple’s Numbers.
- 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.