Wprowadzenie do symulacji Monte Carlo w programie Excel

Uwaga: Staramy się udostępniać najnowszą zawartość Pomocy w Twoim języku tak szybko, jak to możliwe. Ta strona została przetłumaczona automatycznie i może zawierać błędy gramatyczne lub nieścisłości. Chcemy, aby ta zawartość była dla Ciebie przydatna. Prosimy o powiadomienie nas, czy te informacje były pomocne, u dołu tej strony. Oto angielskojęzyczny artykuł do wglądu.

Ten artykuł został dostosowany za pomocą analizy danych programu Microsoft Excel i modelowania biznesoweGo Wayne L. Winston.

  • Kto korzysta z symulacji Carlo Monte?

  • Co się dzieje po wpisaniu w komórce funkcji = Los () ?

  • Jak można symulować wartości w odrębnej zmiennej losowej?

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

  • Jak firma karty powitania określa liczbę kart do wygenerowania?

Chcemy dokładnie oszacować prawdopodobieństwa niepewnych zdarzeń. Na przykład co to jest prawdopodobieństwo, że przepływy gotówkowe nowego produktu będą miały dodatnią wartość bieżącą netto (NPV)? Jaki jest czynnik ryzyka portfela inwestycji? Symulacja Monte Carlo umożliwia nam modelowanie sytuacji, które stanowią niepewność, a następnie odtwarza je na komputerze tysiące razy.

Uwaga:  Symulacja Monte Carlo jest realizowana na podstawie symulacji komputera wykonywanych w trakcie 1930s i 1940s w celu oszacowania prawdopodobieństwa, że reakcja łańcuchowa wymagana dla Atom bomb na Detonate będzie działać pomyślnie. Physicists w tej pracy był dużym wentylatorem hazardu, więc otrzymali symulacje o nazwie kodu Monte Carlo.

W następnych pięciu rozdziałach zostaną wyświetlone przykłady korzystania z programu Excel w celu wykonywania Monte Carlo.

Wiele firm korzysta z symulacji Carlo Monte jako ważnej części procesu podejmowania decyzji. Oto kilka przykładów.

  • General Motors, Proctor, Gamble, Pfizer, Bristolu-Myers Squibb i Eli Lilly wykorzystuj symulację do oszacowania zarówno średniej wartości zwrotnej, jak i czynnika ryzyka nowych produktów. W przypadku GM informacje te są używane przez PREZESa w celu ustalenia, które produkty są dostępne na rynku.

  • GM wykorzystuje symulację dla działań, takich jak prognozowany dochód netto dla firmy, prognozowane koszty strukturalne i dotyczące zakupu oraz określające podatność na różne rodzaje ryzyka (takie jak zmiany stopy procentowej i wahania kursu walutowego).

  • Lilly wykorzystuje symulację w celu określenia optymalnego potencjału produkcyjnego dla każdego narkotyku.

  • Proctor i Gamble korzysta z symulacji do modelu i optymalnie hedgingu ryzyka związanego z wymianą walut obcych.

  • Sears wykorzystuje symulację w celu określenia, ile jednostek każdego wiersza produktu należy zamówić u dostawców, na przykład do liczby par doków, które mają być zamawiane w tym roku.

  • W przypadku ropy naftowej i środków na narkotykach jest używana symulacja wartości "Opcje prawdziwe", na przykład wartość opcji rozwijania, kontraktu lub odkładania projektu.

  • Funkcje planowania finansowego wykorzystują symulację Monte Carlo w celu określenia optymalnych strategii inwestycyjnych dotyczących emerytury klientów.

Gdy wpiszesz formułę = Los () w komórce, otrzymasz liczbę, która na równi może przyjmować dowolną wartość z zakresu od 0 do 1. W związku z tym około 25% czasu należy uzyskać liczbę mniejszą lub równą 0,25; około 10 procent czasu, w którym należy uzyskać numer o wartości co najmniej 0,90 itd. Aby sprawdzić, jak działa funkcja Los, zapoznaj się z plikiem Randdemo. xlsx przedstawionym na rysunku 60-1.

Obraz książki

Uwaga:  Po otwarciu pliku Randdemo. xlsx nie będą wyświetlane te same liczby losowe przedstawione na rysunku 60-1. Funkcja Los zawsze automatycznie oblicza ponownie wartości liczbowe generowane podczas otwierania arkusza lub wprowadzania nowych informacji w arkuszu.

Najpierw skopiuj z komórki C3 na C4: C402 formuła = Los (). Następnie nadaj nazwę zakresowi C3: C402 danych. Następnie w kolumnie F można śledzić orednią liczb losowych 400 (komórka F2), a następnie używać funkcji LICZ. Jeżeli należy określić ułamki z zakresu od 0 do 0,25, 0,25 i 0,50, 0,50 oraz 0,75, a 0,75 i 1. Po naciśnięciu klawisza F9 liczby losowe są obliczane ponownie. Zwróć uwagę, że średnia liczb 400 jest zawsze około 0,5, a około 25 procent wyników jest w przedziałach czasu z 0,25. Wyniki te są zgodne z definicją liczby losowej. Ponadto należy pamiętać, że wartości generowane przez los w różnych komórkach są niezależne. Jeśli na przykład liczba losowa wygenerowana w komórce C3 jest dużą liczbą (na przykład 0,99), to nic nie poinformuje nas o wartościach innych generowanych liczb pseudolosowych.

Załóżmy, że popyt dla kalendarza jest regulowany przez poniższą zmienną losową:

Żądać

Prawdopodobieństwo

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Jak często program Excel odtwarza lub symuluje to zapotrzebowanie na kalendarze? Lewę jest kojarzenie każdej możliwej wartości funkcji Los z możliwym zapotrzebowaniem na kalendarze. Poniższe zadanie zapewnia, że zapotrzebowanie 10 000 będzie wynosić 10 procent czasu itd.

Żądać

Przypisana liczba losowa

10 000

Mniej niż 0,10

20 000

Większe niż lub równe 0,10 i mniejsze niż 0,45

40 000

Większe niż lub równe 0,45 i mniejsze niż 0,75

60 000

Większe niż lub równe 0,75

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

Obraz książki

Kluczem do symulacji jest użycie liczby losowej w celu zainicjowania odnośnika z zakresu tabeli F2: G5 (nazwane Wyszukiwanie). Liczba losowa większa lub równa 0 i mniejsza niż 0,10 będzie mieć zapotrzebowanie 10 000; liczby losowe większe niż lub równe 0,10 i mniejsze niż 0,45 będą miały zapotrzebowanie na 20 000; liczby losowe większe niż lub równe 0,45 i mniejsze niż 0,75 będą miały zapotrzebowanie na 40 000; liczby losowe większe niż lub równe 0,75 będą miały zapotrzebowanie 60 000. Aby wygenerować liczby losowe 400, skopiuj od C3 do C4: C402 formuły Los (). Następnie należy wygenerować 400 prób lub iteracje zapotrzebowania kalendarza, kopiując z komórki B3 na B4: B402. pionowa formuła Wyszukaj. pionowo (C3; Lookup; 2). Ta formuła zapewnia, że każda liczba losowa mniejsza niż 0,10 generuje zapotrzebowanie 10 000, każda liczba losowa z zakresu od 0,10 do 0,45 generuje zapotrzebowanie na 20 000 itd. W zakresie komórek F8: F11 należy użyć funkcji LICZ. Jeżeli w celu określenia ułamka iteracji 400, co daje każde żądanie. Po naciśnięciu klawisza F9 w celu ponownego obliczenia liczb losowych symulowane prawdopodobieństwa są zbliżone do naszych zajmowanego popytu prawdopodobieństwa.

Jeśli wpiszesz dowolną komórkę, Funkcja ROZKŁAD. normalny. tekst (Los (); średnia. ODW)wygeneruje symulowaną wartość normalnej zmiennej losowej o średniej średnia i odchyleniu standardowym Sigma. Tę procedurę przedstawiono w pliku Normalsim. xlsx przedstawionym na rysunku 60-3.

Obraz książki

Załóżmy, że chcemy symulować 400 prób lub iteracje dla normalnej zmiennej losowej ze średnią 40 000 i odchyleniem standardowym 10 000. (Te wartości można wpisywać w komórkach E1 i E2, a nazwy te są odpowiednio średniej i Sigma). Skopiowanie formuły = Los () z C4 do C5: C403 generuje 400 różnych liczb losowych. Kopiowanie z B4 do B5: B403 formuła rozkład. normalny. ODW, średnia, Sigma) generuje 400 różnych wartości próbnych na podstawie normalnej zmiennej losowej ze średnią 40 000 i odchyleniem standardowym 10 000. Po naciśnięciu klawisza F9 w celu ponownego obliczenia liczb losowych średnia pozostanie blisko 40 000, a odchylenie standardowe blisko 10 000.

Zasadniczo, w przypadku liczby losowej xformuła ROZKŁAD. normalny. ODW (p; średnia; Sigma) generuje po percentylie normalnej zmiennej losowej o średniej średnia i odchylenie standardowe Sigma. Na przykład liczba losowa 0,77 w komórce C4 (zobacz rysunek 60-3) generuje w komórce B4 około 77th percentyl normalnej zmiennej losowej o wartości średniej z 40 000 i odchylenie standardowe w 10 000.

W tej sekcji dowiesz się, jak Monte Carlo może być używana jako narzędzie do podejmowania decyzji. Załóżmy, że popyt na kartę walentynkową jest regulowany następującą odrębną zmienną losową:

Żądać

Prawdopodobieństwo

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Karta z pozdrowieniami sprzedaje się na $4,00, a koszt zmienny każdej karty to $1,50. Pozostała część kart musi być zbywana po cenie $0,20 za jedną kartę. Ile kart powinno być drukowanych?

Zasadniczo wszystkie możliwe wielkości produkcji (10 000, 20 000, 40 000 lub 60 000) są symulowane wiele razy (na przykład w przypadku iteracji na 1000). Następnie określisz, która ilość zamówienia jest w przybliżeniu średnia zysku w iteracjach usługi 1000. Dane dla tej sekcji można znaleźć w pliku Walentynki. xlsx przedstawionym na rysunku 60-4. Nazwy zakresów w komórkach B1: B11 należy przypisać do komórek C1: C11. Zakres komórek: H6 jest przypisany do wyszukiwanianazw. Nasze parametry ceny sprzedaży i kosztów są wprowadzane w komórkach C4: C6.

Obraz książki

W komórce C1 można wprowadzić ilość produkcji próbnej (40 000 w tym przykładzie). Następnie utwórz liczbę losową w komórce C2 z formułą = Los (). Jak opisano wcześniej, zasymulujesz popyt karty w komórce C3 za pomocą formuły Wyszukaj. pionowo (Rand, Wyszukaj, 2). (W formule Wyszukaj. pionowo Los jest nazwą komórki przypisaną do komórki C3, a nie z funkcji Los.)

Liczba sprzedanych jednostek jest mniejsza od ilości produkcji i popytu. W komórce C8 Obliczanie przychodów za pomocą formuły min (produkcja, popyt) * unit_price. W komórce C9 obliczany jest całkowity koszt produkcji za pomocą formuły wyprodukowana * unit_prod_cost.

Jeśli Oferujemy więcej kart, niż są na życzenie, liczba jednostek, do których przekroczy się równa się produkcja pomniejszona o zapotrzebowanie; w przeciwnym razie żadne jednostki nie są pozostawione. Obliczanie kosztu likwidacji w komórce C10 przy użyciu formuły unit_disp_cost * jeżeli (produced>demand, produkcja-popyt; 0). Wreszcie, w komórce C11, możemy obliczyć nasze zyski jako przychód — total_var_cost-total_disposing_cost.

Chcemy mieć wydajny sposób wielokrotnego naciskania klawisza F9 (na przykład 1000) dla każdej ilości produkcyjnej i wyrównać nasze oczekiwane zyski za każdą ilość. Taka sytuacja jest taka, w której dwukierunkowa tabela danych jest oparta na naszej stronie ratowniczej. (Zobacz rozdział 15, "Analiza czułości z tabelami danych", aby uzyskać szczegółowe informacje o tabelach danych). Tabela danych używana w tym przykładzie pokazano na rysunku 60-5.

Obraz książki

W komórce zakres komórek A16: A1015 Wprowadź liczby 1 – 1000 (odpowiadające naszym próbom 1000). Jednym z łatwych metod tworzenia tych wartości jest rozpoczęcie wprowadzania 1 w komórce A16. Zaznacz komórkę, a następnie na karcie Narzędzia główne w grupie Edytowanie kliknij przycisk wypełnienie, a następnie wybierz pozycję Seria , aby wyświetlić okno dialogowe serie . W oknie dialogowym serie pokazanym na rysunku 60-6 wprowadź wartość kroku 1 i wartość końcową równą 1000. W obszarze serie wybierz opcję kolumny , a następnie kliknij przycisk OK. Liczby 1 – 1000 zostaną wprowadzone w kolumnie A, począwszy od komórki A16.

Obraz książki

Następnie wprowadź nasze możliwe ilości (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 ilości produkcyjnej. Odwołujemy się do formuły zysku (obliczanej w komórce C11) w lewej górnej komórce naszej tabeli danych (A15), wprowadzając = C11.

Teraz wszystko gotowe do nakłonienia programu Excel do symulacji 1000 iteracji popytu na każdą ilość produkcyjną. Wybierz zakres tabeli (A15: E1014), a następnie w grupie Narzędzia danych na karcie Dane kliknij pozycję co to zrobić, a następnie wybierz pozycję Tabela danych. Aby skonfigurować dwukierunkową tabelę danych, wybierz odpowiednią ilość produkcji (komórka C1) jako wierszową komórkę wejściową i zaznacz dowolną pustą komórkę (wybrano komórkę I14) jako komórkę wejściową kolumny. Po kliknięciu przycisku OK program Excel symuluje wartości 1000 zapotrzebowania dla każdej ilości zamówienia.

Aby zrozumieć, dlaczego to działa, należy rozważyć wartości umieszczane w tabeli danych w zakresie komórek z zakresu C16: C1015. W przypadku każdej z tych komórek program Excel użyje wartości 20 000 w komórce C1. W C16 wartość wejściowa komórki kolumny 1 jest umieszczana w pustej komórce, a liczba losowa w komórce C2 jest obliczana ponownie. Odpowiedni zysk jest następnie rejestrowany w komórce C16. Wartość wejściowa komórki kolumny równa 2 jest umieszczana w pustej komórce, a liczba losowa ponownie przelicza. Odpowiedni zysk jest wprowadzany w komórce C17.

Kopiując z komórki B13 do C13: E13 średnia formuły (B16: B1015), obliczana jest średnia symulowanych zysków dla każdej wielkości produkcji. Kopiując z komórki B14 do C14: E14 formuły STDEV (B16: B1015), obliczana jest odchylenie standardowe naszych symulowanych zysków dla każdej ilości zamówienia. Za każdym razem, gdy naciskamy klawisz F9, 1000 iteracji popytu jest symulowane dla każdej ilości zamówienia. Produkowanie 40 000 kart zawsze daje największe oczekiwane zyski. Dlatego wydaje się, że wytwarzanie kart 40 000 jest właściwą decyzją.

Wpływ ryzyka na nasze decyzje     Jeśli firma Microsoft wygenerowała 20 000 zamiast kart 40 000, spodziewana wartość zysku spadnie około 22 procent, ale ryzyko (zmierzone przez odchylenie standardowe zysku) spadnie prawie 73 procent. W związku z tym, w przypadku niezwykle averseych czynników ryzyka, producent 20 000 karty może być właściwą decyzją. W przypadku niekorzystnej sytuacji produkcja kart 10 000 ma zawsze standardowe odchylenie o wartości 0 kart, ponieważ jeśli wytwarzam 10 000 kart, zawsze będzie ona sprzedawana bez żadnych resztek.

Uwaga:  W tym skoroszycie opcja obliczeń jest ustawiona na wartość automatycznie, z wyjątkiem tabel. (Użyj polecenia Oblicz w grupie Obliczanie na karcie formuły). To ustawienie zapewnia, że nasza tabela danych nie będzie obliczana ponownie, chyba że naciśniemy klawisz F9, co jest dobrym pomysłem, ponieważ duża tabela danych spowalnia pracę, jeśli będzie obliczana ponownie za każdym razem, gdy wpiszesz coś w arkuszu. Uwaga: w tym przykładzie po naciśnięciu klawisza F9 zysk zostanie zmieniony. Dzieje się tak, ponieważ przy każdym naciśnięciu klawisza F9 jest używana inna sekwencja 1000 liczb losowych, które generują wymagania dotyczące każdej ilości zamówienia.

PrzeDział ufności dla średniej wartości zysku     W tym przypadku pytanie naturalne, z którym należy zwrócić uwagę, to, w jakich interwałach są 95 procentowe, że dojdzie do prawdziwej średniej zysku? Ten interwał jest nazywany interwałem ufności (95%) dla średniej wartości zysku. 95 procentowy interwału ufności dla średniej dodanej symulacji jest obliczany za pomocą następującej formuły:

Obraz książki

W komórce J11 oblicza się dolną granicę interwału ufności 95 procent dla średniej wartości zysku, gdy kalendarze 40 000 są tworzone za pomocą formuły D13 — 1.96 * D14/sqrt (1000). W komórce J12 oblicza górną granicę dla naszego interwału ufności 95% z formułą D13 + 1.96 * D14/sqrt (1000). Te obliczenia przedstawiono na rysunku 60-7.

Obraz książki

Jesteśmy 95% na to, że nasze średnie zyski po przeniesieniu kalendarzy 40 000 znajdują się między $56 687 a $62 589.

  1. GMC dealer uważa, że popyt na 2005 Envoys będzie rozkładem normalnym ze średnią 200 i odchyleniem standardowym 30. Koszt otrzymania Envoy to $25 000, a sprzedaje Envoy dla $40 000. Połowa wszystkich Envoys nie sprzedanych po pełnej cenie może zostać sprzedana za $30 000. Rozważa on zamawianie 200, 220, 240, 260, 280 lub 300 Envoys. Ile należy zamówić?

  2. Mały supermarket na rynku próbuje określić, ile kopii z czasopisma ma być uporządkowanych w każdym tygodniu. Uważają, że ich popyt na osoby jest regulowany przez następującą, dyskretną zmienną losową:

    Żądać

    Prawdopodobieństwo

    15

    0,10

    20

    0,20

    art

    0,30

    30

    0,25

    35

    0,15

  3. Supermarket płaci $1,00 dla każdej kopii osób i sprzedaje ją w $1,95. Każda niesprzedana kopia może zostać zwrócona dla $0,50. Ile kopii osób powinno się zamówić w sklepie?

Potrzebujesz dodatkowej pomocy?

Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel, uzyskać pomoc techniczną w społeczności witryny Answers bądź zasugerować nową funkcję lub ulepszenie w witrynie UserVoice dotyczącej programu Excel.

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×