What-If Analysis

Quick reference

• 00:04 What-if Analysis in Excel is built around the concept of where we've created a model of some kind
• 00:11 and then we're looking at trying to run different scenarios through it to see how things actually react to changes in that model.
• 00:18 So if we look at the dataset that we have here we've got some Discharges,
• 00:22 we've got Charges that were covered. The Total Medical Payments and we've got an average payment.
• 00:27 The average payment is set up by dividing these two cells together. So one of the scenarios we may want to look at is where we were to change
• 00:35 either the Total Medical Payments or the Total Discharges and see what effects it might have on
• 00:40 the actual Average Payment. So we have a couple of tools we can use to actually do this. The first one
• 00:46 is the Scenario Manager. So that is under What- if Analysis on the Data tab
• 00:52 And we go into the Scenario Manager.
• 00:55 Now right now we have no scenarios defined. I would always suggest before you actually go and start using these things in earnest that you
• 01:03 actually create a Base Case scenario that allows you to get back to where you were. So we're going to start with Base Case
• 01:10 and it's going to say what cells would you like to change? I'm going to change C4 Total
• 01:16 Discharges for this particular procedure. It tells me that I've created this and say OK.
• 01:23 And it'll ask me what value I want. I'm going to set it to just the value inside the cell right now say OK.
• 01:28 So this will always give me my Base Case that I can return to.
• 01:32 Now I'm going to go and say you know what? Let's try another one here. Let's try and change this
• 01:37 For an increase of say 50. So we'll call this one Increase
• 01:44 DRG039
• 01:50 to 79 Discharges. That's what we'll call this particular scenario
• 01:55 that we're looking at. Something that gives me a real good clue later when I'm looking back at it.
• 02:00 I am certainly going to change this particular cell
• 02:04 and say OK
• 02:07 and then what value do I want 79
• 02:09 and say OK.
• 02:11 And nothing happens at this point until I click Show and what happens when I click Show is this
• 02:16 value will update as will the formula that it's based off of.
• 02:21 This is the reason why I say that you need your Base Case because if I close this right now
• 02:26 it's going to stay like that. So I can always roll back right now to my Base Case and say Show and it'll return to what I had beforehand.
• 02:34 So that's using the Scenario Manager. You can create all kinds of diferent scenarios for
• 02:39 one by one line changes and whatnot but remember to always have that Base Case there.
• 02:43 Now another tool that we have is Goal Seek and Goal Seek is really neat
• 02:47 kind of stuff because you can actually run scenarios through formulas to see what actually ends up happening.
• 02:52 Let's say for example that maybe for some reason somebody is looking at these particular billings and says you know what
• 02:59 this number is way too high. We want to try and set a target that makes this particular operation a lot more affordable to people because its such
• 03:05 an important one we want to to get that down to 850 that's our target.
• 03:10 So how do we do that? Well, we know that because it's based off of these two cells we can
• 03:15 start toggling either one of these things to try and come up with what it is. So
• 03:20 some users might say ok well if I change this up to 20 what happens to it or
• 03:26 if I go to 25 where does it get to. So that's one way of doing it. I'm just going to back those off.
• 03:31 The other way, the faster way
• 03:33 is to go into our What-if Analysis and go to Goal Seek
• 03:38 and what Goal Seek says is I'm going to set the cell you choose to a specific value.
• 03:44 So we're going to set this cell
• 03:47 F11 we're going to change it to the value of 850.
• 03:51 What cell would we like to change in order to make that happen? This is part of the formula that refers to E11 and C11. Which
• 03:59 one would we like to change? Well let's see if we can change the medical payments
• 04:04 to get the average payment down what would we actually need to do to those payments?
• 04:09 So this is what our completed Goal Seek scenario looks like. Set cell F11 to value 850
• 04:14 by changing cell E11 and we say OK.
• 04:19 And as you can see it spins through, it makes some modifications and it basically does all the
• 04:23 math of increasing or decreasing to try and get it to the right area so
• 04:27 that's how to use Goal Seeking in Excel; a really useful and actually quite under used tool for sure.

Lesson notes are only available for subscribers.