Explore
For Enterprise
Join for Free
Log In

Arts and Humanities
Business
Computer Science
Data Science
Information Technology
Health
Math and Logic
Personal Development
Physical Science and Engineering
Social Sciences
Language Learning
Imperial College London
Try out Imperial's GMPH degree with their new open courses
Get a sample of the topics covered in the fully online Global Master of Public Health degree
deeplearning.ai
New! TensorFlow: Data and Deployment Specialization
Join Andrew Ng and Laurence Moroney in the latest Specialization from deeplearning.ai.
Degrees
Certificates

Explore all of Coursera

Browse
Top Courses
For Enterprise
Log In
Join for Free

3.2 Changes in discrete and continuous time

Loading...

Introduction to Spreadsheets and Models

University of Pennsylvania

4.2 (2,342 ratings) | 66K Students Enrolled

Course 2 of 4 in the Finance & Quantitative Modeling for Analysts Specialization

The simple spreadsheet is one of the most powerful data analysis tools that exists, and it’s available to almost anyone. Major corporations and small businesses alike use spreadsheet models to determine where key measures of their success are now, and where they are likely to be in the future. But in order to get the most out of a spreadsheet, you have the know-how to use it. This course is designed to give you an introduction to basic spreadsheet tools and formulas so that you can begin harness the power of spreadsheets to map the data you have now and to predict the data you may have in the future. Through short, easy-to-follow demonstrations, you’ll learn how to use Excel or Sheets so that you can begin to build models and decision trees in future courses in this Specialization. Basic familiarity with, and access to, Excel or Sheets is required.

Skills You'll Learn

Monte Carlo Method, Microsoft Excel, Linear Programming (LP), Solver

Reviews

4.2 (2,342 ratings)

5 stars
1,233 ratings
4 stars
636 ratings
3 stars
290 ratings
2 stars
119 ratings
1 star
64 ratings

DA

Feb 07, 2017

A particularly useful course. It built on my existing Excel knowledge and "expertise". I really enjoyed it, as all the skills learned in this course can be applied in the real world.

AS

Jun 04, 2018

Thank you Sir, not only for this course but the splendid work you have done as MD of Wharton online in making the in-house expertise available to millions like me through Courera.

From the lesson

Addressing Uncertainty and Probability in Models

This module was designed to introduce you to how you can use spreadsheets to address uncertainty and probability. You'll learn about random variables, probability distributions, power, exponential, and log functions in model formulas, models for calculating probability trees and decision trees, how to use regression tools to make predictions, as well as multiple regression. By the end of this module, you'll be able to measure correlations between variables using spreadsheet statistical functions, understand the results of functions that calculate correlations, use regression tools to make predictions, and improve forecasts with multiple regression.

3.0 Introduction1:55

3.1 Random variables and probability distributions11:03

3.2 Changes in discrete and continuous time5:22

3.3 Power, exponential, and log functions8:34

3.4 Probability trees and decision trees5:52

3.5 Correlation and Regression7:28

Taught By

Don Huesman
Managing Director, Wharton Online

Transcript

The models we have looked at so far, use spreadsheet columns to indicate the passing of time, from January to February, from one year to the next. This way of representing passage of time and the change in variables over time, is referred to as changes in discrete time. It's possible to design a spreadsheet model to show changes in continuous time. To describe the difference between discrete and continuous time, my colleagues here at Worden uses the analogy of a digital clock versus an old fashioned analog clock. The digital clock moves forward in jumps. The analog clock, theoretically, could have an infinite number of moments between one and two o'clock. To implement variables that occur in continuous time, we will need to change the structure of our models, and the formulas we use as objective functions. Let's look at an example in a spreadsheet. As a reminder, models like this cash flow spreadsheet use columns for the passing of months or years. So, for example, column C here has data from January. Column D has data from February. Even if the columns in this model represented days or minutes, this approach is still considered treating time as discreet time. Continuous time will require a different layout for the model. I've copied here a tally of customers for a new start up company. The company has been in business for one year. Each month on the sheet is a cumulative count of all customers since the start in January and to the end of that month. The numbers are interesting, this doesn't look like linear growth, it looks like exponential growth. We'll talk more about that in another lecture but for now let's focus on the passage of time. How do we set things up, so that the passage of time is continuous? In this case, let's say we're trying to forecast the number of customers we might have next year. And not just at the end of each month, but at any point in the year. I did a quick scatter plot of last year's numbers. I highlighted the numbers from January through December. I chose to insert a chart of the type Scatter. And you see the result. Again, the increase in numbers looks pretty good. It's better than straight line growth. It has a curve to it. I added a formula in row seven to show the growth rate from period to period that occurred last year. I also added some forecast variables that we'll use for next year to calculate possible exponential growth. Those include the total number of customers at the base period from last year at 270. That's from December. Next, our forecast of the rate of increase per period. For now, let's say .3. And now, in cell A14, I'll put the number of periods to calculate. I'm setting up these numbers as variables that will be easy in the future for me to change. Now I've added in cell B18 an equation that calculates exponential growth. CF is the customers at a future period that I'm trying to calculate. CB Is an input in my formula that refers to customers in my base period, that is December of last year. The E in the formula is a mathematical constant associated with exponential growth calculations. It's roughly 2.718. R is the growth rate per period And T is the number of periods after the base that I'm looking for. So the formula in B18 includes cell references for total customers at the base period, the rate of increase per period that I'm forecasting, and the number of periods. It also uses the mathematical constant E. In Excel, that's the EXP function. Notice the I can now change the time period by any number including fractions of a period. For example, in this case, here's a forecast for the number of customer's I expect to have in a month and a half. I can change that to 7.25 or any other fractional number, and see the results in B18. That's the difference between a model that handles time as continuous and a model like the cash flow forecast we looked at earlier, that handles time as discreet time.

Explore our Catalog

Join for free and get personalized recommendations, updates and offers.

Coursera provides universal access to the world’s best education, partnering with top universities and organizations to offer courses online.

© 2019 Coursera Inc. All rights reserved.

Get it on Google Play

Coursera
About
Leadership
Careers
Catalog
Certificates
MasterTrack™ Certificates
Degrees
For Enterprise
For Government
For Campus

Community
Learners
Partners
Developers
Beta Testers
Translators

Connect
Blog
Facebook
LinkedIn
Twitter
Instagram
YouTube
Tech Blog

More
Terms
Privacy
Help
Accessibility
Press
Contact
Directory
Affiliates