Included with the program is "Roofing Sample.xls", a sample ExcelT spreadsheet that can be a terrific asset as part of a roofing estimating program. It is specifically formatted to work with the Ziatek ® RoofDraw program. The spreadsheet includes a sample database with a few fictitious items that are only intended to guide the user in how to set up his or her own items. The pricing and conversion factors in the sample items are intentionally not valid - do not use these items for real estimates without first entering YOUR DATA ! The spreadsheet supports an easy-to-use assembly type of takeoff.
Figure 36, Sample Estimating Spreadsheet
To use the spreadsheet, first create a roof project in Ziatek ® RoofDraw . In RoofDraw, select "All Data" from the Quantity drop-down list. Using the SEND button, transfer the data to the spreadsheet. (Ensure that the spreadsheet is open and that cell F1 is selected, first.) The spreadsheet range F1:H9 will be populated with data from RoofDraw.
Select cell A12, the upper-left corner of the estimate data, and click on the button labeled "Select Items". From the pop-up window, select the "Composition Shingle" assembly and click OK. With that action, all of the items in that assembly are completely taken off and priced! To complete the estimate, total the Extended Price column and insert any add-on items that are not already in the unit prices. Add-ons might include things like tax, bond, overhead and profit.
A few of the salient features include:
- The cells in the range G1:G9 are named, so that a formula could reference "Ridge" instead of the more cryptic cell address "G5".
- Each item in the database can have a prototype formula assigned to it. When the item is inserted into the estimate, a true ExcelT formula is created "on-the-fly" from the user-defined prototype.
- An assembly is easily selected from a pop-up list.
- Items in the database are assigned to an assembly by number. See the description of the database below.
Figure 37, The Database Page
Click on the "Database" tab at the bottom of the spreadsheet to access the database page. A brief description of how to create assemblies and items follows.
- The list of names for the pop-up assemblies list are entered at the top of column I.
- Next to each assembly name, a unique number is assigned in column H.
- As an item is added to the database, the assembly number to which the item is assigned is entered in column A.
- Column B is where the name of the item is entered.
- The formula prototype for the item is entered in column C. The prototype is simply an Excel formula that is embedded between "{}", a pair of curly braces. The user is encouraged to use the cell names from Estimate!G1:G9 instead of the cell addresses as that makes the formula much easier to read and understand. For instance, the formula prototype in cell C18 above is:
{=ROUND(1.1*(Starter+Other_Horiz.+Slope)/10+0.9,0)}
- Column D is where the desired units of measure are entered. There are no rules for units labels as this value can be any text string.
- The unit price in column E should be the price for the unit of measure indicated in column D.
With a rudimentary knowledge of how to use Excel and the short list of special features listed above, the user will quickly be assembling professional estimates in record time. By combining this spreadsheet with the Ziatek ® RoofDraw program, a complete and accurate estimating solution
Literature