How to Price Lumber and Materials Internally for Any Size Project – Part 1
One of my earliest frustrations as a framing estimator was waiting for the lumber company to price my lists. I would spend a few days , maybe a week putting lists together for a tract job consisting of a few plans with 3 or 4 elevations each then send it off to the lumber company. Then I would wait, and wait, and wait for them to get the list back to me. This time would vary depending on the company, we usually got pricing from 2-3 suppliers. I may get it back in a couple of days, maybe more. If my salesman was busy it could take a few more days.
Once I had the lists back I had to check each line item to be sure everything I wanted priced was included in the quote. I always thought that there has to be a better way. While continuing my education one of my instructors requested that we create our take-offs on columnar sheets to keep neat, but at the same time use some of the columns for calculating material pricing. From this concept I took upon a task of creating spreadsheet templates in Microsoft Excel to perform this function.
This was the better answer I was looking for! These spread sheets allowed me to price a job in a matter of minutes based on the suppliers MBF/ MSF/ LF pricing. In addition to speeding up price turnaround time to my customer, I was able to minimize errors. This was because I was no longer relying on someone else manually entering my list into their program for output. Additionally, if there was an error I had only myself to blame. Over the years I have tuned up the spreadsheets to suit different needs. It has grown a little more complex, but I feel with a decent base of general Excel functions anyone is capable of doing this. A person using estimating programs or design software that has the ability to create and maintain a price database may not find this as helpful, but for anyone working with material lists this can be a huge time saver.
First, you need to have a solid understanding of LF (Linear Foot) to BF (Board Foot) conversions. The same goes for Square Foot conversions for sheet goods, as well as the differences between how different goods are prices. Some products are sold by the BF, SF, LF or Each price.
Next, once you establish pricing with your supplier you will have a basis for creating your template. My template is based on dimensional lumber and sheet goods being sold by MBF/ MFS or per thousand board/ square feet and EWP being sold per LF. Below is an example of my master price page.
- The first thing you may notice is there is only a quantity of one for each item.
- Since this is driven by the suppliers unit price there is no need to supply them with individual lists.
- The pricing from the supplier is entered in the “Price” column all other columns are multiplying based on the Quantity and Length entered.
Let’s take a look at the first line Treated 2×4. One piece at 20’ gives us a total of 20 Lineal feet. The Excel formula would be entered to have the “Qty” cell multiplied the Length cell. Now board foot conversion is Thickness -X- Width –X- length divided by 144, or short hand Thickness –X- Width divided by 12. I have chosen the latter method because since it gives you a BF conversion factor per LF of material you have.
So for our treated 2×4 example 2×4=8 divide 8 by 12 and you get .6666666. I have found that rounding the third digit up one gives you the best calculation to match lumber company pricing. So for every linear foot of 2×4 you have .666666 or .667 Board Feet. So the BF cell Excel formula would be LF cell multiplied by .667.
The pricing cell is also a decimal. This is because as mentioned before the supplier’s price is per thousand board feet which would mean that in the treated 2×4 example 1000 BF of 2×4 PT#2 would cost $600 dollars.
Since we are not buying 1000 board feet we are only buying 13.34 board feet you move the decimal 3 places to the left to give us a per board foot price. This means that one board foot would cost 60 cents. The extension is multiplying the price cell by the BF cell. Each item has to have the BF calculation done specific to the size of lumber. If you are familiar with lumber invoices this format should resemble a supplier invoice.
Then, you would need to continue to build the template to include one of each item you use on the entire job. Below is an example of some sheet goods and EWP in the template.
The sheet goods are calculated based on SQ FT of the panel. So in the LF is actually an “Each” carry-over of the Qty and in the BF column is actually the SQ FT of the panel multiplied by the Qty. Price and extension are the same as the dimensional calculations.
In the EWP example you will notice the BF column only has a dash in it. This is only a place holder to remind me there is no data entered in this cell since the price for EWP is based on LF. You will notice the price is dollars and cents. The rest of the cells will be calculated the same except the extension is price cell multiplied by LF cell. Since I have re-used and expanded the template I have shaded some of the items grey to indicate they are not used on this project. This will save me and my supplier from wasting time pricing items that are not applicable to this job.
Once you have the master template completed you can create a copy of it and send to your supplier and have them fill in the price column, or I have found that I can call my supplier and in a few minutes review pricing for each line and enter the prices while on the phone. In that example I recommend sending a completed copy of the master sheet to your supplier to make sure they have a record of the pricing they quoted you on the phone.
In Part 2 of this series, we will review linking pricing on the master sheet to your individual plan and elevation sheets. Stay Tuned!
Jonathan Wagner – Design Trainee
Gould Design, Inc.