Wprowadzenie do Monte Carlo symulacji 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ł pochodzi z programu Microsoft Excel analizy i modelowanie biznesowe przez Wayne Winston L..

  • Kto korzysta Monte Carlo symulacji?

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

  • Jak można symulować wartości osobne zmienną losową?

  • Jak można symulować wartości losowej zmiennej normal

  • Jak firmy karty z pozdrowieniami ustalić, ile kart da?

Chcemy dokładnie oszacować prawdopodobieństwa pewności zdarzeń. Na przykład co to jest prawdopodobieństwo, że przepływów gotówkowych nowego produktu dodatnią wartość bieżącą netto (NPV)? Co to jest czynnik ryzyka o nasza oferta inwestycji? Symulacja Monte Carlo umożliwia nam sytuacje modelu prezentowanie wątpliwości, a następnie odtworzyć je na komputerze tysięcy razy.

Uwaga: Nazwa Monte Carlo symulacji pochodzi z symulacji komputera w czasie 1930s i 1940s oszacowanie prawdopodobieństwo, że reakcji wymagane przez bomba atom do detonacji będzie działać pomyślnie. Physicists biorących udział w tym pracy były duży wentylatory przedstawiające hazard, więc ich przekazała symulacji Monte Carlonazwę kodową.

W następnych pięciu rozdziały będą widoczne przykłady wykorzystania programu Excel do wykonywania symulacji Monte Carlo przez.

Wiele firm za pomocą symulacji Monte Carlo jako ważne część procesu podejmowania decyzji. Oto kilka przykładów.

  • Ogólne Motors, Proctor i hazard Pfizer, Squibb Myers Bristolski i Eli Lilly za pomocą symulacji oszacować zarówno średni zwrot i czynnik ryzyka nowych produktów. U GM to informacje są używane przez Dyrektor Generalny do określenia, które produkty są formatowane do obrotu.

  • GM używa symulacji działań, takich jak prognozowanie dochód netto dla firmy, przewidywania koszty strukturalnych i zakupów i określanie jego wrażliwości na różnych rodzajów ryzyka (na przykład stopy procentowej zmiany i zmianami kurs).

  • Lilly używa symulacji, aby określić wydajność fabryki optymalne dla każdego leków.

  • Proctor i hazard używa symulacji modelu i optymalnie zabezpieczyć ryzyko walutowe.

  • Sears użyto symulacji, aby ustalić liczbę jednostek dla każdej linii produktów powinny być uporządkowane od dostawców — na przykład liczba par spodnie dokerskie, które powinny być uporządkowane w tym roku.

  • Oil i leków firmy za pomocą symulacji do wartości "rzeczywistą opcji", takich jak wartość opcji rozszerzenia, umowy lub opóźnić projektu.

  • Finansowe urlopowe za pomocą symulacji Monte Carlo Określanie strategii optymalnego inwestycji dla wycofywanie swoich klientów.

Podczas wpisywania formuły =LOS() w komórce, możesz uzyskać numeru jednakowo prawdopodobnie przyjmuje wartości od 0 do 1. W związku z tym około 25% czasu, należy uzyskać liczby mniejsza niż lub równa 0,25; około 10 procent czasu powinna pobierać numeru jest co najmniej 0.90 i tak dalej. Aby pokazano, jak działa funkcja RAND, zapoznaj się z pliku Randdemo.xlsx, pokazano na ilustracji 60-1.

Obraz książki
Rysunek 60-1 pokazujące LOS, funkcja

Uwaga: Po otwarciu pliku Randdemo.xlsx nie zobaczą te same liczby losowe pokazano na ilustracji 60-1. Funkcja RAND zawsze automatycznie oblicza ponownie liczby, które generuje po otwarciu arkusza lub wprowadzenie nowych informacji do arkusza.

Najpierw skopiować z komórki C3 do C4:C402 formuły =LOS(). Następnie nazwę zakresu C3:C402 danych. Następnie w kolumnie F, można śledzić średnią 400 liczb losowych (komórka F2) i użyj funkcji LICZ ustalenie ułamki, które są między 0 a 0,25, 0,25 i 0,50, 0,50 i 0,75 i 0,75 i 1. Po naciśnięciu klawisza F9, są obliczane ponownie liczby losowe. Powiadomienie, że średniej wartości liczb 400 jest zawsze około 0,5 i że około 25 procent wyników znajdują się w odstępach 0,25. Wyniki te są zgodne z definicją liczbę losową z zakresu. Należy również zauważyć, że wartości wygenerowane przez LOS w różnych komórkach są niezależne. Na przykład jeśli liczbę losową wygenerowany w komórce C3 jest dużą liczbą (na przykład 0,99.), informuje nam nic o wartości inne liczby losowe wygenerowane.

Załóżmy, że żądanie dla kalendarza podlega następujące osobne zmienną losową:

Żądanie

Prawdopodobieństwo

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Jak możemy program Excel może odtworzyć lub symulować, to żądanie kalendarzy wielokrotnie? Lewy należy skojarzyć każdej możliwe wartości funkcji LOS możliwe żądanie kalendarzy. Następującego przypisania zapewnia, że żądanie 10 000 będzie wystąpić 10 procent czasu i tak dalej.

Żądanie

Liczba losowa przypisywana

10 000

Mniejsza niż 0,10

20 000

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

40 000

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

60 000

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

Aby wykazać symulacji żądanie, spójrz na plik Discretesim.xlsx, pokazano na ilustracji 60-2 na następnej stronie.

Obraz książki
Rysunek 60-2 symulowanie zmienną losową dyskretnego

Klucz do naszych symulacji jest użycie liczbę losową z zakresu zainicjować wyszukiwania z zakresu tabeli F2:G5 (o nazwie wyszukiwania). Liczby losowe większe niż lub równa 0 i mniejsza niż 0,10 dadzą żądanie 10 000; liczby losowe większe niż lub równe 0,10 i mniejsza niż 0,45 dadzą żądanie 20 000; liczby losowe większe niż lub równe 0,45 i mniejsza niż 0,75 dadzą żądanie 40 000; i liczby losowe większe niż lub równe 0,75 dadzą żądanie 60 000. Możesz wygenerować 400 liczby losowe, kopiując z C3 do C4:C402 formuły RAND(). Następnie wygenerować 400 prób lub iteracji, kalendarz zapotrzebowania przez skopiowanie z B3 B4:B402 formuły VLOOKUP(C3,lookup,2). Ta formuła gwarantuje, że dowolną liczbę losową wartość mniejszą niż 0,10 generuje żądanie 10 000, dowolną liczbę losową między 0,10 a 0,45 generuje żądanie 20 000 i tak dalej. W zakresie komórek F8:F11 za pomocą funkcji LICZ określisz część naszych 400 iteracji produkcji każdego żądanie. Firma Microsoft naciśnij klawisz F9, aby ponownie obliczyć liczby losowe, symulowany prawdopodobieństwa zbliżają prawdopodobieństwa naszym założonej żądanie.

Jeśli wpiszesz w dowolnej komórce formuły NORMINV(rand(),mu,sigma), wygeneruje symulowany wartość Normalny zmienną losową o średniej mu i odchylenie standardowe sigma. Ta procedura przedstawiono w pliku Normalsim.xlsx, pokazane w rysunku 60-3.

Obraz książki
Rysunek 60-3 symulowanie zmienną losową normalny

Załóżmy, że chcemy zasymulowania 400 prób lub iteracji dla normalnego zmienną losową o średniej 40 000 i odchylenie standardowe o wartości 10 000. (Możesz można wpisz następujące wartości w komórkach E1 i E2 i odpowiednio nazwy tych komórek, Średnia i sigma.) Kopiowanie formuły =LOS() z C4 do C5:C403 generuje 400 różne liczby losowe. Kopiowanie z B4 do B5:B403 formuła NORMINV(C4,mean,sigma) generuje 400 różnych wersji próbnej wartości z normalnym zmienną losową o średniej 40 000 i odchylenie standardowe o wartości 10 000. Firma Microsoft naciśnij klawisz F9, aby ponownie obliczyć liczby losowe, średniej pozostaje zbliżony 40 000 i odchylenia standardowego zbliżony 10 000.

Zasadniczo liczbę losową xformuły NORMINV(p,mu,sigma) generuje pty percentyl losowej zmiennej normal z średniej mu i odchylenie standardowe sigma. Na przykład liczba 0,77 w komórce C4 (patrz rysunek 60-3) generuje w komórce B4 około 77th percentyl normalny zmienną losową o średniej 40 000 i odchylenie standardowe o wartości 10 000.

W tej sekcji zostanie wyświetlony, jak symulacji Monte Carlo może być używany jako narzędzie podejmowania decyzji. Załóżmy, że zapotrzebowania na Walentynki karta podlega następujące osobne zmienną losową:

Żądanie

Prawdopodobieństwo

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Karta z pozdrowieniami sprzedawanych dla $4.00, a 1,50 PLN jest koszt zmiennej produkcji każdej z kart. Pozostała karty musi być usuwane przy koszcie 0,20 zł na karcie. Ile kart powinny być drukowane?

Zasadniczo możemy symuluje każdej ilości możliwych produkcji (10 000, 20 000, 40 000 lub 60 000) wielokrotnie (na przykład 1000 powtórzeń). Następnie określamy, która ilość kolejności daje maksymalny zysk średnia nad iteracji 1000. Dane można znaleźć w tej sekcji w pliku Valentine.xlsx, pokazano na ilustracji 60-4. Nazwy zakresu komórek B1:B11 przypisać C1:C11 komórki. Zakres komórek G3:H6 przypisano nazwę wyszukiwania. Nasze ceny sprzedaży i parametrów kosztu są wprowadzone w komórkach C4: C6.

Obraz książki
Symulacja karty Walentynki rysunek 60-4

Możesz wprowadzić liczbę wersji próbnej produkcji (40 000 w tym przykładzie) w komórce C1. Następnie należy utworzyć liczbę losową z zakresu w komórce C2 przy użyciu formuły =LOS(). Opisane wcześniej symulowania żądanie karty w komórce C3 z formuły VLOOKUP(rand,lookup,2). (W formuła Wyszukaj.pionowo rand jest przypisana do komórki C3, nie LOS nazwę komórki).

Liczba sprzedanych jednostek jest mniejsza z naszych ilość produkcji i żądanie. W komórce C8 obliczyć naszych zysk z formułą MIN (wyprodukowane, żądanie) * unit_price. W komórce C9 obliczyć całkowity koszt produkcji przy użyciu formuły wyprodukowano * unit_prod_cost.

Jeśli firma Microsoft warzywa karty więcej niż żądanie, liczba jednostek pozostałej produkcyjnej jest równa minus żądanie; w przeciwnym razie nie jednostki pozostają. Firma Microsoft obliczyć koszt usuwania C10 komórki z formułą unit_disp_cost * Jeżeli (wyprodukowano > żądanie, wyprodukowano — żądanie, 0). Na koniec w komórce C11 możemy obliczyć naszych zysku jako Przychód — total_var_cost — total_disposing_cost.

Chcemy skuteczny sposób naciśnij klawisz F9, ile razy (na przykład 1000) dla każdej ilości produkcji i są zgodne z naszych oczekiwanych zysku dla każdej ilości. Sytuacja jest jednym w którym dwukierunkowa tabela danych przychodzące naszych ratunkowych. (Aby uzyskać szczegółowe informacje o tabelach danych, zobacz rozdział 15 "Charakter analizy z danych w tabelach,"). Tabela danych, w tym przykładzie użyto jest wyświetlana w rysunku 60-5.

Obraz książki
Rysunek 60-5 dwukierunkowa tabela danych dla symulacji karty z pozdrowieniami

W zakresie komórek A16:A1015 wprowadź liczby 1 — 1000 (odpowiadającą naszych badań 1000). Łatwym sposobem tworzenia tych wartości jest rozpoczęcie wprowadzając 1 w komórce A16. Zaznacz komórkę i na karcie Narzędzia główne w grupie Edytowanie, kliknij przycisk wypełnienia i wybierz serię, aby wyświetlić okno dialogowe serii. W oknie dialogowym serii pokazano na ilustracji 60-6 wprowadź wartość kroku 1 i zatrzymywanie wartość 1000. W obszarze Serie w wybierz opcję kolumny, a następnie kliknij przycisk OK. Numery 1 — 1000 będą wprowadzane w kolumnie, rozpoczynając w komórce A16.

Obraz książki
Rysunek 60-6 przy użyciu okna dialogowego serię wypełniania w wersji próbnej liczby od 1 do 1000.

Następnie firma Microsoft wprowadź naszych ilości możliwych produkcji (10 000, 20 000, 40 000, 60 000) w B15:E15 komórek. Chcemy obliczanie zysku dla każdego numeru wersji próbnej (od 1 do 1000) i każdej ilości produkcji. Wprowadzając = C11możemy dotyczą formułę dla zysku (obliczeniowe w komórce C11) w lewej górnej komórki tabeli naszych danych (A15).

Firma Microsoft przystąpić do lewy programu Excel do symulowania 1000 powtórzeń żądanie dla każdej ilości produkcji. Zaznacz zakres tabeli (A15:E1014), a następnie w grupie Narzędzia danych na karcie Dane kliknij pozycję What analizy, a następnie wybierz tabeli danych. Aby skonfigurować dwukierunkowa tabela danych, naszych ilość produkcji (komórka C1) jako Wierszowa komórka wprowadzania i wybierz przycisk dowolną pustą komórkę (Wybraliśmy komórki I14) jako Kolumnowa komórka wprowadzania. Po kliknięciu przycisku OK, program Excel symuluje 1000 żądanie wartości dla każdego ilość zamówienia.

Aby dowiedzieć się, dlaczego to działa, należy rozważyć wartości wprowadzane przez tabeli danych w zakresie komórek C16:C1015. Dla każdego z tych komórek program Excel użyje wartości 20 000 w komórce C1. W C16 wartość komórki wejściowej kolumna 1 jest umieszczana w pustą komórkę i liczba w komórce C2 jest obliczana ponownie. Odpowiednie zysku następnie został zapisany w komórce C16. Następnie wartości komórki wejściowej kolumny 2 zostanie umieszczony w pustej komórce, a następnie ponownie oblicza liczbę losową w komórce C2. W komórce C17 zostanie wprowadzona odpowiednich zysk.

Przez skopiowanie z komórki B13 C13:E13 formuła AVERAGE(B16:B1015), możemy obliczyć średni zysk symulowany dla każdej ilości produkcji. Przez skopiowanie C14:E14 STDEV(B16:B1015)formułę z komórki B14, możemy obliczyć odchylenie standardowe symulowany zysk dla każdej ilości kolejności. Za każdym razem, możemy naciśnij klawisz F9, 1000 powtórzeń żądanie są symulowane dla każdej ilości kolejności. Zawsze produkcji 40 000 karty daje największą zysku oczekiwany. Dlatego wydaje się, że produkcji 40 000 kart jest decyzja pisane z wielkiej litery.

Wpływ ryzyka na naszej decyzji     Firma Microsoft wyprodukowano 20 000 zamiast karty 40 000, naszych oczekiwanych zysku pomija około 22 procent, ale nasze ryzyka (mierzonych według odchylenie standardowe zysku) pomija niemal 73 procent. W związku z tym jeśli nie będziemy bardzo averse ryzyko, produkcji 20 000 karty może być prawo decyzji. Niektórych zawsze produkcji 10 000 karty ma odchylenie standardowe kart 0, ponieważ wtedy, gdy firma Microsoft daje 10 000 karty, firma Microsoft zawsze sprzedaż z nich bez dowolnego resztek.

Uwaga: W tym skoroszycie opcji obliczania ustawiono Automatyczne z wyjątkiem tabel. (W grupie Obliczanie na karcie formuły, użyj polecenia obliczeń). To ustawienie gwarantuje, że tabela naszych danych nie ponownych chyba że firma Microsoft naciśnij klawisz F9, który jest dobrym pomysłem, ponieważ tabeli dużych danych będzie spowolnić pracę, jeśli go jest obliczana ponownie za każdym razem, wpisujesz określony do arkusza. Należy zauważyć, że w tym przykładzie po naciśnięciu klawisza F9, Średnia zysków zmieni się. Dzieje się tak, ponieważ zawsze po naciśnięciu klawisza F9 inną kombinację liczb losowych 1000 jest używany do generowania wymagania dla każdej ilości kolejności.

Przedział ufności dla oznacza zysku     Naturalne pytanie, aby zadać w takiej sytuacji to, do jakich interwału możemy 95 procent się, że należy do zakresu PRAWDA Średnia zysków? Interwał ten jest nazywany 95% przedział ufności dla średniej zysk. 95% przedziału ufności dla średniej dowolne dane wyjściowe symulacji jest obliczana według następującej formuły:

Obraz książki

W komórce J11 obliczyć dolną granicę dla przedział ufności 95 procent na Średnia zysków po wyprodukowano 40 000 kalendarzy przy użyciu formuły D13–1.96*D14/SQRT(1000). W komórce J12 można obliczyć górną granicę dla naszych ufności 95 procent formułą D13+1.96*D14/SQRT(1000). Tych obliczeń są wyświetlane w rysunek 60-7.

Obraz książki
Rysunek 60-7 95 procent ufności dla średniej zysku po 40 000 kalendarze są sortowane

Pracujemy 95 procent się, że nasze Średnia zysków po 40 000 kalendarze są sortowane jest między 56,687 $ a $62,589.

  1. Pośrednik GMC uważa, że żądanie 2005 Envoys będzie zwykle dostarczany z średnią 200 i odchylenie standardowe 30. Odbieranie wysłannika jego koszt wynosi 25 000 USD, a on zostanie sprzedany wysłannika 40 000 zł. Połowa wszystkich Envoys nie sprzedawany na pełną cenę mogą być sprzedawane za 30 000 zł. Jest on ustalając kolejnością 200, 220, 240, 260, 280 lub 300 Envoys. Ile powinien on zamówień?

  2. Małe supermarketem próbuje określić liczbę kopii osób czasopisma należy zamówień z każdego tygodnia. Ich podejrzenie, że ich żądanie dla osób podlega następujące osobne zmienną losową:

    Żądanie

    Prawdopodobieństwo

    15

    0,10

    20

    0,20

    25

    0.30

    30

    0,25

    35

    0,15

  3. Supermarketów oprocentowaniem 1,00 dla każdej kopii osób i sprzedawanych go do $1.95. Skoroszyty niesprzedane mogą zostać zwrócone dla $0,50. Ile kopii osób powinna magazynie kolejność?

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.

×