For the PDCA Cycle, which one would you use, an Ad Hoc Analysis or a Pre Set Analysis?
Ad Hoc means “for this” or “for this situation” in Latin. Ad Hoc Analysis is an analysis for a temporary and particular situation done without preparation and in a short time. On the other hand, what’s Pre-Set Analysis?
(Duration: 5:28)
＜＜ Related Posts ＞＞
- What is the basic of data analysis? That’s the PDCA Cycle! (DMAIC: Analyze Phase)
- 【Case Study】How to rotate the PDCA cycle with data analyses【Mentoring program】
What’s an Ad Hoc Analysis?
Hi, this is Mike Negami, Lean Sigma Black Belt.
When you start a data analysis, there are two ways you can do it. They are an Ad Hoc Analysis and a Pre-Set Analysis. The first one, Ad Hoc means “for this” or “for this situation” in Latin. It’s an analysis for a temporary and particular situation done without preparation and in a short time.
For example, your sales team got a potential big deal, but that client is expecting you to give a large discount. You would like to calculate whether you will gain enough profit or not. In this scenario, that analysis would be particular and tentative, so that is an Ad Hoc Analysis.
Another example, you have some data, then you edit it manually by copy-and-pasting, putting formulas or adding graphs. Those analyses are for some particular subject and tentative, so they would also be Ad Hoc analyses. By that way of thinking, most of analyses that we normally work on are Ad Hoc analyses.
The way to do this Ad Hoc analysis is the same as the basics of data analyses that I introduced in the past. Consider and complete the required final output layouts first, then collect necessary data.
＜＜ Related Post ＞＞
What’s a Pre-Set Analysis
However, one issue with this Ad Hoc analysis is when you need to do the same analysis again or regularly. Since you have to do it again manually, it will be time-consuming. In that case, you should use the Pre-Set Analysis.
In this method, just by replacing the source data on one worksheet, all results on the other worksheets will be updated.
For the PDCA Cycle of your operation, you need to regularly analyze results at the “Check” step after the “Do” step. You want to minimize the update task. You should have a Pre-Set Analysis.
Also, you can make some of your daily tasks drastically faster and more accurate by applying this concept to them. I’ll make videos that will help you learn to make Pre-Set analyses.
For example, we completed the user interface screens of my client’s project in the previous video. My client will update the screens every month, so we would like to make a Pre-Set Analysis and to make the update procedure as fast and easy as we can.
How can we achieve it? Copy your source data accumulated every day and paste it onto a worksheet in your master file, all the other sheets will be updated. A tip here is that you should not directly edit the source data at all, but use Excel functions and macros to bring the necessary results from the source data to the other worksheets.
Referring the source data sheet by Excel functions and Macros
For example, in this cell, the SUMIFS function is used and refers to the source data sheet.
Another example, there is no formula in this cell. By clicking this button, a macro will refer to the source data, do necessary calculations and output each result to each cell.
It’s better to use this macro method when you need complex calculations that cannot be done by Excel functions, or when the number of functions becomes too many. When they are too many, Excel becomes too slow and its file size becomes too large. In such a case, using macros solves those issues. It may be a very high hurdle to master using macros, but I would like encourage you to challenge yourself to do it.
Pros and Cons of Pivot Tables
Also there is another way, which is to use Pivot Tables, but I don’t recommend it for this purpose. Pivot Tables are very handy, but their output design is not flexible so it’s difficult to make the whole screen well. They also tends to increase Excel’s file size considerably. Actually, just Excel functions alone can give the same result as a Pivot Table.
On the contrary, for the Ad Hoc Analysis, we want to complete it quickly, so the Pivot Tables are very effective.
Today’s topics were Ad Hoc Analysis and Pre-Set Analysis.
