Introduktion til Monte Monte-simulering i Excel

Denne artikel er tilpasset fra Microsoft Excel Data Analysis and Business Modeling af Wayne L. Winston.

  • Who du bruger Monte Monte Monte-simulering?

  • Hvad sker der, når du skriver =SLUMP() i en celle?

  • Hvordan kan man simulere værdier af en diskret tilfældig variabel?

  • Hvordan kan man simulere værdier fra en almindelig tilfældig variabel?

  • Hvordan kan et kortfirma bestemme, hvor mange kort der skal produceres?

Vi vil gerne nøjagtigt vurdere sandsynligheden for ikke-sikrede hændelser. Hvad er f.eks. sandsynligheden for, at et nyt produkts pengestrømme vil have en positiv nutidsværdi (NUTIDSVÆRDI)? Hvad er risikofaktoren ved vores investeringsportefølje? Monte Monte-simulering gør det muligt for os at modellere situationer, der skaber usikkerhed, og derefter afspille dem på en computer tusindvis af gange.

Bemærk!:  Navnet Monte Monte-simulering kommer fra computersimuleringerne, der blev udført i løbet af 1930'erne og 1940'erne for at vurdere sandsynligheden for, at den kædes reaktion, der kræves for en atombombe til detonate, vil fungere korrekt. De fysiske spillere, der var involveret i dette arbejde, var store fans af arne, så de gav simuleringerne kodenavnet Monte Monte.

I de næste fem kapitler får du vist eksempler på, hvordan du kan bruge Excel til at udføre Monte Monte-simulering.

Mange virksomheder bruger Monte Monte-simulering som en vigtig del af deres beslutningsproces. Her er nogle eksempler.

  • General Motors, Proctor og Pfizer, Pfizer, Bristol-Myers Squibb og Eli Lilly bruger simulering til at anslå både den gennemsnitlige returnering og risikofaktoren for nye produkter. Hos GM bruges disse oplysninger af den administrerende direktør til at bestemme, hvilke produkter der kommer på markedet.

  • GM bruger simulering til aktiviteter som f.eks. prognose af nettoindtægt for virksomheden, skøn over struktur- og købsomkostninger og fastlæggelse af dens følsomhed over for forskellige risikotyper (f.eks. ændringer i rentesatser og udsving i valutakurser).

  • Lilly bruger simulering til at bestemme den optimale plantekapacitet for hver ydelse.

  • Proctor og 2010 bruger simulering til at modellere og opnå optimal risiko for fremmed exchange.

  • Sears bruger simulering til at bestemme, hvor mange enheder af hver produktlinje, der skal bestilles fra leverandører – f.eks. antallet af par af dockere, der skal bestilles i år.

  • Olie- og transportvirksomheder bruger simulering til at værdiulere "reelle muligheder", f.eks. værdien af en mulighed for at udvide, sammentløbe eller udsætte et projekt.

  • Økonomiske planlæggere bruger Monte Monte Monte-simulering til at bestemme optimale investeringstrategier for deres kunders tilbagetrækning.

Når du skriver formlen =SLUMP() i en celle, får du et tal, der med lige stor sandsynlighed antager en hvilken som helst værdi mellem 0 og 1. Dermed bør du, omkring 25 procent af tiden, få et tal, der er mindre end eller lig med 0,25; omkring 10 % af tiden skal der være et tal, der er mindst 0,90 osv. For at demonstrere, hvordan funktionen SLUMP fungerer, skal du se nærmere på Randdemo.xlsx, der er vist i Figur 60-1.

Billede af bog

Bemærk!:  Når du åbner filen Randdemo.xlsx, vises de samme tilfældige tal ikke i Figur 60-1. Funktionen SLUMP genberegner altid automatisk de tal, der genereres, når et regneark åbnes, eller når der angives nye oplysninger i regnearket.

Først skal du kopiere fra celle C3 til C4:C402 formlen =SLUMP(). Derefter navngives området C3:C402 Data. Derefter kan du i kolonne F spore gennemsnittet af de 400 tilfældige tal (celle F2) og bruge funktionen TÆL.HVIS til at bestemme brøker mellem 0 og 0,25, 0,25 og 0,50, 0,50 og 0,75 og 0,75 og 0,75 og 1. Når du trykker på F9, genberegnes de tilfældige tal. Bemærk, at gennemsnittet af de 400 tal altid er ca. 0,5, og at ca. 25 % af resultaterne er i intervaller på 0,25. Disse resultater er i overensstemmelse med definitionen af et tilfældigt tal. Bemærk også, at de værdier, der genereres af SLUMP i forskellige celler, er uafhængige. Hvis f.eks. det tilfældige tal, der genereres i celle C3, er et stort tal (f.eks. 0,99), fortæller det os ikke noget om værdierne af de andre tilfældige tal, der genereres.

Antag, at behovet for en kalender styres af følgende diskrete tilfældige variabel:

Behov

Sandsynlighed

10.000

0,10

20.000

0.35

40,000

0,3

60.000

0,25

Hvordan kan vi Excel afspille eller simulere dette behov for kalendere mange gange? Tricket er at knytte hver mulig værdi af funktionen SLUMP til et muligt behov for kalendere. Følgende opgave sikrer, at et behov på 10.000 forekommer 10 % af tiden osv.

Behov

Vilkårligt tildelt tal

10.000

Mindre end 0,10

20.000

Større end eller lig med 0,10 og mindre end 0,45

40,000

Større end eller lig med 0,45 og mindre end 0,75

60.000

Større end eller lig med 0,75

For at demonstrere simulering af behov skal du Discretesim.xlsx filfilen, der er vist i Figur 60-2 på den næste side.

Billede af bog

Nøglen til vores simulering er at bruge et tilfældigt tal til at starte et opslag fra tabelområdet F2:G5 (navngivet opslag). Tilfældige tal, der er større end eller lig med 0 og mindre end 0,10, giver en efterspørgsel på 10.000. tilfældige tal, der er større end eller lig med 0,10 og mindre end 0,45, giver en efterspørgsel på 20.000. tilfældige tal, der er større end eller lig med 0,45 og mindre end 0,75, giver en efterspørgsel på 40.000. og tilfældige tal, der er større end eller lig med 0,75, giver et behov på 60.000. Du opretter 400 tilfældige tal ved at kopiere formlen SLUMP()fra C3 til C4:C402. Du genererer derefter 400 forsøg eller gentagelser af kalenderkrav ved at kopiere fra B3 til B4:B402 formlen LOPSLAG(C3,opslag,2). Denne formel sikrer, at et vilkårligt tal, der er mindre end 0,10, genererer et behov på 10.000, et vilkårligt tal mellem 0,10 og 0,45 genererer et behov på 20.000 osv. I celleområdet F8:F11 skal du bruge funktionen TÆL.HVIS til at bestemme den brøkdel af vores 400 gentagelser, der giver hvert behov. Når vi trykker på F9 for at genberegne de tilfældige tal, er de simulerede sandsynligheder tæt på vores antagede behovssandsynligheder.

Hvis du skriver formlen NORMINV(rand(),mu,sigma)i en celle, genererer du en simuleret værdi af en almindelig tilfældig variabel, der har en middelværdi mu og standardafvigelse sigma. Denne fremgangsmåde er illustreret i Normalsim.xlsx, der er vist i Figur 60-3.

Billede af bog

Lad os antage, at vi vil simulere 400 forsøg eller gentagelser til en almindelig tilfældig variabel med en middelværdi på 40.000 og en standardafvigelse på 10.000. Du kan skrive disse værdier i cellerne E1 og E2 og navngive disse cellers middelværdi og sigma. Når du kopierer formlen =SLUMP() fra C4 til C5:C403, genereres der 400 forskellige tilfældige tal. Når formlen NORMINV(C4;middelværdi;sigma) kopieres fra B4 til B5:B403, genereres der 400 forskellige prøveværdier fra en almindelig tilfældig variabel med en middelværdi på 40.000 og en standardafvigelse på 10.000. Når vi trykker på F9 for at genberegne de tilfældige tal, forbliver middeltallet tæt på 40.000, og standardafvigelsen er tæt på 10.000.

I bund og grund genererer formlen NORMINV(p,mu,sigma) den p'tefraktil for en almindelig tilfældig variabel med en middelværdi mu og en standardafvigelse sigma. Det vilkårlige tal 0,77 i celle C4 (se Figur 60-3) genererer f.eks. i celle B4 cirka den 77. fraktil for en normal tilfældig variabel med en middelværdi på 40.000 og en standardafvigelse på 10.000.

I dette afsnit kan du se, hvordan Monte Laura-simulering kan bruges som et beslutningsprocesværktøj. Antag, at behovet for et Valentinsdagkort er underlagt følgende diskrete tilfældige variabel:

Behov

Sandsynlighed

10.000

0,10

20.000

0.35

40,000

0,3

60.000

0,25

Kortet sælger for 4,00 USD, og de variable omkostninger ved produktion af hvert kort er 1,50 USD. Restkort skal afhændes til en pris på $ 0,20 pr. kort. Hvor mange kort skal udskrives?

Grundlæggende simulerer vi hver mulige produktionsmængde (10.000, 20.000, 40.000 eller 60.000) mange gange (f.eks. 1000 gentagelser). Derefter bestemmer vi, hvilken ordreantal der giver den maksimale gennemsnitlige fortjeneste over de 1000 gentagelser. Du kan finde dataene for denne sektion i filens Valentine.xlsx vist i Figur 60-4. Du tildeler områdenavnene i cellerne B1:B11 til cellerne C1:C11. Celleområdet G3:H6 tildeles navneopslag. Vores salgspris- og omkostningsparametre angives i cellerne C4:C6.

Billede af bog

Du kan angive et produktionsantal for prøveversionen (40.000 i dette eksempel) i celle C1. Derefter skal du oprette et tilfældigt tal i celle C2 med formlen =SLUMP(). Som beskrevet tidligere simulerer du behovet for kortet i celle C3 med formlen LOPSLAG(slump, opslag,2). (I formlen LOPSLAG er slump cellenavnet tildelt celle C3, ikke funktionen SLUMP).

Antallet af solgte enheder er den mindste af vores produktionsmængde og -behov. I celle C8 beregner du vores indtægter med formlen MIN(produceret,behov)*unit_price. I celle C9 beregner du de samlede produktionsomkostninger med formlen produceret*unit_prod_cost.

Hvis vi laver flere kort, end der er behov for, er antallet af enheder, der er tilbage, lig med produktion minus behov. Ellers efterlades ingen enheder til overs. Vi beregner vores afhændelsesomkostninger i celle C10 med formlen unit_disp_cost*HVIS(fremstillet>,produceret–behov,0). Endelig beregner vi i celle C11 vores overskud som indtægter – total_var_cost-total_disposing_cost.

Vi vil gerne have en effektiv måde at trykke på F9 mange gange (f.eks. 1000) for hver produktionsmængde og vores forventede overskud for hver mængde. Denne situation kan hjælpe os med en tovejsdatatabel. (Se kapitel 15 "Følsomhedsanalyse med datatabeller", hvis du vil have mere at vide om datatabeller). Den datatabel, der bruges i dette eksempel, vises i Figur 60-5.

Billede af bog

I celleområdet A16:A1015 skal du angive tallene 1-1000 (svarende til vores 1000 forsøg). En nem måde at oprette disse værdier på er at starte med at skrive 1 i celle A16. Markér cellen, og klik derefter på Fyld i gruppen Redigering under fanen Hjem,og vælg Serie for at få vist dialogboksen Serie. I dialogboksen Serie vist i Figur 60-6 skal du angive en trinværdi på 1 og en stopværdi på 1000. I området Serie i skal du vælge indstillingen Kolonner og derefter klikke på OK. Tallene 1-1000 angives i kolonne A startende i celle A16.

Billede af bog

Derefter angiver vi vores mulige produktionsmængder (10.000, 20.000, 40.000, 60.000) i cellerne B15:E15. Vi vil beregne overskuddet for hvert prøvenummer (1 til 1000) og hver produktionsmængde. Vi refererer til formlen for fortjeneste (beregnet i celle C11) i den øverste venstre celle i vores datatabel (A15) ved at angive =C11.

Vi er nu klar til at Excel til si hele 1000 gentagelser af efterspørgsel for hver produktionsmængde. Vælg tabelområdet (A15:E1014), og klik derefter på What if-analyse i gruppen Dataværktøjer under fanen Data, og vælg derefter Datatabel. Hvis du vil konfigurere en tovejsdatatabel, skal du vælge vores produktionsmængde (celle C1) som inputcelle for række og vælge en tom celle (vi valgte celle I14) som inputcelle for kolonne. Når du klikker på OK, Excel simulerer 1000 behovsværdier for hvert ordreantal.

For at forstå, hvorfor dette fungerer, skal du overveje de værdier, som datatabellen placerede i celleområdet C16:C1015. For hver af disse celler Excel en værdi på 20.000 i celle C1. I C16 er inputcelleværdien 1 for kolonne placeret i en tom celle, og det vilkårlige tal i celle C2 genberegnes. Det tilsvarende overskud registreres derefter i celle C16. Derefter placeres inputværdien for kolonnecellen 2 i en tom celle, og det vilkårlige tal i C2 genberegnes igen. Det tilsvarende overskud angives i celle C17.

Ved at kopiere fra celle B13 til C13:E13 formlen MIDDEL(B16:B1015)beregner vi gennemsnitlig simuleret fortjeneste for hver produktionsmængde. Ved at kopiere fra celle B14 til C14:E14 formlen STDAFV(B16:B1015)beregner vi standardafvigelsen for vores simulerede overskud for hvert ordreantal. Hver gang vi trykker på F9, simuleres 1000 gentagelser af efterspørgsel for hver ordreantal. Produktion af 40.000 kort giver altid det største forventede overskud. Det ser derfor ud til, at det er den rigtige beslutning at producere 40.000 kort.

Risikopåvirkningen af vores beslutning      Hvis vi fremstillede 20.000 i stedet for 40.000 kort, falder vores forventede overskud ca. 22 procent, men vores risiko (målt efter standardafvigelsen på fortjeneste) falder næsten 73 procent. Hvis vi er ekstremt forsigtige med risikoen, kan det derfor være den rigtige beslutning at producere 20.000 kort. Produktion af 10.000 kort har for øvrigt altid en standardafvigelse på 0 kort, for hvis vi producerer 10.000 kort, sælger vi dem alle uden rester.

Bemærk!:  I denne projektmappe er beregningsindstillingen angivet til Automatisk bortset fra tabeller. (Brug kommandoen Beregning i gruppen Beregning under fanen Formler). Denne indstilling sikrer, at vores datatabel ikke genberegnes, medmindre vi trykker på F9, hvilket er en god ide, fordi en stor datatabel vil sænke dit arbejde, hvis den genberegnes, hver gang du skriver noget i regnearket. Bemærk, at når du i dette eksempel trykker på F9, ændres den gennemsnitlige fortjeneste. Dette sker, fordi der hver gang, du trykker på F9, bruges en forskellig rækkefølge på 1000 tilfældige tal til at generere behov for hvert ordreantal.

Tillidsinterval for gennemsnitlig fortjeneste      Et naturligt spørgsmål at stille dig i denne situation er: I hvilket interval er vi 95 % sikre på, at den reelle gennemsnitlige fortjeneste falder? Dette interval kaldes for et tillidsinterval på 95 % for gennemsnitlig fortjeneste. Et tillidsinterval på 95 % for middelværdi for simuleringsoutput beregnes af følgende formel:

Billede af bog

I celle J11 beregner du den nedre grænse for et tillidsinterval på 95 % af den gennemsnitlige fortjeneste, når der produceres 40.000 kalendere med formlen D13-1,96*D14/SQRT(1000). I celle J12 beregner du den øvre grænse for vores 95 procent tillidsinterval med formlen D13+1,96*D14/SQRT(1000). Disse beregninger vises i Figur 60-7.

Billede af bog

Vi er 95 procent sikre på, at vores gennemsnitlige overskud, når der er bestilt 40.000 kalendere, ligger mellem DKK 56.687 og DKK 62.589.

  1. En GMC-forhandler mener, at efterspørgsel efter 2005 Envoys normalt fordeles med en middelværdi på 200 og en standardafvigelse på 30. Hans omkostninger til at modtage en Envoy koster 25.000 USD, og han sælger en Envoy for 40.000 USD. Halvdelen af alle Envoys, der ikke er solgt til fuld pris, kan sælges for $ 30.000. Han overvejer at bestille 200, 220, 240, 260, 280 eller 300 Envoys. Hvor mange skal han bestille?

  2. En lille mængde mennesker forsøger at finde ud af, hvor mange kopier af People Magazine de skal bestille hver uge. De mener, at deres behov for Personer styres af følgende diskrete tilfældige variabel:

    Behov

    Sandsynlighed

    15

    0,10

    20

    0.20

    25

    0.30

    30

    0,25

    35

    0,15

  3. Det en til enhver tid 1,00 USD for hver kopi af Personer og sælger det for $ 1,95. Hver usåret kopi kan returneres for $ 0,50. Hvor mange kopier af Personer skal gemme ordren?

Har du brug for mere hjælp?

Du kan altid spørge en ekspert i Excel Tech Community eller få support i Answers-forummet

Har du brug for mere hjælp?

Udvid dine Office-færdigheder
Gå på opdagelse i kurser
Få nye funktioner først
Bliv Office Insider

Var disse oplysninger nyttige?

×