Client Profile.
The client is a bespoke furniture manufacturing company based in California, USA, specializing in store fixture and display furniture. Their client base includes hundreds of retail stores across the world.
Business Need.
The company offers a range of products made of different types of wood such as MDF, particle board, laminates etc. as well as metal millwork furniture. With a high product-mix and no single utility application:
- It was increasingly becoming difficult to manage orders and generate custom quotations using manual log book systems using Excel spreadsheets to generate sales orders
- Lengthy and tedious documentation processes
- Difficulties in choosing the appropriate wood type from 29 standard types offered
Thus, they were looking to reinvent their order management processes by automating quote generation. This would help them increasing productivity and adhere to the industry recognized quality standards.
Challenges.
An assessment of as-is process highlighted that the client used 31 different Excel spreadsheets for every customized order to manually enter and save specifications and calculate the total price. This threw up the following challenges for the project team:
- Lack of visibility in the value chain limiting decision making capabilities with siloed data saved in different spreadsheets
- Slight change in formulae-based and interlinked spreadsheets lead to alterations in complete pricing calculations
- Zero control over formula locking on Excel sheets and could be altered by anyone accessing the sheet at client’s end
Solution.
We designed MS Access database using VB scripts to manage order documentation and processing and automate BoM calculation. A single intuitive interface offered suggestive inputs and streamlined internal flow requests between sales, design, manufacturing, installation and customer service divisions. It offered:
- Automated BoM generation and manufacturing documents for custom fixture specifications
- Generation of flags for engineering team to prepare designs per order
- Order visibility to track and trace the jobs in progress
- Assignment of rights to vary/update costs in the database only to admin panel for better controls
Approach.
Understanding and analyzing the ‘as-is’ process to define the scope of automation
- The automation team understood the as-is Excel sheet-based, manually driven processes where the pre-sales team either visits the customer site or interacts with the customer to get product specifications and populate spreadsheets enabled with formulae and rules.
- The project team received details such as:
- Type of primary building material for wooden millwork products which included MDF, particle board, laminates and 29 others with each having 5-6 sub-types.
- Existing Excel spreadsheets consisting of various logs and database related details.
- The analysis of the process highlighted a good opportunity to automate error-free population of specification sheets.
Developing automation interface using VB Scripts and MS Access Database
- Based on the inputs, KPIs to design the sales documentation process for automation were defined.
- The automation and digital transformation team generated the codes using VB scripts to design an intuitive and guided desktop-based interface having following characteristics:
- Drop down list at every stage to eliminate the need for a technical expert on the sales team for selecting material type and its specification.
- Control for admin panel at the client’s end to update costs by maintaining a specific price ratio for manufactured and bought-out items.
- Suggestive specifications for number units needed such as wooden sheets, accessories, and laminates, etc., unit price, customer address, name, etc. for accuracy.
Two level quality checks to ensure zero errors
- A two-level quality check was defined to ensure zero error during the adoption of automation: One done by the senior automation specialist on project team and another performed by the client.
- QC process included creation of a separate Excel sheet other than the inputs received and having add-on functionalities to be offered in the automation interface.
- All results were verified in the tool against the newly developed Excel sheet to ensure accuracy.
- Likewise, the client also developed Excel sheets with further added functionalities and verified the accuracy of the interface.
Deployment and integration with the client’s business system
- Upon strict QC and approval from the client, MS Access database along with the VB Scripts for customization was dispatched to the client.
- Deployment of the tool in their order management systems ensured end-to-end quotation and record maintenance as well as production visibility.
- Intuitive dashboard generated detailed visualization reports for pre-defined KPIs and offered greater clarity in order management.
Business Impact.
Reduced TAT for quotation by 80%
Better control over price margin with changing costs of raw material
Enhanced process efficiency with zero human intervention
Decreased material wastage and operational risks
