Transcripts
1. Introduction: Hi there. My name is Al, and I'm a solutions
architect at Coda, but I've also been an Excel
power user for many years. I first started using excel at an internal finance
team at Google. I'm really excited to teach this class to share with
you some advanced features, and functionality in
Excel that will help you, and your teammates
be more productive, analyze data better,
and ultimately make better business decisions. Today's class is all about how to use some
advanced features, and functionality in Excel. These features
include offsets, NPV, and the Goal Seek
feature to help you find the most optimum
input for your model. Now, this is an advanced course, but I'm going to walk you
through all these features step-by-step so you can
apply them to your models. I see a lot of teams struggle, and make bad business
decisions or investment decisions when they don't use some of
these features. They tried to put together
a formula in an ad hoc way. While many Excel classes teach you about how to
be more productive, and how to automate tasks. This class is all about how
to think more analytically, and critically about your data. The key takeaway I
want you to walk away with is how to use
these functions, and formulas to make better
investment decisions. Whether you work in finance, marketing or another
field where you have to evaluate
multiple opportunities. There's a lot of
dollars or a lot of resources on the line. You'll walk away with
the skills you'll need to help your team
think more critically, and analytically to make
better business decisions. For the class project, you'll pretend to be an analyst
at a big online retailer. Your goal is to help this
online retailer figure out what new product
lines to invest in, given historical revenue data. You'll also try to predict future revenue
using growth rates, and also using Goal Seek, and advanced feature
in Excel to find the most optimal
growth rate given the constraints in the model that you'll eventually built. I really hope that you'll post your questions in the
discussion forum of the class, and also post screenshots, and questions about
your class project in the project
section of the class. Now let's go ahead, and
get started with Lesson 1.
2. Use OFFSET W/ AVG & SUM: This lesson is all about how to use the offset function in combination with the average
and the sum functions. This is a really
useful function, especially for time-series data, because it allows you
to quickly sum up or average data across months, quarters, or years
to quickly share this data with your teammates
and your colleagues. We're going to write a basic
offset function in Step 1, where we're going to reference
the first month of new MRR in cell C25 in this table below and
we're going to offset this cell by two rows
and three columns. It is a really, really
basic offset function. Before we get into
doing this up, let's just quickly
take a look at our simple growth model here. We have revenue and
customer growth here with a few assumptions. We also have a monthly
recurring revenue here for new MRR, lost MRR, the growth rate. We won't be using all these
different metrics but just giving you an idea
of what this looks like. Let's start writing
this offset function. We're going to start in
cell C25 so equals offset. Now the offset function can take up to an optional
number of arguments, five arguments in total. In this case, we're
only going to use the first three
reference rows and columns. Heightened width
we'll use later. But for now, let's go to offset. We're going to click on our
reference, which is C25. We're going to offset it by
two rows and three columns. Two rows, see how this row
is the second parameter. Then three columns
and hit "Enter". Let's look at what this does. Ten thousand four hundred and
forty-five is the number. Remember we offset
this by two rows and three columns starting in C25. A basic offset
function like this, all it does is it goes
down two rows, 1, 2, and then three columns
to the right, 1, 2, 3. That's how we get to 10,445
in this offset function. You can also use
negative numbers here. If this was negative two. This will go from
2,373 up two rows, and negative columns will go backwards as you might imagine. This is a basic way of
using offset functions, but the offset function
really comes into play when you're building some more advanced models
and you want to summarize the data or average data depending on what
your needs are. Let's go to number
two. We're going to write an offset
function below, that uses the starting month
and ending month inputs, these two cells right
here, to sum up, the new MRR revenue for
a specific time period. We want to give our
teammates the ability to enter in different
number of months here. This could be one for January, two for February, and notice how
these month numbers are along the top right here. If it's 3-6, we want to sum up all
the new MRR revenue from March through June. This sum should be basically 3-6 right here and the
total should be $14,482. Let's go ahead and
start writing this offset function right now. We're going to
start using a more advanced use case of
the offset function. Offset, we're going to
start with the reference, which is C25 again. Now, do we want to
move this reference around by any rows or columns? In this case, no, we want
to stay on this cell. That's where we're going to
put comma zero, comma zero. We tell Excel, we don't want
to shift this reference around by any rows
or any columns. Now the height and
width is telling Excel how big of a selection, what's the range that you want to put inside this
offset function? Let's test, for
example, if I put one as the height and
five as the width, let's see what happens
as an example. Nothing happens, but what
we're telling Excel to do, is take everything
from cell C25 and take a width of five cells, this will be 1, 2, 3, 4, 5. What does that mean? This means that right now this formula, it says that there's an error, but that's because it's
returning the reference of these five cells. If I add a sum around
this offset function, it's going to give me the sum of these five cells; 1, 2, 3, 4, 5, 15,279, and
matches 15,279. You can see how we can construct this offset function to put inside or nest within a sum function or
an average function or whatever it is that you want. Let's build this offset
function to take into account these starting months
and ending months. I'm going to delete the
sum function for now. Let's just zoom in a little bit. Instead of putting 0015 here, we want to tell Excel if there is a starting month
that's greater than one, so maybe it's starting
March in this case, we want to actually shift over the columns by a certain amount. Instead of this zero for
the columns parameter, I'm going to select
the starting month and then put minus one. The reason is because
if this is three, this will start from
C25 and we'll go 1, 2, 3, and notice how this would put the reference in cell column F, which is Month 4. We actually will want to take the starting month that our teammates and colleagues
enter into the cell, subtract one, so that
this becomes number two. This will be starting
from C25 will go to 1, 2, and properly
start in Month 3. We've shifted over to a
recap to start in C25. We're shifting down
zeros and we're shifting over to three columns, three months in this case, since we're entering
the number three here, the height is how wide or what's the height of
the range we want to use. In this case, we want
to stay on Row 25. Here is the last parameter,
which is the width. How do we tell Excel basically
we want from cells 3-6? We can take this
ending month number that our colleagues enter in. All we can do is take 6
minus 3 and then plus 1. The reason why we
want to do that is because the width has to include the first
and ending cells. What that looks like is this. This part of the parameter
equals to 6 minus 3, which is 3 plus 1 which is 4. What Excel is going
to do is start in cell E25 and then
go over 1, 2, 3. This is the width, the four
that we just talked about, which is the C11
minus C10 plus 1. Now that we have our offset
function written correctly, we can not put a
sum around this. Now we have 14,482. If I take three
through six remember starting month is three
ending month is six, 1,442 I change this to a one, this will now be
one through six. This is now 19,536. If I change this from a
seven through twelve, this will take from July
through December and 36,991. This offset function
is really interesting because it can return you a given range of cells depending on how you
enter in the parameters here. Then once that
range is returned, you can then put a sum function around this or an average
and that's what we did here in this nested
sum offset combination. Let's build another
variation of the sum and offset function where
we're going to sum up a new MRR revenue
starting Month 1. We are always going to start
in Month 1 right here. Then we're going to set
the ending amount or the ending month as whatever
you entered in step two. We're going to go
to any month here. Let's move this back to six for instance, and
this would be one. Essentially we want
to tell Excel is if my colleague
enters in a six here, I want to take everything
from one through six. If my colleague enters
in a seven here, I want to take everything
from Month 1 through Month 7. But we're going to build this in a different way by writing the sum function first and putting the reference
to the first cell, which is C25 because we're
starting in Month 1, column how do we tell
Excel which cell to go to, depending on what my colleague enters into cell C11
the ending month. Normally you might just put C25, then put something like that
and have a simple reference. But in order to
reference that cell, we're going to write
an offset function. I didn't write offset
correctly. We're going to start with C25 yet again. Then, how many rows
do we want to offset? We don't want to
go any rows below, so we're going to
put a zero there. Then how many columns
do we want to offset starting from new MRR? That's going to be
the ending month. We're going to put
the ending month. This will shift
things over by 1, 2, 3, 4, 5, 6, 7. This puts us in a Month 8, so we actually want to do a
minus one here to account for that extra movement
over on the cells. Now if I put a closing
parenthesis around this, this gives me 24,262. If I sum up Month 1 through Month 7 here so Month 1 through Month 7, 24,262, 24,262. This is just a different
way of building out this sum function
using offset as the second reference cell because we're telling
Excel starting in C25 move over to the right, a given number of columns, in this case, 7 minus 1. This part of the
function actually returns back to
the sum function, this cell right here. We're telling Excel to sum
everything up from C25 through H25 using
this offset function to reference the cell. Practice using these two
versions of the offset function. Typically you'll
use this version of the offset function
with the sum function more often because you're typically trying some different
parts of your model up. This one could be
interesting if you have your team as provided
inputs into where they want to sum up data
or average data. But this is a little
hard to debug, whereas this one is a
typical nested function where you have offset
within the sum function.
3. Pick Scenarios Using OFFSET: We will continue to use
the offset function, but this time for a different
use case scenario analysis. You'll typically
use a function like the choose function when you're
doing scenario analysis, but the offset function has some advantages because your
teammates and colleagues can add new conditions or new assumptions to the
scenario analysis. The offset function will
automatically pick up these new scenarios without you having to adjust
the formulas. You can automate your modeling a little bit with this tip. We're going to continue using this SaaS growth model we
started using in lesson one. But notice how the
new customers line, this is now missing. We don't have any data except for the first month
of January 2020, lesson one, we had some
hard coded numbers here, but in lesson two, we're going to use
the offset function to help our teammates select different scenarios using
different growth rates to predict with a new
customer amounts will be. This is really common method you use when you're
building out a model, and for just to quickly understand what
we're going to do here, we're going to give
our teammates ability to enter the numbers 1, 2 or 3 here. Depending on what
option they pick, this will correspond to different growth
rate assumptions. If they picked one, this will
correspond to 10 percent. They pick three, this would
correspond to 20 percent. Then these numbers will grow depending on what
growth rate is entered here. Let's start with step one, and we're going to write
a basic choose function starting in cell D22,
which is right here. We're going to take into account the growth rate that's selected based on
what our teammates enter into the
scenario in cell C8. Let's us go off over
here really quickly and figure out how the
choose function works. I write equals choose. It's a very basic function. All it does is takes in a
few different parameters. The first one is
what cell contains the number you're going to choose or the index
you are going to choose. That's going to be our enter
scenario cell which is C8. Then in value 1,2,3, notice how these are
all optional arguments. I'm going to select D12, then D13, then D14. Hit "Enter". I'm just going to paste this
formatting over here. Notice how this
corresponds to 20 percent, because we're telling Excel is based on what number
is entered in here, the index number, take that number from the options I've provided in
values 1,2, and 3. Since this is three,
it takes D14. If I pick one, it gives
me the 10 percent because that's the first value it
might choose function. Two of course, will
be 15 percent. Now that we know how the
choose function works, we can use this choose
function in the formula to predict new customers
in future months in 2020. All I'm going to
do is write equals the previous month times
parentheses one plus. Let's write the function
one more time the show. We're going to choose based
on what's indicated here by our teammates and then
provide the different rates. The growth rates fall by commas. Then that's it. But we're actually going
to want the I want to make sure we do here is lock-in. That's all references
because we're going to drag this formula over to the right. Notice how this is 39 percent growing by the
second growth rate assumption, which is 15 percent. Let's just just drag
this over to the right. You'll see how the
numbers proportionately increase based on what
growth rates are entered. I put a one here, it'll grow less fast. I put a three here grows more. That's how we can use the
choose function to do a quick basic scenario analysis in our model and
gives our teammates ability to change
the assumptions here that affects the rest of the model for
MRR and our model. In step two, let's add two more growth rate assumptions to the customer scenarios. Let's just do the function
we wrote in D22 accordingly. Let's say we want to add
in some new assumptions, growth rate, assumption
number four. This will be option number four. Let's say this is the worst case scenario we are
going to put five percent. Let's do another one. I'm just going to just copy this
and change the number. This will be the most
optimistic scenario, which is like 25 percent. Let me just copy
the formatting down here to these numbers. How do we take into account these new assumptions
because our view, recall, our original function only takes into
account these three. Anytime you add new assumptions, you'll then have to go back
into your choose function and put in the additional
options. I have four. I make that as reference. Then I have this fifth
one. Is it this one? Let me do that again. The fifth option is D16. Lock that in and then
like I did before, I have to drag this all the way to the right to
make sure that this accounts for the new rates
or new growth rates. If I put four here, this will be five, this
broke. That's fine. But you can see how if you
have a really complex model, you will have to
constantly adjust the choose function
here and then drag those formulas out if you have new growth
rate assumptions. Similarly, if you delete new
growth rate assumptions, this won't work anymore. I think there's actually
just defaults to zero. Notice this is all three fours. Doesn't really handle adding and deleting growth or
assumptions here in your model which makes scenario analysis really hard because
you have to constantly debug your formulas to account for these new
growth or assumptions. For step 3, we're going to use the offset function
one more time. Instead of using the
choose function let's use the offset function and D22 that picks the correct
growth or assumption. The best part about
this is it won't matter if we add or remove growth or
assumptions to our list. Instead of using choose, I'm going to delete all
this and write offset. I'm going to start in cell D11. If you recall how the
offset function works, we have a reference here. We're going to lock this
reference in for now. Now how many rows do I
want to offset this by? Well, we know that
our teammates are going to be entering that
scenario number here. I can simply use this input as the number
of rows to offset, I I want to lock this in so it doesn't move
when I drag this over. If this is five, this will
go from rate 1, 2, 3, 4, 5. If it's one that will
go just down one. Number of columns
I want to offset over this will be zero, and this should just work. I think I might have got
a closing parentheses. In this case, if I put one here, I need to drag this
over to the right. Look here, how if I put
in number one here, I put a two here, three, it does the same thing
as the choose function. But the cool thing here is if I automatically add in a
new growth or assumption, just copy and paste this again. Make this a five. This
will be 25 percent. I don't have to readjust
this offset function again because it
already knows a look at this and offset my
growth rate assumption by however many cells
starting in D11. If I put five here, it already picks
up this new cell. Because what, again, just to
recap what this is doing, it's offsetting from D11, going down five rows, 1, 2, 3, 4, 5, no columns. It returns that growth
rate back into my formula. Now I can quickly
add assumptions. I can delete assumptions. Well deleting would also not really work because I have to, this will just be
defaulting to zero. But the cool thing is I can add new growth rate assumptions and the beauty of the offset
function is that it would pick up those new
growth rates depending on what my teammates or
colleagues enter into cell C8.
4. Make Decisions W/ IRR & NPV: IRR and NPV stand for internal rate of return
and net present value. These are functions
that are really important for you and
your teammates to learn because they can help you evaluate different
investments or business opportunities
given certain risks and profitability of
these opportunities. This will help you create a business case to
bring to management or to whoever on which
opportunity to pursue. Now these functions are
actually pretty easy to use, but knowing these scenarios and the specific
environment in which to use them is going
to be important. I'll talk about all
that in this lesson. The goal of this
lesson is to teach you some formulas and
frameworks to help you and your teammates
figure out how to evaluate different
investment decisions. Especially if you're looking on investing in real
estate or machinery. This is all about how to use formulas to help you guys
collaborate and figure out how to make a sound
business decisions. Before we get into the
steps here for this lesson, let's quickly take a look
at some of the assumptions in our fictitious real
estate model here. We have some
assumptions here on. We're basically looking at a
real estate investment and this specific real
estate property has a thousand square feet. The rent per square foot of $50. The OpEx is $25 per square foot. The purchase price
of $10 million. We have a few other growth rates here for income expense and
also the expected cap rate. We have a very abbreviated
income statement which shows our gross
revenue every year. Starting in Year 1 and all
the way through Year 11. This number simply grows by that growth rate
we have here in the income operating expenses is the square feet multiplied by the OpEx per square foot. This also grows at a
fixed rate every year, which gives us our net
operating income in Row 32. This is our income statement, and down here is our
statement of cash flows. Again very basic
in before Year 1. Before we generate
any cash flow. Our first outlay of cash is
our purchase in Year 0 so it's negative $10 million
for this investment. We are pushing this
property and then our cash flow from operations
is simply the NOI, the net operating
income every year starting in Year 1, Year 2, etc. Then the net cash is
simply the net amount. Now you'll notice here that
in Year 10 we have our NOI, but we're missing the cash we get from selling the actual
property because we want to see this investment is worth making a seller property to
hopefully make a profit, and this number is missing. Our goal is to figure
out what that number is and also to figure out some other formulas and metrics to figure out
whether or not we should invest in this property versus some other property in
this real estate example. In Step 2, the cash flow from sale in the
casual segment is missing and we're going to
assume that the property that's $10 million property
is sold in Year 10. Calculate this value by dividing the NOI itself by the cap rate. We have a cap rate assumption
which is 10 percent. All we need to do here
for us to calculate the cash flow from the sale
of this property after 10 years of holding onto it and getting the cash flows is taking the net operating income
at the end of Year 10, which is this number,
2.392 million. Then we're simply going
to divide that by the cap rate to get the
expected sale price. We have an estimated sale
price of $24 million. It's rounded up. That's assuming that we get these expected cash flows
and they grow every year and that by the end of Year 10 this is already going to be our expected net
operating income, which becomes our cash
flows for Year 11. If we were to hold onto the
property, in this case, we're selling the property
at the end of Year 10. Our cash from the
seller's property is going to be equivalent to this number which
you calculated, which is dependent
on the cap rate. If you look at our cash flows over the period of the 10 years. We have negative 10 million for the first purchase
of the property. We have then cash flows from Year 1 through 10 and
at the end of Year 10, we sell this property
for $24 million or cash. At the end of Year
10 is $26 million. It seems like a pretty
good investment. Let's take a look at what the
internal rate of return is. Now that we have a complete
cash flow statement. Again, this is a very
simple cash flow statement, but this is more to
show the power of using these formulas
to help evaluate different investment and
business opportunities that your company
might be facing. We're going to calculate the
internal rate of return on this investment by simply doing equals IRR, or internal
rate of return. I can literally just
give all the values from my net operating income. Each of these values
is going to be plugged into this IRR equation. It will spit back out the
discount rate at which my net present value of
my cash flow is zero. Let's once again equals IRR. I just realized we are
using the wrong numbers. We should actually be
including the net outflow, which is the 10 million. Let's include all this data. There we have the internal rate
of return is 20.7 percent. I forgot that you can't just use the yearly annual cash flows. We have to include
the initial outlay, which is $10 million, as
well as the final sale. I was using the net
operating income by accident and should
be using the net cash. This is the internal
rate of return on this project assuming
and this again, the IRR, I'm not going
to get too deep into what internal rate of return is. But this is a number that
you can compare across your various investment
opportunities to see whether or not this
is a good investment. If I found another
investment that had a 25 percent IRR then I would obviously go with that
investment over this one, because this is the
discount rate needed to have the net present value of
our cash flows to be zero. Now here, I just talked a little bit about
net present value. Let's take a little bit into
what net present value is and how we can calculate that
by using the NPV function. We're going to double check that this net present value of this number using this IRR is
zero by using NPV function. I'm just going to
write equals NPV and for the first parameter I need to input
the discount rate, [NOISE] which is the
internal rate of return, which I just calculated
so 20.7 percent, then similar to
the IRR function, I have to provide
a bunch of values. Now, [NOISE] I'm first
going to just select all the cash flows from
Year 1 through Year 10. I'll show you what this
looks like after we do it. Notice how this equals the net present value
of these cash flows. Assuming a discount rate of
20.7 percent is $10 million. In reality, in
order to check that this NPV is zero for this rate. You would actually also input in the initial outflow of cash, which is this
negative 10 million. As you can imagine, this will become zero. This basically shows
that our IRR is correct. But it's important to know that the net present
value of all these cash flows should equal to your initial outlay, which is $10 million. That's why you'll see
this as 10 million, we don't include that
negative 10 million here in the purchase in Year 0. But if I do include it obviously it sets
everything to $0. Now what's interesting here is if you change this
discount rate, you'll notice how the
NPV will change as well. Remember how the
first parameter in this NPV function is the IRR. Let's say I decrease this
investment IRR to 15 percent. Now our net present value
has increased because we are looking at a
lower discount rate to discount our cash flows
to the net present value. This will not be the correct IRR for this project because our net present
value is greater than zero. Of course, if I
increase this to beyond the IRR to 30 percent, then the net present value of
the cash flows is negative. Since again, the first
parameter we're using in the present value
is indeed the IRR. Typically when you're comparing
investment opportunities, you might use a combination of the IRR and also the
net present value to get the absolute dollar
amount for a given investment. Let's bring this back
to 20.7 percent. If I were comparing investments just based on their
internal rate of return, I will just look at this number. But sometimes when you're comparing different
opportunities, you might also want
to look at the absolute net present value of cash flows to get an idea of
how much cash is coming in. You might remove
this initial outlay to see what that total
net present value is, assuming various discount rates.
5. Search W/ Ease Using Wildcards: Sometimes you don't know
exactly what you want to sum up or lookup or
average in your dataset. That's when you can
use wildcards in Excel to do some fuzzy
searching for your data. You can use this lesson to build a fuzzy search algorithm for your teammates when they don't know exactly what
they're searching for, but they know a few letters or something in the name itself. Searching with
wildcards is one of the most underutilized
features in Excel. The reason why I like it
is because it really gives you and your teammates
a way to search for data but not knowing the exact spelling or order of the characters
in the list. I think it's a much
better way of looking for data and you want to set something up that's easy
for your teammates to use. Just an example, we're going
to count the number of addresses below that end
with the letters street, St, using a COUNTIF function. You'll see these
addresses below. Some of them end with south, some of them end with drive, Dr. We see that a
few that have St, St St St. You could do a variation of the
mid and find function, which we've done and which I've shown in
previous classes. But wildcards really make
this a much easier exercise. You can Google for all
different wildcards available. But I'm going to show
you the most common ones that I use when I
need to search for data and I don't know how the data is laid out
specifically in the list. We're going to write a
basic COUNTIF function, which we've all done before. We're just going to go through our range here, select that. The criteria here
is instead of doing equals some specific
value or whatever, or greater than or
less than some value, we're going to
write double quote, then the star St, double quote. We get the number four. You can quickly eyeball this
and see there's four addresses with the
letters street at the end. What's actually
happening here is Excel is going through
this entire list. This star basically tells Excel, I don't care what comes
before the letters St, as long as it ends
with the letters St. Then I want to count that
in my COUNTIF function. If St is somewhere in the beginning or in the
middle, it won't count it. This star basically tells
Excel any characters, any numbers, letters
before St, it's cool. I want to include it in
my COUNTIF function. You can see here if
I put in Rd here, it would only include this one specific address
with Buttermilk Road. If I put Dr. here, then it has only two addresses
that have Dr. at the end. The star one is one my favorite ones because it's like doing a fuzzy search on the list when you don't know exactly
how things are spelled. You don't know
what's going to come before the letters
or after letters, but you know that
without a doubt it has to end with St in
this case for street, because that's where
you're interested in, in terms of your dataset. Now we can also use
other functions using these wildcards. We're going to sum
up the number of sales for these customer IDs. We're only going to look
at the customers that have nine characters in
their customer ID using the SUMIF function. You'll notice that some of
these have 1,2,3,4,5,6,7,8, 9 characters, but
other ones have 10 characters because they might have an extra letter
in there customer ID. We only want to sum up the sales that have
customer IDs with nine. Maybe the customers that have nine customer IDs are like the old legacy customers,
something like that. One way you could
do this, of course, is we're going be doing a length of the cell. Knows how that's nine
and there's a few of 10. Then you can sum the customer IDs that have a specific number,
in this case nine. But with the wildcards again, I want to stress how
important it is to use wildcards in certain situations. Is that you don't have to do this extra
formula on the side. This question mark
wildcard tells Excel only include cells that have
exactly that number of characters in the cells. I'm going to write SUMIF. The range is going to be
this list of customer IDs. Did that by accident. Now the criteria is going
to be question marks. Again, you put the number of question marks you
want to tell Excel, here are the exact
number of characters in the cell that I want to
include in my criteria. We're going to do
1,2,3,4,5,6,7,8, 9. This tells Excel only pull back the values that
have nine characters, not 10, not eight, just nine, comma. The sum range would just be
the sales and we have 14,914. You could double-check this by, notice how we did the
count a over here. We could do equals SUMIF, just doing this
double-check our work. SUMIF if this is
equivalent to nine, and then sum this up, we get the same number, 14,914. Using a combination of the question mark wildcard
and the star wildcard, this allows you to do some
pretty powerful searching across your data. I'll just Google to see some of the top wildcards that you
can use and the combinations. There are some really cool
combinations you can do. But using a combination of the star and question mark
wildcards are probably the most common ones that
I use to search my data. To show you how this
might work for your team, we have our same data and now we have address plus
customer plus sales. We're going to do a
fuzzy search to VLOOKUP. This is how you might actually do something for your teammates, which is your teammates only know the street name
in the address. They want to be able to
quickly type in a street name and see what the number of sales are for that
given street names. They only know the
word Buttermilk, or they only know
the word Ross Clark, or they only know
the word Montgomery. But they don't know
the address number or the suffix which is street, drives, circle or whatever. We're going to write a
fuzzy search allowing our teammates to enter
in something like they might on Google
where they don't know the complete search query and they want to get
back some smart results. An example might
be equals VLOOKUP. The VLOOKUP value here we're
going to do is double quote, star, Buttermilk,
star, double quote. We're going to look up
in this table array, which is just from here to here. We're going to pull back the
sales number, which is 3,0. Normally when you're
doing a VLOOKUP, you're always looking
up a specific value. You want to match through
a specific match. But in this case,
I'm telling Excel, I want to pull back any addresses that have the
word Buttermilk in them. It doesn't matter what
comes before or after. That's why I have star before the Buttermilk and
after the Buttermilk. This means I don't care
what the address number is, I don't care what
the road street is. I just want to know
which addresses have the word Buttermilk
in them and that comes out to 239 right here. This allows my teammates to
do a fuzzy search again, within my VLOOKUP formula, when they don't know
the exact address, which is 321 Buttermilk Road. Now you can make this a
little more flexible. I believe you can actually
make this as a reference and I think what you can do here is actually just do a star. Let's say we have the
word Buttermilk here. I think you can actually delete this and do star, that star. Maybe if l put the
quote around that. This may not work either. I have to look into
how you can do this. Maybe you have to do a ampersand with the cell reference.
That's how it will work. If I wanted to make this more of a flexible
search for my teammates, I might say something like
enter address, road name here. Then they can type
in things like they only know, Montgomery, which is this
address right here, and it pulls back 1562, maybe they only know Florence. It's like a more flexible
way of searching for stuff without having to know
the full address again. Play around with the
different wildcards. Google how you might be
able to combine them. But I would say the
question mark, the star, wildcards are the ones
that I use the most to do fuzzy search so that I can make my tools and models in Excel more
flexible for my teammates.
6. Find Optimum Inputs W/ Goal Seek: [MUSIC] When you have a model
with multiple inputs, sometimes you just want to tweak one input to maximize profit, minimize cost, or maybe find the break-even point within
your model or your business. The Goal Seek
function feature is perfect for this because
you can tell Excel to optimize one specific cell using one input in your model. In this lesson,
we'll pretend to be a ridesharing company like
Uber or Lyft to figure out what is the optimum
number of rides that we need to have in
our system to break-even. When you're building
out a model, sometimes you want
to be able to find the most optimum input
to maximize profit, to find the break-even
point without having to do a bunch of trial and error. We're going to look at
this fictitious model for how a ridesharing company, let's say like Uber or Lyft, might model out their profits. Let us try to understand
quickly what the D19, this total profit
number represents. We have a few assumptions here, the number of rides that this ridesharing company
has to do in their market. Their take rate is how much of each ride the ridesharing
company takes from each fare. The total fair per
ride, lets say the average is 15 bucks. That means the take rate for this fictitious
company is simply 25 percent times
15 which is 3.75. Let's say they're
entering a new market, like a new city to launch their
ridesharing capabilities. They might have fixed
marketing costs of $100,000, and their cost per ride would be simply a total fare per
ride minus the take per ride which is their
total net profit. The total profit here
is simply 100,000, [NOISE] investment in
their marketing efforts plus their take rate, which is their
profit in each ride, multiply it by the number of
rides that are being done. You can see here if there's
only 100 rides then they have a net loss 99,625. This increases,
let's say to 2,000. Their net loss is a little less. What is the break-even
point for this example? Is it 5,000 rides? No, it's way too much. With little, is it 50,000?
That's a little too much. You can see how I
could do a bunch of trial and error to
figure out what this number should
be to get to a zero. Because I want to find
the break-even number of rides that will give this fictitious ridesharing
company a break-even of $0. We're adjusting the
number of rides here in D10 and we're adjusting
the total profit, but we want to be able figure
out the precise number of rides to get this to break-even. Instead of guessing
with trial and error, we're going to use this
function called the Goal Seek, which has a feature that you
can use for your models. You can go to the "Data" tab, go to "What-If-Analysis", and then go to "Goal Seek". This works when you only have to maximize or optimize
one specific variable. In this case, it's going
to be total profit. We're going to cell which is the cell or value we want
to optimize, which is D19. We want to set this
value to zero because that's going to be the point at which we're at break-even. How are we going to get to
this zero break-even point? By changing this input, which is number of rides. We're not going to
change the take rate or any of these other assumptions, just number of rides because this is how our
model is built out. Once you have these
parameters set, hit "Okay". You'll see that Excel quickly
comes up with the solution, which is 26,667
rides to be exact, to hit a break-even
point of $0 when we have a fixed marketing
cost of $100,000 and your take is 3.75 per ride. If you add one more here, you'll see how this
net profit now is $5, so it's a little too high. This is a real interesting
way of either trying to find the break-even point or perhaps you want to find
the total number of rides to reach $1
million in profits. We could try doing
that really quickly by going to "Data", "What-If-Analysis", "Goal Seek". We can set, again, the cell D19. Instead of setting it to zero, we'll set it to one million by changing the cell D10
which is number of rides. Let's see how many rides it takes to get to a
profit of $1 million, and that's going to
be 293,333 rides. When you're working with your team and trying
to collaborate to find an optimal solution, instead of doing
trial and error, you can use this
advanced data feature, which is Goal Seek, to find the optimal assumptions or inputs to maximize profits, to find break-even, and other metrics that you want
to achieve for your team.
7. Complex Optimizations W/ Solver: Building off of the previous
lesson where we used Goal Seek to optimize one
input and maximize one output, sometimes you'll have
more advanced use cases, and your teammates
tell you there are other constraints in your model that you have to account for. When you have multiple variables and multiple constraints
you need to take into account as you're finding the optimal inputs
for your model, you can use the advanced
solver feature in Excel, which is an ad-in. We're going to
pretend we're still this ride-sharing company, and we'll have
multiple constraints and multiple other variables to account for in order to maximize our profit
for our company. For more advanced
models where you have multiple constraints
you need to work under, and you want to maximize a
bunch of different inputs, you can't use the
Goal Seek feature, which we've talked
about on lesson five, you're going to have to use
a more advanced feature called Solver. First, before we get into using Solver on our model here, we're going to
quickly in Step One, take a moment to understand
what the model looks like. Again, using our ride-sharing
company as an example, we have a few assumptions
here, the take rate is 25 percent. Now we have an assumption of the total number of
assignable drivers is the total number of drivers that Uber or
Lyft or whatever, may have at their disposal
to work in their company to provide rides to customers. There's a total of 300,000
drivers who have signed up to drive for our
ridesharing company, and the total number
of rides that each driver can
do per day is 10. These are just three assumptions that we have in our model. Now if we look at
this list of cities, these are potential cities that our ridesharing company
might want operate in. This column, the rides
requested per day, this is the maximum number of rides that are
requested for that day. If we have an assumption of
10 rides per driver per day, that means the drivers needed
in each of these cities is simply the rides requested
per day divided by 10. This, you'll see is the total
number of drivers needed. The total number is
actually 386,000, which is more than the number of assignable
drivers that we have for all of our customers. We have our supply and
demand issue here. We also have these inputs of
the average fare per city. You can see this is sorted
in descending order. Well, it is sorted
in descending order. New York has the
highest average fare, going down with Mumbai
being the lowest. If we assume this 25
percent take rate, which is our ridesharing
company's profit per ride, we simply take 25 percent, multiply it by the average fare, and then we can get
the profit per ride. Now our goal is to enter
in the number of drivers assigned here to
maximize total profit. Look here, if I put 100 here, we get a profit of 469, which is simply 100 times
the profit per ride. If I put another 100 here, we get 336 because
the fare is lower, and we get a total
profit here, 805. If you were to do this
by hand or manually, you would look at
the constraints in this model, which is, well, we know we only need
45,000 drivers here. One temptation is to say, well, if we have 300,000
drivers available, why don't we just put them
all in the highest fare city and we can get a total
profit of 1.4 million? Well, the problem
is, we don't need 300,000 drivers in New York, we only need 45,000. The maximum number of
drivers that we can put into this market for New
York is simply 45,000. This is the maximum
profit we can take from New York
which is 210,000. Now you might go down the
line and see, okay, well, our next highest
profitable city, actually looks like it's
going to be London, 4.39. We're going to try to maximize the number of drivers we want in this city, which is 37,400. Then the next highest
profitable city looks like it's going to be Sao Paulo, so we might do 20,000 here. We can just keep on going
through the list until we get to a maximum number
of drivers assigned, which is going to
be our constraint here, which is 300,000. This is going to be
obviously very slow to do especially with a
long list of cities. But I don't want to do this
manually by going through each city and finding which city has the most profit for my ridesharing company, and then trying to plug in
the maximum number available drivers needed to
maximize the profit. We want to figure out what
our maximum profit is given these constraints
in the system. Now that we understand
this model really quickly, let's look at Step Number Two. We actually just did
this a little bit. We did through trial and error, adding drivers to the
driver's assigned column to see how this
affects total profit. We quickly discovered
that we can't simply just assign 300,000 drivers to
the highest profitable city, because we don't need
300,000 drivers in New York. We only need 45,000
as the maximum. Instead of doing this manually, one city by one city to
maximize our profit, we're going to use
the Solver feature. If you don't have Solver
yet in your Excel, you'll see this
under the Data menu, and you'll see Solver here. If you don't have that, click on the "Tools" menu in your Excel, it's actually outside
of the window here, but you should have a
Tools tab in your Excel. Then go to Excel add-ins, and you should see Solver
add-in right here. If you don't see
it, just take it off right here, hit, "Okay." Then you'll see
Solver show up under the Data tab in the ribbon. Now we're going to click on the "Solver" tab to
help us calculate the optimal number of drivers to put in the drivers
assigned column. Now before we get
into the Solver menu, there's a few things
that we want to look at here to figure out
how to set this up. We want to set the objective
to the total profit, which is going to be this. Well, but change the number
of drivers assigned, which is these cells right here. The constraints include
drivers assigned must be less than or equal
to the drivers needed, which is simply this number, has to be less than 300,000. The total driver's assigned also must be less than or
equal to assignable drivers. That means this number cannot be greater
than this number, and also these numbers
all has to be integers. That's a lot of talking. Let's just take a
look to see what this means using the
actual Solver menu. This might show up a little
small on the screen, but let's go through
this one by one. We have some objective. We want this objective, which is cell I28, that's our total profit of all of our cities after
we've assigned drivers. We want to simply
maximize that number. We can set this to some number. In this case, we just
want to find the optimal maximum number
that we can use, that we can calculate given
the constraints in the model. Now, we set our
objective to be I28, which is our total profit, and we need to tell
Excel what are the labels we're going to pull to get to that maximum profit. That's going to be this form right here, changing
variable cells. It's going to be from
H17 through H27. These are the drivers we want to assign to each different city. We're going to be
changing all these cells or rather Solver is
going to be changing all the cells using
the algorithm. Now we're going to put in the constraints
within our model. This is the most
important part of Solver, because we have to
tell Excel what are the constraints we're
working with in order to get to this maximum profit. Let's start adding constraints. The first constraint
we talked about is the number of
drivers assigned. This has to be
less than or equal to the total number
of drivers needed. Because remember, we
can't put more than 45,000 drivers in New York, more than 35,000 in Los Angeles. That's what you say, H17
through H27 has to be less than or equal
to E17 through E27. We're going to add them
to the constraint. We're going to say, the driver's assigned this
total number right here, which sums up all the drivers
assigned in that column, it has to be less
than or equal to the total number of assignable
drivers, which is 300,000. If we recall, in our
total market analysis, we need more drivers
than we actually have, so we have to figure out
how to best optimize this small pool of
300,000 drivers, and add. The final constraint
is that these numbers, Excel is going to put this
through some algorithm. We want to make sure that
doesn't come up with some fractions or decimals, because there's no such thing
as like half of us driver, one-third of a driver. We're going to say these numbers
all have to be integers. Just saying that
this is an integer. We have those three constraints in our system, hit "Okay." Now our Solver menu is
pretty much all set up. We have our objective, we have the cells
we're changing, which is number of
drivers assigned. Then we have these
three constraints. Now a few things here
we want to look at. You probably want
to check this off, make unconstrained
variables non-negative. This simply means
that these variables that we're changing
have to be above zero. It doesn't really
make sense to have negative 500 drivers assigned, so we want to check this off. Typically, you'll want to use the GRG linear solving
method or algorithm. This handles most situations, linear and non-linear
situations. Leave that as default. You can click on "Options" here. There's a whole bunch of
other options you can do, like, Ignore Integer
Constraints. We obviously want to keep
that on check because we want to keep
these constraints. A GRG linear, use multi-start, you click on that to find
more accurate answers. But usually, speaking in
this model like this, you can just leave
all these menu items as is, hit "Okay". We have our Solver set up. Let's try to see if Solver can figure out the total number of drivers to assign for
each different city. Hit "Solve" and wow,
that was pretty fast. Solver found a solution. We're going to hit "Okay." Let's just hit
"Close", and let's see what Solver came up with. As expected, it
tried to maximize the cities with the most
profit like New York, it put 45,000 there,
35,000 Los Angeles. These are all the maximum. But you can see as
you get down to the lower profit cities, it only assigned
33,600 drivers to Sao. It actually said,
we're going to put in zero drivers in
Hong Kong and zero in Mumbai because those are our
least profitable cities, in order to maintain this maximum number of
drivers to just 300,000, which we have the financing
assumption over here. That is how you can use
Solver on your team, to basically solve a complex
optimization problem where you have
multiple constraints, you're changing multiple
levers or multiple cells here, which are these cells, and you have some number
you're trying to hit. It could be a maximum, it could be a minimum, it
could be a certain value. In this case, we know that given our constraints in our
system of 300,000 drivers, and these drivers needed, the total profit we can make in this scenario is just
over a $1 million. That's how you can use Solver, a much more advanced
feature in Excel to prevent you having
to do trial and error, and going through this manually, and just having Solver do the hard lifting to
find these numbers.
8. Final Thoughts: Thank you so much for
taking this class on advanced Excel features
and functionality. I hope you'll walk away with more critical and analytical
thinking skills when it comes to evaluating and
analyzing your data in Excel. The most important
part about some of these features and
functions in Excel is knowing the specific
use cases and niche scenarios in
which to use them. But when you learn them, you can then make a
business case to bring back to management or to C-suite on whether or not to invest in a specific business or
investment opportunity. Please post your questions and thoughts in the discussion
forum of the class. I'll be happy to provide you feedback and also
take screenshots of your class project as
you're working on them in the project section
of this class. I hope to see you in one of my other Excel classes
here on Skillshare.
Al Chen, Excel Trainer & Coda Evangelist
