What's Best!
by Lindo Systems
Production Scheduling and Distribution
Production scheduling and distribution at a forestry companyForestal Celco S.A., a large Chilean forestry company, had a problem. Their system for optimising production scheduling and distribution was not serving them very well. The DOS-based system was cumbersome, data entry was laborious, report capabilities were limited, and it was poorly designed for sensitivity analysis.
"I was hired to design and implement a new system, naturally under time pressure, to be used in their upcoming annual planning exercise." recalls Dr. Andre Laroze, Forest Management and Planning consultant and professor at the Catholic University of Chile.
"Given the user's spreadsheet knowledge, it was clear the system should be integrated with Excel 5.0. The question was how to combine the considerable advantages of this platform with an efficient problem specification. The answer was to try a combination of What'sBest!, Excel and Visual Basic." The result was an efficient problem solving tool that was interactive and easy to use.
Data input is handled using several pre-defined worksheets. "The approach is very simple, requires no additional learning curve, and takes advantage of the user's familiarity with the spreadsheet environment," explains Dr. Laroze, "Considerable gains were achieved in data set-up efficiency. Setting up different scenarios requires a minimum amount of data re-entry”. The system uses Excel's file management capabilities for handling different sets of data, and password protection is used to safeguard everything outside the well-defined data entry cells.
Visual Basic's structured programming features are used for generating the model in a separate worksheet. The system is set up for maximum versatility, allowing the user to define the problem's actual dimension, the restrictions to be considered, the data to be used, and the objective goals of each particular scenario. The system automatically detects what is relevant to the problem at hand and writes the appropriate equations and imports the relevant data from corresponding worksheets.
Visual Basic is instrumental in defining the adjustable cells, generating equations, and producing an efficient model to be solved. To preserve integrity, the Visual Basic procedures are kept encrypted in an independent workbook as an auto-running macro. Model generation using this approach requires significantly less time than the DOS-based system it was designed to replace.
What'sBest! is used to solve the model, and the system copies the solution to another workbook that generates reports and graphics for the scenario. Since the system is set up in an environment in which the user is comfortable, the client has been able to easily add and customise reports without the aid of an outside consultant.
The system takes full advantage of the capabilities of Excel and Visual Basic. It uses pull-down menus and dialog boxes to navigate through the different worksheets and viewing and printing controls. A customised toolbar allows the user to perform pre-feasibility checks, generate the model, and copy the solution to the report workbook. It even has its own Help sub-system covering everything from the system's user manual to modelling tips specific to the problem.
One of the biggest benefits of the new system is that it allows Forestal Celco to easily investigate a number of different scenarios before making critical decisions. Unlike their old system, they can now quickly and easily investigate the effect of changes in things like demand behaviour, economic environment, company policies, and production constraints. "It is safe to say that their emphasis has changed from 90% set-up and 10% analysis to 25% set-up and 75% analysis," states Dr. Laroze, "The new system is used not only to solve a particular problem, but has become an analytical tool. By investigating different scenarios the system is used to gain a better understanding of their problem, and, as an extension, for designing and evaluating alternative plans."