Welcome to Highline Excel 2013 video number six.
If you want to download this workbook for week two, click on the link below the video.
In week two, we're talking all about formulas and functions in cell references and formula inputs.
So here we built this budget last video.
I can see very clearly I'm using these percentages, these are expenses as a percent of revenue,
and I'm using all these formula inputs in this whole table, so any intersecting cell here, I can see I'm pointing down here.
Now why, for this calculation right here, why am I pointing down to this 22.5, instead of actually hard coding it right into the formula?
Because I can do "What if" analysis.
So what if, in the budget meeting, wage expense is not 22, its 27%,
and as soon as I type this here, the whole table will update instantly.
So, “What if” analysis, that's why excel was invented. Right?
Now here we have a budget, I just did CTRL+Z,
and this is one set of assumptions, but I may have one or two or three sets of assumptions
and instead of having to go through and change each individual one, when I'm having discussions in my budget meetings,
we can simply save a set of formula inputs using the “Scenario Manager”,
save a couple different sets and then easily change between the different sets.
So, I'm going to highlight this range here and save this as a set,
up on the data ribbon, “What if” analysis,
“Scenario Manager”
there are no scenarios,
I'm going to click “Add”,
because I've highlighted the cells, it has the changing cells right here,
And I'm going to give it something like Set01, you can call it Budget Set One or something like that but I'm going to call it Set01,
Click OK.
You have the opportunity to change it right here in this dialog box, I usually don't do that, I'm going to click OK.
So we have a set here, now watch this, I'm going to come here and I'm going to change these,
so this is now 27, and this is six, this is four, 27, three, and eight.
This is a completely different set of assumptions, I'm going to highlight and actually, if you do this a lot, there's a keyboard for it.
Now we learned about ALT keyboards, so if I hit Alt, I can see that data is "A",
“What if” is "W",
and "S" is “Scenario’.
Now notice that's three keys.
If you do this all the time, you can use the old 2003 ALT keyboard,
it used to be tools, drop down menu and then E for scenario.
Now with the range highlighted, Alt+T+E
And I'm going to say "Add" and because I have some new numbers there, I'm simple going to type Set02.
Click OK.
So now I have two sets and I can switch back and forth between them.
So I can say show and instantly I show there,
Show.
And we'll see how to add a button here in just a moment.
Now another cool trick, what if we have some percentages and we just wanted to increase them or decrease them by some amount?
Well, one point one,
if I multiplied 1.1 times each one of these, it would increase that percentage by 10%.
Well, there's a great trick, it's a "Paste special" trick.
If we copy this cell,
highlight these numbers and go up to the home,
“Paste” button drop down,
and "Paste Special" down here,
there's this thing called "Operation",
and whatever you have copied, you could add it, subtract it, multiply and divide for all of the numbers highlighted.
So guess what?
We're going to multiply.
Now it will actually change the numbers here,
one problem though, is if we do it right now, because we're pasting something,
it would replace all the formatting, so I'm going to say do values only.
So on the top for "Paste", "Values", from the "Operation", "Multiply" and click OK.
And just like that, we have increased all of our percentage by 10%.
Now we can say this is as a scenario,
ALT+T+E,
ALT+A,
and then I'm going to type SET03, click OK
click OK,
click Close.
Now I'm going to delete this, we don't need this here.
We would like to add a button up here, so we don't always have to go up to that "Data" Ribbon, "Scenarios",
which is fine, it's certainly easier than typing everything out,
but now let's go to the top, right click, "Customize Quick Access Toolbar"
We're going to go to, not "Popular", but "All Commands".
Click in somewhere in the list and type "S" and we want to go down and find "Scenarios"
So I'm going to put this "Scenario", "Scenario Manager" will get you the “Scenario Manager” dialog box,
but I'm going to click this "Scenario" button, then "Add",
click OK.
And so now right up here,
I have SET01,
SET02,
SET03.
"Scenario" to say formula inputs, totally awesome,
even “Add” a button and also our "Paste Special" to increase a number of budget percentages by a certain percentage.
Alright, we'll see you next video.