Production Planner Built in Excel

Improving Efficiency in Production Planning 

High-end candle and fragrance business, Parks Candles, came to Xlteq with a problem involving their production planning and supply needs. Starting life as a single shop over 30 years ago, they expanded over the following decades until today where they are now producing over 32,000 candles a day to meet demand. Their current production process was paper based, and they required a simple solution to bring the process up-to-date. They also needed management reports to be data driven which would drive the production plan and help maintain stock levels.

Xlteq conducted an in-depth review of the customer needs, and offered various solutions using Excel and Access. Whilst the client expressed the ultimate desire for an Access database solution, they requested an intermediary phase to be built using an Excel workbook as this was a more familiar tool for their staff to use. Although producing this solution in Excel created several unique challenges and was further complicated by one employee using an Apple Mac, Xlteq was able to fully develop a production planning tool which met all the customer requirements, and ultimately provided enhanced functionality over and above the initial specification.

Order Recording

Use of the Excel workbook centers on the recording of customer orders on a “master sheet”. This sheet not only stores the individual customer orders, but also provides a straightforward production capacity indicator. This displays an early warning where production is running over or under capacity, per day, per production line, taking into account individual line capacity as defined by the client.

As accuracy is paramount on this sheet, several custom-built tools were created for the user to assist them in dealing with the orders. The standard right-click menu functionality found in Excel was utilised and enhanced to provide the user with a familiar and easy to use method to access the required functionality. The method used to do this also needed to be compatible with an Excel on an Apple Mac as the standard VBA code is not compatible between the two platforms.

An example of the functionality provided above was to allow the user to search through their list of almost 900 SKUs and select the ones they required to be entered into the planner. Entering a search term displays all SKUs where any of the fields were found to match (e.g. SKU, Container description, Fragrance name, etc). Results are immediately returned using a pop-up form using efficient Excel coding techniques.

Reports

Three reports are used to aid the production planning. Each report is automatically created on a separate Excel tab whenever data in the main planner is updated without the need for the user to request it to be run. Over 600 orders are analysed, and the results calculated, formatted, and displayed in a fraction of a second.

The first report is a simple Production plan, providing an easy to read summary of the company orders.

A Material Requirements report allows simple stock control functionality. Information changed here is feed back into the Master Planning Sheet, ensuring data accuracy is maintained.

The third report was a complex Production Capacity Map requiring over 400 lines of VBA code. This allowed the user to display several weeks’ worth of production (based on a customisable defined range), split by day and production line. Furthermore, it calculated the required number of hours needed per day, manpower estimates, and highlighted productions which were over/under capacity along with any associated manpower capacity issues.

Material Requirements

Another part of the tool was to build a screen that displays the orders and stock parts needed to fulfil those order. This required the report to be definable by a date range, and to be based on a specific stock level (Full, Top level SKU or Sub Level SKU). It also has the functionality to combine the same SKU’s from different products (final packaged goods) into one row or show them separately. 

Finally, an extremely complicated requirement was the need to take the overwritten order quantity from the stock control section (see below) and use this in the report. In all, this report required almost 800 lines of VBA code to be written.

Stock Control

A critical requirement for the tool was the ability to be able create a “shopping list” of items required to be ordered so that stock levels could be maintained. This required complex Excel VBA code to deal with the following requirements:

  • The recommended order quantity had to take into account current stock levels, stock on order, and the current customer orders.
  • Orders are made up of individual items (e.g. boxes, fragrances, wicks, wax etc.) and some of these items are themselves made up of individual components (e.g. fragrance oils, wax formulas, etc). The Stock Control system had to detect all these hierarchical levels of stock items and calculate the stock order quantity at the lowest level.
  • The user requested the ability to override the recommended order quantity of stock at all stock hierarchy levels. A change at the highest level would have to be recalculated for the multiple items at the lowest level.
  • Where different orders required the same stock items, these needed to be combined to only display one stock order quantity for that specific item.
  • An external client database of individual parts was imported into the Excel tool to provide the details for the component parts for each order. The fields returned from this database were not stored in a standard way and some data manipulation was required.
  • Extraneous data was also provided from the external client database which had to be detected and removed.
  • A few products were part of a “box set”. Within each box set, each candle is recorded as its own order, however the combined set of candles will share some common items (e.g. box, box stickers, etc). The stock order VBA code had to detect these special types of orders as there was no specific flag in the external database to highlight them. The VBA code then had to ensure that any “box set” items were only ordered for the first instance of the candle orders in the box rather than for each candle.
  • Order quantities were required to be rounded up under specific conditions.

Outcome

Xlteq delivered to Parks Candles a highly sophisticated production planning, ordering, and stock control tool that was built on time and within budget. Subsequently, additional requests for further enhancements and reporting functionality have been added to the tool.


Contact us for a free consultation

Either call us now on 020 3817 6945 or fill out the form below and one of our team will be in touch.

The contact form requires that you configure reCAPTCHA in the site configuration.

The contact form requires that you select an email template.