Analysere Excel 2007

Microsoft Office Excel 2007: Data Analysis and Business Modeling
af Wayne L. Winston

Wayne L. Winston er professor i beslutningsteori ved Indiana Universitys Kelley School of Business og har fået tildelt flere MBA-undervisningspriser. I mere end 20 år har han undervist kunder fra virksomheder på Fortune 500-listen i brugen af Excel til at træffe intelligente forretningsbeslutninger. Wayne og hans forretningspartner, Jeff Sagarin, har udviklet det statistik-, sporings- og klassificeringssystem, der benyttes af det professionelle basketballhold Dallas Mavericks. Han har også vundet to gange i Jeopardy!.

Du kan få flere oplysninger om andre bøger om systemet Microsoft Office 2007 her: Microsoft Press.

Denne artikel indeholder

Følsomhedsanalyse med datatabeller

Kommandoen Målsøgning

I denne artikel beskrives det, hvordan du kan anvende følsomhedsanalysen sammen med datatabeller i Microsoft Office Excel 2007 til at træffe vigtige forretningsbeslutninger ved at beregne output fra bestemte forudsatte parametre eller input. Du lærer også at bruge funktionen Målsøgning i Office Excel 2007 til at beregne en værdi for et input i regneark, der får værdien for en given formel til at stemme overens med det angivne mål. Funktionerne i Office Excel 2007 kan bedst forklares ved hjælp af eksempler. Derfor vises et eksempel med åbningen af en limonadebod, der demonstrerer, hvordan funktionerne Følsomhedsanalyse og Målsøgning bruges til at analysere forretningsresultater.

Følsomhedsanalyse med datatabeller

Spørgsmål: Jeg overvejer at åbne forretning i det lokale storcenter, hvor jeg skal sælge kvalitetslimonade. Inden jeg åbner forretningen, er jeg interesseret i at finde ud af, hvordan overskud, indtægter og variable omkostninger hænger sammen med udbudspris og produktionspris pr. enhed.

De fleste modeller af regneark indeholder forudsætninger om bestemte parametre eller input for modellen. I eksemplet med limonadeboden omfatter inputtet:

  • Salgsprisen for et glas limonade.

  • De variable omkostninger forbundet med produktionen af et glas limonade.

  • Følsomheden for efterspørgslen på limonade i forhold til salgsprisen.

  • De årlige driftsomkostninger for limonadeboden.

Ud fra inputforudsætningerne er det muligt at beregne interessant output. I limonadeeksemplet kan interessant output for eksempel være:

  • Overskud på årsbasis

  • Indtægter på årsbasis

  • Variable omkostninger pr. år

Trods gode intentioner kan forudsætninger for inputværdierne indeholde fejl. Eksempelvis kan vores bedste bud på de variable produktionsomkostninger for et glas limonade være $ 0,45, men denne forudsætning kan vise sig at være forkert. Ud fra følsomhedsanalysen bestemmes det, hvordan outputtet i et regneark varierer som følge af inputændringer. Det kan for eksempel være interessant at se, hvordan en ændring af produktprisen påvirker overskud, indtægter og variable omkostninger pr. år. Du kan bruge en datatabel i Office Excel 2007 til at gøre det nemmere at variere et eller to input og udføre en følsomhedsanalyse. Med en envejsdatatabel kan du finde ud af, hvordan en ændring af ét input påvirker forskellige outputværdier. Med en tovejsdatatabel kan du finde ud af, hvordan en ændring af to inputværdier påvirker et enkelt output. De tre eksempler viser, hvordan du bruger en datatabel til at opnå meningsfyldte følsomhedsresultater.

Du kan se, hvad der kræves for at foretage denne analyse i figur 1, 2 og 4. Inputforudsætningerne er angivet i området D1:D4. Den forventede efterspørgsel på limonade pr. år (se formlen i celle D2) er lig med 65000–9000*pris. Navnene er angivet i C1:C7, så de passer til cellerne D1:D7.

Indtægterne på årsbasis er beregnet i celle D5 via formlen efterspørgsel*pris. De variable omkostninger pr. år er beregnet i celle D6 via formlen enhedsomkostninger*efterspørgsel. Og i celle D7 er overskuddet beregnet via formlen overskud–faste_omkostninger–variable_omkostninger.

Eksempeldata for følsomhedsanalyse

Figur 1  De input, der ændrer rentabiliteten for limonadeforretningen

Lad os forestille os, at jeg ønsker at finde ud af, hvordan prisændringer (f.eks. fra $ 1,00 til $ 4,00 i intervaller af $ 0,25) påvirker overskud, indtægter og variable omkostninger pr. år. Da det kun er ét input, der ændres, kan en envejsdatatabel løse problemet. Datatabellen er vist i figur 2.

Du opretter en envejsdatatabel ved først at angive inputværdierne i en kolonne. De ønskede priser (rangerende fra $ 1,00 til $ 4,00 i intervaller af $ 0,25) er angivet i C11:C23. Derefter angives de formler, som datatabellen skal beregne, i kolonnen ved side af og en række op. Formlen for overskud er angivet i celle D10, formlen for indtægter i celle E10 og formlen for variable omkostninger i celle F10.

Derefter markerer du området (C10:F23). Tabelområdet starter én række over det første input, og den sidste række er den, der indeholder den sidste inputværdi. Den første kolonne i området er den kolonne, der indeholder inputværdierne, og den sidste kolonne indeholder et output. Når du har markeret tabelområdet, skal du klikke på fanen Data på båndet. Klik på Hvad hvis-analyse under Dataværktøjer, og klik derefter på Datatabel. Derefter udfyldes dialogboksen Datatabel i figur 3.

Envejsdatatabel med variable priser

Figur 2  Envejsdatatabel med variable priser

Dialogboksen Datatabel med indstillinger for kolonnens inputcelle

Figur 3  Oprette en datatabel

Som kolonnens inputcelle skal du bruge den celle, hvor de angivne inputværdier – de værdier, der er angivet i den første kolonne i datatabelområdet – skal tildeles. Da de angivne inputværdier er priser, vælges D1 som kolonnens inputcelle. Når du har klikket på OK, oprettes der en envejsdatatabel i figur 4.

Envejsdatatabel med variable priser

Figur 4  Envejsdatatabel med variable priser

Overskud, indtægter og variable omkostninger beregnes for en pris på $ 1,00 i området D11:F11. I cellerne D12:F12 er overskud, indtægter og variable omkostninger beregnet for en pris på $ 1,25 og opefter. Den profitmaksimerede pris blandt de angivne priser er $ 3,75. En pris på $ 3,75 giver et overskud på årsbasis på $ 58.125,00, indtægter på årsbasis på $ 117.187,50 og variable omkostninger på $ 14.062,50.

Lad os forestille os, at jeg ønsker at finde ud af, hvordan overskuddet på årsbasis varierer, når prisen varierer fra $ 1,50 til $ 5,00 (i intervaller af $ 0,25), og variable enhedsomkostninger varierer fra $ 0,30 til $ 0,60 (i intervaller af $ 0,05).

Eftersom det er to inputværdier, der ændres, skal vi bruge en tovejsdatatabel (se figur 5). Værdierne for det ene input angives i den første kolonne i tabelområdet (jeg anvender området H11:H25 til prisværdierne) og værdierne for det andet input i den første række af tabelområdet (i dette eksempel indeholder området I10:O10 listen over de variable omkostninger). En tovejsdatatabel kan kun have én outputcelle, og formlen for outputtet skal placeres øverst til venstre i tabelområdet. Derfor er formlen for overskud placeret i celle H10.

Tovejsdatatabellen viser overskuddet som en funktion af prisen og de variable enhedsomkostninger

Figur 5  Tovejsdatatabellen viser overskuddet som en funktion af prisen og de variable enhedsomkostninger

Du markerer tabelområdet (cellerne H10:O25) og klikker på fanen Data. Klik på Hvad hvis-analyse under Dataværktøjer, og klik derefter på Datatabel. Celle D1 (pris) er kolonnens inputcelle, og celle D3 (variable enhedsomkostninger) er rækkens inputcelle. Derved sikrer du, at værdierne i den første kolonne i tabelområdet bruges til priser, og værdierne i den første række bruges til variable enhedsomkostninger. Når du klikker på OK, får du vist en tovejsdatatabel i figur 5. Hvis du for eksempel har en pris på $ 3,50 og variable enhedsomkostninger på $ 0,40, vil overskuddet på årsbasis være $ 58.850,00. Den profitmaksimerede pris er fremhævet for alle enhedsomkostninger. Bemærk, at når enhedsomkostningerne øges, stiger den profitmaksimerede pris også, da en del af omkostninger tillægges kunderne. Det er selvfølgelig kun muligt at garantere, at den profitmaksimerede pris i datatabellen er inden for $ 0,25 af den faktiske profitmaksimerede pris.

Her er nogle yderligere bemærkninger om det pågældende problem:

  • Når du ændrer inputværdier i et regneark, ændres de værdier, der er beregnet i datatabellen, også. Hvis du eksempelvis øger de faste omkostninger med $ 10.000, reduceres alle overskudsværdierne i datatabellen med $ 10.000.

  • Du kan slette eller redigere en del af en datatabel. Hvis du vil gemme værdierne i en datatabel, skal du markere tabelområdet, kopiere værdierne og højreklikke og markere Indsæt speciel. Derefter vælger du Værdier i menuen Indsæt speciel. Men hvis du vælger denne mulighed, vil inputændringer i regnearket ikke længere medføre en opdatering af beregningerne i datatabellen.

  • Når du konfigurerer en tovejsdatatabel, skal du sørge for ikke at blande rækkens og kolonnens inputceller sammen. En sammenblanding vil skabe meningsløse resultater.

  • De fleste vælger at indstille tilstanden for beregninger i regneark til Automatisk. Med denne indstilling vil alle ændringer i regnearket resultere i en genberegning af datatabellerne. Dette er oftest at foretrække, men hvis datatabellerne er store, kan automatisk genberegning tage meget lang tid. Hvis den regelmæssige genberegning af datatabeller forsinker arbejdet, kan du klikke på knappen Microsoft Office, Excel-indstillinger og fanen Formler. Derefter markerer du Automatisk, undtagen for datatabeller. Når Automatisk, undtagen for datatabeller markeres, genberegnes alle datatabellerne kun, hvis du trykker på tasten F9 (genberegning). Du kan også klikke på knappen Beregningsindstillinger (i gruppen Beregning under fanen Formler) og derefter klikke på Automatisk, undtagen for datatabeller.

Toppen af siden

Kommandoen Målsøgning

Spørgsmål: Hvor mange glas limonade skal en limonadeforretning sælge for en given pris, før regnskabet går lige op?

Funktionen Målsøgning i Office Excel 2007 gør det muligt at beregne en værdi for et input i regneark, som tilpasser værdien for en given formel til det angivne mål. Lad os forestille os, at der i eksemplet med limonadeforretningen eksempelvis er faste omkostninger, faste enhedsomkostninger og en fast salgspris. Ud fra disse oplysninger er det muligt at bruge Målsøgning til at beregne det antal limonadeglas, der skal sælges, før regnskabet går lige op. I bund og grund integrerer Målsøgning en effektiv funktion til løsning af ligninger i regnearket. Du skal opgive tre forskellige oplysninger til Excel for at anvende Målsøgning:

  • Angiv celle    Angiver, at cellen indeholder den formel, der beregner de ønskede oplysninger. I limonadeeksemplet indeholder Angiv celle formlen for overskuddet.

  • Til værdi    Angiver den numeriske værdi for det mål, der er beregnet i Angiv celle. I limonadeeksemplet skal Til værdi angives til 0, da du forsøger at finde den salgsmængde, hvor regnskabet går lige op.

  • Ved ændring af celle    Angiver den inputcelle, der ændres, indtil det mål, der blev defineret i cellen Til værdi, er beregnet i Angiv celle. I limonadeeksemplet indeholder Ved ændring af celle det samlede limonadesalg for året.

Fremgangsmåden for dette afsnit er vist i figur 6. Der er igen forudsat faste omkostninger på årsbasis for $ 45.000,00 og variable enhedsomkostninger for $ 0,45. Der antages en pris på $ 3,00. Spørgsmålet er, hvor mange glas limonade der skal sælges om året, før regnskabet går lige op.

Data til konfiguration af funktionen Målsøgning til analyse af nulresultatet

Figur 6  Disse data anvendes til konfiguration af funktionen Målsøgning til analyse af nulresultatet

Først angiver du et tal for efterspørgsel i celle D2. Klik på Målsøgning i gruppen Hvad hvis-analyse under fanen Data. Udfyld dialogboksen Målsætning som vist i figur 7.

Dialogboksen Målsætning med indtastninger af analysen af nulresultat

Figur 7  Dialogboksen Målsætning udfyldt med indtastninger for en analyse af nulresultatet

Dialogboksen indikerer, at celle D2 (årlig efterspørgsel eller salg) skal ændres, indtil celle D7 (overskud) når en værdi på 0. Når du klikker på OK, vises resultaterne i figur 6. Hvis der sælges ca. 17.647 glas limonade om året (eller 48 glas pr. dag), går regnskabet lige op. Den ønskede værdi findes ved at variere efterspørgslen i celle D2 (skifte mellem høje og lave værdier), indtil der findes en værdi, der resulterer i et overskud på $ 0. Hvis der er mere end én løsning på et problem, vises der stadig kun ét svar i Målsøgning.

Toppen af siden

Del Facebook Facebook Twitter Twitter Mail Mail

Var disse oplysninger nyttige?

Fantastisk! Har du mere feedback?

Hvordan kan vi forbedre det?

Tak for din feedback!

×