Prisijunkite prie „Microsoft“
Prisijunkite arba sukurkite paskyrą.
Sveiki,
Pasirinkti kitą paskyrą.
Turite kelias paskyras
Pasirinkite paskyrą, kurią naudodami norite prisijungti.

Šis straipsnis buvo pritaikytas pagal Wayne L. Winston microsoft Excel duomenų analizę ir verslo modeliavimą .

  • Kas naudoja Monte Carlo modeliavimą?

  • Kas nutinka, kai langelyje įvedate =RAND() ?

  • Kaip imituoti atskirų atsitiktinių kintamųjų reikšmes?

  • Kaip imituoti normalaus atsitiktinio kintamojo reikšmes?

  • Kaip sveikinimo atvirukų įmonė gali nustatyti, kiek kortelių gaminti?

Norėtume tiksliai įvertinti nežinomų įvykių tikimybę. Pavyzdžiui, kokia tikimybė, kad naujo produkto pinigų srautai turės teigiamą grynąją dabartinę vertę (NPV)? Koks yra mūsų investicijų portfelio rizikos veiksnys? Monte Carlo modeliavimas leidžia mums modeliuoti situacijas, kurios kelia abejonių, ir tada žaisti juos ant kompiuterių tūkstančius kartų.

Pastaba:  Pavadinimas Monte Carlo modeliavimas ateina iš kompiuterių modeliavimas atliekamas per 1930 ir 1940 m., siekiant įvertinti tikimybę, kad grandininė reakcija, reikalinga atomo bombų susprogdinti veiks sėkmingai. Fizikai dalyvauja šiame darbe buvo dideli gerbėjai azartinių lošimų, todėl jie davė modeliavimus kodo pavadinimas Monte Carlo.

Tolesniuose penkiuose skyriuose pamatysite pavyzdžių, kaip galite naudoti "Excel" Monte Carlo modeliavimui atlikti.

Daugelis įmonių naudoja Monte Carlo modeliavimą kaip svarbią savo sprendimų priėmimo proceso dalį. Toliau pateikiami keli pavyzdžiai.

  • General Motors, Proctor ir Gamble, Pfizer, Bristol-Myers Squibb ir Eli Lilly naudoja modeliavimą, kad apskaičiuotų ir naujų produktų vidutinę grąžą, ir rizikos koeficientą. Į GM, ši informacija yra naudojama ceo nustatyti, kurie produktai atvykti į rinką.

  • GM naudoja modeliavimą tokiai veiklai kaip korporacijos grynųjų pajamų prognozavimas, struktūrinių ir pirkimo sąnaudų prognozavimas ir jos jautrumo įvairių rūšių rizikai nustatymas (pvz., palūkanų normų pokyčiai ir valiutų kursų svyravimai).

  • Lilly naudoja modeliavimą, kad nustatytų optimalų augalo pajėgumą kiekvienam vaistui.

  • Proctor ir Gamble naudoja modeliavimą modeliuojant ir optimaliai rizikos apsidraudimui užsienio valiuta.

  • Sears naudoja modeliavimą, kad nustatytų, kiek kiekvienos produkto linijos vienetų turėtų būti užsakyta iš tiekėjų, pvz., porų dokų kelnės, kurios turėtų būti užsakyti šiais metais.

  • Naftos ir narkotikų bendrovės naudoja modeliavimą, kad verttų "realius variantus", pvz., pasirinkimo išplėsti, sudaryti sutartį arba atidėti projektą vertę.

  • Finansų planuotojas naudoja Monte Carlo modeliavimą, kad nustatytų optimalias investavimo strategijas savo klientų išėmimui iš prekybos.

Įvesdami formulę =RAND() į langelį, gausite skaičių, kuris bus lygiai toks pat tikėtinas kaip bet kuri reikšmė nuo 0 iki 1. Taigi, maždaug 25 procentų laiko, turėtumėte gauti skaičių, mažesnį arba lygų 0,25; maždaug 10 procentų laiko turėtumėte gauti skaičių, kuris yra bent 0,90 ir t. t. Norėdami parodyti, kaip veikia funkcija RAND, peržiūrėkite failo Randdemo.xlsx, kaip parodyta 60–1 pav.

Book Image

Pastaba:  Kai atidarote failo Randdemo.xlsx, nematysite tų pačių atsitiktinių skaičių, kaip parodyta 60-1 pav. Funkcija RAND visada automatiškai perskaičiuoja skaičius, kuriuos sugeneruoja atidarius darbalapį arba kai į darbalapį įvedama nauja informacija.

Pirmiausia nukopijuokite iš langelio C3 į C4:C402 formulę =RAND(). Tada galite pavadinti diapazoną C3:C402 duomenys. Tada F stulpelyje galite sekti 400 atsitiktinių skaičių vidurkį (langelis F2) ir naudoti funkciją COUNTIF, kad nustatytumėte trupmenas nuo 0 iki 0,25, 0,25 ir 0,50, 0,50 ir 0,75 bei 0,75 ir 1. Paspaudus klavišą F9, atsitiktiniai skaičiai perskaičiuojami. Atkreipkite dėmesį, kad 400 skaičių vidurkis visada yra maždaug 0,5, o apie 25 procentus rezultatų yra 0,25 intervalais. Šie rezultatai atitinka atsitiktinio skaičiaus apibrėžimą. Taip pat atkreipkite dėmesį, kad rando sugeneruotos reikšmės skirtinguose langeliuose yra nepriklausomos. Pavyzdžiui, jei atsitiktinis skaičius, sugeneruotas langelyje C3, yra didelis skaičius (pvz., 0,99), mums nieko nepasakys apie kitų sugeneruotų atsitiktinių skaičių reikšmes.

Tarkime, kad kalendoriaus poreikį reglamentuoja šis atskiras atsitiktinis kintamasis:

Paklausos

Tikimybė

10 000

0,10

20 000

0.35

40,000

0,3

60 000

0.25

Kaip "Excel" gali daug kartų atkurti arba imituoti šį kalendorių poreikį? Gudrybė yra susieti kiekvieną galimą funkcijos RAND reikšmę su galima kalendorių paklausa. Šis priskyrimas užtikrina, kad 10 000 poreikis įvyks 10 procentų laiko ir t. t.

Paklausos

Atsitiktinis priskirtas numeris

10 000

Mažiau nei 0,10

20 000

Didesnis už arba lygus 0,10 ir mažesnis už 0,45

40,000

Didesnis už arba lygus 0,45 ir mažesnis už 0,75

60 000

Daugiau arba lygu 0,75

Norėdami parodyti paklausos modeliavimą, pažiūrėkite į failo Discretesim.xlsx, kaip parodyta 60–2 pav. kitame puslapyje.

Book Image

Mūsų modeliavimas yra naudoti atsitiktinį skaičių norint inicijuoti peržvalgą iš lentelės diapazono F2:G5 (įvardytoji peržvalga). Atsitiktiniai skaičiai, didesni arba lygūs 0 ir mažesni už 0,10, pateiks 10 000 paklausą; atsitiktiniai skaičiai, didesni arba lygūs 0,10 ir mažesni už 0,45, pateiks 20 000 paklausą; atsitiktiniai skaičiai, didesni arba lygūs 0,45 ir mažesni už 0,75, pateiks 40 000 paklausą; ir atsitiktiniai skaičiai, didesni arba lygūs 0,75, pateiks 60 000 paklausą. Galite sugeneruoti 400 atsitiktinių skaičių kopijuojant iš C3 į C4:C402 formulę RAND(). Tada sugeneruojate 400 kalendoriaus poreikio bandomųjų versijų arba iteracijų nukopijuodami iš B3 į B4:B402 formulę VLOOKUP(C3,lookup,2). Ši formulė užtikrina, kad bet koks atsitiktinis skaičius, mažesnis už 0,10, sugeneruoja 10 000 poreikį, bet koks atsitiktinis skaičius nuo 0,10 iki 0,45 sugeneruoja 20 000 ir t. t. Langelių diapazone F8:F11 naudokite funkciją COUNTIF, kad nustatytumėte 400 iteracijų, pateikiančių kiekvieną poreikį, dalį. Kai paspaudžiame F9 atsitiktiniams skaičiams perskaičiuoti, imituojamos tikimybės yra artimos mūsų numanomoms paklausos tikimybėms.

Jei į bet kurį langelį įvesite formulę NORMINV(rand(),mu,sigma),, sugeneruosite sumodeliuotą normalaus atsitiktinio kintamojo reikšmę, kurios vidurkis yra mu ir standartinis nuokrypis sigma. Ši procedūra pavaizduota failo Normalsim.xlsx, parodyta 60–3 paveiksle.

Book Image

Tarkime, kad norime imituoti 400 bandymų arba iteracijų įprastam atsitiktiniam kintamajam, kurio vidurkis yra 40 000, o standartinis nuokrypis – 10 000. (Šias reikšmes galite įvesti langeliuose E1 ir E2 ir pavadinti šiuos langelius atitinkamai vidurkiais ir sigma.) Kopijuojant formulę =RAND() iš C4 į C5:C403 sugeneruojama 400 skirtingų atsitiktinių skaičių. Kopijuojant iš B4 į B5:B403 formulė NORMINV(C4;vidurkis;sigma) generuoja 400 skirtingų bandomųjų reikšmių iš įprasto atsitiktinio kintamojo, vidurkis – 40 000, o standartinis nuokrypis – 10 000. Paspaudus klavišą F9 atsitiktiniams skaičiams perskaičiuoti, vidurkis išlieka artimas 40 000, o standartinis nuokrypis – 10 000.

Iš esmės, atsitiktinio skaičiaus x atveju formulė NORMINV(p,mu,sigma) generuoja normaliojo atsitiktinio kintamojo p-ąjįprocentilį su vidurkiu mu ir standartiniu nuokrypiu sigma. Pavyzdžiui, langelyje C4 atsitiktinis skaičius 0,77 (žr. 60–3 pav.) langelyje B4 sugeneruoja maždaug 77-ąjį normalaus atsitiktinio kintamojo procentilį, kai vidurkis yra 40 000, o standartinis nuokrypis – 10 000.

Šiame skyriuje pamatysite, kaip Monte Carlo modeliavimas gali būti naudojamas kaip sprendimų priėmimo priemonė. Tarkime, kad Valentino dienos kortelės poreikį reglamentuoja šis atskiras atsitiktinis kintamasis:

Paklausos

Tikimybė

10 000

0,10

20 000

0.35

40,000

0,3

60 000

0.25

Sveikinimo atvirukas parduoda už 4,00 USD, o kintanti kiekvienos kortelės gamybos kaina yra 1,50 USD. Likusios kortelės turi būti šalinamos už 0,20 $ už kortelę. Kiek kortelių turėtų būti spausdinama?

Imituojame kiekvieną galimą gamybos kiekį (10 000, 20 000, 40 000 arba 60 000) daug kartų (pvz., 1 000 iteracijų). Tada mes nustatome, kuris užsakymo kiekis duoda maksimalų vidutinį pelną per 1 000 iteracijų. Šios sekcijos duomenis galite rasti failo Valentine.xlsx, parodytame 60-4 pav. Langelių B1:B11 diapazonų pavadinimus priskiriate langeliams C1:C11. Langelių diapazonui G3:H6 priskiriama pavadinimų peržvalga. Mūsų pardavimo kainos ir savikainos parametrai įvedami langeliuose C4:C6.

Book Image

Langelyje C1 galite įvesti bandomąjį gamybos kiekį (šiame pavyzdyje – 40 000). Tada sukurkite atsitiktinį skaičių langelyje C2 su formule =RAND(). Kaip aprašyta anksčiau, imituojate kortelės poreikį langelyje C3 su formule VLOOKUP(rand,lookup,2). (VLOOKUP formulėje randas yra langelio pavadinimas, priskirtas C3 langeliui, o ne funkcija RAND.)

Parduotų vienetų skaičius yra mažesnis už mūsų gamybos kiekį ir paklausą. Langelyje C8 mūsų pajamos skaičiuojamos pagal formulę MIN(pagaminta,paklausa)*unit_price. Langelyje C9 galite apskaičiuoti bendrą gamybos kainą pagal formulę , pagamintą*unit_prod_cost.

Jei gaminame daugiau kortelių nei pareikalaujama, likusių vienetų skaičius lygus gamybai atėmus paklausą; kitu atveju vienetų neliko. Mes apskaičiuojame šalinimo išlaidas langelyje C10 naudodami formulę unit_disp_cost*IF(sukurta>paklausa,gauta–paklausa,0). Galiausiai langelyje C11 mes apskaičiuojame savo pelną kaip pajamas– total_var_cost total_disposing_cost.

Norime veiksmingai paspausti klavišą F9 daug kartų (pvz., 1000) kiekvienam gamybos kiekiui ir sutapti mūsų laukiamą kiekvieno kiekio pelną. Ši situacija yra tokia, kai mūsų gelbėjimui naudojama dvipusė duomenų lentelė. (Daugiau informacijos apie duomenų lenteles žr. 15 skyriuje "Slaptumo analizė su duomenų lentelėmis".) Šiame pavyzdyje naudojama duomenų lentelė rodoma 60–5 pav.

Book Image

Langelių diapazone A16:A1015 įveskite skaičius 1–1000 (atitinka mūsų 1 000 bandymų). Vienas iš lengvų būdų sukurti šias reikšmes – pradėti nuo 1 įvedimo į langelį A16. Pažymėkite langelį, tada skirtuko Pagrindinis grupėje Redagavimas spustelėkite Užpildyti ir pasirinkite Seka , kad būtų rodomas dialogo langas Seka . Dialogo lange Seka , parodyta 60–6 pav., įveskite žingsnio reikšmę 1, o stabdymo reikšmę – 1000. Srityje Sekos srityje pasirinkite parinktį Stulpeliai , tada spustelėkite Gerai. Skaičiai 1–1000 bus įvesti A stulpelyje pradedant nuo langelio A16.

Book Image

Tada į langelius B15:E15 įrašysime galimus gamybos kiekius (10 000, 20 000, 40 000, 60 000). Norime apskaičiuoti kiekvieno bandomojo numerio (nuo 1 iki 1000) ir kiekvieno gamybos kiekio pelną. Mes nurodome pelno formulę (apskaičiuotą langelyje C11) mūsų duomenų lentelės viršutiniame kairiajame langelyje (A15), įvesdami =C11.

Dabar esame pasirengę apgaule "Excel" imituoti 1 000 kiekvieno gamybos kiekio iteracijų. Pažymėkite lentelės diapazoną (A15:E1014), tada skirtuko Duomenys grupėje Duomenų įrankiai spustelėkite Kas, jei analizė, tada pasirinkite Duomenų lentelė. Norėdami nustatyti dvikryptių duomenų lentelę, pasirinkite mūsų gamybos kiekį (langelį C1) kaip eilutės įvesties langelį ir kaip stulpelio įvesties langelį pasirinkite bet kurį tuščią langelį (pasirinkote langelį I14). Spustelėjus Gerai, "Excel" sumodeliuoja 1 000 kiekvieno užsakymo kiekio poreikio reikšmių.

Norėdami suprasti, kodėl tai veikia, atsižvelkite į reikšmes, įtrauktas į duomenų lentelę langelių diapazone C16:C1015. Kiekvienam iš šių langelių "Excel" naudos reikšmę 20 000 langelyje C1. Langelyje C16 stulpelio įvesties langelio reikšmė 1 įdedama į tuščią langelį, o atsitiktinis skaičius langelyje C2 perskaičiuojamas. Atitinkamas pelnas įrašomas langelyje C16. Tada stulpelio langelio įvesties reikšmė 2 įdedama į tuščią langelį, o atsitiktinis skaičius langelyje C2 vėl perskaičiuojamas. Atitinkamas pelnas įvedamas langelyje C17.

Kopijuodami iš langelio B13 į C13:E13 formulę AVERAGE(B16:B1015), apskaičiuojame kiekvieno gamybos kiekio vidutinį imituotą pelną. Kopijuodami iš langelio B14 į C14:E14 formulę STDEV(B16:B1015), apskaičiuojame standartinį kiekvieno užsakymo kiekio imituojamo pelno nuokrypį. Kiekvieną kartą paspaudus F9, imituojama 1 000 poreikio iteracijų kiekvienam užsakymo kiekiui. Kuriant 40 000 kortelių visada gaunamas didžiausias numatomas pelnas. Todėl atrodo, kad 40 000 kortelių gamyba yra tinkamas sprendimas.

Rizikos poveikis mūsų sprendimui      Jei vietoj 40 000 kortelių sukūrėme 20 000, mūsų numatomas pelnas sumažėja maždaug 22 procentais, tačiau mūsų rizika (matuojama standartiniu pelno nuokrypiu) sumažėja beveik 73 procentai. Todėl, jei esame labai apversti rizikuoti, 20 000 kortelių gamyba gali būti teisingas sprendimas. Beje, gaminant 10 000 kortelių visada yra standartinis nuokrypis nuo 0 kortelių, nes jei gaminame 10 000 kortelių, visada parduodame jas visas be jokių likusių.

Pastaba:  Šioje darbaknygėje skaičiavimo parinktis nustatyta į Automatinis, išskyrus lenteles. (Naudokite komandą Skaičiavimas, esančią skirtuko Formulės grupėje Skaičiavimas.) Šis parametras užtikrina, kad mūsų duomenų lentelė nebus perskaičiuota, nebent paspausime F9, o tai gera mintis, nes didelė duomenų lentelė sulėtins jūsų darbą, jei ji perskaičiuos kiekvieną kartą, kai ką nors įvesite į darbalapį. Atkreipkite dėmesį, kad šiame pavyzdyje paspaudus F9 vidurkis pasikeis. Taip atsitinka todėl, kad kiekvieną kartą paspaudus F9, kiekvieno užsakymo kiekio poreikiams generuoti naudojama skirtinga 1 000 atsitiktinių skaičių seka.

Vidutinis pelnas patikimumo intervalas      Fizinis klausimas šioje situacijoje yra tai, į kokį intervalą mes 95 procentų, kad tikrasis vidutinis pelnas sumažės? Šis intervalas vadinamas vidurkio pelno 95 procentų patikimumo intervalu. Bet kurios imitacinės išvesties vidurkio 95 procentų patikimumo intervalas apskaičiuojamas pagal šią formulę:

Book Image

Langelyje J11 apskaičiuojate 95 procentų patikimumo intervalo vidutinio pelno apatinę ribą, kai 40 000 kalendorių yra sukurti naudojant formulę D13–1.96*D14/SQRT(1000). Langelyje J12 apskaičiuojate viršutinę mūsų 95 procentų patikimumo intervalo ribą naudodami formulę D13+1.96*D14/SQRT(1000). Šie skaičiavimai pavaizduoti 60–7 paveiksle.

Book Image

Esame 95 procentai įsitikinę, kad mūsų vidutinis pelnas, kai užsakyta 40 000 kalendorių, yra nuo 56 687 IKI 62 589 EUR.

  1. GMC prekybininkas mano, kad 2005 m. "Envoys" paklausa paprastai pasiskirstys su vidurkiu 200, o standartinis nuokrypis – 30. Jo kaina gauti pasiuntinį yra $ 25.000, ir jis parduoda už $ 40,000. Pusė visų pasiuntinių, parduotų ne visa kaina, gali būti parduota už 30 000 EUR. Jis svarsto eilę 200, 220, 240, 260, 280 ar 300 "Envoys". Kiek jis turėtų užsisakyti?

  2. Mažas prekybos centras bando nustatyti, kiek žmonės žurnalo kopijų jie turėtų užsisakyti kiekvieną savaitę. Jie mano, kad jų žmonės poreikį reglamentuoja šis atskiras atsitiktinis kintamasis:

    Paklausos

    Tikimybė

    15

    0,10

    20

    0.20

    25

    0.30

    30

    0.25

    35

    0,15

  3. Prekybos centras moka 1,00 $ už kiekvieną kopiją žmonės ir parduoda už $ 1,95. Kiekvieną neparduotą kopiją galima grąžinti už 0,50 EUR. Kiek žmonės kopijų turėtų būti parduotuvės užsakymas?

Reikia daugiau pagalbos?

Visada galite paklausti „Excel“ technologijų bendruomenės specialisto arba gauti palaikymo bendruomenėse.

Reikia daugiau pagalbos?

Norite daugiau parinkčių?

Sužinokite apie prenumeratos pranašumus, peržiūrėkite mokymo kursus, sužinokite, kaip apsaugoti savo įrenginį ir kt.

Bendruomenės padeda užduoti klausimus ir į juos atsakyti, pateikti atsiliepimų ir išgirsti iš ekspertų, turinčių daug žinių.

Ar ši informacija buvo naudinga?

Ar esate patenkinti kalbos kokybe?
Kas turėjo įtakos jūsų įspūdžiams?
Paspaudus mygtuką Pateikti, jūsų atsiliepimai bus naudojami tobulinant „Microsoft“ produktus ir paslaugas. Jūsų IT administratorius galės rinkti šiuos duomenis. Privatumo patvirtinimas.

Dėkojame už jūsų atsiliepimą!

×