Spreadsheet Tutorial

Let’s take a look at the “Board Feet Sample” spreadsheet package. Open the file, and note the tabs at the bottom of your screen labeled “Lumber Takeoff”, “Lumber Bid”, “Bid Comparison”, “PO Attachment”, and “Inventory”. Each tab is a separate spreadsheet of the same takeoff file and, of course, each has a different purpose and use. The “Lumber Takeoff” sheet actually controls the data posted to all of the other spreadsheets – that is, when any takeoff item in the “Lumber Takeoff” sheet is changed, the change is also made to the same takeoff item in the other spreadsheets. Be careful not to change any data in this file except in the “Lumber Takeoff” sheet. For example, if you opened the “Bid Comparison” sheet and changed the “Job Total” amount for line item #3, “2x10-8’ floor/clng joist” from 24 to 36 pieces, the change would only occur on the “Bid Comparison” sheet. All other sheets would remain at 24 pieces. However, if you make the same change on the “Lumber Takeoff” sheet, the other spreadsheets will update to 36 pieces automatically.

Now, let’s look at the “Lumber Bid Sheet”. Click on the tab at the bottom of the screen to open that spreadsheet. It’s pretty self-explanatory, in that this is the sheet you print and send to your supplier(s) for pricing on the takeoff items listed. Here’s where some sound construction management practices come into play, though. Be sure to insist to your supplier(s) that they use this sheet to show you their unit pricing for each takeoff item. They may complain loudly about this, but it is essential if you are going to get a true “apples to apples” comparison of each supplier’s pricing. They all have a slightly different format for producing a lumber price list, and that’s fine for giving you a “bottom line total”, but it is imperative that you be able to see the unit pricing that results in that “bottom line.” You’ll see why soon.

Next, click on the “Bid Comparison Sheet”. On this form, you’ll be able to insert each supplier’s pricing (up to four suppliers) in order to get a side-by- side comparison of every lumber item in your takeoff. Let’s say you received the following unit pricing from four suppliers for 9’ studs:

Supplier A = 282 per thousand board feet (MBF), or about $1.692 each
Supplier B = 292 per thousand board feet, or about $1.752 each
Supplier C = 275 per thousand board feet, or about $1.650 each
Supplier D = 301 per thousand board feet, or about $1.806 each

Now, it’s pretty obvious that Supplier C has everybody beat on 9’ studs, but let’s say that you looked at the bottom line total for the four suppliers and found that Supplier D actually came in with the best total cost for the project. I would venture to guess that the first thing you’re going to do is call Supplier D and tell him you like his offer, but could he just tweak that price on those 9’ studs a bit to get more in line with the other three suppliers that bid on your project? I think the usefulness of this spreadsheet is pretty obvious from that example.

Let’s move on to the “PO Attachment”. It’s pretty self-explanatory, too, in that this will be the spreadsheet you print and send to Supplier D when it’s time to order the lumber. But let’s say your jobsite has a limited staging area for materials, and you don’t want to be worrying night and day about a couple of million dollars worth of lumber lying around, begging to be stolen. Because the takeoff is produced in a “by floor level, by building type” format, you could schedule delivery of the lumber by line item number and, because the totals by building type are broken down on your “Lumber Takeoff” sheet, even by building type as well. Example: let’s say your Superintendent is ready to drop lumber to begin framing, but only wants the drops to come by floors (in this case, for 4-story buildings). You would therefore call Supplier D and tell him to ship line item #s 3 through 83 on the “PO Attachment” sheet. Better yet, let’s say that your Superintendent only wants the first floor materials for one of the “Type I” buildings and two of the “Type II” buildings. You could print the “Lumber Takeoff” sheet and send it to Supplier D, and then tell him that you only want half of the totals in column M and two-thirds of the totals in column P for line items #s 3 through 83. Why? Because there are two building type I’s and three building type II’s in your project (see cells N6 and Q6).

Now, how do we keep track of the mess we just ordered? Click on the last tab, “Inventory”. Here, you’ll be able to log each shipment of each line item number, and the spreadsheet will keep a running total of how much has been shipped and how much remains to be shipped. How many times have you thought you left lumber in the yard, but still got charged for it as though you had taken delivery? The possibilities are endless with this spreadsheet, but the important thing is that you’ll be able to track every stick of lumber with ease.

Hopefully by now you see that we try to give you much more than just a lumber takeoff with our integrated package. Using proven construction management techniques, this package could easily save you thousands of dollars in wasted and lost lumber. As always, we are open to any suggestions on improving our product, so we encourage you to provide feedback if at all possible. Thanks for your time, and good luck with your next project!