Another day, another 100,000 decisions

The client

The client is a 100 year old Australian business now headquartered overseas. It manufacturers a wide range of products for the construction industry.

Operations are international with production facilities in Australia, New Zealand, SE Asia and the US. Revenue is in excess of A$ 1.6 billion per year. In one product range alone – fibre cement sheets – the company has over 250 different varieties.

Objectives

  • Management wanted a costing and production planning model that would help optimise key strategic, pricing and production decisions.
  • This would be used to make decisions such as: What should be manufactured where in order to minimise overall production costs? What is a profitable sale price for a product in any given market at any given time?

“The company faced a number of new problems in the market. Determining the best way to resolve these problems was never going to be easy. Especially in a business producing hundreds of different products in a manufacturing environment with around 100,000 variables.”

Long term decisions

In order for the company to continue growing its share of domestic and export markets, it needs highly competitive products and pricing.

The longer term strategy is to lower costs by rationalising production around the world. This involves assessing:

  • Whether plants and production lines should be upsized, modified, downsized, or product mix moved
  • How many days per week to operate and what shift structures to use.

Medium and short term decisions

At a tactical level, day to day issues require ongoing decision making:

  • Which products should be manufactured and where; in which countries, at which plants and on what lines?
  • How to price products including discount structures while ensuring profitability.
  • How to respond to short term demand, or one-off sales opportunities, in different markets; where to supply from and how to schedule the increased production.
  • Whether to add complete shifts or use staff overtime. In making these decisions the company has to be able to optimise unit production costs and transportation costs and determine a profitable price for each product at each plant.

Can’t do this the old way

To date, these decisions were being supported with spreadsheet costing and production models. These were only partially successful due to traditional problems with spreadsheets and problems specific to the requirements of the company.

The common problem with spreadsheets is that they quickly become very large and unmanageable. They are difficult to understand and usually can’t be maintained by anyone other than the person who created them.

They tend to be easily corruptible and unreliable because of the amount of manual data, hidden formulae and macros and the hidden assumptions involved. And they are rarely integrated within the organisation; there are usually disparate spreadsheets owned by different people using different versions of information.

Like most organisations, the spreadsheet models used by our client had other problems specific to the business.

They were not set up to describe the business as it existed in reality. This was primarily due to the difficulty in dealing with complexities such as costs. For example, all costs defined in the spreadsheets are variable, whereas in reality certain production costs – such as shift labour costs – are fixed.

The spreadsheets also suffered from a lack of flexibility. The data structures were fixed, making it a huge task to add a plant, production line, product line. Markets were not considered in the spreadsheets, planners had to intuitively decide which plant to run the numbers on.

Spreadsheets are also unsuitable for determining optimum production runs. The user is required to identify and set up each production option to trial, and the spreadsheet calculates the result. By comparison, an optimisation model considers all the relevant options to find the best possible combination.

Real results

GPR Dehler developed a new Decision Support System (DSS) capable of integrating all the production and sales information.

This new system consists of two key components – a model and an optimiser.

The modelling component is based around the typical manufacturing process used by the company. A database contains details of the equipment, characteristics and constraints at each of the company’s plants.

The company’s staff can set up the production or sales scenario they require. This includes selecting plants, processes, products, shifts, market regions, distribution channels and any relevant pricing and rebate structures.

The optimiser crunches through the options – which products will be made at each plant on each line and for each market region. Within minutes it determines an optimum production configuration – based on minimising cost or maximising EBIT. The results, such as an income and expenditure statement and detailed results for budgeting and pricing decisions, are posted to an Excel spreadsheet.

Too big a problem

One of the main obstacles with developing an optimisation system of this nature is the sheer magnitude of options. In this client’s case there are around 100,000 variables within around 50,000 constraints. The number of possible solutions is so large that it would take a powerful computer years to find the best option.

This problem was overcome by installing intelligent optimisation software. This eliminates the majority of impossible, inferior and irrelevant options – substantially reducing the number crunching required of the computer.

How it is used three examples

To date the optimisation modelling tool has been used to test a range of production scenarios. The results are being used to generate production plans and budgets. It is also proving highly effective at assessing individual sales opportunities and pricing decisions.

1. There was a diminishing demand for one of the core products. It was estimated that the demand would drop by 5% during the current financial year, but that this could be avoided if the selling price was reduced by 2%. Management needed to determine the better option – to accept the drop in volume and maintain price, or drop the price and maintain volume? The model showed that the latter was better by $300,000.

2. An opportunity to export 200,000 square-metres of a product was considered. Management needed to know if the proposed price was acceptable and what the impact would be on current production. The model showed that the deal was a good one and that the added production could be accommodated without loss of any existing production.

3. Two plants each have similar manufacturing lines to produce similar products. Could the company close one of the four lines, to save on fixed costs, and still meet the production plan? The model showed that it could, but that there would be no spare capacity to take advantage of growth opportunities.

Easy on paper

This project, like many of our engagements, looks straightforward on paper. The reality is far from it. Our skill is not just in identifying problems and designing solutions, but in making those solutions work often in a tough business and cultural environment.

GPR Dehler has an excellent record of implementing change programs in Australia, New Zealand, Asia, Europe, North America and Southern Africa. Everything we do is geared towards achieving results not writing reports. We have the management and planning skills as well as hands-on consultants with experience to overcome obstacles and transform good ideas into effective and successful programs. Significantly, we do this with minimum disruption to our clients’ business operations.