Enhance Excel-Based Construction Estimating Tool
Budget: $30 – $250 CAD
Introduction
We are a construction company that uses an Excel spreadsheet to produce estimates for projects. Below are the steps that we want users to follow. We’ve highlighted yellow areas that require your services.
Current estimating Excel spreadsheet
User starts working in the Division Items tab where our company’s cost codes are listed in chronological order. Every code has specific hyperlinks to bring you to the Division Breakdown tab. This is where values are entered with detail. The Division Summary tab summarizes our estimate in divisions. This tab helps us understand the costs of the project on a higher level and it also shows us the cost per square foot. The Labour Rate tab is linked to the Division Breakdown tab to help us easily change our labour rate across the spreadsheet if needed. Similar principle with the Cost Code tab. It allows us to make updates efficiently across the spreadsheet. Lastly, Sheet 1 tab collects all the data from the Division Breakdown tab.
Current estimating process
The Division Items tab is “the home page” for users. They select cost codes needed to complete the project and fill the values required Division Breakdown tab. Once completed, users review their estimate in the Division Summary tab to make sure that the cost per square foot is realistic. This high-level review might lead to the user going back to the Division Items tab and making revisions. Every tab has areas to leave notes. We encourage users to leave notes every time they make revisions or when they want to clarify/share important information to coworkers during a team review. Once the Lead Estimator or General Manager approves the total cost in this spreadsheet, it gets uploaded to *Sage Construction management’s cloud-based software. *Sage requires specific requirements for user to upload Excel files online (i.e. data must be in specific rows, tab must be names Sheet 1, etc.) Refer to the link below for all rules to follow for the Sheet 1 tab.
https://help.sagecm.intacct.com/Content/Modules/Import/ImportEstimate.htm
Our goals
1. We want to incorporate a functional *Work Breakdown Schedule into this spreadsheet. Having a WBS tab for every location in a project (i.e., kitchen, bedroom, etc.) will give us confidence to submit fixed cost estimates because users will be asked to identify every step required to complete a project. This said, we need a WBS cover page (WBS All Location tab) that sums up the data of each WBS location tab. Adding hyperlinks where possible to let users navigate this spreadsheet efficiently because it will have multiple tabs.
2. Sheet 1 tab successfully uploads this information onto Sage:
• Cost code numbers
• Cost code description
• Cost code division numbers
• Cost code division names
• Lump sum (LS) values these categories for every cost code:
o Material cost
o Labour cost
o Equipment cost
o Subcontract cost
o Other cost
Sheet 1, needs to automatically be linked to other data from this estimate. Below are things that we are not successful in uploading:
• Only lump sums of labour hours are captured on this tab. Because the amount of hours and rate are linked, when we upload the estimate in Sage, only lump sums are shown (i.e. Project Management does not show total number of hours and hourly rate in Sage. It’s only seen in the WBS or Division Breakdown tabs).
o Is it possible to have the quantity of hours and rates used in the spreadsheet linked to Sheet 1?
o Now that we’re using WBS tabs with locations, can you also start populating column AH in Sheet 1?
o Lastly, can we do the same with column AI for comments?
3. Can the spreadsheet be saved in .xls format and keep its functions after you complete all the improvements?
Conclusion
We broke down the steps required by our users to complete each TAB of this spreadsheet. Items highlighted need your attention. We’re open to your feedback and suggestions.
We are a construction company that uses an Excel spreadsheet to produce estimates for projects. Below are the steps that we want users to follow. We’ve highlighted yellow areas that require your services.
Current estimating Excel spreadsheet
User starts working in the Division Items tab where our company’s cost codes are listed in chronological order. Every code has specific hyperlinks to bring you to the Division Breakdown tab. This is where values are entered with detail. The Division Summary tab summarizes our estimate in divisions. This tab helps us understand the costs of the project on a higher level and it also shows us the cost per square foot. The Labour Rate tab is linked to the Division Breakdown tab to help us easily change our labour rate across the spreadsheet if needed. Similar principle with the Cost Code tab. It allows us to make updates efficiently across the spreadsheet. Lastly, Sheet 1 tab collects all the data from the Division Breakdown tab.
Current estimating process
The Division Items tab is “the home page” for users. They select cost codes needed to complete the project and fill the values required Division Breakdown tab. Once completed, users review their estimate in the Division Summary tab to make sure that the cost per square foot is realistic. This high-level review might lead to the user going back to the Division Items tab and making revisions. Every tab has areas to leave notes. We encourage users to leave notes every time they make revisions or when they want to clarify/share important information to coworkers during a team review. Once the Lead Estimator or General Manager approves the total cost in this spreadsheet, it gets uploaded to *Sage Construction management’s cloud-based software. *Sage requires specific requirements for user to upload Excel files online (i.e. data must be in specific rows, tab must be names Sheet 1, etc.) Refer to the link below for all rules to follow for the Sheet 1 tab.
https://help.sagecm.intacct.com/Content/Modules/Import/ImportEstimate.htm
Our goals
1. We want to incorporate a functional *Work Breakdown Schedule into this spreadsheet. Having a WBS tab for every location in a project (i.e., kitchen, bedroom, etc.) will give us confidence to submit fixed cost estimates because users will be asked to identify every step required to complete a project. This said, we need a WBS cover page (WBS All Location tab) that sums up the data of each WBS location tab. Adding hyperlinks where possible to let users navigate this spreadsheet efficiently because it will have multiple tabs.
2. Sheet 1 tab successfully uploads this information onto Sage:
• Cost code numbers
• Cost code description
• Cost code division numbers
• Cost code division names
• Lump sum (LS) values these categories for every cost code:
o Material cost
o Labour cost
o Equipment cost
o Subcontract cost
o Other cost
Sheet 1, needs to automatically be linked to other data from this estimate. Below are things that we are not successful in uploading:
• Only lump sums of labour hours are captured on this tab. Because the amount of hours and rate are linked, when we upload the estimate in Sage, only lump sums are shown (i.e. Project Management does not show total number of hours and hourly rate in Sage. It’s only seen in the WBS or Division Breakdown tabs).
o Is it possible to have the quantity of hours and rates used in the spreadsheet linked to Sheet 1?
o Now that we’re using WBS tabs with locations, can you also start populating column AH in Sheet 1?
o Lastly, can we do the same with column AI for comments?
3. Can the spreadsheet be saved in .xls format and keep its functions after you complete all the improvements?
Conclusion
We broke down the steps required by our users to complete each TAB of this spreadsheet. Items highlighted need your attention. We’re open to your feedback and suggestions.