Тази статия е адаптирана от Microsoft Excel анализ на данни и бизнес моделиране от Wayne L. Winston.

  • Who симулация на Монте Карло?

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

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

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

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

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

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

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

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

  • General Motors, Proctor и Gamble, Pfizer, Bristol-Myers Squibb и Eli Lilly използват симулация, за да оценяват както средната доходност, така и коефициента на риск на новите продукти. В GM тази информация се използва от изпълнителния директор, за да се определи кои продукти идват на пазара.

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

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

  • Proctor и Gamble използват симулация за моделиране и оптимално хеджиране на валутния риск.

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

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

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

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

Book Image

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

Първо копирайте от клетка C3 в C4:C402 формулата =RAND(). След това можете да наимените диапазона 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. Тези резултати съответстват на дефиницията на случайно число. Също така имайте предвид, че стойностите, генерирани от RAND в различни клетки, са независими. Ако например случайното число, генерирано в клетка 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 формулата RAND(). След това генерирате 400 пробни или итерации на търсенето в календара, като копирате от B3 в B4:B402 формулата VLOOKUP(C3,lookup,2). Тази формула гарантира, че произволно число, по-малко от 0,10, генерира търсене от 10 000, произволно число между 0,10 и 0,45 генерира търсене от 20 000 и т.н. В диапазона от клетки F8:F11 използвайте функцията COUNTIF, за да определите дробта на нашите 400 итерации, които дават всяко търсене. Когато натиснете F9, за да преизчислим случайните числа, симулираните вероятности са близки до нашите предполагаеми вероятности за търсене.

Ако въведете във всяка клетка формулата NORMINV(rand();mu,sigma), ще генерирате симулирана стойност на нормална случайна променлива със средно му и стандартно отклонение сигма. Тази процедура е илюстрирана във файловата Normalsim.xlsx, показана на Фигура 60-3.

Book Image

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

По същество за случайно число xформулата NORMINV(p,mu,sigma) генерира p-топроцентил на нормална случайна променлива със средно му и стандартно отклонение сигма. Например случайното число 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 итерации. Можете да намерите данните за този раздел във файла Valentine.xlsx, показан на Фигура 60-4. Задавате имената на диапазоните в клетки B1:B11 на клетки C1:C11. На диапазона от клетки G3:H6 се присвоява справката за име. Нашите параметри на продажната цена и разходите се въвеждат в клетки C4:C6.

Book Image

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

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

Ако произвеждаме повече карти, отколкото са в търсенето, броят на единиците, останали над, е равен на производството минус търсенето; в противен случай няма останали единици. Изчисляваме нашите разходи за реализация в клетка C10 с формулата unit_disp_cost*IF(произведени>търсене,произведени –търсене;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. В областта Серия В изберете опцията Колони и след това щракнете върху 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) като входна клетка за редове и изберете празна клетка (избрахме клетка I14) като входна клетка за колона. След като щракнете върху OK, Excel симулира 1000 стойности на търсенето за всяко количество поръчки.

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

Чрез копиране от клетка B13 в C13:E13 формулата AVERAGE(B16:B1015)изчисляваме средната симулирана печалба за всяко производствено количество. Чрез копиране от клетка B14 в C14:E14 формулата STDEV(B16: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*D14/SQRT(1000). В клетка J12 изчислявате горната граница за нашия доверителен интервал от 95 процента с формулата D13+1,96*D14/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 или да получите поддръжка в Общността за отговори от.

Нуждаете се от още помощ?

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

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

Доколко сте доволни от качеството на езика?
Какво е повлияло на вашия потребителски опит?

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

×