We’ll discuss the difference between an Ad Hoc Analysis and a Pre-Set Analysis
[Intro Video]
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.
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.
This is the source data accumulated every day.
When you copy the last month's data and paste it onto this worksheet, 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 froFm the source data to the other
worksheets.
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.
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.
Thank you very much for viewing.
Please click the ‘Subscribe’ button.
Also click and watch my other related videos.
Thanks