Data Analytics Part 2b: Introduction to scenario planning & what-if analysis
Links to previous articles in the series:
Data Analytics Part 1: ETL for business analysts
Data Analytics Part 2a: Best practices of data analysis
The Road Not Taken
Two roads diverged in a yellow wood, and sorry I could not travel both, and be one traveler, long I stood and looked down one as far as I could, to where it bent in the undergrowth
I still remember this literature analysis I had together with my classmates when I was studying in my high school, and since then it's became one of those thought-provoking piece of art I've ever read. The title of this poem is "The Road Not Taken" by Robert Frost. Then a few years passed by in a few blinks of the eyes and I studied decision trees, what-if analysis, scenario planning and all sorts of other jargon my school of business forced upon me - I had to remember them because they were bound to be "exam's hot topics". Before I dive deeper into what-if analysis, let me entertain you with a story...
I used to teach a diploma subject called "Information for Control & Decision Making" about a year ago, and in my first lesson, I asked my students, "Look at the subject title. What is information used for?" They were a little doubtful, until someone was confident enough to say, "Control" and another, "Decision making". I pressed on by asking them, "So, what is control, and what is decision making?" At this point, the whole class went quiet. Then I went on the board and started drawing a 3-by-3 matrix - I swear, matrices are the best thing I could draw and make my parents proud. That and circles.
As I continued my lecture, the students' eyes were glued on the whiteboard as I drew my crooked lines explaining "The Matrix of Information". But this article isn't about "The Profit Equation" of which I would not digress too much. I suppose for my dear colleagues here, this would serve as a high-level flash card.
So where does this road lead to now?
My earlier meditative article focuses on lookup where I mentioned in my introduction that "knowing where to look some information out effectively would help you in your analytics." Those lookup skills help you to provide high-quality information (some call these insights), so the leaders can make better decisions by relying on the information you present when they are at the poetic "yellow woods". You all must have realized by now, you gotta combine data from all sorts of places just to get the kinds of information you wish to present in your meetings (or at least, prepare yourself so you, or your leaders don't get bazooka-ed for not knowing what you ought to know - your business operational metrics).
What is "What-If Analysis" and how does it differ from "Scenario Planning"?
I am not able to provide you the text book definitions, and I thank God I am not writing lecture notes for my students. I will just go by the gut (contextual definitions)... WHAT-IF ANALYSIS is one of the many tools deployed in quantifying the multiple scenario that would and could potentially unfold over the entire life of the business. SCENARIO PLANNING is a process that involves identifying the various possibilities of the future for the business. Some may argue they are used interchangeably, or rather... Technical people like us tend to say what-if analysis more than scenario planning because we would always ask around, "WHAT IF we decide to pay Agency X USD3,000 for a digital marketing campaign of our newly launched product, can we gain enough traction by the end of Dec 2019?"
As a business analyst, it is important to know how to plan for the various scenarios that may unfold - here's where you gotta do market research to understand the industry better, and how these would all be a great blend to provide insights to the company, and most importantly, preventing leaders from making unprofitable business decisions that may be emotionally fueled. Perhaps, this is why most people in office rarely speak to me about the work I do as I am apathetic with my data. I believe that my data-apathy could give me unbiased insights that I can share my insights empathically with my leaders.
Ok, Alex. Enough with stories. Gimme "Excel" examples already!!!
You may already know that there are 4 tools for "what-if analysis" in Excel - Goal seek, solver, scenario manager, and data table. But when would we know which is suitable for what scenario or case? Here I categorize them into two functional uses - "Sighted" and "Blindfold". By sighted, I mean you have all the variables you require to perform your calculation, just that you find it tedious to perform repeated calculations across many possibilities. Blindfold means that you do not know the input values for you to get to your end goal (outputs). I've prepared a flowchart for you to understand when to use certain tools better using Draw.Io. Let us go through each of the stages of making decisions on what model fits your situation best.
Let's start with the prerequisites
- You know what the outputs are. For example, you have established the fact that PROFIT will be your output. So, SALES and COSTS are your input variables.
- All the input variables are identified. When I say all, it also means the other "fractal" of your inputs above. Example, total units sold & unit selling price are your fractal inputs for SALES; total units produced & total unit variable cost are your fractal inputs for COSTS.
- You cannot be having unlimited inputs or outputs. Your constraints, while some technical accountants will call them limiting factors, must be identified to build a good "what-if model". Common examples would be: labor hours, machine hours, material inputs.
NB: The basic profitability function put in tandem with fractality. For simplicity's sake, fractals are the "fragments" that belong to a greater element. For example, stones are fractals of mountains. In the example chart above, we can argue that machine hours, labor hours and material input are also fractal to unit variable cost. This term was introduced by Benoit Mandelbrot into mathematics (later into the financial market). I attempt to introduce this term in cost management, so if you know anything being mentioned about this already, please let me know!
Do you have your input parameters defined?
Your input parameters are the values that are assigned to the input variables. I wouldn't wanna complicate this article further. Let's forget about fractal inputs and we just walk through the bookstore scenario written by excel-easy to understand what input parameters really are.
Assume you own a book store and have 100 books in storage. You sell a certain % of books for the highest price of $50 and a certain % of books for the lower price of $20. Case 1a. How much revenue will you get from selling half of the books at $50 each and the other half at $20 each? Case 1b. How much revenue will you get from selling 60% of the books at $50 each and the remainder at $20 each? Case 2. Calculate the total revenue for the different combinations of 'revenue' and '% of books sold for the highest price'. -Exerpt from excel-easy.com-
- $50 is the value assigned to the input variable of highest selling price
- $20 is the value assigned to the lower selling price
- 50:50 and 60:40 are the values (in this case, as ratio) assigned to the number of books sold
Which models to use?
To answer the earlier question, "Do I have my input parameters defined?", the answer is YES. So I will use the SIGHTED MODELS to solve this problem. Next question is, "Are the cases basic and limited?" Well, for Cases 1a and 1b, they are quite basic. So you can make do with scenario manager because they are only one-way, or univariate analysis. Trust me, it is quite tedious doing this... I still choose to do my univariate analysis using data table. For Case 2, you will need to use data table as it is a two-way, or bivariate analysis (revenue and % of books sold for the highest price).
Following the same scenario, let's ask another two questions...
Case 3. How many books you need to sell for the highest price, to obtain a total revenue of exactly $4,700?
Case 4. As per (3), with the following constraints:
- Ceiling price of a book is $70
- Floor price of a book is $15
Uh oh... What are my input parameters? I have none, because I have to now determine them! That's why the next models are termed BLINDFOLD because you don't know the input parameters. You know a few things tho...
- Input constraints (ceiling price being $70; floor price being $15)
- Target revenue ($4,700)
Goal seek can solve Case 3 because it only involves the change of one input without constraints. However, we need to use Solver to tackle Case 4 and the likes of it.
Alex, where are the solutions? I ain't got time to do your homework!
After reviewing my article several times, I realized this article is getting too long, and I feel nobody (especially me) is interested in reading long articles - the more technical they are, the drowsier I get when I read them. So, I decided to break this part into two. I will be providing the technical solutions and explaining the science behind them in my following article. Before I leave, I highly recommend you these two videos from Leila Gharani as I will expound deeper into fractal inputs after providing you my solutions to the above cases.
Here's my traditional toast to all you aspiring analysts - May our journey in data analysis be an interesting one, filled with many conditions and constraints that we may learn to be better every single day.
Salut, mes amis!
Pour l'amour des donées,
Alex