Created ERP system in Excel
Setting
The Giftwrap industry is quite particular, obviously gifts are given throughout the year at birthdays, wedding days and other special, mostly personal, occasions. In Europe however the real gift giving occurs in December: Both Christmas and, in parts of Europe, St. Nicolas (December 5/6) are occasions where everyone receives a gift, as such the giftwrap requirement during the period of mid-November to mid-December is some 25 times the normal monthly requirement, causing all kind of problems, not the least of which is working capital.
Challenge
Enper is a successful medium sized Dutch giftwrap company and as Managing Director I had to marry a limited supply of working capital with trying to achieve a maximum amount of output in as short a time span as possible. Clearly you could decide to manufacture at full throttle throughout the year, however, since orders for the December rush would only start arriving late August, this would lead to all type of risks/problems:
- Do you receive sufficient orders to sell everything you’ve produced
- For that matter, did you produce the required designs, ending the year with stocks of unsellable designs is deadly
- Do you have sufficient working capital to buy all the paper, inks and storage space
The trick is therefore to wait as long as possible with manufacturing, but once started, to produce as much as humanly possible:
- Make you design runs as long as possible, but produce only what you have sold
- Do the design runs in the right order, minimizing change-over time
- Keep the daily order changes in mind (in some 2 months you receive all your December orders, so the optimum print runs change daily)
Solution
I wrote, in Excel, an ERP system that kept all these limitations in mind. During the day all new orders were added to a spread sheet, after which, at night, a very lengthy macro was run, keeping track of issues like:
- Present stocks of designs on the different types of paper
- Availability of paper types and ink, including ETAs
- Requested delivery dates (do not forget normal monthly sales)
- The design currently running
- Etc.
Every morning a new production schedule would be waiting at the printer. This system allowed for an efficiency increase / lowering of working capital of some 20%
See also: