Įvadas į Monte Carlo modeliavimas programoje "Excel"

Šis straipsnis buvo pritaikytas iš "Microsoft Excel" duomenų analizės ir verslo modeliavimo pagal Wayne L. Winston.

  • Kas naudoja Monte Carlo modeliavimą?

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

  • Kaip galima imituoti diskrečiųjų kintamųjų reikšmes?

  • Kaip galima imituoti įprastų atsitiktinių kintamųjų reikšmes?

  • Kaip Sveikinimo atviruko įmonė nustato, kiek kortelių gali gaminti?

Norėtume tiksliai įvertinti neaiškių įvykių tikimybes. Pavyzdžiui, kas yra tikimybė, kad naujo produkto pinigų srautai turės teigiamos grynosios dabartinės reikšmės (NPV)? Koks yra mūsų investicijų portfelio rizikos veiksnys? Monte Carlo modeliavimas leidžia mums modeliuoti situacijas, kurios kelia netikrumą, o tada jas leisti kompiuteriu tūkstančius kartų.

Pastaba:  Pavadinimas Monte Carlo modeliavimas kilęs iš kompiuterio modeliavimų, atliktų 1930 ir 1940 metais, siekiant įvertinti tikimybę, kad grandininė reakcija, reikalinga atomo bombų detonuoti, galėtų sėkmingai veikti. Fizikai, dalyvaujantys šiame darbe, buvo dideli azartinių lošimų gerbėjai, todėl jie davė simuliacijų, kad "Code name Monte Carlo".

Kituose penkiuose skyriuose matysite pavyzdžius, kaip galite naudoti "Excel", kad atliktumėte "Monte Carlo" modeliavimą.

Daugelis kompanijų naudoja Monte Carlo modeliavimą kaip svarbią savo sprendimų priėmimo proceso dalį. Štai keletas pavyzdžių.

  • "General Motors", "Proctor and Gamble", "Pfizer", "Bristol-Myers Squibb" ir "Eli Lilly" naudoja modeliavimą, kad įvertintų vidutinę grąžą ir naujų produktų rizikos faktorių. Į GM, šią informaciją generalinis direktorius naudoja, kad nustatytų, kurie produktai patenka į rinką.

  • GM naudoja modeliavimą tokiai veiklai kaip pelno prognozės grynosios pajamos, prognozuojamos struktūrinės ir pirkimo išlaidos ir nustatomos jos imlumas įvairių rūšių rizikai (pvz., palūkanų normos pokyčiai ir valiutos kurso svyravimai).

  • "Lilly" naudoja modeliavimą, kad nustatytų optimalų kiekvieno narkotiko gamybos pajėgumą.

  • "Proctor and Gamble" naudoja modeliavimą ir optimaliai apdraustų valiutų keitimo riziką.

  • "Sears" naudoja modeliavimą, kad nustatytų, kiek kiekvienos produktų eilutės vienetų reikėtų nurodyti iš tiekėjų, pvz., "Dockers" kelnių, kurios turėtų būti užsakytos šiais metais, porų skaičiumi.

  • Naftos ir farmacijos įmonės naudoja modeliavimą kaip reikšmę "realiosios parinktys", pvz., galimybės plėstis, sutartį arba atidėti projektą, reikšmė.

  • Finansiniai planuotojai naudoja Monte Carlo modeliavimą, kad nustatytų optimalias investavimo strategijas savo klientų pensijai.

Kai langelyje įvesite formulę = Rand () , gaunate skaičių, kuris taip pat gali daryti bet kurią reikšmę nuo 0 iki 1. Taigi, maždaug 25 procentai laiko, turite gauti skaičių, mažesnį arba lygų 0,25; maždaug 10 procentų laiko reikia gauti skaičių, kuris yra bent 0,90, ir t. t. Norėdami pademonstruoti, kaip veikia funkcija RAND, Pažvelkite į failą Randdemo. xlsx, rodomą 60-1 paveiksle.

Book Image

Pastaba:  Atidarę failą Randdemo. xlsx, nematysite tų pačių atsitiktinių skaičių, kurie nurodyti 60-1 paveiksle. Funkcija RAND visada automatiškai perskaičiuoja numerius, kuriuos generuoja, kai atidaromas darbalapis arba kai nauja informacija įvedama į darbalapį.

Pirmiausia nukopijuokite iš langelio C3 į C4: C402 formulę = Rand (). Tada pavadinkite intervalą C3: C402 duomenys. Tada stulpelyje F galite sekti 400 atsitiktinių skaičių (langelio F2) vidurkį ir naudoti funkciją COUNTIF, kad nustatytumėte frakcijas, kurios yra tarp 0 ir 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 procentai rezultatų yra 0,25. Šie rezultatai atitinka atsitiktinio skaičiaus apibrėžimą. Taip pat nepamirškite, kad skirtingose langeliuose RAND sukurtos reikšmės yra savarankiškos. Pavyzdžiui, jei atsitiktinis skaičius, sugeneruotas langelyje C3, yra labai daug (pvz., 0,99), jis pasako mums apie kitų atsitiktinių skaičių.

Įsivaizduokite, kad kalendoriaus paklausą reglamentuoja šis atskiras atsitiktinis kintamasis:

Paklausos

Tikimybė

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0.25

Kaip mes galime paleisti "Excel" arba imituoti šį kalendorių poreikį daug kartų? Fokusas – susieti kiekvieną galimą funkcijos RAND reikšmę su galimu kalendoriais. Ši užduotis užtikrina, kad "10 000" pareikalautų 10 procentų laiko ir pan.

Paklausos

Priskirtas atsitiktinis numeris

10 000

Mažiau nei 0,10

20 000

Daugiau arba lygu 0,10 ir mažiau nei 0,45

40 000

Daugiau arba lygu 0,45 ir mažiau nei 0,75

60 000

Daugiau arba lygu 0,75

Norėdami parodyti paklausos modeliavimą, peržiūrėkite failą Diskretesim. xlsx, rodomą paveiksle 60-2 kitame puslapyje.

Book Image

Mūsų modeliavimo raktas – naudoti atsitiktinį skaičių, kad būtų galima inicijuoti peržvalgą iš lentelės intervalo F2: G5 (pavadintas Peržvalga). Atsitiktiniai skaičiai, didesni už arba lygu 0 ir mažiau nei 0,10, gaus 10 000 paklausą; kai kurie atsitiktiniai skaičiai yra didesni nei arba lygu 0,10 ir mažesni nei 0,45, gaus 20 000 paklausą; kai kurie atsitiktiniai skaičiai yra didesni nei arba lygu 0,45 ir mažesni nei 0,75, gaus 40 000 paklausą; ir atsitiktiniai skaičiai, didesni nei arba lygu 0,75, gaus "60 000" paklausą. Galite generuoti 400 atsitiktinius numerius nukopijuodami nuo C3 iki C4: C402 formulės Rand (). Tada sugeneruokite "400" bandomąsias versijas arba iteracijas, nukopijuodami nuo B3 iki B4: B402 formulės VLOOKUP (C3, peržvalgos, 2). Ši formulė užtikrina, kad bet koks atsitiktinis skaičius, mažesnis už 0,10, generuoja "10 000" paklausą, bet koks atsitiktinis skaičius tarp 0,10 ir 0,45 generuoja 20 000 paklausą ir kt. Langelių diapazono F8: F11 naudokite funkciją COUNTIF, kad nustatytumėte mūsų "400" iteracijų trupmeną, kuri duoda kiekvienam poreikiui. Kai paspausite F9, kad perskaičiuotų atsitiktinius skaičius, imituojamos tikimybės yra arti mūsų numanomos paklausos tikimybių.

Jei į bet kurį langelį įvesite formulę NORMINV (Rand (), mu, Sigma), sugeneruojate įprasto atsitiktinio kintamojo, kurio vidutinis MV ir standartinis nuokrypis yra Sigma, imituojamą reikšmę. Ši procedūra rodoma faile Normalsim. xlsx, pavaizduoto paveiksle 60-3.

Book Image

Tarkime, kad norime imituoti "400" bandomąsias versijas arba iteracijas, kad būtų įprastas atsitiktinis kintamasis, o vidurkis – 40 000 ir standartinis nuokrypis – 10 000. (Galite įvesti šias reikšmes langeliuose E1 ir E2 ir pavadinti šiuos langelius atitinkamai ir Sigma.) Nukopijavus formulę = Rand () nuo C4 iki C5: C403 generuoja "400" skirtingus atsitiktinius numerius. Kopijavimas iš B4 į B5: B403 formulė NORMINV (C4, vidurkis, Sigma) generuoja 400 skirtingas bandomąsias reikšmes iš įprasto atsitiktinio kintamojo su vidurkio 40 000 ir standartiniu nuokrypiu 10 000. Kai paspausite klavišą F9, kad perskaičiuotų atsitiktinius skaičius, vidurkis lieka arti 40 000 ir standartinis nuokrypis yra arti 10 000.

Iš esmės atsitiktinio skaičiaus xformulėje NORMINV (p, mu, Sigma) sukuriamas pth procentilis, kuriame yra įprastas atsitiktinis kintamasis su vidurkio mu ir standartiniu nuokrypiu Sigma. Pvz., atsitiktinių skaičių 0,77 langelyje C4 (žiūrėkite 60-3 paveikslą) generuoja langelyje B4 maždaug 77-ojoje procentilio įprastame atsitiktinio kintamojo, kurio vidurkis yra 40 000, o standartinis nuokrypis – 10 000.

Šiame skyriuje matysite, kaip galima naudoti "Monte Carlo" imitavimą kaip sprendimų priėmimo įrankį. Tarkime, kad Valentino dienos kortelės paklausą reglamentuoja toks diskretiškas atsitiktinis kintamasis:

Paklausos

Tikimybė

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0.25

Sveikinimo atvirukas parduodamas $4,00, o kintanti kiekvienos kortelės gamybos savikaina yra $1,50. Likusias korteles reikia pašalinti $0,20 už kortelės kainą. Kiek kortelių turėtų būti spausdinama?

Iš esmės mes modeliuojame kiekvieną galimą gamybos kiekį (10 000, 20 000, 40 000 arba 60 000) daug kartų (pvz., 1000 iteracijų). Tada nustatysime, kuris užsakymo kiekis pateikia maksimalų vidutinį pelną, palyginti su "1000" iteracijomis. Šio skyriaus duomenis galite surasti faile Valentino. xlsx, Parodytame 60-4 paveiksle. Galite priskirti langelius B1: B11 į langelius C1: C11. Langelių diapazonas G3: H6 priskiriamas pavadinimo peržvalgai. Mūsų pardavimo kainos ir savikainos parametrai įvedami langeliuose C4: C6.

Book Image

Galite į langelį C1 įrašyti bandomąją gamybos kiekį (40 000 šiame pavyzdyje). Tada sukurkite atsitiktinį skaičių langelyje C2 naudodami formulę = Rand (). Kaip aprašyta anksčiau, galite imituoti kortelės, esančios langelyje C3, paklausą naudojant formulę VLOOKUP (RAND, lookup, 2). (VLOOKUP formulėje Rand yra į langelį C3 priskirtas langelio pavadinimas, o ne funkcija RAND).

Parduotų vienetų skaičius yra mažesnis nei mūsų gamybos kiekis ir paklausa. Langelyje C8 galite apskaičiuoti pajamas pagal formulę min (pagaminta, paklausa) * unit_price. Langelyje C9 galite apskaičiuoti bendrąją gamybos kainą, naudodami unit_prod_costformulę.

Jei pagaminame daugiau kortelių, nei yra paklausa, vienetų, kurie liko lygūs gamybos minus paklausai, skaičius; Priešingu atveju nėra likę vienetų. Apskaičiuojame mūsų perleidimo kainą langelyje C10, naudodami formulę unit_disp_cost * jei (pagamintas>paklausa, pagaminta – paklausa, 0). Galiausiai, langelyje C11, apskaičiuojame savo pelną kaip pajamas – total_var_cost total_disposing_cost.

Norėtume, kad būtų efektyvus būdas paspausti F9 daug kartų (pvz., "1000") kiekvienam gamybos kiekiui ir sutampa mūsų planuojamas kiekvieno kiekio pelnas. Ši situacija yra ta, kurioje dvikryptė duomenų lentelė gaunama mūsų gelbėjimui. (Daugiau informacijos apie duomenų lenteles žr. 15 skyrių "jautrumo analizė su duomenų lentelėmis"). Šiame pavyzdyje naudojama duomenų lentelė parodyta 60-5 paveiksle.

Book Image

"A1015" langelių diapazonas: 1 – 1000 (atitinka mūsų "1000" bandomąsias versijas). Paprastas būdas kurti šias reikšmes yra pradėti įvedant 1 langelyje A16. Pažymėkite langelį, tada skirtuko Pagrindinis grupėje Redagavimas spustelėkite užpildasir pasirinkite sekos , kad būtų rodomas dialogo langas seka . Dialogo lange seka , rodomame 60-6 paveiksle, įveskite 1 žingsnio reikšmę ir 1000 reikšmę. Srityje seka pasirinkite parinktį stulpeliai , tada spustelėkite gerai. Skaičiai nuo 1 iki 1000 bus įvesti stulpeliu, kuris prasideda langelyje A16.

Book Image

Toliau įvesime galimus gamybos kiekius (10 000, 20 000, 40 000, 60 000) langeliuose B15: E15. Norime apskaičiuoti kiekvieno bandomojo numerio (nuo 1 iki 1000) ir kiekvieno gamybos kiekio pelną. Mes nurodome pelno (apskaičiuoto langelyje "C11") formulę savo duomenų lentelės viršutiniame langelyje (A15) įvedant = C11.

Dabar esame pasiruošę apgauti "Excel" į "Excel", imituojančią kiekvieno gamybos kiekio paklausos iteraciją 1000. Pažymėkite lentelės sritį (A15: E1014), tada skirtuko Duomenys grupėje Duomenų įrankiai spustelėkite ką daryti IF, tada pasirinkite duomenų lentelė. Norėdami nustatyti dvipusio duomenų lentelę, pasirinkite mūsų gamybos kiekį (langelį C1) kaip eilutės įvesties langelį ir pasirinkite bet kurį tuščią langelį (pasirinkome langelį I14) kaip stulpelio įvesties langelį. Spustelėjus Gerai, "Excel" modeliuoja 1000 paklausos vertes kiekvienam užsakymo kiekiui.

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

Nukopijuodami iš langelio B13 į C13: E13 formulės vidurkį (B16: B1015), apskaičiuojame vidutinį kiekvieno gamybos kiekio modeliavimą. Nukopijuodami iš langelio B14 į C14: E14 formulė STDEV (B16: B1015), apskaičiuojamas kiekvieno užsakymo kiekio mūsų modeliuojamo pelno standartinis nuokrypis. Kiekvieną kartą, kai paspausite F9, "1000" paklausos iteracijų kiekis yra modeliuojamas kiekvienam užsakymo kiekiui. "40 000" kortelių gamyba visada duoda didžiausią numatomą pelną. Todėl pasirodo, kad "40 000" kortelių gamyba yra tinkamas sprendimas.

Rizikos poveikis mūsų sprendimui     Jei pagaminome 20 000, o ne "40 000" korteles, mūsų planuojamas pelnas sumažės maždaug 22 procentai, bet mūsų rizika (matuojant pagal standartinį pelno nuokrypį) krenta beveik 73 procentais. Taigi, jei labai nemėgsta rizikuoti, "20 000" kortelių gamyba gali būti tinkamas sprendimas. Beje, gaminant "10 000" korteles, visada yra standartinis 0 kortelių nuokrypis, nes jei pagaminsime "10 000" korteles, mes visada parduosime juos visus, bet ne likučius.

Pastaba:  Šioje darbaknygėje skaičiavimo parinktis nustatyta kaip Automatinis, išskyrus lenteles. (Skirtuko Formulės grupėje skaičiavimas naudokite komandą skaičiavimas.) Šis parametras užtikrina, kad mūsų duomenų lentelė nebus perskaičiuotas, nebent mes paspauskite F9, tai yra gera mintis, nes didelė duomenų lentelė sulėtins jūsų darbą, jei ji perskaičiuos kiekvieną kartą, kai įvedate ką nors į darbalapį. Pastaba Šiame pavyzdyje, kai paspausite F9, vidutinis pelnas pasikeis. Taip nutinka, nes kiekvieną kartą paspaudus F9, skirtinga 1000 atsitiktinių skaičių seka naudojama kiekvieno užsakymo kiekio paklausai generuoti.

Vidutinio pelno patikimumo intervalas     Natūralus klausimas, kurį reikia užduoti šioje situacijoje yra, į kokį intervalą mes 95 procentai tikri, kad tikrasis vidutinis pelnas sumažės? Šis intervalas vadinamas 95 procentų pasikliovimo intervalu. 95 procentų pasikliovimo intervalas pagal bet kurį modeliavimo išvestį apskaičiuojamas pagal šią formulę:

Book Image

Langelyje J11 galite apskaičiuoti mažesnį 95 procentų patikimumo intervalo limitą, kai 40 000 kalendoriai gaminami naudojant formulę D13 – 1.96 * D14/SQRT (1000). Naudodami langelio J12, galite apskaičiuoti aukščiausią 95 procentų pasikliovimo intervalo limitą naudodami formulę D13 + 1.96 * D14/SQRT (1000). Šie skaičiavimai nurodyti 60-7 paveiksle.

Book Image

Mes 95 procentai tikri, kad mūsų vidutinis pelnas, kai "40 000" kalendoriai užsakomi, yra tarp $56 687 ir $62 589.

  1. GMC prekiautojas mano, kad 2005 Envoys paklausa paprastai bus paskirstyta su 200 vidurkiu ir standartiniu nuokrypiu 30. Jo gavimo pasiuntinys yra $25 000, jis parduoda pasiuntinį, skirtą "$40 000". Pusė visų pasiuntinių, kurie neparduodami už visą kainą, gali būti parduodami $30 000. Jis svarsto užsakymų 200, 220, 240, 260, 280 arba 300 Envoys. Kiek jis turėtų užsisakyti?

  2. Mažas prekybos centras bando nustatyti, kiek žurnalo žmonių kopijų turėtų užsisakyti kiekvieną savaitę. Jie tiki, kad jų paklausą žmonėms reglamentuoja šie diskretiniai atsitiktiniai kintamieji:

    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ą žmonių kopiją ir parduoda ją "$1,95". Visas neparduotas kopijas galima grąžinti "$0,50". Kiek žmonių kopijų turi saugyklos užsakymas?

Reikia daugiau pagalbos?

Visada galite paprašyti specialisto iš „Excel“ technologijų bendruomenės, gauti pagalbos iš Atsakymų bendruomenės arba siūlyti naują funkciją ar patobulinimą „Excel“ vartotojo balse.

Pastaba:  Šis puslapis išverstas automatiškai, todėl gali būti gramatikos klaidų ar netikslumų. Mūsų tikslas – padaryti, kad šis turinys būtų jums naudingas. Prašome mus informuoti, ar radote reikiamos informacijos. Čia yra straipsnis anglų kalba, kuriuo galite pasinaudoti kaip nuoroda.

Tobulinkite savo „Office“ įgūdžius
Ieškoti mokymo
Pirmiausia gaukite naujų funkcijų
Prisijunkite prie „Office Insider“ dalyvių

Ar ši informacija buvo naudinga?

Dėkojame už jūsų atsiliepimus!

Dėkojame už jūsų atsiliepimą! Panašu, kad gali būti naudinga jus sujungti su vienu iš mūsų „Office“ palaikymo agentų.

×