What is the best way to automate quote preparation?
Efficiency question :
The level of detail required to prepare a quote varies considerably depending on the industry and the nature of the products or services offered. A construction company, for example, will not break down its costs and work in the same way as a service company.
An effective approach begins by isolating the elements used to calculate the quote in a calculation constants section. This may include raw materials, costs, labour time and the different parameters required to complete the work. The product or service can then be broken down into several elements that the organization can easily update when prices, required time or other parameters change.
A dynamic input form can then allow the representative or estimator to collect the information required from the customer. Questions can change based on previous answers in order to progressively define the work to be completed. The information collected is then compared with the calculation constants to automatically determine the cost of the work.
Several documents can be produced from the same calculation: the quote or proposal for the customer, the work order for the team completing the job, or a document used to order the required raw materials.
Key considerations:
The first issue is determining the level of detail that is truly required for the calculation. The parameters must be precise enough to produce a reliable quote while remaining easy to maintain when prices, costs or work methods change.
It is also necessary to determine who can view or modify the calculation constants. Some of this information may represent know-how or data that the organization does not want to make available to every representative.
Language must also be considered when quotes need to be produced in French or English. Forms and reports can exist in both languages while using the same calculation constants.
Challenges:
The quality of the automation depends largely on how the product or service is broken down. The raw materials, steps, labour time and parameters that truly influence the price must be identified so that the calculation can adapt to different situations.
The input form must also guide the user without forcing them to answer unnecessary questions. A dynamic form can display or refine certain questions based on the answers already provided.
Finally, the different documents to be produced must be planned, as well as which ones need protection against modification. A PDF output can be used to transmit a quote or work order in a format that is not intended to be modified.
Technology:
Two approaches can be used in particular.
A first solution is to develop an Excel utility, with VBA to automate its operation and, when necessary, Power Query to retrieve data from other sources. The calculation constants, input form, calculations and various reports can then be grouped in a single tool. Some calculation sheets can be hidden so the user works only with the parts of the tool they need.
A second approach is to use Power Apps to create input forms that are accessible from a computer or tablet. Parameters, raw materials and calculation constants can then be managed centrally for all representatives.
The data can be stored in Dataverse, allowing different representatives to work with the same database. The information becomes available to the organization as soon as it is entered, without waiting for the representative to return to the office.
Developer's advice:
Before developing the tool, determine which of the two main families your quote process belongs to.
The first is the model-type quote: one form is used to prepare the quote for one customer or one project at a time. This approach may be suitable when the primary objective is to automate the calculation and document production.
The second is based on a centralized database in which customers, projects and quotes are stored in the same environment. This approach not only supports quote preparation, but also makes it possible to use historical data to produce performance statistics for representatives and for the organization.
This choice should be made from the outset because it directly affects how the tool will be structured and how the data can be used later.