Loading...

(Optional) Week 3 Review

Course video 15 of 21

How can you evaluate and compare decisions when their impact is uncertain? In this module you will learn how to build and interpret simulation models that can help you to evaluate complex business decisions in uncertain settings. During the week, you will be introduced to some common measures of risk and reward, you’ll use simulation to estimate these quantities, and you’ll learn how to interpret and visualize your simulation results.

Operations Analytics

Пенсильванский университет

Курс 2 из 5 — Бизнес-аналитика Специализация

This course is designed to impact the way you think about transforming data into better decisions. Recent extraordinary improvements in data-collecting technologies have changed the way firms make informed and effective business decisions. The course on operations analytics, taught by three of Wharton’s leading experts, focuses on how the data can be used to profitably match supply with demand in various business settings. In this course, you will learn how to model future demand uncertainties, how to predict the outcomes of competing policy choices and how to choose the best course of action in the face of risk. The course will introduce frameworks and ideas that provide insights into a spectrum of real-world business challenges, will teach you methods and software available for tackling these challenges quantitatively as well as the issues involved in gathering the relevant data. This course is appropriate for beginners and business professionals with no prior analytics experience.

Просмотреть программу курса

Получаемые навыки

Simulation, Mathematical Optimization, Solver, Decision Tree

Преподаватели

Senthil Veeraraghavan
Associate Professor of Operations, Information and Decisions
Sergei Savin
Associate Professor of Operations, Information and Decisions
Noah Gans
Anheuser-Busch Professor of Management Science, Professor of Operations, Information and Decisions

Текст видео

Welcome, this is the review session for

week three of operations in analytics course.

As before, this review session is completely optional,

if you feel that the simulation concepts are clear to you,

you can skip this review and move on to the practice problems.

After that if you feel ready, tackle week three's homework.

However, if before trying the practice problems, you would like to have

an additional look at how a simulation can be set up and run, then keep watching.

In this review session, we will go over another simulation example.

Here's the business context we're going to model.

We're looking at the ESI, a company that produces chemical solvent.

The company has a contract that requires it to deliver 35 tons

of solvent at the price of 10,000 Euros per ton every day.

While the expected value of the solvent produced each day is exactly 35 tons,

the actual production amount is hard to control exactly,

due to the nature of the production process.

And the actual amount of solvent produced can be different from the expected value.

The analysis of the past production output shows that the actual daily production

output can be modeled as a normal random variable with mean of 35 tons and

the standard deviation of 7 tons.

The cost to ESI to produce one ton of solvent is 9,500 Euros.

If the actual production exceeds 35 tons, the extra production above

35 tons must be salvaged at a price of 9,000 Euros.

If the production falls short of 35 tons,

ESI is penalized at the amount of 2,000 Euros for each ton below 35.

So here are the questions we would like to answer in this problem.

First, we will calculate the profit ESI earns on a day when its production turns

out to be 31 tons.

Then, we'll repeat this calculation for a day when the production is 37 tons.

Answering these first two questions will prepare us to write down

an algebraic formula for the daily profit V.

As a function of the production output p.

Finally, we will set up and run a simulation for

the daily profit value using 100 simulation runs and the seed 123

to estimate the expected value and the standard deviation of daily profits.

Okay, let's start by calculating ESI's profit on a day when its

production happens to be 31 tons.

Since the production on that day is below 35 tons, the company will earn revenue by

selling all of its production at 10,000 Euros per ton.

And will also incur production costs of 9,500 Euros per ton.

In addition, it will also incur a cost of 2,000 Euros per ton below 35 tons.

So on the revenue side, ESI will earn 310,000 Eeuros.

On the cost side, it will incur the production

cost of 294,500 Euros.

It will also incur a penalty cost of 8000 Euros on that day,

so the profit will be 310000-

294500- 8000, 7500 Euros.

Now let's do the same calculation for a day when ESI's production is 37 tons.

Since production on that day is above 35 tons

the company will earn revenue in two ways.

First it will sell 35 tons at 10,000 Euros per ton.

Second, it will sell the remaining 2 tons at a discount price

of 9,000 euros per ton.

It will incur a production cost of 9500 Euros per ton.

So let's count.

The regular revenue on that day will be 350,000,

the discount revenue on that day will be 180,00 Euros,

the production cost will amount to 351,500 Euros.

So the total profit on that day

will be 16,500 Euros.

Now let's put together both cases, with production

being below versus at or above 35 tons, using algebra.

If P<35, three components contribute to the profit.

Now let's put both cases together, with production

being below versus at or above 35 tons, using algebra.

If P<35, three components contribute to the profit.

First the firm incurs a production cost of 9500*P.

Then there's a revenue of 10,000*P.

Finally there's a penalty cost of 2000*35-P.

So putting these three components together we get,

10000*P-9500*P-2000*(35-P) and

the final answer is 2500*P-70000.

In the second case, when the production P is at or

above 35, we also have three profit components.

First we have a production cost of 9500*P.

On the revenue side we have regular revenue of 35*10000

plus the discount revenue of 9000*(P-35).

So the first 35 tons ESI sells at 10000 Euros per ton.

And everything above 35 it sells at the discount price of 9000 Euros per ton.

So putting these three components together we get,

100000*35 + 9000*P- 35- 9500*P.

And the final answer is -500*P + 35,000.

So we can combine both cases using IF function in Excel.

If P is less than 35, then the first formula applies and

if P is at or above 35, then the second formula applies.

The last question in this problem, asks us to set up and run a simulation of

the daily profit value using 100 simulation runs and the seed = 123.

We have prepared an Excel template Europa Solvent_0 that we

will use to simulate the distribution of the daily profit.

Let's open this file and set up and run our simulation.

Let us set up a simulation of the daily production and daily profit values for

the ESI company using 100 simulation runs and the seed 123.

We will start with the file Europa Solvent_0.

This file contains the problem data and is ready for the simulation set up.

For convenience, we have also copied here the formula from the PowerPoint slides

that calculates the daily profit value V for any daily production value P.

Okay, in order to prepare our simulation,

let's first add headers to the sales D1, E1, and F1.

In D1 we enter Simulation Run.

This header will indicate that below, in the column D,

we will count the instances of our random variables.

In E1, we will enter Daily Production.

In tons.

So in the column E we will be generating random instances of the ESI's

daily production values.

Finally, in the cell F1 we will enter profit in Euros.

Okay.

This column will contain the daily profit calculated using the random instances

of the data production from column E.

This simulation will have 100 simulations runs,

in other words we will generate 100 random instances of daily production and for

each which of those we will calculate a corresponding daily profit value.

First let's number our simulation runs in column D.

Let's put 1 in the cell D2.

And let's keep adding ones until we get 100 simulation runs.

Okay, let's double check, yes we have 100 simulation runs

Now it is time to generate 100 random daily production values.

These values will be stored in the cells E2 through E101.

In order to generate random production values we will use the Analysis ToolPak.

We go to Data > Data Analysis,

select Random Number Generation, click OK.

In the Random Number Generation dialog, we put 1 in the Number of Variables box.

We put a 100 in the Number of Random Numbers.

So, we instruct Excel to generate 100 instances of a single random variable.

Then in the distribution we select normal.

And then for the parameters of this normal we must select 35 for

the mean and 7 for standard deviation.

This will instruct Excel to generate 100 random instances of daily production

values using a normal distribution with these parameters.

Now let's use 123 for the random seed and

finally in the output options we will select

an output range, and we put cell E2 here.

This will instruct Excel to put the random instances of the daily

production values into the cells E2 through E101.

Now let's click OK.

Excel has now filled the cells, E2 through E101 with random instances of the normal

random variable with mean 35 and standard deviation seven, as we requested.

Let's hide some rows to make our presentation more compact.

Select some rows, for example 12 through 100,

and hide.

Here we go.

And let's color the E column values

And make them bold to indicate that we're dealing with simulation inputs here.

Okay, now it is time to turn to the output of our simulation.

The daily profit values.

Let's go into the cell F2 and

put in the formula that will calculate the daily profit value P for

any given value of the production in the cell E2.

As the formula we copied from the slides indicates,

we must enter in the cell F2 the full in expression.

= If the production, which is E2, is less than 35,

then the value of the profit is given by 2,500 times

the value of the production minus 70,000.

Otherwise, it's equal to minus 500

times the value of production plus 35,000.

As the result for this particular case indicates,

Europa will lose more than 21,000 Euros on a such day.

Now we copy and paste the profit formula

from the cell F2 to the cells F3 through F101.

We also color the cells F2 through F101

to indicate that we're dealing with the output of the simulation.

Okay, the final step is to calculate the sample mean and

the standard deviation of the simulated values of the daily profit.

Let's put the corresponding headers

into the cells E103 and E104.

Sample Mean.

Simple Standard Deviation.

And let's calculate the values themselves.

Average of all the profit values

and standard deviation, STDEV of all the profit values.

We can make them blue and bold to indicate that those are output values.

As the results indicate,

the sample mean of the daily profit value is around 8,752 Euros.

And the simple standard deviation is around 9,415 Euros.

Okay, to reiterate,

here's a snapshot of the complete simulation setup with formulas.

We saved it in the Excel file called Europa Solvent.

As the results of the simulation indicate, the estimate for

the expected daily profit is about 8,752 Euros, and the estimate for

the standard deviation of the daily profit is about 9,415 Euros.

О Coursera

На онлайн-курсах, специализациях и дипломных программах у вас будут первоклассные преподаватели из лучших университетов и учебных заведений мира.

Join a community of 40 million learners from around the world

Earn a skill-based course certificate to apply your knowledge

Gain confidence in your skills and further your career

Coursera делает лучшее в мире образование доступным каждому, предлагая онлайн-курсы от ведущих университетов и организаций.

© Coursera Inc., 2019 Все права защищены.

Загрузить из App Store

Загрузить в Google Play