2.5 Limits to simple, deterministic models

Loading...

En provenance du cours de University of Pennsylvania

Introduction to Spreadsheets and Models

1702 notes

University of Pennsylvania

Introduction to Spreadsheets and Models

1702 notes

Cours 2 sur 5 dans 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.

À partir de la leçon

From Spreadsheet to Model

In this module, you'll move from spreadsheet to model, so you can begin to create your own models that reflect real-world events. You'll learn how to organize and lay out model elements, as well as the types of objective functions and their use. You'll also learn what-if analysis and scenarios, sensitivity analysis, and other classic models. By the end of this module, you'll be able to design a spreadsheet reflecting assumptions, decision variables, and outcomes, create a basic cashflow model, evaluate a small business opportunity, conduct what-if analysis, identify key variables using sensitivity analysis, and linear programming models and deterministic models.

2.0 Module Introduction2:48

2.1 Using assumptions and decision variables in spreadsheet models2:47

2.2 Structuring a spreadsheet to model variables, objectives, and objective functions2:54

2.3 Constructing simple cashflow model4:43

2.4 What-if analysis and sensitivity analysis4:29

2.5 Limits to simple, deterministic models5:30

Rencontrer les enseignants

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

So far, we have built models to explore a new job opportunity, to evaluate

the cost of building a new product, and to analyze cash flows in a business.

In all of these models we've been using what mathematicians call linear functions.

Models built using linear functions have limits.

In this lecture, we'll explore those limits, and find out how alternative

mathematical functions can address other problems that linear functions can't.

Also, our models so far have incorporated variables that are fixed.

They are known quantities.

That makes our models deterministic.

Other models can be built to address uncertainty in business scenarios.

Let's take a look at what makes the models we've worked with so far deterministic.

Let's assume that we have worked with the sales forecast and

cash flow model over the course of a full year.

Now we have actuals in each period that we can use to compare to our forecast.

They're here in row 19.

I also added rows to show cumulative sales for the year.

From the prior year, from current actuals, and the forecast.

To generate a cumulative number,

I added the cumulative number from the prior period to the current period sales.

For example in cell D20, I added the value of C20,

which was January's total to the period sales for February in D17.

Let me graph these two cumulative sales numbers.

The prior years sales and the projected sales.

So in other words, the ranges from C20 through N21.

I'll choose to insert a simple two dimensional line graph.

Notice how the two cumulative lines run parallel with this years cumulative

numbers ending somewhat higher.

This is exactly what we would expect since we programmed the model with a fixed and

predetermined growth rate.

Let me clear this chart.

Compare that prior graph to this new one that shows projected

sales numbers versus actual sales numbers.

These lines show much more variation.

Now what's the difference?

The projections are based on linear formulas,

that are fixed in their logic and pre-determined.

The actuals show the kind of random behavior you see in a real marketplace.

Let me clear that chart to show you another change

that's been made in this model design.

I extended the decision variables and key assumptions ranges to allow for

per period estimates, rather than applying a single

annual assumption about growth or charges for returns.

That does help surface some hidden features in the numbers.

For example, the model now allows for assumptions about sales and

return rates that are discreet by time, and

reflect a seasonal burst of activity at the end of the calendar year.

But that doesn't change the underlying determinism of the model.

In our next module, we'll look at ways to move beyond these deterministic models,

to incorporate more real world variability in our forecasts.

Now let's summarize Module 2.

In this module, we explored approaches to building spreadsheets

that changed our electronic ledgers into models.

We pulled out of the logic in our spreadsheet formulas, the decisions we

are making, and our key assumptions about the business scenario we are in.

We made clear the output metrics we are most interested in

by putting them together in a specific location on the model.

We organized our model so

that variables that applied in multiple locations were located in only one place.

We used range names to clarify the logic in our formulas.

And then, using these new structures,

we conducted what if analysis to see how changes in our assumptions

about the business environment might affect our outcomes.

We learned how to collect these changes and assumptions and

decisions as scenarios for reuse at a later time.

We learned how to test the sensitivity of the model's outputs

to changes in decision variables and assumptions.

Following that technique, we identified the relatively more important variables

for the focus of our analysis.

Finally, we looked at the limitations of the models

that are based on simple linear functions and that are deterministic.

Our future modules will explore ways to get beyond those limitations.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2018 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play