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

Advanced Excel Crash Course Section 14: What-If Analysis
2 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€91.74
List Price:  €128.44
You save:  €36.70
£78.54
List Price:  £109.95
You save:  £31.41
CA$135.28
List Price:  CA$189.39
You save:  CA$54.11
A$152.35
List Price:  A$213.30
You save:  A$60.94
S$133.70
List Price:  S$187.19
You save:  S$53.48
HK$782.09
List Price:  HK$1,094.96
You save:  HK$312.87
CHF 88.44
List Price:  CHF 123.82
You save:  CHF 35.38
NOK kr1,060.96
List Price:  NOK kr1,485.39
You save:  NOK kr424.42
DKK kr684.80
List Price:  DKK kr958.74
You save:  DKK kr273.94
NZ$164.32
List Price:  NZ$230.05
You save:  NZ$65.73
د.إ367.22
List Price:  د.إ514.12
You save:  د.إ146.90
৳10,977.27
List Price:  ৳15,368.62
You save:  ৳4,391.35
₹8,288.18
List Price:  ₹11,603.79
You save:  ₹3,315.60
RM470.40
List Price:  RM658.58
You save:  RM188.18
₦159,205.14
List Price:  ₦222,893.57
You save:  ₦63,688.42
₨27,945.54
List Price:  ₨39,124.87
You save:  ₨11,179.33
฿3,570.70
List Price:  ฿4,999.13
You save:  ฿1,428.42
₺3,209.07
List Price:  ₺4,492.83
You save:  ₺1,283.75
B$499.58
List Price:  B$699.43
You save:  B$199.85
R1,877.33
List Price:  R2,628.34
You save:  R751
Лв179.79
List Price:  Лв251.71
You save:  Лв71.92
₩133,020.69
List Price:  ₩186,234.29
You save:  ₩53,213.60
₪366.08
List Price:  ₪512.52
You save:  ₪146.44
₱5,560.69
List Price:  ₱7,785.19
You save:  ₱2,224.50
¥14,903.11
List Price:  ¥20,864.95
You save:  ¥5,961.84
MX$1,671.47
List Price:  MX$2,340.12
You save:  MX$668.65
QR364.79
List Price:  QR510.72
You save:  QR145.93
P1,358.93
List Price:  P1,902.56
You save:  P543.62
KSh13,752.81
List Price:  KSh19,254.48
You save:  KSh5,501.67
E£4,780.94
List Price:  E£6,693.51
You save:  E£1,912.56
ብር5,681.93
List Price:  ብር7,954.93
You save:  ብር2,273
Kz83,641.63
List Price:  Kz117,101.63
You save:  Kz33,460
CLP$93,803.57
List Price:  CLP$131,328.75
You save:  CLP$37,525.18
CN¥719.51
List Price:  CN¥1,007.35
You save:  CN¥287.83
RD$5,921.90
List Price:  RD$8,290.90
You save:  RD$2,369
DA13,438.45
List Price:  DA18,814.37
You save:  DA5,375.92
FJ$226.07
List Price:  FJ$316.51
You save:  FJ$90.44
Q780.87
List Price:  Q1,093.26
You save:  Q312.38
GY$20,947.35
List Price:  GY$29,327.13
You save:  GY$8,379.77
ISK kr13,640.79
List Price:  ISK kr19,097.66
You save:  ISK kr5,456.86
DH1,002.59
List Price:  DH1,403.66
You save:  DH401.07
L1,770.35
List Price:  L2,478.57
You save:  L708.21
ден5,647.93
List Price:  ден7,907.33
You save:  ден2,259.40
MOP$805.83
List Price:  MOP$1,128.19
You save:  MOP$322.36
N$1,866.41
List Price:  N$2,613.05
You save:  N$746.63
C$3,683.13
List Price:  C$5,156.53
You save:  C$1,473.40
रु13,265.46
List Price:  रु18,572.18
You save:  रु5,306.71
S/368.80
List Price:  S/516.34
You save:  S/147.53
K382.03
List Price:  K534.86
You save:  K152.83
SAR374.99
List Price:  SAR525.01
You save:  SAR150.01
ZK2,507.99
List Price:  ZK3,511.28
You save:  ZK1,003.29
L456.51
List Price:  L639.13
You save:  L182.62
Kč2,309.58
List Price:  Kč3,233.51
You save:  Kč923.92
Ft36,119.60
List Price:  Ft50,568.89
You save:  Ft14,449.28
SEK kr1,035.66
List Price:  SEK kr1,449.97
You save:  SEK kr414.30
ARS$85,041.86
List Price:  ARS$119,062.01
You save:  ARS$34,020.14
Bs691.17
List Price:  Bs967.67
You save:  Bs276.49
COP$389,392.21
List Price:  COP$545,164.67
You save:  COP$155,772.46
₡50,975.09
List Price:  ₡71,367.17
You save:  ₡20,392.07
L2,469.97
List Price:  L3,458.06
You save:  L988.09
₲730,103.58
List Price:  ₲1,022,174.22
You save:  ₲292,070.64
$U3,841.23
List Price:  $U5,377.88
You save:  $U1,536.64
zł395.04
List Price:  zł553.07
You save:  zł158.03
Already have an account? Log In

Transcript

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

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