Įvadas į Montecarlo imitavimas programoje "Excel"

Pastaba:  Norėtume jums kuo greičiau pateikti naujausią žinyno turinį jūsų kalba. Š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. Gal galite šio puslapio apačioje mums pranešti, ar informacija buvo naudinga? Čia yra straipsnis anglų kalba , kuriuo galite pasinaudoti kaip patogia nuoroda.

Šiame straipsnyje buvo pritaikytas iš "Microsoft" "Excel" duomenų analizė ir modeliavimas verslo iš Wayne L. Winston.

  • Kas naudoja Montecarlo modeliavimas?

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

  • Kaip galite imituoti reikšmės yra atskiras atsitiktinis kintamasis?

  • Kaip galite imituoti reikšmių normalus atsitiktinis kintamasis?

  • Kaip atvirukas bendrovė nustatyti kiek kortelių gauti?

Mums tiksliai nustatyti neaiškus įvykių tikimybės. Pvz., kas yra tikimybė, kad naują produktą pinigų srautų teigiamas grynoji dabartinė vertė (NPV)? Kas yra mūsų investicijų portfelio rizikos veiksniui laipsnį? Montecarlo modeliavimas leidžia modelio sunkumus, pateikti paklaida ir tada paleisti juos kompiuteryje tūkstančių kartų.

Pastaba: Pavadinimas Montecarlo modeliavimas kilęs iš kompiuterio modeliavimas, per 1930 ir 1940 metais įvertinti tikimybę, kad grandininė, reikalingi, atom bomba detonuoti veiktų sėkmingai. Įtraukti į šį darbą fizikai buvo lošimas, patinka, kad jie Montecarlokodo pavadinimas suteikė ir modeliavimas.

Kitus penkis skyriuose bus rodomi pavyzdžiai, kaip galite naudoti norėdami atlikti Montecarlo modeliavimas "Excel".

Daugelis įmonių naudoti Montecarlo modeliavimas kaip svarbi jų sprendimus. Štai keli pavyzdžiai.

  • BALF, Proctor ir Gamble, Pfizer, tojo ir Eli Lilly naudoti modeliavimas įvertinti grąžos vidurkis ir naujų produktų rizikos veiksniui laipsnį. Ne GM, norint nustatyti, kokie produktai yra rinkos į vadovo yra naudoja šią informaciją.

  • GM naudoja modeliavimas prognozę grynasis pelnas korporacijos, prognozuoti struktūrinius ir pirkimo išlaidas ir nustatyti jos jautrumo įvairių rūšių rizikos (pvz., palūkanų normos pokyčius ir valiutos kurso).

  • Lilly naudoja modeliavimas nustatyti optimalų augalą kiekvienai vaistų.

  • Proctor and Gamble naudoja modeliavimas modeliavimo ir optimaliai apdrausti valiutų riziką.

  • Sears naudoja modeliavimas nustatyti, kiek vienetų kiekvieno produkto linijos turėtų būti užsakyta iš tiekėjų, pvz., doko kelnių, kuris turėtų būti nurodyta šių metų skaičius.

  • Oil ir narkotikų įmonės naudoja modeliavimas reikšmę "real parinktis", pvz., galimybę išplėsti, sutarties arba atidėti projekto reikšmę.

  • Finansinio planavimo priemonės naudoti Montecarlo modeliavimas nustatyti savo klientų likvidavimas optimalių investavimo strategijas.

Kai rašote formulės =RAND() langelyje, gausite skaičius, taip pat gali atlikti bet kuris kitas skaičius tarp 0 ir 1. Taigi, maždaug 25 procentų laiko, gausite skaičius mažesnis ar lygus 0,25; apie 10 procentų laiko turėtų gauti skaičių, kuris yra bent 0,90, ir taip toliau. Norėdami parodyti, kaip veikia funkcija RAND, peržiūrėkite failą Randdemo.xlsx, rodomas 60-1 pav.

Book Image
60-1 iliustracija parodyti funkcija RAND

Pastaba: Kai atidarote failą Randdemo.xlsx, nematysite ne pačią atsitiktinius skaičius rodomas 60-1 pav. Funkcija RAND visada automatiškai perskaičiuoja skaičius, ji sukuria atidarius darbalapį arba, kai nauja informacija yra įvesti į darbalapį.

Pirmiausia, kopijuokite langelio C3 į C4:C402 =RAND()formulės. Tada galite pavadinti C3:C402 duomenųdiapazoną. Tada F stulpelį, galite sekti 400 atsitiktinį skaičių (langelyje F2) vidurkį ir naudokite funkciją COUNTIF nustatyti trupmenas, kurie yra tarp 0 ir 0,25, 0,25 ir 0,50, 0,50 ir 0,75, ir 0,75 ir 1. Kai paspaudžiate klavišą F9, perskaičiuojamos atsitiktinį skaičių. Pranešimas 400 skaičių vidurkį visada yra maždaug 0,5, ir kad maždaug 25 procentų rezultatų kas 0,25. Šie rezultatai yra apibrėžimo atsitiktinį skaičių. Taip pat Atkreipkite dėmesį, nepriklauso generuoja RAND iš skirtingų langelių reikšmes. Pavyzdžiui, jei generuoja atsitiktinį skaičių langelyje C3 yra daug (pavyzdžiui, 0,99), jis sako mums nieko apie kitus sugeneruoti atsitiktinį skaičių reikšmes.

Tarkime, kad pagal poreikį, kalendoriaus taikomos toliau atskiras atsitiktinis kintamasis:

Pagal poreikį

Tikimybė

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0.25

Kaip mes Excel leisti arba imituoti šį kalendorių pagal poreikį daug kartų? Esmė ta susieti kiekvieną funkcija RAND vertė su galima pagal poreikį, kalendoriai. Šias užduotis užtikrina, kad reikalavimą 10 000 pasitaiko 10 procentų laiko ir pan.

Pagal poreikį

Atsitiktinis skaičius priskirta

10 000

Mažiau nei 0,10

20 000

Didesnis už arba lygus 0,10 ir mažesnė už 0,45

40 000

Didesnis už arba lygus 0,45 ir yra mažesnės nei 0,75

60 000

Didesnis arba lygus 0,75

Parodyti modeliavimas pagal poreikį, peržiūrėkite failą Discretesim.xlsx, kitame puslapyje rodomas 60-2 pav.

Book Image
60-2 grafiką imituoti atskiras atsitiktinis kintamasis

Mūsų modeliavimas svarbiausia peržvalgos lentelės diapazono F2:G5 (pavadintas peržvalgos) pradėti naudoti atsitiktinį skaičių. Atsitiktinius skaičius didesnis už arba lygus 0 ir mažesnė nei 0,10 bus yield reikalavimą 10 000; atsitiktinis skaičius didesnis už arba lygus 0,10 ir mažesnė už 0,45 bus yield reikalavimą 20 000 lt; atsitiktinis skaičius didesnis už arba lygus 0,45 ir yra mažesnės nei 0,75 bus yield reikalavimą 40 000; ir atsitiktinis skaičius didesnis už arba lygus 0,75 bus yield poreikį 60 000. Galite sukurti 400 atsitiktinį skaičių iš kopijavimą iš C3 C4:C402 formulės Rand (). Tada sugeneruoti 400 bandymai arba iteracija, kalendoriaus pareikalavus kopijavimą iš B3 B4:B402 VLOOKUP(C3,lookup,2)formulę. Ši formulė užtikrina, kad atsitiktinį skaičių mažiau 0.10 sukuria 10 000 paklausios, bet atsitiktinis skaičius nuo 0,10 iki 0,45 generuoja reikalavimą 20 000, skaičius ir pan. F8:F11 langelių diapazone, naudokite funkciją COUNTIF nustatyti esančių mūsų 400 iteracijos duoda kiekvienam pagal poreikį. Kai mes paspauskite F9, kad būtų perskaičiuotas atsitiktinį skaičių, imituotą tikimybės yra arti mūsų tikimybės numanoma pagal poreikį.

Jei formulės NORMINV(rand(),mu,sigma)tipo bet kurį langelį, jums sukurs imituotą įprastas atsitiktinis kintamasis, kurių vidutinis mu ir standartinis nuokrypis sigmavertę. Šią procedūrą iliustruoja failą Normalsim.xlsx, rodomas 60-3 pav.

Book Image
Pateiktame paveikslėlyje 60-3 imituoti įprastas atsitiktinis kintamasis

Tarkime, kad norime imituoti 400 bandymai, ar kartojimą, į įprastą atsitiktinis kintamasis, turintis 40 000 vidurkis ir standartinis nuokrypis yra 10 000. (Galite įveskite šias reikšmes langeliuose E1 ir E2 ir pavadinkite šių langelių reiškia ir sigma, atitinkamai.) Kopijuokite formulės =RAND() C4 į C5:C403 generuoja 400 skirtingų atsitiktinį skaičių. Kopijavimas iš B4 B5:B403 NORMINV(C4,mean,sigma) formulė sukuria 400 bandomąją skirtingas reikšmes iš įprastas atsitiktinis kintamasis su 40 000 vidurkis ir standartinis nuokrypis yra 10 000. Paspaudus klavišą F9, kad būtų perskaičiuotas atsitiktinį skaičių vidurkį lieka netoli 40 000 ir standartinio nuokrypio arti 10 000.

Iš esmės, per atsitiktinį skaičių x, formulės NORMINV(p,mu,sigma) generuoja įprastas atsitiktinis kintamasis vidutinis mu ir standartinis nuokrypis sigmapojo procentilio reikšmę. Pvz., atsitiktinį skaičių 0,77 langelyje C4 (pateikta 3 paveikslas 60) generuoja langelyje B4 maždaug 77-oji įprastas atsitiktinis kintamasis procentilio 40 000 vidurkis ir standartinis nuokrypis yra 10 000.

Šiame skyriuje bus rodomi kaip Sirius modeliavimas gali būti naudojamas kaip sprendimų įrankį. Tarkime, kad pagal poreikį, Valentino dienos kortelės taikoma šių atskirų atsitiktinis kintamasis:

Pagal poreikį

Tikimybė

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0.25

Sveikinimo atviruko parduoda už $4.00, ir kintamojo gamybos išlaidų ir kiekviena kortelė yra $1.50. Likusių kortelės turi būti šalinamos kaina $0,20 už kortelę. Ar spausdinti kiek kortelės?

Iš esmės, mes imituoti galimas gamybos kiekis (10 000, 20 000 lt, 40 000 ar 60 000) daug kartų (pavyzdžiui, 1000 iteracija). Tada mes nustatyti, kurioje užsakymo kiekį duoda maksimalų pelno vidurkis virš 1000 kartojimą. Duomenis galite rasti šiame skyriuje failo Valentine.xlsx, rodomas 60-4 pav. Langelių C1:C11 priskiriate B1:B11 langelių diapazono vardai. Langelių diapazonas G3:H6 priskirta pavadinimas peržvalgos. Mūsų pardavimo kainų ir sąnaudų parametrų yra įvestas lauke langelių C4:C6.

Book Image
4 paveikslėlis 60 Valentino dienos kortelės modeliavimas

Langelyje C1 įveskite bandomosios gamybos kiekis (Šiame pavyzdyje 40 000). Tada sukurkite atsitiktinį skaičių langelyje C2 su =RAND()formulės. Kaip aprašyta anksčiau, galite imituoti, kad kortelės langelyje C3 su formulės VLOOKUP(rand,lookup,2)pagal poreikį. (VLOOKUP formulę, rand yra langelio C3, o ne funkcija RAND priskirtą langelio pavadinimą.)

Parduota vienetų skaičius yra mažesnis mūsų gamybos kiekį ir pagal poreikį. C8 langelyje galite apskaičiuoti savo pajamas, kuriame yra formulė MIN (sukurto, pagal poreikį) * unit_price. Langelyje C9, jūs apskaičiuoti visos gamybos išlaidų formulę pateikė * unit_prod_cost.

Jei negalime pateikti daugiau kortelių nei pagal poreikį, vienetų skaičius liko lygi gamybos atėmus pagal poreikį; kitu atveju nėra vienetų yra kairėje. Mes apskaičiuoti mūsų šalinimo sąnaudos C10 langelį su formule, unit_disp_cost * IF (pateikė > pagal poreikį, pateikė – pagal poreikį, 0). Galiausiai, langelyje C11, mes apskaičiuoti mūsų pelno kaip pajamų – total_var_cost – total_disposing_cost.

Mums efektyvus būdas kiekvieną gamybos kiekį, paspauskite F9 daug kartų (pavyzdžiui, 1000) ir sutampa mūsų numatomas pelno kiekvieną kiekį. Tai yra viena abipusis duomenų lentelę, kurioje ateina mūsų pagalbą. (Pateikta 15 skyriaus "Slaptumo analizės su duomenų lentelėmis," išsamios informacijos apie duomenų lenteles.) Šiame pavyzdyje naudojama duomenų lentelė rodoma 60-5 pav.

Book Image
Atviruko modeliavimas skaičius 60-5 abipusis duomenų lentelę

A16:A1015 langelių diapazoną, įveskite norimus skaičius 1 – 1 000 (atitinka mūsų 1000 bandymai). Pradėkite įvesdami 1 langelyje A16 yra vienas lengvas būdas sukurti šias reikšmes. Pasirinkite langelį, tada skirtuko Pagrindinis grupėje Redagavimas spustelėkite užpildyti, ir pasirinkite sekos dialogo langą seka . Sekos dialogo lange rodomas skaičius 60-6, įveskite veiksmo reikšmę, lygią 1 ir sustabdyti vertė yra 1000. Sekos srityje pasirinkite norimą stulpelių parinktį ir spustelėkite gerai. Skaičius 1 – 1000 bus įvestas stulpelio langelyje A16 pradžios.

Book Image
60-6 paveikslėlis naudojant sekos dialogo langas užpildyti į bandomąją skaičius nuo 1 iki 1000

Toliau mes mūsų galimų gamybos kiekius (10 000, 20 000 lt, 40 000, 60 000) lauke įveskite langelių B15:E15. Mes norime apskaičiuoti pelno kiekvieno bandomojo naudojimo skaičius (nuo 1 iki 1 000) ir kiekvieno gamybos kiekis. Įvedant = C11vadiname formulė pelno (apskaičiuotas langelį C11) mūsų duomenų lentelę (A15) viršutinį kairįjį langelį.

Mes pasiruošę dabar apgauti "Excel" į imituoti 1000 kartojimą, pagal poreikį kiekvieną gamybos kiekį. Pasirinkite lentelę, diapazoną (A15:E1014), ir tada skirtuko Duomenys grupė duomenų įrankiai spustelėkite analizė kas, jei ir pasirinkite duomenų lentelė. Norėdami nustatyti abipusis duomenų lentelę, pasirinkite mūsų gamybos kiekį (langelyje C1) kaip eilutės įvesties langelis ir pasirinkite bet kurį tuščią langelį (pasirinkome langelio I14) stulpelio įvesties langelis. Spustelėjus Gerai, "Excel" imituoja 1000 pagal poreikį reikšmės kiekvieno užsakymo kiekį.

Norėdami suprasti, kodėl tai veikia, apsvarstykite Įdėjo duomenų lentelės langelių diapazone C16:C1015 reikšmes. Kiekvienos iš šių langelių, "Excel" naudos 20 000 lt reikšmė langelyje C1. C16, stulpelio įvesties langelis reikšmę, lygią 1 yra į tuščią langelį ir atsitiktinį skaičių langelyje C2 perskaičiavimo metu. Tada atitinkamas pelno įrašytas C16 langelyje. Tada stulpelio langelį įvesties reikšmė 2 į tuščią langelį, ir dar kartą perskaičiuojama atsitiktinį skaičių langelyje C2. Atitinkamų pelno įvedama C17 langelyje.

Kopijuojant iš langelio B13 į C13:E13 AVERAGE(B16:B1015)formulę, mes apskaičiuoti pelno vidurkis imituotą kiekvieną gamybos kiekį. Kopijuojant langelio B14 į C14:E14 STDEV(B16:B1015)formulę, mes apskaičiuoti standartinį nuokrypį mūsų imituotą pelno kiekvieno užsakymo kiekį. Kiekvieną kartą, kai mes paspauskite F9, 1000 kartojimą, pagal poreikį imituoti kiekvieną užsakymo kiekį. Gamybos 40 000 kortelės visada duoda didžiausią numatomas pelno. Todėl atrodo, kad gamybos 40 000 kortelės yra tinkamas sprendimas.

Rizikos mūsų sprendimo pasekmes     Jeigu mes 20 000 lt vietoj 40 000 kortelių, mūsų numatomas pelno praleidžia apie 22 procentus, tačiau mūsų rizikos (tai įvertinama pagal pelno standartinis nuokrypis) praleidžia beveik 73 procentų. Todėl, jei mes labai vengti rizikos, 20 000 kortelių gamybos gali būti teisingas sprendimas. Gaminančios 10 000 kortelės visada turi standartinis nuokrypis 0 kortelių, nes jei mes 10 000 kortelių, mes visada parduoti visi jie be jokių leftovers.

Pastaba: Šioje darbaknygėje apskaičiavimo parinktis nustatyta Automatiškai, išskyrus lenteles. (Naudokite komandą skaičiavimo, skirtuko Formulės grupėje skaičiavimas). Šis parametras užtikrina, kad mūsų duomenų lentelė ne perskaičiuoti, nebent paspauskite F9, kuris yra gerų idėjų, nes didelių duomenų lentelės bus sulėtinti savo darbą, jei ji perskaičiuojama kas kartą, kai jūs įvedate tekstą į savo darbalapį. Atkreipkite dėmesį, kad šiame pavyzdyje, kai paspaudžiate klavišą F9, pelno vidurkis bus pakeisti. Taip atsitinka, nes kiekvieną kartą, paspaudus klavišą F9, naudojamas skirtingas seka 1000 atsitiktinių skaičių reikės kiekvieno užsakymo kiekį.

Patikimumo intervalą reiškia pelno     Natūralaus klausimą užduoti tokiu yra, į ką intervalą mes 95 procentų tikrai teisinga pelno vidurkis pateks? Šis intervalas vadinamas pelno vidurkis 95 % pasikliovimo intervalą. 95 % pasikliovimo intervalą išvesčiai modeliavimas vidurkis apskaičiuojamas pagal šią formulę:

Book Image

J11 langelyje jūs apskaičiuoti apatinė 95 procentų pasikliautinąjį intervalą, pelno vidurkis, kai su formulės D13–1.96*D14/SQRT(1000)pagaminta 40 000 kalendorių. J12 langelyje jūs apskaičiuoti mūsų 95 procentų pasikliautinąjį intervalą, kuriame yra formulė D13+1.96*D14/SQRT(1000)viršutinė riba. Šių skaičiavimų rodomos 60-7 pav.

Book Image
60 7 paveikslėlis 95 % pasikliovimo intervalą vidurkis pelną, kai išdėstomi 40 000 kalendorius

Mes yra 95 procentų tikri, kad mūsų vidurkis pelną, kai išdėstomi 40 000 kalendorius yra tarp $56,687 ir $62,589.

  1. GMC atstovas mano, kad paraiška dėl 2005 Envoys normaliai paskirstyta su 200 vidurkis ir standartinis nuokrypis 30. Jo kaina yra atstovui gauti yra $ 25 000 ir jis parduoda 40 000 EUR yra atstovui. Pusė visų pasiuntiniai, nėra parduodami ne visą kainą galima parduoti 30 000 $. Jis yra atsižvelgiant į tai, 200, 220, 240, 260, 280 ar 300 pasiuntiniai. Kiek jis turėtų užsisakyti?

  2. Maža parduotuvė bando nustatyti, kiek kopijų žmonės žurnalas jie turėtų būti, kad kiekvieną savaitę. Jie mano, kad jų paraiška dėl žmonių taikomos toliau atskiras atsitiktinis kintamasis:

    Pagal poreikį

    Tikimybė

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0.25

    35

    0,15

  3. Prekybos 1,00 mokamos kiekvieno kopijos žmonių ir parduoda 1,95 $. Kiekvienos neparduotos kopijos gali būti pateikti 0,50 $. Asmenų, kiek kopijų turėtų parduotuvės tvarka?

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.

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ų.

×