Wprowadzenie do symulacji Monte Carlo w programie Excel

Ten artykuł został zaadaptowany z książki Wayne'a L. Winstona "Analiza danych i modelowanie biznesowe programu Microsoft Excel ".

Omówienie

• Kto korzysta z symulacji Monte Carlo?
• Co się stanie po wpisaniu w komórce formuły =LOS() ?
• Jak można symulować wartości dyskretnej zmiennej losowej?
• Jak można symulować wartości normalnej zmiennej losowej?
• W jaki sposób firma produkująca kartki okolicznościowe może określić liczbę kartek do wyprodukowania?

Chcielibyśmy dokładnie oszacować prawdopodobieństwo zdarzeń niepewnych. Na przykład, jakie jest prawdopodobieństwo, że przepływy gotówkowe związane z nowym produktem będą miały dodatnią wartość bieżącą netto (NPV)? Jaki jest czynnik ryzyka naszego portfela inwestycyjnego? Symulacja Monte Carlo pozwala nam modelować sytuacje, które przedstawiają niepewność, a następnie odtwarzać je na komputerze tysiące razy.

W kolejnych pięciu rozdziałach przedstawiono przykłady wykorzystania programu Excel do przeprowadzania symulacji Monte Carlo.

Kto korzysta z symulacji Monte Carlo?

Wiele firm wykorzystuje symulację Monte Carlo jako ważną część procesu decyzyjnego. Oto kilka przykładów.

• General Motors, Proctor and Gamble, Pfizer, Bristol-Myers Squibb i Eli Lilly wykorzystują symulację do oszacowania zarówno średniego zwrotu, jak i czynnika ryzyka nowych produktów. W GM informacje te są wykorzystywane przez dyrektora generalnego do określenia, które produkty trafiają na rynek.
• GM wykorzystuje symulację do takich działań, jak prognozowanie dochodu netto korporacji, przewidywanie kosztów strukturalnych i zakupu oraz określanie jej podatności na różne rodzaje ryzyka (takie jak zmiany stóp procentowych i wahania kursów walut).
• Lilly wykorzystuje symulację do określenia optymalnej wydajności rośliny dla każdego leku.
• Proctor and Gamble wykorzystuje symulację do modelowania i optymalnego zabezpieczania ryzyka walutowego.
• Sears wykorzystuje symulację, aby określić, ile sztuk każdej linii produktów należy zamówić u dostawców - na przykład ile par spodni Dockers należy zamówić w tym roku.
• Firmy naftowe i farmaceutyczne używają symulacji do wyceny "rzeczywistych opcji", takich jak wartość opcji rozszerzenia, skrócenia lub odroczenia projektu.
• Planiści finansowi wykorzystują symulację Monte Carlo do określenia optymalnych strategii inwestycyjnych dla emerytury swoich klientów.

Co się stanie po wpisaniu w komórce formuły =LOS()?

Po wpisaniu formuły =LOS() w komórce otrzymasz liczbę, która z równym prawdopodobieństwem przyjmie dowolną wartość z zakresu od 0 do 1. Tak więc w około 25 procentach przypadków powinieneś otrzymać liczbę mniejszą lub równą 0,25; W około 10% przypadków powinieneś otrzymać liczbę o wartości co najmniej 0,90 itd. Aby zademonstrować, jak działa funkcja RAND, przyjrzyj się Randdemo.xlsx pliku pokazanemu na rysunku 60-1.

Najpierw skopiuj z komórki C3 do komórek C4:C402 formułę =LOS(). Następnie nadaj zakresowi nazwę C3:C402 Dane. Następnie w kolumnie F można śledzić średnią z 400 liczb losowych (komórka F2) i użyć funkcji LICZ.JEŻELI do określenia ułamków z zakresu od 0 do 0,25, 0,25 do 0,50, 0,50 do 0,75 oraz 0,75 do 1. Po naciśnięciu klawisza F9 liczby losowe są obliczane ponownie. Zauważ, że średnia z tych 400 liczb jest zawsze równa w przybliżeniu 0,5, a około 25% wyników znajduje się w przedziałach 0,25. Te wyniki są zgodne z definicją liczby losowej. Należy również zauważyć, że wartości generowane przez funkcję RAND w różnych komórkach są niezależne. Jeśli na przykład liczba losowa wygenerowana w komórce C3 jest duża (na przykład 0,99), nie mówi nam nic o wartościach innych wygenerowanych liczb losowych.

Jak można symulować wartości dyskretnej zmiennej losowej?

Załóżmy, że popyt na kalendarz jest regulowany przez następującą dyskretną zmienną losową:

Popyt	Prawdopodobieństwo
10 000	0,10
20 000	0.35
40,000	0,3
60 000	0,25

Jak to możliwe, aby program Excel wielokrotnie odgrywał lub symulował to zapotrzebowanie na kalendarze? Sztuka polega na tym, żeby skojarzyć każdą możliwą wartość funkcji RAND z ewentualnym zapotrzebowaniem na kalendarze. Poniższe przypisanie gwarantuje, że zapotrzebowanie na wartość 10 000 wystąpi w 10 procentach przypadków i tak dalej.

Popyt	Przypisany numer losowy
10 000	Mniejsza niż 0,10
20 000	Większa lub równa 0,10 i mniejsza niż 0,45
40,000	Większa lub równa 0,45 i mniejsza niż 0,75
60 000	Większa lub równa 0,75

Aby zademonstrować symulację popytu, spójrz na plik Discretesim.xlsx, pokazany na rysunku 60-2 na następnej stronie.

Kluczem do naszej symulacji jest użycie liczby losowej do zainicjowania wyszukiwania na podstawie zakresu tabeli F2:G5 (nazwanego odnośnikiem). Liczby losowe większe lub równe 0 i mniejsze niż 0,10 zwrócą zapotrzebowanie na 10 000; liczby losowe większe lub równe 0,10 i mniejsze niż 0,45 zwrócą zapotrzebowanie 20 000; Liczby losowe większe lub równe 0,45 i mniejsze niż 0,75 zwrócą zapotrzebowanie 40 000; a liczby losowe większe lub równe 0,75 zwrócą zapotrzebowanie 60 000. Można wygenerować 400 liczb losowych, kopiując z komórki C3 do komórki C4:C402 formułę LOS(). Następnie generuje się 400 prób (iteracji) zapotrzebowania kalendarzowego, kopiując z komórki B3 do komórki B4:B402 formułę WYSZUKAJ.PIONOWO(C3;wyszukaj;2). Ta formuła gwarantuje, że dowolna liczba losowa mniejsza niż 0,10 generuje zapotrzebowanie na wartość 10 000, dowolna liczba losowa z zakresu od 0,10 do 0,45 generuje zapotrzebowanie na wartość 20 000 i tak dalej. W zakresie komórek F8:F11 użyj funkcji LICZ.JEŻELI, aby określić część z 400 iteracji, które dają każde zapotrzebowanie. Kiedy naciśniemy klawisz F9 w celu ponownego obliczenia liczb losowych, symulowane prawdopodobieństwa są bliskie założonemu prawdopodobieństwu popytu.

Jak można symulować wartości normalnej zmiennej losowej?

Wpisanie w dowolnej komórce formuły ROZKŁAD.NORMALNY.ODW(LOS();MU;SIGMA) spowoduje wygenerowanie symulowanej wartości normalnej zmiennej losowej o średniej mu i sigma odchylenia standardowego. Procedura ta została zilustrowana w pliku Normalsim.xlsx, pokazanym na rysunku 60-3.

Załóżmy, że chcemy zasymulować 400 prób lub iteracji normalnej zmiennej losowej ze średnią 40 000 i odchyleniem standardowym równym 10 000. (Możesz wpisać te wartości w komórkach E1 i E2 oraz nazwać te komórki odpowiednio mean i sigma). Skopiowanie formuły =LOS() z komórki C4 do zakresu C5:C403 spowoduje wygenerowanie 400 różnych liczb losowych. Kopiując z komórki B4 do komórki B5:B403, formuła ROZKŁAD.NORMALNY.ODW(C4;średnia;sigma) generuje 400 różnych wartości próbnych z normalnej zmiennej losowej o średniej wartości 40 000 i odchyleniu standardowym równym 10 000. Po ponownym obliczeniu liczb losowych za pomocą klawisza F9 średnia pozostaje blisko 40 000, a odchylenie standardowe blisko 10 000.

Zasadniczo dla liczby losowej x formuła ROZKŁAD.NORMALNY.ODW(p;mu;sigma) generuje p-typercentyl normalnej zmiennej losowej ze średnią mu i sigma odchylenia standardowego. Na przykład liczba losowa 0,77 w komórce C4 (patrz rysunek 60-3) generuje w komórce B4 w przybliżeniu 77-ty percentyl normalnej zmiennej losowej ze średnią 40 000 i odchyleniem standardowym równym 10 000.

W jaki sposób firma produkująca kartki okolicznościowe może określić liczbę kartek do wyprodukowania?

W tej sekcji zobaczysz, w jaki sposób symulacja Monte Carlo może być wykorzystana jako narzędzie do podejmowania decyzji. Załóżmy, że popyt na kartki walentynkowe zależy od następującej dyskretnej zmiennej losowej:

Popyt	Prawdopodobieństwo
10 000	0,10
20 000	0.35
40,000	0,3
60 000	0,25

Karta z pozdrowieniami kosztuje 4,00 zł, a koszt zmienny wyprodukowania każdej kartki wynosi 1,50 zł. Pozostałe karty należy zutylizować, uiszczając opłatę w wysokości 0,20 USD za kartę. Ile kart należy wydrukować?

Zasadniczo symulujemy każdą możliwą wielkość produkcji (10 000, 20 000, 40 000 lub 60 000) wiele razy (na przykład 1000 iteracji). Następnie określamy, która wielkość zamówienia daje maksymalny średni zysk w ciągu 1000 iteracji. Dane dla tej sekcji można znaleźć w Valentine.xlsx pliku pokazanym na rysunku 60-4. Nazwy zakresów z komórek B1:B11 przypisuje się do komórek C1:C11. Zakres komórek G3:H6 ma przypisaną nazwę odnośnika. Nasze parametry ceny sprzedaży i kosztu zostaną wprowadzone w komórkach C4:C6.

W komórce C1 można wprowadzić próbną wielkość produkcyjną (w tym przykładzie 40 000). Następnie utwórz liczbę losową w komórce C2 przy użyciu formuły =LOS(). Jak opisano wcześniej, należy zasymulować zapotrzebowanie na kartę w komórce C3 za pomocą formuły WYSZUKAJ.PIONOWO(los;wyszukaj;2). (W formule WYSZUKAJ.PIONOWO los jest nazwą komórki przypisaną do komórki C3, a nie funkcji RAND).

Liczba sprzedanych sztuk jest mniejsza w stosunku do wielkości produkcji i popytu. W komórce C8 nasz przychód zostanie obliczony za pomocą formuły MIN(produkowano;popyt)*unit_price. W komórce C9 całkowity koszt produkcji oblicza się według wzoru wyprodukowane*unit_prod_cost.

Jeśli wyprodukujemy więcej kart niż jest zapotrzebowanie, liczba pozostałych jednostek równa się produkcji minus popyt; W przeciwnym razie nie pozostaną żadne jednostki. Koszt utylizacji obliczany jest w komórce C10 za pomocą wzoru unit_disp_cost*JEŻELI(wyprodukowane>zapotrzebowanie;wyprodukowane–popyt;0). Na koniec w komórce C11 nasz zysk obliczamy jako przychód — total_var_cost-total_disposing_cost.

Chcielibyśmy efektywnie naciskać F9 wiele razy (na przykład 1000) dla każdej wielkości produkcji i podliczać nasz oczekiwany zysk dla każdej ilości. W takiej sytuacji z pomocą przychodzi nam dwukierunkowa tabela danych. (Zobacz rozdział 15, "Analiza wrażliwości z tabelami danych", aby uzyskać szczegółowe informacje o tabelach danych). Tabelę danych użytą w tym przykładzie pokazano na rysunku 60-5.

W zakresie komórek A16:A1015 wprowadź liczby od 1 do 1000 (co odpowiada naszym 1000 próbom). Łatwym sposobem utworzenia tych wartości jest rozpoczęcie od wprowadzenia liczby 1 w komórce A16. Zaznacz komórkę, a następnie na karcie Narzędzia główne w grupie Edytowanie kliknij przycisk Wypełnij i wybierz pozycję Serie , aby wyświetlić okno dialogowe Serie . W oknie dialogowym Seria , pokazanym na rysunku 60-6, wprowadź wartość kroku 1 i wartość końcową 1000. W obszarze Series In wybierz opcję Kolumny, a następnie kliknij przycisk OK. Liczby od 1 do 1000 zostaną wprowadzone w kolumnie A, rozpoczynając od komórki A16.

Następnie wprowadzam możliwe wielkości produkcji (10 000, 20 000, 40 000, 60 000) w komórkach B15:E15. Chcemy obliczyć zysk dla każdego numeru próbnego (od 1 do 1000) i każdej wielkości produkcji. Odwołujemy się do formuły zysku (obliczonej w komórce C11) w lewej górnej komórce tabeli danych (A15), wprowadzając = C11.

Teraz jesteśmy gotowi, aby oszukać program Excel do symulacji 1000 iteracji zapotrzebowania dla każdej wielkości produkcji. Zaznacz zakres tabeli (A15:E1014), a następnie w grupie Narzędzia danych na karcie Dane kliknij przycisk Analiza warunkowa i wybierz tabelę danych. Aby skonfigurować dwukierunkową tabelę danych, wybierz wielkość produkcji (komórka C1) jako komórkę wprowadzania wiersza i wybierz dowolną pustą komórkę (wybraliśmy komórkę I14) jako komórkę wprowadzania kolumny. Po kliknięciu przycisku OK program Excel symuluje 1000 wartości zapotrzebowania dla każdej wielkości zamówienia.

Aby zrozumieć, dlaczego tak działa, analizuj wartości umieszczone w tabeli danych w zakresie komórek C16:C1015. W przypadku każdej z tych komórek program Excel użyje wartości 20 000 w komórce C1. W komórce C16 wartość 1 w komórce wprowadzania w kolumnie jest umieszczana w pustej komórce, a liczba losowa w komórce C2 jest obliczana ponownie. Odpowiadający zysk jest wówczas rejestrowany w komórce C16. Następnie wartość wejściowa 2 w komórce kolumny jest umieszczana w pustej komórce, a liczba losowa w komórce C2 jest ponownie obliczana. Odpowiedni zysk zostanie wprowadzony do komórki C17.

Kopiując z komórki B13 do komórek C13:E13 formułę ŚREDNIA(B16:B1015), obliczamy średni symulowany zysk dla każdej wielkości produkcji. Kopiując z komórki B14 do komórki C14:E14 formułę ODCH.STANDARDOWE(B16:B1015), obliczamy odchylenie standardowe naszych symulowanych zysków dla każdej wielkości zamówienia. Za każdym razem, gdy naciskamy klawisz F9, symulowanych jest 1000 iteracji zapotrzebowania dla każdej wielkości zamówienia. Wyprodukowanie 40 000 kart zawsze przynosi największy oczekiwany zysk. Dlatego wydaje się, że wyprodukowanie 40 000 kart jest właściwą decyzją.

Wpływ ryzyka na naszą decyzję Gdybyśmy wyprodukowali 20 000 zamiast 40 000 kart, nasz oczekiwany zysk spadłby o około 22 procent, ale nasze ryzyko (mierzone odchyleniem standardowym zysku) spada o prawie 73 procent. Dlatego, jeśli jesteśmy wyjątkowo niechętni ryzyku, wyprodukowanie 20 000 kart może być właściwą decyzją. Nawiasem mówiąc, wyprodukowanie 10 000 kart zawsze ma odchylenie standardowe równe 0 kart, ponieważ jeśli wyprodukujemy 10 000 kart, zawsze sprzedamy je wszystkie bez żadnych resztek.

Przedział ufności dla średniego zysku Naturalnym pytaniem, które należy zadać w tej sytuacji, jest: w jakim przedziale jesteśmy w 95 procentach pewni, że spadnie prawdziwy średni zysk? Ten interwał jest nazywany 95-procentowym przedziałem ufności dla średniego zysku. 95-procentowy przedział ufności dla średniej wyników każdej symulacji oblicza się za pomocą następującej formuły:

W komórce J11 należy obliczyć dolną granicę 95-procentowego przedziału ufności dla średniego zysku, kiedy utworzono 40 000 kalendarzy przy użyciu formuły D13–1,96*D14/PIERWIASTEK(1000). W komórce J12 górną granicę 95-procentowego przedziału ufności oblicza się za pomocą formuły D13+1,96*D14/PIERWIASTEK(1000). Obliczenia te przedstawiono na rysunku 60-7.

Jesteśmy na 95 procent pewni, że nasz średni zysk po zamówieniu 40 000 kalendarzy wynosi od 56 687 zł do 62 589 zł.

Problemy

1. Dealer GMC uważa, że popyt na Envoys 2005 będzie rozkładał się normalnie ze średnią 200 i odchyleniem standardowym 30. Jego koszt otrzymania wysłannika wynosi 25 000 dolarów, a sprzedaje go za 40 000 dolarów. Połowa wszystkich Emisariuszy, którzy nie zostali sprzedani za pełną cenę, może zostać sprzedana za 30 000 dolarów. Rozważa zamówienie 200, 220, 240, 260, 280 lub 300 posłów. Ile powinien zamówić?

2. Mały supermarket próbuje ustalić, ile egzemplarzy magazynu People powinni zamawiać każdego tygodnia. Uważają, że ich popyt na People jest regulowany przez następującą dyskretną zmienną losową:

   Popyt	Prawdopodobieństwo
   15	0,10
   20	0.20
   25	0.30
   30	0,25
   35	0,15

3. Supermarket płaci 1,00 USD za każdą kopię People i sprzedaje ją za 1,95 USD. Każdy niesprzedany egzemplarz można zwrócić za 0,50 USD. Ile egzemplarzy aplikacji People powinien zamówić sklep?

Potrzebujesz dodatkowej pomocy?

Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel lub uzyskać pomoc techniczną w społecznościach.