Book Your FREE Initial Consultation Now!
or Call (06) 8760 312
A Case Study:
Structural Engineering Quoting Template
In 2009 ProcessIT was engaged to assist with the Development and Automation of a Quotation Spreadsheet in Microsoft Excel. The Customer was Patton Engineering, a leading Steel Fabrication firm based in Whakatu, Hawke’s Bay.
The Client utilised a Senior Engineer to do a manual Quantity Survey of Time and Labour required for large steel fabrication Projects. Large fabrication Projects could take up to a month of dedicated time. ProcessIT worked closely with Management and Engineers to develop and provide an Automated Excel Solution enabling accuracy improvements, consistency and massive time saving for the Senior Engineer and vastly improved service to Patton Engineering’s end Customers.
The Client had previously procured and operated a very expensive USA designed industry specific application, that while providing some useful functions, was not able to provide Quotes suitable for the Client nor the local market.
A Senior Engineer was required to analyse the Draft Drawing of the proposed structure, to do a full Quantity Survey of all components and manually estimate the time required to produce each component, including Preparation, Cutting, Drilling, Welding and Assembly. This was performed substantially by Experience and “Gut Feel.” Large construction Projects could require the Senior Engineer to spend up to a month on this task. Then the calculations were tallied, margin added and the Customer Quotation prepared.
ProcessIT was selected as the Software Developer to resolve this problem. ProcessIT had the skill set required (Excel Automation and some understanding of engineering) and being locally based was easily able to sit with Engineers to discuss requirements and recommend the direction of possible solutions. ProcessIT was engaged to assist Pattons’ with the Automation of this Quotation Project.
Another Senior Engineer had produced an Excel “database” formulated on calculations based on Materials and the Time it took to process these for various tasks. It was known that the CAD systems Drawing File already contained almost all details of each Component for each Assembly. Senior Management and Engineers briefed and discussed requirements with ProcessITs’ Software Developer. Being Engineers, they specified that the solution must be Excel based for familiarity.
The Initial Project
The Drawing File was used as the starting point, with the drawing data being exported from the CAD Application in a raw text based format. ProcessIT wrote Code Routines to import the raw drawing data into Excel. Once imported each Assembly and its Components were analysed in detail, via coded logic routines to determine the size of each component, the material, cutting angle, number of holes and welds required etc. Then these items were referenced against the “database” to insert and display the related times for each task needed. The process used was a Plan, Develop, Test and Review loop. With ongoing improvement ideas and corrections from the Senior Engineers review of the results and calculations feeding back into the start of another loop as the project progressed.
Following the file Import and Processing, the resulting Excel Workbook was then quickly reviewed by a Senior Engineer to validate and confirm the calculations, with minor manual adjustments applied to particularly complex Operations or Tasks if needed. The resulting Completed Quotation, with adjustable margin, was ready to go to the Customer that Day!
As this Solution became accepted and accuracy confirmed, additional operational tasks were able to be incorporated into the Excel Workbook to allow for previously ignored items such as Staff Tea Breaks, internal handling and freight consolidation etc.
The Senior Engineer no longer has to do the arduous Quantity Survey, freeing him to do actual Engineering! Thus, a saving of a full time Senior Engineers annual salary is realised! Additional to this is the Income from the now Chargeable time, with Margin. The Clients Customer benefited from a more accurate Quotation and received it weeks earlier than they would have using the Previously Manual process.
Ongoing refinement of Patton Engineering’s company processes resulted in the need to for various additional calculations to more accurately reflect the costs incurred. As this was a bespoke solution, these requirements were able to be added as needed by ProcessIT’s Developers.
Sharpening the Pencil
Management determined that the Quoted (Budgeted) time needed to be confirmed. A Time Capture Application was commissioned. This would allow for direct “Quoted versus Actual Time” comparison to verify or correct the old Excel Time vs Material “database”. The New Time System was developed by ProcessIT using the RAD tool (Rapid Application Development) of Microsoft Access. This enabled a sophisticated development in a cost-effective manner with full multi user availability.
Time was captured per Employee, Job Number, Operation/Task and Assembly. An interface was added to enable the results of the original Excel Quote to be exported to the new Database Solution, so direct comparisons could be made. The opportunity was also taken to move the Time vs Material “database” from Excel to an actual Database with the added advantages of concurrent multiusers and much faster searching by taking advantage of Access’s internal indexes.
Using the resulting application, it could be shown that some tasks were consistently performed faster than quoted, so the Time vs Material Database could be adjusted to reduce these time allowances and so provide a more competitive and accurate Quotation. Additionally, it is now possible to identify if an individual Employee was underperforming consistently on a Task and so needed further training or closer management.
Seeking ISO approvals, Patton Engineering embarked on additional Process Monitoring, Data Capture and Quality Compliance. Over time, many additions were made to this Custom Database Application by ProcessIT to enable these new requirements.
As more of the Companies processes were being managed by this system and more business reliance was placed on it, ProcessIT transitioned the host Database to the more robust Microsoft SQL Server – an enterprise scale database engine, offering further performance enhancements.
Patton Engineering’s business continues to rely on this automated application, now known as PETS -and acronym for Patton Engineering Time System.
Fully automating the Quantity Survey and Quoting process has resulted in substantial savings for ProcessIT’s Client, Patton Engineering, while improving their performance and accuracy in a competitive market. Their end Customer now receives a more accurate Quote in a vastly improved timeframe. Additionally, more Data is now available for Management to use in forecasting and business analysis.
Contact us now to discuss your requirements and arrange your Free initial consultation...