0:05

In this lecture, I'll review the basics for navigating a spreadsheet and

Â crafting formulas.

Â We'll take a look at the different types of data that are available to you

Â on a spreadsheet using Excel.

Â And we'll look for some options for displaying them in different ways.

Â 0:28

I'll cover how to control the order of processing in formulas,

Â which can sometimes lead to errors.

Â And I'll provide some shortcuts for copying data in formulas.

Â Imagine that you are young, single, with few commitments, and you've just been

Â offered an opportunity to joining a new start-up venture located in San Francisco.

Â At first look, the opportunity sounds exciting.

Â The company has offered you a starting salary of $75,000 and

Â the benefits include full insurance coverage.

Â 1:03

In this example, we've been offered an interest a new job opportunity

Â with a startup that's based in San Francisco.

Â Now the catch is, it's in San Francisco and we're going to have to move there, and

Â San Francisco can be an expensive place to live.

Â So let's begin by looking at their salary offer.

Â They've been offering as income to me, a salary of $75,000 for the first year.

Â 1:36

Now, notice when I put that number in cell B3, it right justifies.

Â Excel's going to treat this as a value that can be used in formulas

Â as a numeric constant.

Â However, what I'd really like to do in this cell is to show what my payroll

Â take home pay will look like once I've taken the $75,000, divided it by 12 for

Â the 12 months of the year, and applied the tax rate that I'm expecting.

Â So rather than 75,000, what I want to do is to put an equal sign in this cell.

Â 2:12

Which will alert Excel that I'm expecting to write a formula here.

Â I'll take the $75,000, divide it by the 12 months of the year.

Â And then, there's the issue of the tax rates.

Â San Francisco has fairly high local taxes and combined with the federal and

Â state taxes, you have an effective tax rate of about 35%.

Â So that means, I'm really taking home only 65% or 0.65 of this value.

Â Meaning, my take-home pay should be in the neighborhood of $4,000 in change.

Â 2:59

For instance, if I type 1/1/2017,

Â I see a date format that looks like it's being treated as a string of texts.

Â In truth, Excel recognizes that I'm intending to be working with dates and

Â will allow me therefore to do some calculations with dates if I need to.

Â Given an example of that, let say this second month means I

Â see my second paycheck on 2/1/2017.

Â Now, it's not particularly useful for this model but

Â in others you might want to know what the number of days are between 1,1 and 2,1.

Â You can treat these values as numeric data

Â 3:44

by taking a referenced cell, C2, in this formula, subtracting from it

Â the value in B2, and I can see that there's 31 days between those two dates.

Â I do this just to illustrate the fact that Excel has a collection of data types,

Â and dates are treated in a way that's different from either numbers or text.

Â 4:35

Let me stop here and illustrate some of the shortcuts that Excel provides for

Â rapidly building out your spreadsheet models.

Â In this case, what I want to do is illustrate this fill handle that's

Â shown here at the bottom right corner of the selected cell.

Â When I grab that fill handle, when the icon indicates a plus sign, I can pull

Â 5:15

from the value and C2 but it recognize to this a date.

Â And therefore, type out the different months as you see displayed here.

Â Similarly, I can now take my estimated payroll and

Â copy it across the 12 months.

Â Now, in July of the year,

Â the company has suggested that I can participate in their annual pay increase.

Â And I can expect a 5% increase at that point.

Â 6:37

I'd like to be able to walk to my work and

Â in investigating some of the rents that are available for

Â housing near where my workplace is, I've discovered that

Â the least expensive place I can identify is $3,000.

Â As I say, San Fransisco is not a cheap place to live.

Â 6:59

The Common practice in San Francisco is to collect on the first

Â month's rent an additional two months of deposit.

Â So it's not just $3,000, it's also $3,000 plus $6,000.

Â [NOISE] So that first month I'm going to need to have a total

Â of $9,000 to fund my lease for the first month.

Â Now in the second month, I'll be able to go back to the standard $3,000 and

Â that should repeat itself, according to my lease for the remainder of the year.

Â 7:47

Some additional expenses that I'll have

Â to contend with are food, utilities,

Â travel, entertainment, and

Â then the things I haven't thought of.

Â Just to put in some numbers or estimates for this,

Â let's assume we are going to spend $100 a week or so, or $400 a month on food.

Â 8:15

For utilities, this will include phone and related utilities,

Â cable television, that kind of thing.

Â Let's say, we'll spend $300.

Â Travel should be interesting.

Â I believe that we can go without a car in San Francisco and rely on public

Â transportation, and as I say, I want to be close enough to my work to walk.

Â So with public transportation and

Â maybe the occasional Uber from getting to place to place.

Â I'll estimate, I'll spend $200.

Â 8:43

I'd like to get as much as I can for entertainment, but

Â let's say, $500 for now is what I might have to work for.

Â And again, there may be other unanticipated expenses that I need to

Â worry about, and I'll put in $500 for that.

Â 9:21

I start with the equal sign again, to indicate that I'm writing a formula.

Â And in this case, I'm going to type out one of Excel's built in functions,

Â this case it's a sum function.

Â 10:17

So my total expenses are $10,000.

Â My total income is $4,000.

Â This isn't looking very good at the moment.

Â But just to see what the cash on hand might look like

Â 10:32

I'll write a formula that says, in this case,

Â I want to take the value that's in cell B3 indicating my income for

Â the month and subtract from it value in cell B12, the total of my expenses.

Â 11:32

And you can see that things do get better but

Â not a whole lot better, as we move through the 12 months of this simulation.

Â So in this particular case,

Â what I have to do is find ways to make some savings in my expenses.

Â 12:10

That makes life a little bit better.

Â It also illustrates a problem with Excel that you need to keep your eye out for,

Â and that's the order of calculation.

Â So in this particular case, the formula says,

Â the value in the cell should equal 3,000 plus 6,000 divided by two.

Â 12:31

That's not exactly what I mean.

Â And to illustrate the problem, let me jump to another sheet here,

Â where I've put out a description of what I'm calling PEMDAS, or

Â the order of calculation, by default, in Excel.

Â 13:37

And finally, I want to show you the symbol for addition and subtraction.

Â Addition is a plus key, which is shift above the equal sign, and

Â then a minus key, which is the key just to the left of that.

Â 14:12

Whenever you see this kind of situation, where the information

Â in one cell is overriding the display of the information in another cell,

Â there's a way to readjust the column size to allow the full display to appear.

Â So in this particular case, I have information in column B

Â that's hidden by some information I have in column D.

Â To make that change, I can either pull column B to make it wider or

Â I can double click on the line that's separates column B from C.

Â And will automatically adjust for the widest entry in that column.

Â 14:52

So in this formula, as you see, the order of calculation that Excel follows

Â will treat that division sign before it treats this addition sign.

Â Meaning, it'll take 6,000 divided that by two and then add 3,000 to it.

Â That's not what we intend.

Â So using parenthesis, we can force Excel to instead

Â do the addition first, take that product and divide that by two.

Â 16:01

We also need to adjust the cash on hand formula.

Â At the moment, it's showing the results for

Â January of the difference between income and total expenses, as a -2300 in change.

Â In February, that also shows just the results

Â of the difference between the income and total expenses for the month of February.

Â But I actually like to carry forward, either the positive or

Â negative balance from the month of four.

Â So to do that, I will edit this formula.

Â 16:42

When I then use the fill handle to copy that address across,

Â or that formula rather, across.

Â You'll see that we have a running balance that stays negative for

Â the first four months with a positive balance thereafter.

Â Some of these cells have additional

Â 17:05

indications of pennies to decimal placed pennies.

Â I'll show you a way to control that, so that you can either

Â increase decimals or decrease them using the toolbar.

Â And I'll do that for all of these.

Â 17:29

Finally, I want to show you one last function, which is the MIN function.

Â Basically, I want to have in a single cell Whatever would be the minimum value,

Â I mean the lowest value we're likely to see over the course of that 12 months.

Â Now in this small spreadsheet like this,

Â you can just kind of spot the fact that January's the bad month.

Â But if things changed if you added travel expenses for

Â instance to go home in July or if you had some other changes that were

Â unexpected in the other expense line that might change and

Â in bigger spreadsheets with larger columns it might be hard to keep track of that,

Â but I want to use this as an illustration of the minimum function.

Â This is an example of using a built-in function

Â to show a primary result you want to keep your eye on.

Â In this case,

Â it's to what extent do I go into the hole with this flaw of expenses and

Â need to be prepared to finance that either through credit or some other method.

Â So in this case, what I'll do is say, equals the MIN function.

Â And MIN will also take an array as an argument or a range of cells.

Â In this case, the cells that I'm interested in are B13 and

Â with the colon I'll indicate the end of the cell range,

Â which is in this case is M 13.

Â And when I hit the Enter key, the result picks the lowest number that we

Â spotted before from January, meaning that I need to be prepared to finance

Â my San Fransisco adventure, at least to the tune of $2,300.

Â So that's it.

Â This is very simple spreadsheet model, used to plan out expenses

Â associated with a possibility of a job opportunity in San Francisco.

Â It's not a model that I would necessarily keep, but

Â it does illustrate some of the elements of what we'll see in models that are used for

Â more elaborate and difficult business decisions in future sessions.

Â Also I hope that for those of you who are new to Excel, you saw some of the basic

Â shortcuts and techniques for navigating and building a spreadsheet.

Â