In this Capstone you will recommend a business strategy based on a data model you’ve constructed. Using a data set designed by Wharton Research Data Services (WRDS), you will implement quantitative models in spreadsheets to identify the best opportunities for success and minimizing risk. Using your newly acquired decision-making skills, you will structure a decision and present this course of action in a professional quality PowerPoint presentation which includes both data and data analysis from your quantitative models. Wharton Research Data Services (WRDS) is the leading data research platform and business intelligence tool for over 30,000 corporate, academic, government and nonprofit clients in 33 countries. WRDS provides the user with one location to access over 200 terabytes of data across multiple disciplines including Accounting, Banking, Economics, ESG, Finance, Insurance, Marketing, and Statistics.
Navigating a Spreadsheet and Crafting Formulas (Introduction to Spreadsheets)
Loading...
Do curso por University of Pennsylvania
Wharton Business and Financial Modeling Capstone
216 ratings
Na lição
Steps 1 and 2: Yahoo Finance
In this module, which correlates to Steps 1 and 2 in the Project Prompt, you'll be working with a historical data set to calculate performance data and to provide summary statistics on that data. These calculations will allow you to practice using Spreadsheets for financial calculations, and provides the foundational skills and numbers for the next steps of the project. First, you'll use the set to calculate daily returns on a set of securities. You'll then use your Spreadsheet skills to calculate summary statistics. You'll be given the opportunity to test your knowledge with a sample return to see if your calculations are correct. And you may want to refresh your recollection of the content from the Specialization with the lectures included here. The work you complete this week allows you to form the basis for comparing stock performance, which you will use in creating the investment portfolio for your final project as well as the comparison to the performance of a single stock.
Conheça os instrutores
Richard LambertProfessor of Accounting
Accounting- Wharton School
Robert W. HolthausenProfessor
Accounting
Don HuesmanManaging Director, Wharton Online
Innovation Group- Wharton School
Richard WatermanProfessor of Statistics
Statistics-Wharton School
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.
We'll examine mathematical operations on both cells and ranges of cells called,
arrays.
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.
But let's build spreadsheet model to take a closer look at this opportunity.
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.
Let me put $75,000 into this cell B3.
I call that B3 because it's the intersection of row 3 and column B.
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.
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.
As I mentioned, this is for the first month.
I'm starting, let's say, in January of 2017.
There are number of ways I could indicate on the spreadsheet that I mean January.
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
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.
In truth, the way I like to represent months is just simply by typing January.
And I can, if I like, type February in the next column.
But let me show you one other shortcut in Excel that makes this a lot easier to do.
Let me zoom out to show you a 12 month spread.
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
across to the cell M2 and you'll notice that Excel has
both copied what was my intended entry in cell D2
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.
In order to indicate that in this formula, I'm going to change the value and
multiply what is the current formula by the number 1.05,
indicating the value will go up by 5% starting in July.
Again, I have that change in a particular cell, H3 in this case,
and I want to copy it through the rest of the year.
Now, let us look at some expenses.
The big one is housing.
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.
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.
And using the fill handle, I'll copy across all 12 months.
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.
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.
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.
Now, let's take a sub-total.
And look at these expenses compared to my income.
In this particular case, I can use the SUM function that's available in Excel
to add the values from cell B6 through cell B11.
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.
The sum function allows me to take a range of cells, meaning a set of adjacent cells,
and indicate that the sum function should add them all together.
In this case, to indicate that range, I could type out individual numbers or
I could just indicate the beginning of the range, in this case, cell b6.
And use the colon to indicate that I'm trying to tell Excel to work
on a range or on array of numbers.
And then this case, the last cell in that range, or that array is cell B11.
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
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.
So I've lost $7000 already.
Now as we go through this 12 month spreadsheet example,
I can see some hope in the fact that I get a raise in July,
and it's also possible that I'll see some improvement when I get
beyond the stage of having that $6,000 balloon payment for housing in January.
To copy these formulas across the entire range of time that I'm interested
in looking at, I can again use this fill handle and copy all of
the formulas at once, which allows me to fill out the entire spreadsheet.
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.
To do that, let me first think about what it might mean
if what I did to control my housing cost was to share it where I live.
So in this case, what I'm going to do is take that $9000 and
divide the value that's in that formula now by two people.
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.
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.
Formulas in Excel follow a four step order of calculation.
To begin, if your formulas include items in parenthesis,
Excel will attend to those first.
Secondly, if you're formula includes an exponent.
Exponents are indicated by the up caret symbol,
above the six key on your keyboard.
Exponents are treated as the second most important item
in the order of calculation.
Third is multiplication and division.
Multiplication and division is indicated by the slash key.
Or in the case of multiplication, the star.
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.
You'll notice, I put asterisks or apostrophe in front of this formulation.
That is an indication to Excel that I should treat this value as text.
Otherwise, it would consider the plus sign as indication I was starting a formula.
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.
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.
And I get a different number.
So we have a few more changes to make in the spreadsheet before we're done.
We made the appropriate adjustment to show the fact that we've decided to take on
a roommate for the expenses associated with the January rent.
We haven't done anything yet for the rest of the year, so let me add that now.
I will edit this formula.
To first make it a formula using an equals sign and then divide it by two.
And with that, I will grab this fill handle and
pull it through the rest of the year.
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.
And simply make a reference to whatever the value was in the prior month.
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
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.
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.
Explore nosso catálogo
Registre-se gratuitamente e obtenha recomendações, atualizações e ofertas personalizadas.
O Coursera proporciona acesso universal à melhor educação do mundo fazendo parcerias com as melhores universidades e organizações para oferecer cursos on-line.
© 2018 Coursera Inc. Todos os direitos reservados.
