Tutorial

Welcome

This section will briefly explain the Estimating Workbook & What it does

Explaining the Workbook

Usage

The workbook is used to estimate a Construction Project

Custom Ribbons

A Ribbon is a toolbar that appears at the top of the Excel file (Ex. File, Edit, View ect.) and has many buttons and menus to choose from

Three Custom Ribbons will appear when using an Estimating File/Workbook

• EstToolTab --> The Main Ribbon, Used for basically Everything

EstToolTab Ribbon (Click Me)

• Est To VP --> Contains Consolidation Page Features and Export to Viewpoint buttons

Est To VP Ribbon (Click Me)

• Report and Others --> Contains Features and buttons that didn't fit on the EstToolTab Ribbon

Report and Others Ribbon (Click Me)

More In-depth info on these Ribbons can be found at the bottom of this Section or throughout the Help Manual

How Does the Program Know to Show or Not Show the Ribbons?

Each Excel file has something known as 'Custom Document Properties'. If this is the first time hearing of this, you don't really need to worry about it.

Its kind of like a pocket dictionary that each Excel file carries with it. Excel knows to show the Ribbons if the current Excel file's pocket dictionary says 'IsEstimatingFile'.

Click here To See how to view & access the Custom Document Properties if you are interested (you should never change them)

Pages & Purpose

Main Pages

There are 3 Main Pages that Each Estimating Template Operates with

• Tender Evaluation --> Page contains Project Name, Dates & other project Info
• Summary --> Sums Up and Contains Totals Info based on Div's
• Div's --> Pages that Contain the Costs & Numbers

These Pages Cannot be deleting and shouldn't be deleted as they are vital to the programs functionality

Other Pages

There are Several Other Pages that can be Created & Deleted which can help with the Estimating Process

• Recoverable --> Recovers the Selected Pages
• Consolidation --> Consolidates the Selected Pages
• Breakdown --> Totals Alpha'd rows and allows for some Calculation
• Div Copies --> Copies of Div's that are edit-able

The explanation for these pages aren't very good but they kind of act as their name suggests

Div Structure

99% of the time you will spend on an Estimating File will be spent on Div's so its important to know their Structure & Purpose

Beginning Rows

Each Div Initially starts off with 5 rows which act as a title and Column Headers for the Page

These should never be edited as shown below in the Editing Area Section

Rows

After the beginning rows, every row is one of the rows as described in the table below

Each row serves their own purpose and should be used together to get a cleaner template

Indicator	Expected Actions	Name	Examples	Type
DV	Static	Division	Div 1 - General Conditions	Summary
SD	Add/Delete	SubDivision	Testing, Temp services	Summary
HD	Add/Delete	Header	Millwork Subtrade Scope	Summary
ST	Add/Delete	Staff	PM, Superintendent	Cost
LC,MC,OH	Add/Delete/Modify	Special	Overhead, Labour Premium, Small Tools	Cost (similar to ST)
CL	Add/Delete	CostLine	Millwork, Wood Doors	Cost or Summary
CD	Add/Delete	CostDetail	Counter tops, each wood door type	Cost
CM	Add/Delete	Comment	Comment	Comment
SPC	Add/Delete	Space	Space	Space

I know this table doesn't really make anything clear but it is just used as a reference

Along with these Rows lies a hierarchy as shown in the photo below

Where the Structure is DV -> SD -> HD -> ST or CL -> CD

• A Division is Always at the top followed by a Subdivision followed by a header

• Once a Header is Placed, a ST Line or a CL may be placed

• The CL can stand alone or it can have CD's attached to it

• If the CL stands alone it operates identically to a ST Line

• If it has CD's attached, it operates like a HD Line (i.e holds the sum of the CD's)

• Comments & Spaces can fit anywhere in the structure

Its hard to explain with words how the structure exactly exists but the more examples you see the more comfortable you'll be with it

Colors

To help with the Structure, Colors have been used to show the difference and hierarchal position of each row type

The more dark the Row, the higher in position it is and the lighter the lower in position

The Actual Color Scheme being shown depends on the Theme Selected (i.e the colors won't always be purple)

CL/ST

The Text of each ST and CL will either be black or red in color.

Red Indicates that the Line has an actual value while black means there is none being calculated

The Quantity Cell may also be colored yellow indicating that there is an error in the line

Hidden Columns (or Rows)

Columns

Currently there are 5 hidden Columns that exist in a Div Page

• Bond --> (O Column) contains info on bond of CostLine (can be activated in the Report & Others Ribbon)
• Equipment Unit --> (R Column) *Deactivated* should not be seen or used
• Equipment Total --> (S Column) *Deactivated* should not be seen or used
• SubForm Label --> (U Column) Can place letter for a row to be used in Breakdown Sheet (activated with Enable B.D. Checkbox)
• TagLine --> (V Column) Used as a place to mark tagged lines

The User does have the ability to Hide Current Columns or Force Open hidden ones but this action is not recommended and may corrupt the workbook

Rows

By default no rows should be hidden and all should be visible

There are two checkboxes that can hide and unhide rows

• Comments --> Hides/Unhides all Comments
• Zeros --> Hides/Unhides all Zeros

The User has the ability to Hide Current Rows or Force Open hidden ones but this action is not recommended as it may corrupt the workbook

Editing Area

This Photo is blurry because it is resized

The photo above is an example Div with highlighting on it

Basically the areas highlighted in red are what I would call 'Red-Zones' aka don't touch.

The user should never edit these zones however if they do, there is some error correction but it is not guaranteed

Keith's Training Structure

• Add multiple comments at a time
  • Use the down arrow on the button and click to add 5 comments at a time

• Delete multiple lines at a time
  • Highlight multiple lines and hit the Delete line button

• Clear Button
  • If the user wants to delete all quantities, productivity, labour class etc but keep the line then use the Clear CD button
  • EstToolTab
  • Clear CD

• Show and hide columns

1. Subtrade Description – column to put trade names
2. Button is in the EstToolTab in the “Insert/Convert” section
3. Show Bond – subcontractor bonding button
4. Checkbox is in “Report and Review” tab in the “Working Settings” section
5. Enable B.D. – enable the subcontractor bid breakdown column
6. Make B.D. then click “Enable B.D.” in the “Report and Review” tab under “Breakdown”
7. Note that for breakdowns of values on the summary page, this needs to be done using the “Project Info” button and not the bid breakdown column.

• Copy & Paste

1. Can copy entire subdivision by clicking on the subdivision, hit “Copy Section” button and then click “Paste Section”, no need to highlight all headers, cost lines and cost details.
2. Other option is to highlight all lines that are intended to be copied, hit “Copy Section” and then “Paste Section”
3. Can also copy anything from one Div to another Div.
4. Sometimes I will use the “Copy Div” button for separate, itemized or breakout prices
5. Rename the tab “Div Separate Price”
6. Now the user can use all of the functions of the book for their separate prices instead of creating a scratch sheet

• Copy & Paste out of book

1. Can copy any text or comment out of the book and into another excel or word document
2. Change formatting when pasting data to text if the user only wants to keep the text but not the formatting from the book
3. This works well when creating recap sheet or bid form in building connected

• Extra Summary Data – total project cost per unit

1. If the user wants the total project cost per unit shown on the summary page such as cost per suite or cost per rental square foot
2. Use the “Extra Summary Data” in the “Report and Review” tab
3. Fill out the description, unit and quantiy

• Extra Summary CM

1. If the user wants additional information shown on the summary page such as area per floor or anything else
2. Use “Extra Summary CM” button in the “Report and Review” tab
3. Type in any information you want
4. The column letters correspond to the columns in excel

• Breakdown tool

1. Be aware that subtotals can be added to the breakdown
2. Click “Insert Sub Total” button in the “Report and Review” tab under Breakdown
3. Siyuan has it on his to do list to make it possible to select lines that do not disperse the misc money. Line such as cash allowances for example.

• Highlight Div tabs

1. By using the “Approve WS” and “Unapprove WS” and “No Color” the user can change the Div tab from gray to yellow to green. Can be useful while working through a book.

• Changing labour class or unit of measure on multiple lines

1. Highlight multiple lines by dragging the mouse or select multiple lines with “Ctrl + left mouse click”
2. Go to the “EstToolTAb”
3. Use the drop down menu for “Class” or “Unit”
4. Select the labour class or unit
5. All lines that were highlighted now have the same labour class or unit of measure

• Recovery and Full Time Equivalents

1. Can check recovery or manpower on any bid
2. Go to “EstToolTab”
3. Click “Calc Rec”
4. Can add user items such as Super Excess or other recoverable items by going to the “Report and Review” tab
5. Click “Add User Item”
6. Fill in the description and amount

• Compare Budgets

1. This tool can be used to compare 2 different budgets
2. This could be CP1 vs CP2 or wood frame vs LBS
3. Or could compare 2 different but similar projects and focus on $/SF or $/suite or some other metric
4. To do this create a budget comparison and then simply add columns, change them and add formulas. Use the copy and paste formula to keep formatting easy

• Variables

1. User defined quantity variable
2. Go to “EstToolTab”
3. Click the drop down menu of “Variables”
4. Select “Quantity Var”
5. Fill in Variable Name, Value, Unit of Measure and Notes
6. This might be “BLDGAREA”, 120153, SF, Above Grade Area
7. In any cell that requires a quantity, the user can type in the variable name such as “BLDGAREA” and the quantity associated with this variable will be entered into the quantity cell similar to the DUR code.
8. If the variable quantity needs to be changed, simply change it by going to the variable button and change it. Now all quantities using that variable automatically update

• Material unit cost

1. Similar to the Quantity Variable except this variable carries the material cost instead of the quantity.
2. Can be used for any material such as ½” Plywood. Now if the price of plywood changes and the user has 10 lines, only the variable needs to be changed once and all plywood will update
3. Click “Mat Unit Cost” under the Variables drop down in the EstToolTab
4. Fill in Variable, Value, Unit of Measure and Notes
5. ½” PLY, 2.50, SF, April 19, 2023
6. Now in the material unit rate cell in the estimate book, type in the variable name. In this case “1/2” PLY

• Overhead Line - Cost line that uses % total (will replace labour premium and tool allowance)

1. If the user wants to calculate cost based on a percent of total project labour, material, subtrade or total cost this tool can be used
2. Go to EstToolTab
3. Click “Add OH Line”
4. A custom line will be added
5. User can adjust the description but be sure to keep the brackets and the formatting inside the brackets correct)
6. Adjust the %
7. T for Total Project
8. L for Labour
9. M for Material
10. S for Subtrade

Closing Notes

See Also

After reading or skimming through this tutorial, you might want to Check out:

• EstToolTab --> The Main Ribbon, Used for basically Everything
• Est To VP --> Contains Consolidation Page Features and Export to Viewpoint buttons
• Report and Others --> Contains Features and buttons that didn't fit on the EstToolTab Ribbon

However the best way to learn the program is to just use it!