Highline Excel Class 22: Budgets, Scenarios & Scenarios Report

Download Excel file: http://people.highline.edu/mgirvin/ex...

Create a Budget with Formula Inputs in an assumption area and formulas. Then do what if analysis by saving and showing scenarios. Save Variables for a Budget with Scenario Manager. Create Scenario Report based on a Result cell. Add Scenarios button to the Quick Access Toolbar QAT.
See other What If Analysis videos:
Excel Magic Trick 253: Data Table 1 Variable What If Analysis
Excel Magic Trick # 254: Data Table Creates 100 Formulas
Excel Magic Trick 255: Data Table 2 Variable What If Analysis
YTLE#103: Excel Goal Seek
Highline Excel Class 22: Scenarios & Scenarios Report
This is a beginning to advanced Excel class taught at Highline Community College by Mike Gel excelisfun Girvin Busn 214 BTech 109

---

Closed Caption:

welcome to highline excel class number
22
if you want to download this workbook
and follow along click on my youtube
channel that click on my college website
link and you can download the workbook
week for business 214 here in the class
just go to our week for website in this
video we want to talk about assumption
tables there that have formula inputs
how to build some formulas how to run
scenario manager or how to use scenario
manager which will help us store
multiple variables how to create a
scenario report and how to add a button
to our quack for our scenarios
now here's the setup we have a little
budget here and it goes over here always
there are assumptions we're going to
have revenue start their revenue
increase who that is too high this point
015 how about that 1.5% and then some
expenses as a percentage of revenue so
what we want to do is build our formulas
hate for january sales i'm gonna say
equals that one right here and I'm going
to hit tab and this month
februari needs to look back to January's
months which is the month before and
increase by 1.5% 2.0 15 and so we need a
formula for all the way across equals
one cell to my left times and then in
parentheses 1 plus this rate right here
now i need to lock this going across the
columns since I copying it across come
so i hit my f-14 till the dollar signs
in front of the sea
close parentheses what's nice about the
one plus percentage increase or rate
increase is that the one will retain the
500 and this will get whatever the
actual increases and then add them
together in that formula control-enter
and i'm going to drag it all the way
across almost all the way to the top
before the totals now our expenses
notice the orientation of the labels and
our table are vertical their orientated
vertically in the assumption table and
that's the case then you can use mixed
cell references and you don't have to
create four separate formulas i'm going
to highlight the whole range
in advance and i'm going to create a
formula in the active cell that active
cell right there will to take whatever
formula were creating and when we
control enter it will populate all the
cells so you ready equals hey whatever
the sales are for this month and that
needs to be able to move as it goes to
success of months but as a copies down
we need to lock it so since we're
talking across the rows I'm gonna hit
the f4 key dollar sign in front of the
number four row reference but it's not
in front of the sea so it moves as it
goes across the columns and that's going
to be x and here's our rate down here so
for this entire row we need to use this
11.11 but as we copy down the next one
we need to have this green cell right
here move on down so we want it locked
across the columns will come across the
columns are not across the road so I hit
a four dollar sign stops in from the
column but not the RO that's it and we
could uh we're not really required to
use around here like we've talked about
in earlier videos in this class because
this is a budget this isn't some exact
to the penny like payroll taxes we are
actually subtracting pennies
this is an estimate so we don't need the
round here all right now our net income
formula you know we could do our same
trick right there
notice what I did there did that really
quickly do that again i had the formula
right here and then I held control and
tapped enter and that populate all the
cells with the formula I'm gonna go
check out here so you've got the right
one
diagonally furthest one away i hit f2
edit mode and sure enough it got the
october sales and it's got the group for
now our formula here for net income will
be equals whatever 1255 cells above
minus the sum of these four cells right
above me that formula has all relative
cell references and will work
as we copy it across
now i'm going to highlight here and use
my keyboard shortcut alt equals four
autosum I'm immediately gonna hit f2 to
see if it got it right
looks like it did all these relative
ones to the left escape or ctrl enter
now here's the cool thing we've got all
our formulas now we want to come down
here why do we build our formula inputs
like this because of course the original
guys who invented especially that was
their idea
brooklyn and franksen you are building
your formula so then you can just come
here hey our revenue increases not going
to be 1.5% each month when we point 0 0
9 so 9 tenths of a percent and so
instantly we hit enter and all of these
numbers update now or we could go back
and forth on this but this is a budget
so a lot of times you have one two three
scenarios and you want to save all the
variables so this is going to be our
first scenario here so what's this snow
means well then have 500 points 09 etc
here but then we'll change then we'll
have like a 600 1.5 of etc watches these
labels these are the formula inputs you
highlight them and to memorize them so
you can come back to them later you
scenarios now scenarios in this version
is data and then what if in fact let me
make this a little bit bigger here data
whatever even a little bit bigger right
there should doubt not quite data no I
can't get it anyway so there it is it's
it's if you have full screen that's a
bigger icon scenario manager in 2003 you
have to go to tools and then scenario
manager and all it wants is what i
already have a set there i'm going to
delete these when you download this it
won't you won't have those you would see
this and you click Add by the fact that
we've highlighted these put it wants a
name of us a set 1 set 1 by there's the
cells from be 18 all the way to see 23
and then click OK
now you some people come here and edit
them here i usually just change them in
the cells and but you can actually come
up here and change them and add new
scenarios from here I'm going to click
ok i'm going to click close now i'm
going to change this that's gonna be 600
is going to be point zero one five this
is going to be a point 12 this is going
to be point 06 5.07 and this one is
point 10 a dramatically different set of
inputs there so now i'm going to
highlight this and go back up here and
just click Add set to your absolutely
can use this dialog box to Adam was like
to go back to the cells click ok and so
now we have that second set but oh how
do we get back to the other set because
I thought we were memorizing you go back
up here scenario manager and then you
just click and show and sure enough it
remember some now I want to do one more
this will be a thousand point 0 2 this
will be point 15.7 point 06 point oh
eight right so I'm gonna go back up
there and I'm going to add click Add so
now i'm going to call this set 3 set 3
click ok click ok now we have a 1 2 3
i'm gonna click show its you you can
always come back here and click
whichever one you want and then show i'm
going to close this i will show you how
to add a button and then i'm gonna show
you how to create a report from those
three scenarios
here's the quiet the quick access
toolbar and i want to add a snare about
right click and customized quick access
toolbar now you get this
it's automatically there be sure and
select in
choose commands from you want all and
this is great because you can pick any
button you want and add it i'm going to
hit type-s to jump down to the esses and
it should be down here scenario not
scenario manager but this one right here
is going to add a great button where
I'll make it easy for us to change
scenarios
click Add it's added over here and then
at the very bottom i'm going to click OK
now watch this
I simply go to set to and it changes it
set one it changes it i absolutely love
that that is just awesome
one last thing is since our bottom line
is this total net income for this period
here you can create a report that will
show you the three scenarios and how
this variable changes opes you can see
that over here talking to myself right
here and this is the total so we can
create a report and show how this number
changes as the assumptions change our
set 123
so we're going to go back up to data
what if scenario manager and I'm going
to Slick's a summary summary it's going
to ask me I want a scenario something
it's going to which cell i'm going to
click right there because that's the
that's the bottom line and will show us
the three scenarios and how that changes
i'm gonna click OK title insert a new
sheet right there and other set 1 set 2
set 3
here's the current values and then these
and it shows you right down at the
bottom how this whatever was in m15
changes from one scenario to the next
absolutely amazing
ok scenario scenario scenario manager
how to add a button and the report all
right in the next for this class we're
going to talk about data tables but it's
the video is not going to be called a
Highline number 23 you're gonna go look
at magic trick to 53 54 55 and I put the
sheets here so you just watch the video
and the sheets are here they'll also be
in our playlist so there you don't have
to go searching for him and then
four-goal see
this topic of a great video called
youtubers love excel 103 so the sheet
here and then you can either search for
that video youtubers of Excel number one
or three goal seek or you can search for
match XL magic 253 254 255 and also if
you download this workbook i'll have the
link up here for those topics
alright we'll see you next video for
importing data after you watch all these
other ones