Automatized tools with Excel, a concrete example of Lean in administrative fields
A lot of people believe that Lean concepts might be only applied to engineering and production. It is not true. All departments in a company need to use Continuous Improvements principles if they want to reach Excellence.
In administrative departments, I guess, everyone would like to speed up their analysis to solve issues on time and to help the top management to make the best decisions. As well, I believe that employees would like to reduce the processing time for repetitive tasks to realize other things and do not have overtimes. Furthermore, I think that some people do not want to lose a lot of times to find documents and/or files. If yes, it is the sign that you have to apply Lean concepts in your department to be more efficient.
In this article, I will insist on the automatized tools which will allow you to reduce your processing time (Hoshin), to be synthetic, to be ordered (5S) and to facilitate the decision taken (Visual Management).
„A business is a range of data that you have to process if you want to move forward and take the best decision.”
In a business, to make decisions, you have to perform analysis. To realize those tasks, you have to use data from an ERP system and analyze them with Excel. In fact, the ERP system does not perform analysis. It just stores data.
But I observed that a lot of people do not know to perform correct analysis with Excel because, for them, it is too technic and there is too much data to deal with. By consequences, people are losing time and might perform wrong analysis. Impacts might be huge: backlogs, wrong root cause analysis, wrong forecasts, decisions not taken in times,…
To solve those issues and to limit wrong impacts in your business, you might use Macro or VBA language with Excel. For example, your analysis can be done in 5 minutes by pressing a button instead by doing the same manually in 2 hours.
| BEFORE | AFTER |
|---|---|
| Time = 2 hoursSpace = 12 Mo | Time = 5 minSpace = 800 Ko |
| Reduced Processing Time = 115 min (95%)Reduced Size = 11.2 Mo (93.3%)* | |
| Under a year = (with 8 hours on 200 working days)383 hours saved / 48 days savedREDUCED SPACE = REDUCED PROCESSING TIME = COST SAVING | |
* Standard units from IEC (International Electrotechnical Commission) in December 1998
As you can see with this example above, I recommend you to automatize as much as possible your analysis to be performant. Time-saving might be very huge. For automation, you have to be open-minded. Do not start by thinking that programming it is too difficult, it is logic and simple. At the beginning, you will do mistakes and it’s normal! To succeed it, learning with other is among the best solution. Thereby, you will create a competition between your team’s members to reach excellence and increase your knowledge.
Automatization with macros will generate several positive consequences in your organizations:
- You will be rigorous about how you want to classify your data. It will reflect your way of thinking (5S) and it will be easier for others to use data from a classified file than a file with a mess (Time-saving)
- You will generate added value by reducing your process time and generate time to check all potential mistakes (from the system) and to perform other duties (Time-saving and Quality)
- When you have a mistake in your file, instead of changing the formula on each cell, you will just change one formula on the code (Time-saving)
- You will reduce the space on the drive/computer because a formula uses more bits (computing unit) than a figure/text in a cell (5S). It will be easier to open them and send them to people (Time-saving and 5S)
- Your graphs (if you have it) will be automatically updated and you can see the current situation of your business to take best decisions (Visual Management)
- Easy automatical way to store your files with a standard classification (ex: yyyymmdd – Sales Analysis) and you will facilitate the analysis’ search in the future (Time-saving and 5S)
To conclude, do not forget one thing: „A business is a range of data that you have to process if you want to move forward and take the best decision”. Set up KPIs (with automatized tools) is the first ACT to launch the process of continuous improvement. It will be as well your tool of control (dashboard) for all actions put in place.
Have you already try to improve your data processing with automatized tools? What were your results with this action? Did you use macros / VBA for that? Please, feel free to contact our consultant or to give your feedback below.
