Effective Estimating – Part 2 of 6
In the last Effective Estimating blog post, I introduced myself and this series of posts. Today we will be spending some time working on a couple models in Excel (it’s not rocket science, but there is a learning curve) so we can learn to not only take our costs and build bullet proof Estimates and Proposals, but also be able to play incredibly valuable ‘What If’ scenarios.
So let’s look at a few ‘What If’ scenarios first, then we will look at some things you can do in Excel that may make playing What-If scenarios possible.
- What if we use this wood specie instead of that wood specie?
- What if we use this door instead of that door?
- What if we use this drawer type instead of that drawer type?
- What if I outsource my doors and drawer fronts?
- What if I outsource my cabinet components?
- What are the financial implications of Outsourcing in general?
Your Estimating system must be bi-lingual, it must speak two languages equally well. It must speak cost and selling price. There must never be a discrepancy between what you tell a client an item will cost, and the reduction made in total job cost if they opt to not purchase that item. At this point, you might ask, how do we accomplish that? On the low end of the scale, we do it with a spreadsheet, on the high end, we do it with a database. A database is beyond the scope of this post, but we’ll cover the basics for using a spreadsheet.
Seeing formula’s used in Excel models is the first step in learning to use this incredibly useful tool (it’s always easier to learn a new thing when it is used in the same context you want to use it in). My life experience tells me that the primary objective of an Estimate is to Sell a Project, but the secondary objective is absolutely no less important, and that is to Create Right Expectations (aka: Manage Customer Expectations).
Managing Expectations just might be the second hardest thing about cabinetmaking (managing employees claiming the top position for most cabinetmakers). Since there are no second chances at first impressions, then I’m thinking we should put our very best foot forward when it comes to the systems we use to produce our Estimates and Proposals, and let these two instruments of communication be our primary method for managing those expectations. Verbal communications are subject to forgetfulness and/or misinterpretation, so they need to be our last resort for managing expectations.
In my Effective Estimating Video Seminar, we will look at the mechanics of making just such a system a reality, and here, in this post, let’s see if I can try to illustrate a small portion of the process effectively. Maybe a step by step set of instructions with images will allow you to do your own sample spreadsheet as a by-product of just following along.
First, lets open a new Blank Workbook in Excel 2016. Then let’s name the first Tab ‘Options’ (right click on the tab > choose Rename > type Options).
Now let’s add a second tab by clicking on the + sign to the right of our first tab and name this new tab Price Worksheet.
Now let’s add our Headings to our Options tab. I like to add a Fill Color and make my Heading text Bold.
Now let’s add our Door Style options, our Design Costs and our Material costs. This portion is nothing more than typed text, and again, I like to make my cost fields have a Fill Color, in this case green (the color of money). You will also need to change the Number type of all the cells related to money from General to Currency is you want your sample spreadsheet to be sexy.
Now let’s go to our Price Worksheet tab and add our Headings. Again, adding a Fill Color to the heading, and making the text Bold.
Now we’ll get a little more technical, select field A2 > On the Data tab, in the Data Tools group, click Data Validation > In the Data Validation dialog box, on the Settings tab, choose ‘List’ in the Allow pull-down > In the Source field, select the Up Arrow to the far right of the field and navigate to the Options tab and select A2 through A12 and click the Up Arrow again, the results in the field should look like this: =Options!$A$2:$A$12 > Click OK.
That’s it, now go click in the A2 cell and you will see a pull-down option appear to the right of the field, and when you click on it, you will see your list of Styles. Choose A for now.
Now, even more technical (but not overly difficult), click in cell B2 > Select the Formulas tab > Select the Lookup and Reference option > Select ‘Vlookup’ near the bottom of the list. You will see a pop-up box like this:
Now let’s fill this thing out. Select the Lookup_Value Field and enter A2 OR click on the Up Arrow and then click cell A2 and click the Down Arrow> Select the Table_array field and enter Options!A2:B12 OR click the Up Arrow and select fields A2 through B12 and click the Down Arrow > Select the Col_index_num and enter 2 > Select the Range-lookup field and type the word False > Click OK. Don’t forget to set your field type to Currency to make it look sexy, and test your field by selecting a different Style from the pull-down list in cell A2.
NOTE: If your Design field does not recalculate when you select another Style, you need to select File > Options > Formulas and make sure the Automatic radio button is selected in the Calculations options area.
Now let’s repeat what we just did with 2 slight variations for our Material field. Click in cell C2 > Select the Formulas tab > Select the Lookup and Reference option > Select Vlookup near the bottom of the list. Select the Lookup_Value Field and enter A2 OR click on the Up Arrow and then click cell A2 and click the Down Arrow > Select the Table_array field and enter Options!A2:C12 OR click the Up Arrow and select fields A2 through C12 and click the Down Arrow > Select the Col_index_num and enter 3 > Select the Range-lookup field and type the word False > Click OK. Don’t forget to set your field type to Currency to make it look sexy, and test your field by selecting a different Style from the pull-down list in cell A2.
That’s it, you’re done. Obviously if you want to build an entire Estimating Spreadsheet, you will need to do a lot more of this work, but that is the key elements. List all your Materials on the Options page, and tie them to your Price Worksheet page. If I wanted to use this to price my doors, I would then either type in a quantity of needed doors, or do something like this for cabinets where I chose a particular cabinet, and that cabinet had number of doors, drawer fronts, drawer boxes, etc., listed out, and when I chose that cabinet, and added a quantity of that cabinet, I would get a door count, then multiply that door count by my NEW Design field for a total Design cost for doors. Obviously calculating door square footage is more complicated, but it would still be done in the same way.
By doing this, these pull-down selection boxes allow you to provide your clients with quick What-If scenarios and the cost implications of using one door over another, one finish over another, one wood specie over another, etc., etc., etc. OR, you could use a database. A database is much harder to do on the front end, but exponentially more flexible when used, and you can provide your clients with gorgeous reports.
So you can check your work, or look to see what you may have missed or entered incorrectly, here is a link the finished spreadsheet we just created:
And here is a link to a really, really, really old spreadsheet that has not been touched since 2001 (we stopped updating it shortly after our Business Partner Estimating Software was introduced, as mentioned in the first post, my new version of this software is now called Custom Cabinet Estimator). Feel free to play with, modify, or change this spreadsheet any way you want. It is yours to keep and do anything you want with it (obviously the pricing in this old spreadsheet is 17 year old, so make sure you update everything before attempting to use it for anything other than experimenting and /or playing around).
True32 Scheduler-Estimator-2001
Today we covered What-if scenarios, a few Excel tips and tricks and managing Customer Expectations with our Estimates and Proposals. In our next article in this Effective Estimating series, we will delve into the dreaded term “Ballpark Estimate” and the implications of handling this request well or poorly.
To make sure you won’t miss a crucial part of this discussion, enter your email address in the ‘Subscribe to Blog via Email’ field on the right, and each time one of us adds a blog post, you will automatically be notified. Oh, and you won’t hurt our feelings if, depending on where you are seeing this, you Share, Like, Plus or Re-tweet it (there are buttons somewhere on this page to help you with that).
May the Lord bless the work of your hands, heart and mind.
