2.3 Constructing simple cashflow model

Loading...

Do curso por University of Pennsylvania

Introduction to Spreadsheets and Models

1768 classificações

University of Pennsylvania

Introduction to Spreadsheets and Models

1768 classificações

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

Na lição

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

Conheça os instrutores

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

One classic use of a spreadsheet is to build a model of the flow of cash in

a business, tracking revenues based on sales, or income from investments,

and the fixed and variable expenses as they occur over time.

Often the objective is to identify a breakeven analysis,

that is comparing anticipated revenues to fix invariable costs,

identifying in advance shortfalls that might require financing or

a change of plans to reduce both types of costs.

To explore a cash flow model we will look at a potential new business venture and

use the model to make some critical decisions about that venture.

So now let's turn back to the spreadsheet.

After the work to layout your data and

assumptions into a model format, now you can have some fun.

By changing the values identified as decision variables or

as assumptions about business environment,

you can explore multiple possible scenarios for what the future might hold.

For example, we can use our model to think about a large investment in marketing and

sales, and the prospect that such an investment

could change substantially our expected growth rate in unit sales.

For example, we can use our model to think about a larger investment in marketing and

sales and the prospect that such an investment would change substantially our

expected growth rate and unit sales.

So for instance, if we increase our advertising budget by 50% to 300 a month,

we might be able to anticipate an increase in our annual

sales growth to a larger number like, lets say, 50%.

Should that happen,

our end period cash budget looks much better as does our margin.

It can be empowering to ask the what if questions and

think about many possible alternative futures.

What if we took in an investor and spent a lot more money on product development or

on hiring?

What if our growth in the adoption of customers was exponential and not linear?

How could we scale our operations to accommodate exponential demand?

What if the economic environment dramatically slows down?

What could we do to endure?

The assumptions in our models can be saved as scenarios.

For example, let's assume in one case that investments in marketing result in

an increase in unit sales.

Let's create that scenario, as we've done here,

with a 50% annual sales growth anticipated because of the larger advertising budget.

By collecting those values together, I can save them as a scenario.

By choosing data, what if analysis, scenario manager,

adding a new scenario, in this case describing it as high growth.

And clicking OK to save it.

Similarly in a second case, let's consider the case of an economic turndown in

which we still need to invest heavily in sales, but our sales remain flat.

In that case, we'll leave our increase in the advertising budget, but

we'll take back our sales growth rate to a flat 20%

which is what we experienced historically last year.

Those values can similarly be saved as a second scenario

using data, what if analysis scenario manager,

adding a second scenario called low growth.

And now in the future after these scenarios have been created,

it's easy to recall them at a later time choosing high growth or low growth.

These are very simple scenarios with a small set of assumptions that are easy

to recall.

But as you build larger and more complex models, you might find the scenario tool

useful to recall the set of variables associated with last year's performance or

with a change in the business environment you've dealt with before.

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