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.