What IF Analysis – Data Tables Basics (Sensitivity Analysis) - 2 Inputs & Multiple Output - Question

2 minutes
Share the link to this page
You need to purchase the class to view this lesson.
One-time Purchase
List Price:  $139.99
You save:  $40
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  A$180.54
You save:  A$51.58
List Price:  ৳11,866.68
You save:  ৳3,390.72
List Price:  CA$176.88
You save:  CA$50.54
CHF 88.56
List Price:  CHF 123.99
You save:  CHF 35.42
List Price:  kr856.85
You save:  kr244.83
List Price:  €115.16
You save:  €32.90
List Price:  £102.10
You save:  £29.17
List Price:  HK$1,085.16
You save:  HK$310.07
List Price:  ₹10,216.03
You save:  ₹2,919.07
List Price:  RM564.15
You save:  RM161.20
List Price:  ₦53,196.20
You save:  ₦15,200
List Price:  kr1,178.92
You save:  kr336.86
List Price:  NZ$194.68
You save:  NZ$55.62
List Price:  ₱6,730.59
You save:  ₱1,923.16
List Price:  ₨22,489.39
You save:  ₨6,426
List Price:  S$185.22
You save:  S$52.92
List Price:  ฿4,192.56
You save:  ฿1,197.96
List Price:  ₺1,033.78
You save:  ₺295.38
List Price:  B$750.37
You save:  B$214.40
List Price:  R2,095.15
You save:  R598.65
List Price:  Лв225.25
You save:  Лв64.36
List Price:  ₩154,078.01
You save:  ₩44,025.43
List Price:  ₪458.17
You save:  ₪130.91
Already have an account? Log In


Welcome everyone. In this session, we will talk about how to prepare a dashboard. And that dashboard will require a few techniques to be combined in one single case study. We'll be using data table conditional formatting, how to create a drop down how to use indirect formula, along with range naming, as well as garnish the entire thing with the buttons. And you see how this helps us create two input based whatever analysis which can lead you to multiple outputs. First, let me illustrate the main calculation on the left hand side.

And we optimize the window. Oh, now you notice price and quantity sold, those are the two input cells marked with yellow and blue. And the revenue is a formula which is just multiplying the two factors. On the other hand, I have two components of cost, variable cost, fixed cost, variable cost formula suggests that whatever is your revenue 60% of that is going to be your variable cost. Now this is a very hypothetical scenario, but this is Good enough to illustrate what we are up to fixed costs being 20,000 hard coded, and the total cost just comes out to be 35,000. Comparing revenue and total cost, we get a net loss of 5000.

That's a negative. Now, notice on the right hand side table, I have prepared this What if analysis, which is showing you a gamut of numbers in terms of prices, a gamut of quantities sold that sell units. And if you decrease the unit sold, you notice how the numbers change. If you increase the unit soul, that's good for the business. And you see multiple numbers which are in green. Now what are these green values, these are profit values.

And to illustrate my point, what I'm going to do is I'm going to put a number 15 against price and a number 2000 against the initial quantity. The number that I'm getting is negative 5000. Which shows me what it is matching with My previous calculation, thus verifying the accuracy of this entire calculation and not only that, you also see multiple breakeven points. So, this What if analysis using two input variables and showing the third output, which can be profit and also can be revenue if you want it to be. So, most of the tutorials on the internet will show you how to create the two variable data analysis, but they will not show you how can you make sure that this also shows the multiple outputs. Now, that is also something that we will be discussing in the upcoming video.

Sign Up


Share with friends, get 20% off
Invite your friends to TabletWise learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.