2.2 Structuring a spreadsheet to model variables, objectives, and objective functions

Loading...

Del curso dictado por University of Pennsylvania

Introduction to Spreadsheets and Models

1597 calificaciones

University of Pennsylvania

Introduction to Spreadsheets and Models

1597 calificaciones

Curso 2 de 5 en SpecializationBusiness 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.

De la lección

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

Conoce a los instructores

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

In this lecture, we will look at some different ways to lay out all the elements

of a model on a spreadsheet, with distinctive locations for assumptions,

decision variables, objectives or outcome variables, and

the functions that express the logic that connect these variables together.

We also look at using range names to help keep track of the logic in our formulas,

and to avoid introducing errors.

So now, let's turn to the spreadsheet.

It's good practice to make your thinking in a model transparent.

Don't bury your assumptions within calculations or

directly inside the formulas of the spreadsheet.

Instead, reference your assumptions using cell addresses, clearly labeled.

And put them where they can be easily changed.

As in this case, in which B11 references the forecast return rate,

which is then later used, in cell C27, for

calculating returns and allowances for the month of January.

I've also used the color blue to highlight key assumptions.

The use of range names can help with making your logic transparent in formulas.

Previously I created a range name for total expenses and another for

total revenues.

To do that I pointed to a particular range, in this case total cash receipts.

And chose to define a name.

Similarly, I pointed to total cash expenditures, And defined another name.

To show how to use those names, let me replicate this margin formula.

In this case I'm going to say that the value to calculate for

the margin would equal total cash receipts.

So notice it's pulling up my list of range names that I created.

Minus total expenses.

Divided by total cash receipts.

I'll put parentheses around these two, to make certain that the,

processing order is correct.

And you'll notice I get the same value as I did with the calculation for

the margin I created earlier.

After you create a model that clearly represents your best thinking about

the future, you can update the theoretical numbers with actual sales.

So, for example, we projected 12 unit sales in January.

If it comes in at 9, we can simply change the formula to the actuals.

As time moves forward, as you replace projected numbers with actuals,

you can develop a more specific forecast for this particular time frame.

Explora nuestro catálogo

Inscríbete de manera gratuita y obtén recomendaciones personalizadas, actualizaciones y ofertas.

Coursera brinda acceso universal a la mejor educación del mundo, al asociarse con las mejores universidades y organizaciones, para ofrecer cursos en línea.

© 2018 Coursera Inc. Todos los derechos reservados.

Descargar en la App Store

Consíguelo en Google Play