May 2003 issue
ICON: Seize the Opportunity

Using Computer Software to Bid a Cabling Project
By Peter J. Buitenhek

"You ask me if I keep a notebook to record my great ideas. I've only ever had one."   -Albert Einstein, theoretical physicist (1879-1955) "Everything should be made as simple as possible, but not simpler."

My involvement with cabling started in 1979. Back then, communications cabling was simple, each manufacturer had a proprietary scheme. Analogous to driving your car from San Diego to Los Angeles, you would use the IBM Highway if you owned IBM computers - the DEC Highway for DEC computers and so on. Today, for computer communications we are using one structured highway and letting standard interfaces do the busy work. These "communication standards" are a credit to IEEE, NEC, EIA/TIA and BICSI. A simple RJ-45 jack and cable allow near-virtual communication connection to hubs, routers, NIC's and computers. Constantly evolving funneled standards and innovative manufacturing processes have yielded cabling products that are all their marketing people claim them to be. Many contractors bidding communications cabling projects still use "seat-of-their pants" practices. Variances in technician/estimator ability and training nearly out-weigh the technical advances of communications cabling and hardware. Larger organizations utilized formalized training programs and cumbersome prepackaged estimator/design software. Conversely, smaller cabling companies only have homegrown software or best-guess practices to bid sophisticated projects. On a recent cabling installation project for a convention center, the owner received seven bids from cabling contractors that ranged from $91,000 to $235,000. Prior to this bid, the owner did the homework with the top supplier/top material vendor (Graybar and Belden) to determine baseline material costs: $89,000. Obviously, major items like tax; labor and profit dollars were over-looked by some contractors and over-extended by others.

Where to Start?
The first question that always pops up is, "Are computer software programs reliable enough to give me consistent winning bids?"
No. The results are only as good as the person entering the data for analysis. If your design includes all possible components and associated tasks you will, most likely, not be the lowest bidder. However, if you include all possible components and tasks but bid the project at a lower gross profit margin, knowing you may not need every item, you will find that lower GPM will rise with every task completed under budget. So challenge the project managers and technicians to excel at their task and include a bonus in your plan if they meet the challenge.

BidPro Sheet Breakdown and Descriptions

Summarization
The Parameter Sheet sets the company specific information. Project tasks are defined on the Labor Sheet and assigned technicians are applied to these tasks. The Material Sheet list all expected material items needed for task completion. The Summary Sheet is used to fine-tune all Parameter, Labor and Material Sheets supplied costs. The Margins Sheets allows up to six sell options based on desired company profit. The inputs will yield the desired profit options within minutes.

Start to Standardize
Material and labor costs are a good starting point for bid standardizing. By utilizing expected costs for the project, we can structure the bid sell price based on profit margin. If any of these expected costs do not materialize, we can use these monies to purchase test equipment, pay for overruns or keep as profit.
Labor Force: consulting services, drafting, site survey, as-built drawings, installation, project manager and administrative services all require specific skills. List their associated raw pay rates (employee pay prior to company costs for 401K, healthcare, disability, et al). List these services by category, name or assignment.

The 16 WBS or work-breakdown-structure numbers will later be used to assign specific employees or groups to tasks. Your Standard $ Range versus This Bid Raw $ could be helpful in determining the competitiveness of the final bid. Higher priced people will usually increase the labor pool cost. Conversely, the higher paid people should lower the total task labor hours since experienced people will take less time to accomplish tasks. As depicted in Figure 1 Labor Raw Cost, Employee Classification can be specific named people, groups of people or areas of expertise. Project Manager pay-rates may vary within your organization so set the range and actual pay rates to determine exact labor costs.

Figure 2 Burden Rate Percent

Since raw labor costs do not reflect the company's actual cost only the employee pay, the burdened labor rate will be required. Health benefits, 401K and disability/unemployment insurance are some areas that cost your company money in addition to the actual raw pay. Usually a 30 percent burden rate will cover most employee expenses. Therefore, if one hour of the project manager (we'll use Albert as an example) were to be used; the burdened labor rate would be the raw labor cost times one plus the burden rate (1+0.3=1.3)

Figure 3 Burden Labor Cost

Thus Albert would cost the company $20.80 (16 x 1.3). This value will then be used to calculate the sell price of Albert to the customer.

Figure 4 Labor Markup Percent

The sell price per hour of Albert would incorporate the burden and labor rates to give Albert a sell value of $33.28 per hour (20.80 x 1.6).

Figure 5 Margin Calculation

Notice the calculated gross profit margin of 37.5%. This value depicts net sales minus cost of sales or basically how efficiently a company markets their service. As an example, if the labor cost was $30.00 and the sell price $33.28, the corresponding profit of $3.28 would provide a gross profit margin (GPM) of 9.9%. Thus, a higher GPM will reflect higher profit dollars.

Formula for Gross Profit Margin
Gross Profit Margin (GPM) x 100% = (Sell Price minus Cost) divided by Sell Price. Additionally, some companies have indirect costs (additional employee benefits or executive travel benefits) that affect the company bottom-line costs but do not show up in labor cost figures. Therefore, the Direct Job Overhead (DJO) markup can be used in addition to burden margin for this project.

Figure 6 DJO

This markup will add overhead charges to the raw labor cost only; but will not markup that cost with the 60% Labor Markup. The sell price is equal to the cost and thus the profit margin will reflect 0.0% GPM. The net effect of DJO is to add cost to the equation without the associated sell markup, which will reflect a less efficient operating company. The * signifies this quantity does not have a sales markup figure.

Figure 7 Margin Calculations with DJO

Another area of additional labor cost could be "Non-productive" time. Non-Productive time is available to incorporate the time a technician is on-site waiting for other trades, comebacks, meetings etc. The Non-Productive time is the total number of hours per day counted on to complete the task but is really not available. To account for this lost time, we can incorporate an add-on charge per hour to that technician base rate. Since most teams break at the same time and the same duration, this information is converted to an average cost/hour for each technician and added to all technician hourly rates. A 15-minute break every two hours equates to one hour of non-productive time per eight-hour day.

Figure 8 NPT

For Albert the PM, the 1.00 per hour of NPT would result in an additional labor cost of $2.00 per hour ($16.00 per hour divided by eight hours per day). The equivalent raw labor rate for Albert would be $16.00 base plus $2.00 NPT plus $2.40 DJO = $20.40. Again the * signifies cost equals sell price. These extra charges would lower the overall GPM since we are adding additional cost at zero profit dollars, which means we are becoming less and less efficient.

Figure 9 Margin Calculations with DJO and NPT

Figure 9 "Margin Calculation with DJO and NPT" can be shown as follows:
1. Raw Rate is $16.00 per hour for 1 hour times 1 plus 30% Burden Rate = $20.80 cost
$20.80 cost times 1 plus 60% markup = $33.28 sell price
2. NPT cost $2.00 per hour for 1 hour with no markup = $2.00 cost
$2.00 cost times 0% markup = $2.00 sell price
3. DJO is $16.00 times 15% DJO markup for 1 hour = $2.40 cost
$2.40 cost times 0% markup = $2.40 sell price
4. The Total Raw Labor Rate is $16.00 per hour
5. The Total Burden Rate is $25.20 per hour ($20.80 raw plus $2.00 NPT plus $2.40)
6. The Total Sell Rate is $37.68 per hour ($33.28 plus $2.00 NPT plus $2.40 DJO)

Usually not all workers are subject to these added NPT costs. Therefore, for extremely competitive situations we can remove the NPT dollar amount for highly efficient technicians from the bid rate. In fact, we may want to consider plus/minus capabilities to account for future expected labor cost increases, like upcoming raises during the project duration.

Figure 10 Labor Cost Summary Sheet

The negative $(2.00) at the "Plus Extra Labor Cost per hour" input section should remove the value input by the NPT of $2.00 per hour. For many bids we mark the NPT for 1.00 hour per hour and decide at the Summary Sheet if we want to include/exclude a NPT figure for each technician.

Figure 11 Margin Calculations with DJO and NPT minus Extra Cost

Example 1 of raw labor rates, NPT, extra cost and DJO

Figure 12 Example Burden Calculation with DJO, NPT and Extra Costs

If Example 1 was only labor (no material or other costs) the final GPM and averaged sell labor rate would be $27.81 per hour:

Figure 13 Total Sell and GPM

Figure 14 Total Labor Rates

The Labor Sell rate can easily be used if any add-on work is applied to this project. Assuming we use Albert, Bryan and Charlie, a job estimates at 35 labor hours could be quoted at $973.35 (35 x $27.81) with little seat-of-the-pants input. The 33.9% GPM (gross profit margin) is very healthy for a company financially. Personal Note: many companies tell me they easily get 35% plus GPM on all their projects. But after using the BidPro software, find they are averaging 20% - 30% GPM. Remember, consistency in pricing and quality of work will take the company along a strong financial path.
Three additional items can add to the cost of labor: Prevailing Wage, Premium Wage and payroll taxes (if paid up front). Prevailing Wage - usually relating to government and school type jobs, dictate the pay rate for your technician by time-in-grade. There are two methods to handle these cost items: add a blanket dollar amount to all technicians (say $2.50 per hour) or calculate each technician (or groups or areas) at the Prevailing Wage and plug those numbers into the raw labor rate.

Figure 15 Prevailing Wage add

If we continue to utilize the Example 1 input, the Labor Summary would reflect the $2.50 per hour increase for each technician.

Figure 16 Example Burden Calculations with DJO, NPT, Prevailing Wage and Extra Costs

The associated labor rate would also reflect the added Prevailing Wage amount:

Figure 17 Total Labor Rates

The Total Sell and GPM figures:
Figure 18 Total Sell and GPM

The added prevailing wage cost has the usual 60% sell markup. Note the higher GPM of 34.4% (versus 33.9%), which reflects the profit percent of prevailing wage at 37.5%. The Premium Time markup (expressed as a percent) can cover holidays, union, emergency or weekend work. If premium time were chosen, all technician raw labor rates would have risen 10% (i.e., Albert would add $1.60).

Figure 19 Premium Time

The final labor cost item could be the labor tax (if applicable in your area). Some companies pay these taxes up front, as a cost. Some companies always markup all costs. However, for this example we will calculate tax only on material.

Figure 20 Labor Tax as cost

Material Sell Prices: The approaches to material sell prices are threefold:
1. List quantity and material cost along with a standard material markup
2. List same as Item#1 but make each markup unique.
3. Combine the Item#1 and #2.

Figure 21 Material Markup Percent

This standard markup will be applicable to all material entered for your project. If there is a special widget that your company buys but wants to sell at a higher markup, the "Desired Markup %" unique section can be utilized. It will negate the standard markup and use the unique input value.

Figure 22 Material Cost and Sell Prices

The list of material items (description, vendor, supplier, colors and part numbers) can be stored under separate covers. Simply call up the specified material description/price sheet and copy to the material sheet. Add quantities and desired markups for this project and you have a comprehensive material layout. Two items can add costs to your material sheet:
1. Miscellaneous Material
2. Shipping or Freight Charges
Miscellaneous Material is a percent of the Total Material Cost to account for cable hangers, cable ties, setup, ceiling tile damage, oversight, etc. The industry average miscellaneous material cost is 10 percent of the total material cost. Set your specific values.

Figure 23 Miscellaneous Toggle

Freight percentage is calculated from the Total Material Cost (with miscellaneous material charges included) to account for shipping handling, warehousing and pickup. The industry average freight cost is five percent of the total material cost. Set your specific values.

Figure 24 Freight Toggle

Since we are using the Example 1 labor hours of 6.5, the material - labor skew for this example may not be accurate. It will take about 80 hours to install 12,000 feet of communications cabling (average run 152 feet), 75 jacks and associated faceplates. With a CAT5e patch panel the total sell price of this project may be around $7,000. See http://www.profitdeveloper.com for CablePro software package.
The cost of our project is $1,578.44, which has accounted for material, labor and freight charges. The sell price of $2,436.27 will provide us with $706.06 in profit (tax excluded) or 30.9% GPM.

Figure 25 Margin Calculations for Material, Labor and Freight