Correlation and Regression (Introduction to Spreadsheets)

Loading...

Do curso por University of Pennsylvania

Wharton Business and Financial Modeling Capstone

216 ratings

University of Pennsylvania

Wharton Business and Financial Modeling Capstone

216 ratings

Curso 5 de 5 em Specialization Business and Financial Modeling

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.

Na lição

Step 4: Optional exercise using CAPM tables

The Capital Asset Pricing Model, or CAPM, is another tool used by investors to weigh the risks and rewards of potential investments. In this optional module covering Step 4 in the Project Prompt, you can use CAPM as a vehicle to further strengthen your financial modeling skills, including using regression concepts. You may revisit the Specialization lectures below touching on regression. To test whether you've grasped the concepts in the CAPM model, this module includes a short quiz. This assessment is formative, meaning your score will not count towards your final grade. The work you do this week may inform how you build the mixed asset portfolio of your final project, but it is not necessary to complete the final project.

Introduction to Regression (Fundamentals of Quantitative Modeling)7:16

Use of Regression Models (Fundamentals of Quantitative Modeling)15:33

Correlation and Regression (Introduction to Spreadsheets)7:28

Conheça os instrutores

Richard Lambert
Professor of Accounting
Accounting- Wharton School
Robert W. Holthausen
Professor
Accounting
Don Huesman
Managing Director, Wharton Online
Innovation Group- Wharton School
Richard Waterman
Professor of Statistics
Statistics-Wharton School

Excel and sheets include a large collections of statistical functions.

To the most useful in developing models to help predict future events

are correlation and regression.

Correlation helps us make sense out of the data we collect in our business.

For example, does the length of service of our employees of field sales department

correlate to success in sales?

A correlation exists when two sets of data change together.

Regression is a statistical technique for

using one data set to estimate a second correlated data set.

For example, in the market for

diamonds, the market price is correlated with its weight.

Using regression analysis,

we can predict the probable price of a new diamond using its weight.

In this lecture we will use statistical functions

to measure correlations between variables and our business models.

Will review what the correlation coefficient tells us

about the strength of the relationship between two variables.

We'll explore the diamond market example again to practice the use of regression.

For predicting a particular diamonds market price and

we'll use multiple regression to improve our forecast.

Now let's turn to this spread sheet.

One of the most common of the classic business models is the regression

analysis model.

To demonstrate regression,

let's use a dataset that describes the sales of diamonds in Singapore.

The weight of each diamond in a sale is located in column A.

The corresponding price is in column B.

If we plot the two data sets using the scatter plot as I have done here.

The data appear to be almost a straight line but not perfectly.

I can use the Excel correlation function to see how closely changes in weight

correlate to changes in price that is how much to the two numbers vary together.

So, to do that I'll used the Excel function CORREL.

CORREL asked for two arrays,

in this case those are A4 to A51.

Which is the weight.

And secondly B4, to B51.

Which is the price.

When I run that correlation, I get the result, 0.989.

Correlations run from minus 1 to plus 1.

A zero would indicate no correlation,

that is the two numbers don't vary together at all.

This number 0.98 is a very high correlation.

Let me clear away chart one.

Regression analysis takes this one step further.

It asks the question, to what extent can the change in one variable

be predicted by the change in another?

Now, this presumes that we have logic or

theory suggesting that one number drives the change in another number.

In this case are theory is that wait is driving price.

To run our regression choose data.

Data analysis and roll down to the regression option

clicking down OK For

the y variable, that is the price we're predicting,

choose the range B4 to B51.

For the x variable, choose the weight A4 to A51.

Click here to indicate that are ranges include headers.

Click the Labels option to indicate that the ranges

we have identified include labels then click Ok.

Once you click Ok, a regression report will appear in another sheet.

The summary output as a regression appears in this second tab.

The key numbers for here are the three regressions that are listed at the top.

This R represents the correlation we saw before.

R squared is the covariance,

the amount of change in y that is due to a change in x according to the regression.

The adjusted R squared makes allowances for

the relatively small sample size as you here only 47 observations.

But, this model suggests that 97 or

98% of the change in price can be attributed to a change in the weight.

That's the classic regression analysis model used in many business scenarios.

Those include scenarios covered by other courses in the business and

financial modeling specialization.

Let's summarize Module 3.

In this module,

we used some statistical functions to review some historical sales data.

Those included means and standard deviations, and

we then applied what we learned from that analysis to a sales forecast.

We also used functions for including uncertainty in our forecast model.

We used the rand() and randbetween() functions for generating random numbers.

We talked about the difference between uniform and normal distributions and

we used the data analysis tool pack

to generate random numbers that were pulled from the normal distribution.

We looked at forecast models that were structured according to discrete time

with columns or rows indicating the passage of time.

We then compare that to model structures that allow for

the treatment of time as a continuous variable.

We looked at linear and proportionate growth in metrics over time.

We begin to address the topic of nonlinear functions for showing either growth or

declined.

We practice the spreadsheet functions EXP as well as the forecast and

growth functions.

We created the model that link the probability of the events

in the series and

calculated the probability of different series of events using a probability tree.

We also incorporated some revenues and

expenses into those trees in order to create a decision tree.

Modeling the expected value of any given branch.

Finally, we examined two related sets of data using spreadsheet functions for

measuring the strength of correlation between the two sets.

We also use tools for running a regression analysis using values from

one dataset to predict the value of another.

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.

Baixar na App Store

Baixar no Google Play