How to Price Lumber and Materials Internally for Any Size Project – Part 2
For those of you that read part one of this series welcome back. For those of you that are just joining us, part one can be viewed here, as an overview we covered;
- The benefits of being able to price materials internally
- Linear foot to board foot and square foot conversions
- Setting up a master price template
- Inputting pricing obtained from your supplier
Now we are going to review linking pricing from the master sheet to individual plan/elevation sheets. If you are starting from scratch you can copy your master sheet into a new tab and rename the tab to your plan number/elevation.
Your worksheet tabs should look like this:
Now that you have a specific plan/elevation tab you can begin adjusting the quantity and length columns specific to the plan and elevation you are working on. Once your list is complete you can begin linking pricing to the master sheet. This can be a lengthy process if you have several line items to price. You must pay attention to the cells that you are linking because any pricing errors are on the person creating the list and not the supplier. In excel to link data from one cell to another the formula starts with the plus sign (+).
First in the plan/elevation list select the line item you want to price from the “Price column” and hit the plus sign, then click the master sheet and click on the price for the same item.
Using the first item on our list (2×4 treated) the formula should appear at the top of the worksheet in the fx or formula bar and look like this:
Then before leaving this sheet move your cursor to the formula bar and click at the end of the formula then hit the F4 key.
The formula should be modified as follows:
Your display will shift back to the plan/elevation page and the formula will be modified to include $ symbols around the cell you have selected. This is a keyboard shortcut to manually entering the formula that you see above. By changing the formula with the F4 key you have told the work sheet to always select that specific cell. This is very important down the road if you add items to your master sheet, if we were to add a line above 2×4 treated the price cell would no longer be G9 it would be G10. By modifying the formula the link to that cell will automatically adjust to G10.
Next continue linking all items in the plan/elevation list to the master sheet. You can modify your plan/elevation list however you want. I prefer to subtotal each “load” individually. Don’t forget the per-thousand or LF price from your supplier is a pre-tax amount. So when you get to the bottom you will need to sum the extension column and add tax. In California we have to add an additional 1% lumber tax to the subtotal amount on top of sales tax.
Summing the extension column can be done using the following formula:
Tax is applied to the subtotal amount for each “load” section and then totaled at the bottom.
Once you have completed one plan and elevation you can then copy that worksheet to the next plan/elevation and begin adjusting your qty and length. If you copy from a completed plan/elevation you will avoid having to re-link the pricing to the master sheet.
When all plans and elevations are complete you can print for bidding/shipping or, as I like to do, link the load totals, or plan/elevation totals to a package total worksheet. This can be done by adding a tab, I like to add tab in front of the master price tab. Input all the plans and elevations you have prepared lists for and begin linking totals similar to the way we linked pricing to the master sheet. I like having a summary that fits on one page to refer to my package totals while preparing bids.
Similar to this:
As I mentioned this can be a lengthy process, but once complete you have a considerable amount of flexibility when bidding. You can send your master bid sheet to several suppliers and input each supplier’s pricing while saving and renaming each file as the suppliers name/date.
You can then take that information and compare side by side in another Excel sheet. You can compare suppliers to the specific line item, or by the package total. We typically negotiate lumber locks for a period of time, so since I have the pricing I may bid several jobs during the lock that my supplier may not even know about since I do not need them to price any lists. I just simply add a tab and adjust quantity to create an estimate. At the end of the price, I lock myself and the supplier update pricing I save a new version and I automatically have an updated estimate.
This has come in mighty handy when we bid a job that never took off, six months later the builder asked to update our proposal. This tool also works well for updating lists once we have had an opportunity to build a release. If the quantity was off on my estimate for some reason, I can easily go back into my list and adjust to what is actually being shipped and true up my estimate.
In summary, we have reviewed how to create an accurate Excel based material costing system with the ability to increase bid turn around and minimize errors. I hope you find this information to be worthwhile and informative. If you are involved in material pricing this system can save you lots of time as it has done for me. As an estimator, you know that the two days you don’t spend waiting for pricing to come back from you supplier can be spent working on the next bid.
Please share any material costing methods you may use and what you like or don’t like about them. We look forward to your comments!
Read Part 1 in this series here.
Jonathan Wagner – Truss Design Trainee
Gould Design, Inc.