Why this course?

Description

Module 1: Introduction to Excel for Quantity Surveying

1.1 Basics of Excel Interface and Functions

• Overview of Excel layout: Ribbon, Worksheets, Cells

• Entering and formatting data for QS calculations

• Basic functions relevant to QS (SUM, AVERAGE, COUNT)

1.2 Using Excel Formulas and Cell Referencing

• Relative, absolute, and mixed cell references

• Formula auditing and troubleshooting

• Example: Simple quantity addition using SUM with relative and absolute references

1.3 Data Validation and Error Checking

• Using data validation for input control (e.g., numeric only for quantities)

• Handling errors (IFERROR, ISNUMBER) in QS calculations

• Scenario: Preventing input errors in measurement entries

1.4 Creating and Managing Worksheets for QS Projects

• Organizing multiple sheets for different work sections (e.g., Excavation, Concrete, Finishes)

• Linking sheets to consolidate project data

• Template example: Master worksheet linking sub-work worksheets for overall quantity summary

---

Module 2: Quantification and Measurement Calculations Using Excel

2.1 Calculating Areas and Volumes from Dimensions

• Using formulas to calculate areas (length × width) and volumes (length × width × height)

• Real scenario: Calculating earthwork excavation volume from given site measurements

2.2 Complex Measurement Formulas for Irregular Shapes

• Using SUMPRODUCT and array formulas for multiple dimension inputs

• Example: Quantifying compound walls or irregular slabs

2.3 Unit Conversion Techniques in Excel

• Automating unit conversions (e.g., meters to millimeters, cubic meters to liters)

• Scenario: Converting length measurements for steel reinforcement calculation

2.4 Creating Measurement Summary Tables with Dynamic Totals

• Using tables and structured references for easier data manipulation

• Example: Quantity summary by work item, auto-updating as data changes

---

Module 3: Cost Estimation Using Excel Calculations

3.1 Linking Quantities to Unit Rates for Costing

• Setting up unit rate sheets and linking with quantity sheets

• Formula example: Total cost = Quantity × Unit Rate

3.2 Using Lookup Functions for Rate Retrieval

• Applying VLOOKUP and INDEX-MATCH to fetch unit rates based on work codes

• Scenario: Fetching current rates from a rate database sheet

3.3 Calculating Material, Labor, and Equipment Costs Separately

• Structuring sheets to differentiate various cost heads

• Example: Separate costing for materials and labor with subtotal and grand total

3.4 Applying Percentage Markups and Taxes Automatically

• Using formulas to apply overheads, profit margin, GST, and other taxes

• Template: Final cost estimation with breakdown of markups and taxes

---

Module 4: Quantity Surveying Data Analysis and Reporting

4.1 Using Pivot Tables for Quantity and Cost Summarization

• Creating pivot tables to summarize quantities by categories and cost heads

• Example: Pivot report showing quantities by trade and total cost

4.2 Conditional Formatting for Visual Quantity Highlights

• Using conditional formatting to flag quantities exceeding thresholds

• Scenario: Highlighting over-quantities or cost overruns

4.3 Creating Dynamic Dashboards for QS Reports

• Introduction to charts and slicers to create interactive QS dashboards

• Template: Dashboard showing real-time quantities and budget status

4.4 Exporting and Printing QS Reports Professionally

• Formatting worksheets and reports for professional presentation

• Example: Creating printable BOQ sheets and cost summaries

---

Module 5: Advanced Excel Formulas for Quantity Surveying

5.1 Nested IFs and Logical Formulas in Quantity Validation

• Using nested IF, AND, OR for complex logical conditions

• Scenario: Validating quantities based on multiple criteria

5.2 Using SUMIFS and COUNTIFS for Conditional Calculations

• Calculating quantities and costs based on multiple conditions

• Example: Summing quantities for a specific material and location

5.3 Array Formulas and Dynamic Ranges

• Introduction to array formulas for advanced calculation needs

• Example: Calculating weighted average rates dynamically

5.4 Text Functions for Data Cleanup and Processing

• Using LEFT, RIGHT, MID, TRIM, CONCATENATE for preparing measurement data

• Scenario: Cleaning imported measurement text data for calculations

---

Module 6: Templates and Automation in QS Excel Workbooks

6.1 Designing Reusable QS Calculation Templates

• Template structure for different types of QS projects

• Example: Template for residential building quantities

6.2 Using Named Ranges for Easy Formula Management

• Defining and using named ranges for clarity and reusability

• Scenario: Named ranges for unit rate tables and quantity lists

6.3 Creating Drop-Down Lists for Standardized Inputs

• Data validation lists for materials, units, and work types

• Template: Drop-downs to standardize measurement input

6.4 Introduction to Simple Macros for Repetitive Tasks

• Recording macros to automate formatting and data entry steps

• Scenario: Macro to clear input cells and reset calculation sheets

---

Module 7: Real Case Scenario - Earthworks Quantity Calculation

7.1 Setting up the Project Worksheet for Earthworks

• Inputting site dimensions and excavation parameters

• Template example for earthworks measurement input

7.2 Calculating Cut and Fill Volumes Using Excel Formulas

• Using volume formulas based on cross-section dimensions

• Real scenario: Calculating cut and fill balance for a road project

7.3 Costing Earthworks with Rates and Overheads

• Linking quantities to unit costs and applying overhead percentages

• Example: Earthworks costing summary with contingencies

7.4 Reporting and Visualizing Earthworks Quantities

• Generating charts and summary tables for site reporting

• Scenario: Visual representation of earthwork quantities over phases

---

Module 8: Real Case Scenario - Concrete Works Quantity and Cost Estimation

8.1 Inputting Structural Element Dimensions

• Capturing beam, slab, column, and footing sizes

• Template for concrete element measurement entry

8.2 Calculating Concrete Volume and Steel Reinforcement Quantities

• Formula for concrete volume and rebar weight calculation

• Real scenario: Concrete quantity estimation for a multi-storey building

8.3 Linking Quantities to Material and Labor Rates

• Costing concrete and reinforcement separately

• Example: Cost breakdown report for concrete works

8.4 Quality Checks and Cross-Verification Techniques

• Cross-checking volume calculations with design drawings

• Scenario: Error detection using Excel formulas

---

Module 9: Real Case Scenario - Finishes and Fixtures Quantity Surveying

9.1 Quantifying Wall Finishes: Plastering, Painting, Tiling

• Area measurement and quantity calculation for finishes

• Template example for finish quantities entry

9.2 Calculating Fixtures and Fittings Quantities

• Counting and costing doors, windows, sanitary fittings

• Scenario: Quantity sheet for bathroom fixtures

9.3 Applying Rate Analysis for Finishing Items

• Using lookup tables for rates and linking to quantities

• Example: Costing report for finishing works

9.4 Preparing BOQ Section for Finishes and Fixtures

• Consolidating quantities and costs for tender documents

• Template: BOQ section format in Excel

---

Module 10: Practical Templates and Final Project Workbook

10.1 Compiling All Work Sections into a Master QS Workbook

• Linking earthworks, concrete, finishes, and other sheets

• Template: Master workbook with navigation and summary sheets

10.2 Creating a Project Summary and Cost Control Sheet

• Overview sheet with totals, budget comparisons, and variances

• Example: Project cost tracking dashboard

10.3 Final Quality Assurance and Validation Checks

• Using Excel formulas and conditional formatting for final validation

• Scenario: Ensuring all data entries meet QS standards

10.4 Exporting, Sharing, and Updating QS Workbooks

• Best practices for version control and data sharing

• Tips on maintaining and updating project QS Excel files