Въведение в Монте Карло симулация в Excel

Забележка: Бихме искали да ви осигурим най-новото помощно съдържание възможно най-бързо на вашия собствен език. Тази страница е преведена чрез автоматизация и може да съдържа граматически грешки и несъответствия. Нашата цел е това съдържание да ви бъде полезно. Можете ли да ни кажете дали информацията е била полезна за вас, в дъното на тази страница? Ето статията на английски за бърза справка.

Тази статия е адаптирана от анализа на данни и бизнес моделирането на Microsoft Excel от Уейн л. Уинстън.

  • Кой използва симулацията на Монте Карло?

  • Какво се случва, когато въведете = rand () в клетка?

  • Как можете да симулирате стойности на отделни произволни променливи?

  • Как можете да симулирате стойности на нормална произволна променлива?

  • Как може една фирма за поздравителни картички да определи колко карти да се произведат?

Бихме желали точно да изчислим вероятностите за неопределени събития. Например каква е вероятността, че паричните потоци на нов продукт ще имат положителна нетна настояща стойност (NPV)? Какъв е рисковият фактор за нашите инвестиционни портфейли? Симулация Монте Карло ни позволява да моделираме ситуации, които представят несигурност, и след това да ги изпробвате на компютър хиляди пъти.

Забележка:  Симулацията за име Монте Карло идва от компютърните симулации, извършени през 1930 и 1940, за да се прецени вероятността верижната реакция, необходима за детонация на атомната бомба, да работи успешно. Физиците, ангажирани в тази работа, са голям фен на хазарта, така че те дадоха на симулации кодовото име Монте Карло.

В следващите пет глави ще видите примери за това как можете да използвате Excel, за да извършите симулации на Монте Карло.

Много компании използват симулация на Монте Карло като важна част от процеса на вземане на решения. Ето някои примери.

  • General Motors, Проктър и Гембъл, Pfizer, Bristol-Майерс Squibb и Ели Lilly Използвайте симулация, за да изчислите средната възвръщаемост и рисковия фактор за нови продукти. В GM тази информация се използва от изпълнителния директор, за да се определи кои продукти идват на пазара.

  • GM използва симулация за дейности като прогнозиране на нетните доходи за корпорацията, прогнозиране на структурните и покупателни разходи и определяне на нейната чувствителност към различни видове риск (като например промени в лихвените проценти и флуктуации на валутните курсове).

  • Lilly използва симулация, за да определи оптималната производителност на растенията за всеки наркотик.

  • Проктър и Гембъл използва симулация към модела и оптимално хеджиране на валутния риск.

  • Сиърс използва симулация, за да определи колко единици от всяка продуктова линия трябва да бъдат поръчани от доставчици – например броя на двойките панталони за заКачване, които трябва да бъдат подредени тази година.

  • Нефтените и фармацевтичните компании използват симулация за стойност "реални опции", като например стойността на опция за разгъване, сключване на договор или отлагане на проект.

  • Финансовите Planners използват симулация на Монте Карло, за да определят оптимални инвестиционни стратегии за пенсиониране на своите клиенти.

Когато въведете формулата = Ранд () в клетка, получавате число, което е еднакво вероятно да поеме стойност между 0 и 1. По този начин около 25 процента от времето трябва да получите число по-малко или равно на 0,25; около 10 процента от времето, през което трябва да получите номер, който е поне 0,90, и т. н. За да покажете как работи функцията RAND, прегледайте файла Randdemo. xlsx, показан на фигура 60-1.

Book Image

Забележка:  Когато отворите файла Randdemo. xlsx, няма да виждате същите случайни числа, показани на фигура 60-1. Функцията RAND винаги автоматично преизчислява числата, които генерира, когато работният лист е отворен или когато нова информация бъде въведена в работния лист.

Първо, копирайте от клетка C3 в C4: C402 формулата = Ранд (). След това именувате диапазона C3: C402 данни. След това в колона F можете да проследявате средната стойност на случайни числа за 400 (клетка F2) и да използвате функцията COUNTIF, за да определите фракциите между 0 и 0,25, 0,25 и 0,50, 0,50 и 0,75 и 0,75 и 1. Когато натиснете клавиша F9, произволните числа се преизчисляват. ЗаБележете, че средната стойност на номерата на 400 винаги е около 0,5 и че около 25 процента от резултатите са в интервали от 0,25. Тези резултати са съобразени с дефиницията за случайно число. Също така имайте предвид, че стойностите, генерирани от Ранд в различни клетки, са независими. Например ако случайно число, генерирано в клетка C3, е голямо число (например 0,99), то ни казва нищо за стойностите на другите случайни числа, генерирани.

Да предположим, че търсенето на календар се урежда от следните дискретни произволни променливи:

Търсенето

"вероятност"

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Как да разиграваме Excel или да симулираме това изискване за календари много пъти? Номерът е да свържете всяка възможна стойност на функцията RAND с възможно търсене за календари. Следната задача гарантира, че търсенето на 10 000 ще възникне 10 процента от времето и т. н.

Търсенето

Присвоено случайно число

10 000

По-малко от 0,10

20 000

По-голямо или равно на 0,10 и по-малко от 0,45

40 000

По-голямо или равно на 0,45 и по-малко от 0,75

60 000

По-голямо или равно на 0,75

За да демонстрирате симулацията на търсене, прегледайте файла Discretesim. xlsx, показан на фигура 60-2 на следващата страница.

Book Image

Ключът за нашата симулация е да се използва случайно число, за да се инициира търсене от диапазона от таблици F2: G5 (наименувано търсене). Случайни числа, по-големи или равни на 0 и по-малко от 0,10, ще получат заявка от 10 000; произволните числа, по-големи или равни на 0,10 и по-малки от 0,45, ще получат заявка от 20 000; произволните числа, по-големи или равни на 0,45 и по-малки от 0,75, ще получат заявка от 40 000; а произволните числа, по-големи или равни на 0,75, ще получат търсенето на 60 000. Генерирате случайни числа за 400, като копирате от C3 в C4: C402 формулата Ранд (). След това генерирате пробни версии на 400 или итерации на търсенето в календара, като копирате от B3 в B4: B402 формулата VLOOKUP (C3, LOOKUP; 2). Тази формула гарантира, че произволно число по-малко от 0,10 генерира заявка от 10 000, произволното число между 0,10 и 0,45 генерира заявка за 20 000 и т. н. В диапазона от клетки F8: F11 използвайте функцията COUNTIF, за да определите частта от нашите итерации на 400, която генерира всяко търсене. Когато натискаме F9, за да преизчислим произволните числа, симулираните вероятности са близо до предполагаемите вероятности за търсенето.

Ако въведете клетка във формулата NORMINV (Ранд (), MU, Sigma), ще генерирате симулирана стойност на нормална произволна променлива, която има средно MU и стандартно отклонение Сигма. Тази процедура е илюстрирана във файла Normalsim. xlsx, показан на фигура 60-3.

Book Image

Да предположим, че искаме да симулираме изпробване на 400 или итерации за нормалната произволна променлива със средно 40 000 и стандартно отклонение от 10 000. (Можете да въведете тези стойности в клетки Е1 и Е2 и съответно да наименувате тези клетки и Sigma.) Копиране на формулата = Ранд () от С4 към C5: C403 генерира 400 различни произволни числа. Копиране от B4 в B5: B403 формулата NORMINV (C4; средно; Сигма) генерира 400 различни изпробващи стойности от нормална произволна променлива със средна стойност 40 000 и стандартно отклонение от 10 000. Когато натиснете клавиша F9, за да преизчислите случайни числа, средното остава близко до 40 000 и стандартното отклонение, близко до 10 000.

В същността си за случайно число xформулата NORMINV (p; MU; Sigma) генерира p-ия процентил на нормална произволна променлива със средна MU и стандартно отклонение Сигма. Например произволното число 0,77 в клетка C4 (вижте фигура 60-3) генерира в клетка B4 около 77-я процентил на нормална случайна променлива със средна стойност 40 000 и стандартно отклонение от 10 000.

В този раздел ще видите как може да се използва симулация на Монте Карло като инструмент за вземане на решения. Да предположим, че искането за карта за деня на влюбените се подчинява на следната дискретна произволна променлива:

Търсенето

"вероятност"

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Поздравителната картичка се продава за $4,00 и променливите разходи за производство на всяка карта са $1,50. НеИзядените карти трябва да се изхвърлят на цена от $0,20 на карта. Колко карти трябва да бъдат отпечатани?

В общи линии симулираме всяко възможно количество продукция (10 000, 20 000, 40 000 или 60 000) много пъти (например 1000 итерации). След това определяме кои количествени ордери дават максималната средна печалба за итерациите на 1000. Можете да намерите данните за този раздел във файла Валънтайн. xlsx, показан на фигура 60-4. Присвоявате имена на диапазони в клетки B1: Б11 в клетки С1: C11. Диапазонът от клетки G3: H6 е присвоен на " търсенена имена". Нашите параметри за продажни цени и разходи са въведени в клетки C4: C6.

Book Image

Можете да въведете изпробваното производствено количество (40 000 в този пример) в клетка C1. След това създайте произволно число в клетка C2 с формулата = Ранд (). Както е описано по-рано, симулирате търсенето на картата в клетка C3 с формулата VLOOKUP (Ранд, LOOKUP, 2). (Във формулата VLOOKUP Ранд е името на клетката, присвоено на клетка C3, а не функцията Rand.)

Броят продадени части е по-малък от нашето производствено количество и търсене. В клетка C8 изчислявате нашите приходи с формулата min (произведено; търсенето) * unit_price. В клетка C9 изчислявате сумарни производствени разходи с генерираната формула * unit_prod_cost.

Ако ние произвеждаме повече карти, отколкото са в търсенето, броят на единиците, останали като равно на продукцията минус търсенето; в противен случай не се оставят части. Ние изчисляваме нашите разходи за пласмент в клетка C10 с формулата unit_disp_cost * IF (produced>demand, произведено – заявка; 0). И накрая, в клетка C11 ние изчисляваме печалбата си като приход – total_var_cost-total_disposing_cost.

Бихме желали ефективен начин да натиснете F9 много пъти (например 1000) за всяко производствено количество и да съвпадат с очакваната печалба за всяко количество. Тази ситуация е тази, в която една двупосочна таблица с данни идва за наша помощ. (Вижте глава 15, "анализ на чувствителността с таблици с данни" за подробности за таблиците с данни). Таблицата с данни, използвана в този пример, е показана на фигура 60-5.

Book Image

В диапазона от клетки A16: A1015 въведете числата от 1 до 1000 (съответстващи на нашите пробни версии на 1000). Един лесен начин да създадете тези стойности е да започнете, като въведете 1 в клетка A16. Изберете клетката и след това в раздела Начало , в групата Редактиране щракнете върху запълванеи изберете серия , за да се покаже диалоговият прозорец серия . В диалоговия прозорец серия , показан на фигура 60-6, въведете стойността за стъпка от 1 и стоп стойност на 1000. В областта Series in изберете опцията колони , след което щракнете върху OK. Числата от 1 до 1000 ще бъдат въведени в колона A, започвайки от клетка A16.

Book Image

След това въвеждаме нашите възможни количества за производство (10 000, 20 000, 40 000, 60 000) в клетки B15: E15. Искаме да изчислим печалбата за всеки Изпробван номер (от 1 до 1000) и всяко производствено количество. Ние препращаме към формулата за печалба (изчислена в клетка C11) в горната лява клетка на нашата таблица с данни (A15), като въведете = C11.

Сега сме готови да излъжем Excel за симулиране на 1000 итерации на търсенето за всяко производствено количество. Изберете диапазона от таблица (A15: E1014) и след това в групата инструменти за данни в раздела данни щракнете върху какво става, ако анализирате, и след това изберете таблица с данни. За да настроите двупосочна таблица с данни, изберете нашето производствено количество (клетка C1) като входна клетка за ред и изберете произволна празна клетка (избрахме Cell I14) като клетка за въвеждане на колона. След като щракнете върху OK, Excel симулира търсенето на 1000 стойности за всяка поръчка.

За да разберете защо става така, имайте предвид стойностите, поставени от таблицата с данни в диапазона от клетки C16: C1015. За всяка от тези клетки Excel ще използва стойност 20 000 в клетка C1. В C16 стойността на входната клетка на колоната на 1 е поставена в празна клетка и случайно число в клетка C2 се преизчислява. Съответната печалба се записва в клетка C16. След това стойността на въвеждане в клетка на колона от 2 се поставя в празна клетка и случайно число в C2 отново се преизчислява. Съответната печалба е въведена в клетка C17.

Чрез копиране от клетка Б13 на C13: E13 формулата AVERAGE (Б16: B1015), ние изчисляваме средната симулирана печалба за всяко производствено количество. Чрез копиране от клетка Б14 на C14: E14 формулата STDEV (Б16: B1015)изчисляваме стандартното отклонение на нашите симулирани печалби за всяко количество за поръчка. Всеки път, когато натискаме F9, 1000 повторения на търсенето се симулират за всяко количество за поръчка. Създаването на карти на 40 000 винаги дава най-голямата очаквана печалба. Следователно изглежда, че създаването на карти на 40 000 е правилното решение.

Въздействието на риск върху нашето решение     Ако сме произвели 20 000 вместо 40 000 карти, нашата очаквана печалба спада около 22 процента, но нашият риск (измерен чрез стандартното отклонение на печалбата) капки почти 73 процента. Следователно, ако сме силно неблагоприятни за риск, създаването на карти на 20 000 може да бъде правилното решение. Между другото, производството на карти на 10 000 винаги има стандартно отклонение от 0 карти, защото ако произвеждаме карти на 10 000, ние винаги ще ги продаваме без остатъци.

Забележка:  В тази работна книга опцията за изчисление е зададена на автоматично с изключение на таблиците. (Използвайте командата изчисление в групата изчисление на раздела формули.) Тази настройка гарантира, че нашите таблици с данни няма да се преизчисляват, освен ако не натиснете F9, което е добра идея, защото голяма таблица с данни ще забави работата ви, ако се преизчислява всеки път, когато въвеждате нещо в работния лист. Имайте предвид, че в този пример всеки път, когато натиснете F9, средната печалба ще се промени. Това се случва, защото при всяко натискане на F9 се използва различна последователност от случайни числа за 1000, за да се генерират изисквания за всяко количество за поръчка.

Доверителен интервал за средна печалба     Природен въпрос, към който да се запитате в тази ситуация, е в какъв интервал сме 95 процента сигурни, че действителната средна печалба ще спадне? Този интервал се нарича 95 процента доверителен интервал за средна печалба. Доверителен интервал 95 процента за средното за всеки резултат от симулацията се изчислява по следната формула:

Book Image

В клетка J11 можете да изчислите долното ограничение за 95 процента доверителен интервал за средна печалба, когато се произвеждат календари на 40 000 с формулата D13-1.96 * Г14/SQRT (1000). В клетка J12 можете да изчислите горната граница за нашите 95 процента доверителен интервал с формулата D13 + 1.96 * Г14/SQRT (1000). Тези изчисления са показани във фигура 60-7.

Book Image

Ние сме 95 процента сигурни, че нашите средните печалби, когато са подредени календари на 40 000 е между $56 687 и $62 589.

  1. Търговец на GMC вярва, че търсенето на пратеници на 2005 обикновено ще се разпределя със средна стойност 200 и стандартно отклонение от 30. Неговият размер на получаване на Пратеник е $25 000 и продава пратеник за $40 000. Половината от всички Пратеници, които не са продадени на пълната цена, могат да бъдат продадени за $30 000. Обмисля да поръчва 200, 220, 240, 260, 280 или 300 пратеници. Колко трябва да е поръчвал?

  2. Малък супермаркет се опитва да определи колко копия на списанието за хора трябва да поръчват всяка седмица. Те вярват, че тяхното търсене за хора е подчинено на следните дискретни произволни променливи:

    Търсенето

    "вероятност"

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. Супермаркетът плаща $1,00 за всяко копие на хора и го продава за $1,95. Всяко Непродадено копие може да бъде върнато за $0,50. Колко копия на хората трябва да поръчват?

Имате нужда от още помощ?

Винаги можете да попитате експерт в техническата общност на Excel, да получите поддръжка в общността за отговори или да предложите нова функция или подобрение на User Voice за Excel.

Разширете уменията си в Office
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за обратната връзка!

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×