Lean Office with Excel and VBA
Have you ever wondered what people do at their computers all day? How can they possibly be that busy working on their day to day tasks? The answer may surprise you! Computers were supposed to make our jobs easier and give us more time to do other things. The reality is quite the opposite.
We have noted that many people are not as adept at using the software we have placed at their finger tips as we would like to think. As a result, we have found that one of the significant losses in the front office is time wasted that could easily be done by the software. Unfortunately, we simply don’t know how to do it.
Time is wasted entering, retrieving, editing, and manipulating data to consolidate and prepare reports that only require further manipulation and editing so an effective analysis can be performed. This is particularly true for spreadsheet applications.
Pivot Tables are an excellent example of the point we are making here. Some people are aware of them and even fewer know how to use them. Pivot Tables make organizing data a relatively simple chore. Once created, it is a simple matter to refresh the data and regenerate new reports.
We have also observed that many of the tasks being performed using spreadsheets can be easily and readily automated resulting in considerable time savings. When we discuss these opportunities with the current users, we find that most people only use a fraction of the capabilities that are available to them simply because they are not aware of them or simply have have not been trained.
There is something about our current work methodology that supports learning just enough to get the job done rather than learning all we can to perform the job efficiently and effectively.
Resources:
There are many venues available including online training and packaged offsite training programs however, we still prefer learning by the “book”. Many books include a companion CD that not only includes working examples and tutorials from the book, but often times a copy of the book itself is offered in digital form.
If purchasing books seems to be more of a burden than you bargained for, visit your local library. They often carry many books that are better borrowed than purchased, especially if you are starting at the most basic level. In a short period of time, your experience will outweigh the content of the introductory texts.
The next time you find yourself entering even more data and formatting spreadsheets and reports, consider teaching yourself some VBA over the next few weeks to see how much you can do to help yourself by putting your software to work!
Visual Basic for Excel
Microsoft Office Excel 2007 Visual Basic for Applications: Step by Step
We are presently reviewing Microsoft Office Excel 2007 Visual Basic for Applications: Step by Step published by Microsoft. The book requires a minimal degree of familiarity with the Excel and VBA environments and offers a solid interactive approach to familiarize the reader / student with Macros and VBA.
While this may be a little more than just coincidence, the examples developed in the first few chapters of the book were very similar to an actual situation we encountered only a month ago. Although we already developed our solution, it was interesting to note that the “HOW TO DO IT” was already out there, bound in a book and sitting on a shelf.
The ERP system would generate a text file that was manually loaded into a spreadsheet, manipulated, formulas added, data edited, and finally formatted to create the final report. This task would require anywhere from 30 to 45 minutes per day. Using the macros created through the first few chapters allowed this work to be completed in less than a minute.
While books may not make for a great gift, the time you can save to do other things is the best gift you can receive. It is interesting how often we hear “I don’t have time”.
Next Steps
We are more than impressed by companies that support in-house libraries where books and other resources available to their employees. If you don’t have one where you work, this may make for a great suggestion where everyone wins.
Until Next Time – STAY Lean!
Effective Problem Solving with Mind Mapping
Problem solving is a problem for many companies and at times can be one of the most daunting tasks to undertake during the course of an otherwise regular work day.
For some, problems seldom occur while for others this may, unfortunately, be a daily activity. Since problem solving is not usually part of the typical daily agenda of “routine” activities, our ability to find the time and solve them efficiently and effectively is compromised.
For many, just finding time seems to be one of the greatest challenges and perhaps a problem to be solved in itself. Sweeping problems under the rug may be efficient but it is certainly not effective. (So … broom is not the solution we’re proposing).
Background:
While there are several different approaches and “forms” that can be used to manage the overall problem solving process, the two most critical steps that will determine the effectiveness of the solution are:
- Define a Clear and Concise Problem Description / Statement
- Determine the Root Cause(s) of the problem defined by the Problem Statement.
While the first step seems relatively simple, the second step requires a little more effort. There are at least two (2) root causes for most problems that stem from two simple questions:
- Why Made?
- Why Shipped?
These questions imply that defective product was made for a reason (process) and it was shipped to the customer undetected (system). In other words, the customer is not protected from receipt of defective product.
The root cause analysis process forms the basis for all subsequent problem solving activities, including verification, interim and long term corrective actions. A lot of time can be wasted simply because the real root causes were never identified.
Problem Solving Tools for Root Cause Analysis:
Many different tools can be deployed during the Root Cause Analysis process including Ishikawa Diagrams (Fishbone Diagrams), 5 Why (discussed in a previous post), Fault Tree Analysis, Q&A (Question Board), and Brain Storming to name just a few.
Mind Mapping or Process Mapping is a technique that provides an unconstrained approach to the thinking process for multiple input and contribution streams. Maps can also be used to identify interactions or relationships to other elements.
Mind Mapping (Process Mapping)
The center of the map contains the problem statement. We then surround the problem statement with potential inputs or contributors to the problem. These statements in turn become the ”center” of additional levels of inputs and contributors. In some respects, the process map can be very similar to a Bloom Diagram and certainly supports the logic found with fishbone diagrams.
The The draw back to “Mapping” is that most are usually developed on Whiteboards and not easily or readily translated into a software solution.
Software Solutions and Templates
While there are many spreadsheet based solutions, few provide an effective interface to support the use of mapping techniques. Even most fishbone diagrams developed in Excel are quirky and awkward at best.
While we typically do not endorse specific software solutions, however, FREEMIND is one software that we consider to be among the best of available solutions and can be downloaded free of charge. The download and installation process only requires a few minutes.
The developers of FREEMIND provide a clean, intuitive solution for creating and maintaining process or mind maps. While other commercial packages are available, FreeMind is more than capable of handling most problem solving challenges and quite simply is time and money well saved.
The FreeMind homepage provides a better description of the software and it’s capabilities than we could provide here. Our goal was to introduce “Mapping” as an effective and efficient tool that can be used in the problem solving process.
After spending some time with the software, you will quickly discover that there are many other opportunities where this software can serve you. We have a mind map that we use to manage weekly and daily reports, another for key metrics, and yet another for our business structure. The ability to use hyperlinks makes it an easy process to access external reports and resources .
The FreeMind main page provides an excellent overview and provides examples of their software in action. This is definitely worth looking into and may just save some time for real problem solving.
We are presently using FreeMind version 0.9.0 RC 6.
Home: http://freemind.sourceforge.net/
Copyright 2000-2009 Joerg Mueller, Daniel Polansky, Christian Foltin, Dimitry Polivaev, and others.
Click here to see a sample process map to achieve delivery of 100% on time – in full: Mapping with FreeMind. We have also uploaded two documents (one of the original map and a word document showing a pictorial of the mind maps we created) into our Free Downloads box. See the ORANGE box on the sidebar to get your copy.
If you have a copy of FreeMind, simply change the extension on our Delivery file from “.txt” to “.mm” Of course, don’t typ the quotes. This is just a sample for example purposes only. Feel free to edit or modify these files in any manner you choose.
Until Next Time – STAY Lean!
Going DEEP with OEE
Does anyone actually look at their daily equipment availability? Instead of using TEEP that may be based on calendarized availability, looking at the Daily Equipment Effectiveness Performance of your operation may provide some interesting insights.
Working overtime due to material or equipment availability occurs many times. Unfortunately, we find that sometimes these very same machines are idle during the week.
Just something to invoke some thoughts for your operation and to perhaps identify another opportunity to improve performance.
Until Next Time – STAY Lean!
Technology Reigns
We’ve been extremely busy as of late and logging into the system to post has not been a luxury we’ve been able to enjoy.
Technology has fortunately provided the means for us to post remotely. This is a test.
Until next time – STAY lean!
OEE in the Automotive Industry
The automotive industry appears to be rebounding at a faster rate than most (if not all) experts may have anticipated. Many OEM’s and their suppliers are attempting to boost production to replenish inventories and support renewed demand for their products. Reduced inventories throughout the supply chain are creating demand that is difficult to replenish at the rate required. Short runs to bootstrap the “pipeline” are taking their toll on OEE rates but also provide the opportunity to identify new improvement initiatives.
General Motors and Toyota have both announced that increased demand for their product is anticipated for the next few months. The increases are exciting for all involved, however, the ramp up to recovery may be more painful to achieve for some. How is your company performing? Those with fixed “cells” or processes may not be experiencing the same degree of frustration as those having flexible processes running multiple part numbers.
Overall Equipment Effectiveness (OEE) typically suffers during these times due to the frequent changeovers and short volume runs. If there was a time when you can’t change over or setup and run fast enough, this may be it. Hang on and enjoy the ride.
Until Next Time – STAY lean!
Instant Turnaround for Overall Equipment Effectiveness (OEE)
Did you know that your leadership style may be affecting your Overall Equipment Effectiveness? A highly engaged and motivated workforce is the one single distinguishing trait of the top companies in industry today. Most leaders and managers alike recognize that having the right people on the team are key to the success of their companies. The vision and mission statements of many companies include statements like ”… our people are our greatest asset …” or “… we will attract and retain the best talent …” If we believe these statements are true , then we would envision a working environment where everyone is an engaged team member of a seamless organization that is fully supported by the management team. Does this describe the culture of your organization?
What does this have to do with OEE?
LEAN is a CULTURE and one of the key metrics of lean is OEE. So, could it be that OEE is a direct reflection of the culture that exists in your company? What are the trends showing? Does the data reflect a high-velocity company striving to improve its operations to become the new definition of agile or lean? Who is leading the charge to improve performance? Where is your team? If your team isn’t behind you – where are they?
What motivates your employees to bring themselves to work everyday? Their motivation to work is probably not the same as your reason for being in business. This presents one of the first challenges that the management team will have to address - aligning company and employee goals where they become synonymous and mutually beneficial. This doesn’t mean we’re going to rewrite the business plan, but rather, we relate how achieving company goals will enable the employee to achieve theirs. The key is making your company the place where they want to work – a company they can be proud of and even more so because they are a part of its continuing success.
We contend that self-motivated employees work harder – they take the initiative to get things done – quickly. Many operations are dependent on people who control their level of productivity. It can be a difficult challenge to motivate people to work harder than their own perceived value. The resistance to improve is rooted in the expectation that people will be required to do more work for the same value. If you are operating in a union environment, the employees tend to be more sensitive to changes in their work structure.
In contrast to their external behavior, people want to be challenged and they are willing and wanting to do more. However, they will not automatically give you their discretionary effort unless they have earned your trust and respect first. Without it, they will only give you the value of what they think their time is worth. Becoming interested in what people are doing, acknowledging their performance and treating them with respect is at the core of an instant turnaround.
When people become engaged and their achievements are recognized they will take ownership of their operation. OEE results will improve as employees suggest ways to improve their processes and strive to achieve new goals. None of this will happen unless the executives and senior management team take an active role to become part of the action.
How to get your Instant Turnaround
We just completed a review of Instant Turnaround: Getting people excited about coming to work and working hardby Harry Paul and Ross Reck, PH.D. You will learn how to tap into and harness the discretionary effort of everyone on your team. This book tells a story that reveals how your leadership style and that of your front line management team affects and directly impacts the performance of your team. This leadership style works. A lean culture is dependent on the engagement and effective interactions of all of your employees – including the executive management. This book teaches a simple yet powerful strategy to tap into and harness the discretionary effort that every employee is wanting and willing to give - if the conditions are right.
Get it now: Instant Turnaround!: Getting People Excited About Coming to Work and Working Hard
What is your leadership style?
A true leader is and holds people accountable, works with integrity, is approachable, listens, inspires, smiles, and even knows how to have fun. The list of leadership traits and characteristics is long and many great leaders would count themselves as falling short on many of them. If your team shares mutual respect and trust, you are working in great company. If your team fears you, why should they stay? Remember, most people want to belong to a winning team! If you manage by walking around (MBWA), you will certainly have an opportunity to communicate your praise and expectations to your team. They will respond in kind!
Until Next Time – STAY lean!