3.2 Changes in discrete and continuous time

Loading...

From the course by University of Pennsylvania

Introduction to Spreadsheets and Models

1647 ratings

University of Pennsylvania

Introduction to Spreadsheets and Models

1647 ratings

Course 2 of 5 in the Specialization Business and Financial Modeling

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 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.

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

Meet the Instructors

Don Huesman
Managing Director, Wharton Online
Innovation Group- Wharton School

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.

© 2018 Coursera Inc. All rights reserved.

Download on the App Store

Get it on Google Play