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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Финансови проектанти Използвайте Монтекарло симулиране, за да определи оптимално инвестиране стратегии за пенсиониране на своите клиенти.

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

Book Image
Фигура 60-1, показващи RAND функция

Забележка: Когато отворите файл 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. Тези резултати са съвместими с дефиницията на случайно число. Обърнете внимание, че стойностите, генерирани от 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
Фигура 60-2 симулиране дискретно случайна променлива

Ключът към нашия симулиране е да използвате случайно число да започне lookup от таблицата диапазон 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 създава търсене на 10000, случайно число между 0,10 и 0.45 създава търсене от 20 000 и т.н. В диапазон от клетки F8:F11 използвайте функцията COUNTIF да определите каква част от нашите 400 итерации получаване всяка заявка. Когато ние натиснете F9, за да го преизчислите случайни числа, симулиран вероятностите се затвори, за да ни предполагаема поискване вероятностите.

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

Book Image
Фигура 60-3 симулиране нормален случайна променлива

Да предположим, че искаме да наподобите 400 опити или повторения, за нормален случайна променлива с 40 000 средна стойност и стандартно отклонение на 10 000. (Можете да въведете тези стойности в клетки E1 и E2 и дайте име на тези клетки да кажете и Сигма, съответно.) Копиране на формулата = случайност() от C4 C5:C403 генерира 400 различни случайни числа. Копиране на от B4 B5:B403 формулата NORMINV(C4,mean,sigma) генерира 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.

В този раздел ще видите как черна Карло симулиране може да се използва като инструмент за вземане на решения. Да предположим, че търсенето на карта на PerformancePoint се управлява от следните дискретно случайна променлива:

Търсене

Вероятност

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
Фигура 60-4 симулиране на PerformancePoint карта

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

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

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

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

Book Image
Таблица с фигура 60-5 двупосочна данни за симулиране на поздравителна картичка

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

Book Image
Фигура 60-6 с помощта на серията в диалоговия прозорец за да попълните пробен числата от 1 до 1000

След това влиза нашите възможни производствен количества (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

В клетка Й11 вие изчисли долната граница за 95 процентен доверителен интервал на средна печалба, когато са произведени 40 000 календари с формула D13–1.96*D14/SQRT(1000). В клетка Й12 вие изчисли горната граница за нашите 95 процентен доверителен интервал с формула D13+1.96*D14/SQRT(1000). Тези изчисления са показани в фигура 60-7.

Book Image
Фигура 60-7 95 процента доверителния интервал за средната печалба, когато са подредени 40 000 календари

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

  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.

×