Microsoft Office Excel 2007: gegevensanalyse en businessmodellering
door Wayne L. Winston
Wayne L. Winston is een professor in de Besluitvormingswetenschappen aan de Kelley School of Business van de Universiteit van Indiana en heeft talloze MBA-onderwijsprijzen gewonnen. Al meer dan 20 jaar onderwijst hij klanten van Fortune 500-bedrijven hoe zij Excel kunnen gebruiken om slimmere bedrijfsbesluiten te nemen. Wayne en zijn zakenpartner, Jeff Sagarin, hebben het volg- en waarderingssysteem voor spelersstatistieken ontwikkeld dat wordt gebruikt door het professionele basketballteam, Dallas Mavericks. Daarnaast heeft hij twee keer Jeopardy! gewonnen.
Ga voor meer informatie over andere boeken over het 2007 Microsoft Office-systeem naar Microsoft Press.
In dit artikel
Dit artikel legt uit hoe u gevoeligheidsanalyse met Microsoft Office Excel 2007 gegevenstabellen kunt gebruiken om belangrijke bedrijfsbeslissingen te nemen door de uitvoer te berekenen van bepaalde aangenomen parameters of invoerwaarden. U leert ook hoe u de functie Doelzoeken gebruikt in Office Excel 2007 om een waarde te berekenen voor de invoerwaarde in een werkblad die de waarde van een bepaalde formule laat overeenkomen met een opgegeven doel. We kunnen u het beste uitleggen hoe u deze functies van Office Excel 2007 gebruikt, aan de hand van een voorbeeld. We gebruiken een voorbeeld over het openen van een winkel met delicatessenlimonade om u te laten zien hoe u gevoeligheidsanalyse en de functie Doelzoeken gebruikt om bedrijfsresultaten te analyseren.
Gevoeligheidsanalyse met gegevenstabellen
Vraag: Ik wil graag een winkel beginnen in het winkelcentrum om delicatessenlimonade te verkopen. Voordat ik de winkel ga openen, ben ik benieuwd naar hoe mijn winst, opbrengst en variabele kosten afhangen van de prijs die ik reken en van de kosten per eenheid.
De meeste werkbladmodellen bevatten aannames over bepaalde parameters of invoerwaarden in het model. In ons limonadevoorbeeld zijn de invoerwaarden bijvoorbeeld:
- De prijs waarvoor een glas limonade wordt verkocht.
- De variabele kosten van het produceren van een glas limonade.
- De gevoeligheid van de vraag naar limonade voor de berekende prijs.
- De jaarlijkse vaste kosten van het draaien van een limonadetentje.
Op basis van invoerwaardeaannames kunnen we belangrijke uitvoerwaarden berekenen. Voor het limonadevoorbeeld is de belangrijke uitvoerwaarde bijvoorbeeld:
- Jaarlijkse winst
- Jaarlijkse opbrengst
- Jaarlijkse variabele kosten
Ondanks onze beste bedoelingen kunnen aannames over invoerwaarden fout zijn. Onze beste gok over de variabele kosten van een glas limonade kann €0,45 zijn, maar het is mogelijk dat onze aanname fout is. Gevoeligheidsanalyse bepaalt hoe de uitvoer van een spreadsheet verandert als de invoerwaarden worden gewijzigd. We willen bijvoorbeeld zien welk effect een verandering in de productprijs heeft op de jaarlijkse winst, de opbrengst en de variabele kosten. Dankzij een gegevenstabel in Office Excel 2007 kunnen we eenvoudig één of twee invoerwaarden veranderen en een gevoeligheidsanalyse uitvoeren. Met een gegevenstabel met één variabele kunt u bepalen hoe het veranderen van één invoerwaarde een aantal uitvoerwaarden verandert. Met een gegevenstabel met twee variabelen kunt u bepalen hoe het veranderen van twee invoerwaarden één uitvoer wijzigen. Onze drie voorbeelden laten zien hoe eenvoudig het is om een gegevenstabel te gebruiken en zinvolle gevoeligheidsresultaten te krijgen.
In het bestand in de figuren 1, 2 en 4 is te zien wat we moeten doen voor deze analyse. Onze invoerwaardeaannames worden gegeven in het bereik D1:D4. We nemen aan dat de jaarlijkse vraag naar limonade (zie de formule in cel D2) gelijk is aan 65000–9000*prijs. Ik heb de namen in C1:C7 zo gemaakt dat ze corresponderen met de cellen D1:D7.
Ik heb de jaarlijkse opbrengst berekend in cel D5 met de formule vraag*prijs. In cel D6 heb ik de jaarlijkse variabele kosten berekend met de formule kosten_per_eenheid*vraag. Als laatste heb ik in cel D7 de winst berekend met de formule opbrengst–vaste_kosten–variabele_kosten.
Figuur 1 De invoerwaarde die de winstgevendheid van een limonadewinkel verandert
Stel dat ik wil weten hoe veranderingen in de prijs (bijvoorbeeld van €1,00 tot en met €4,00 in stappen van €0,25) invloed hebben op de jaarlijkse winst, de opbrengst en de variabele kosten. Omdat we slechts één invoerwaarde wijzigen, kunnen we ons probleem oplossen met een gegevenstabel met één variabele. De gegevenstabel is te zien in Figuur 2.
Als u een gegevenstabel met één variabele wilt opzetten, begint u met het invoeren van de invoerwaarden in een kolom. Ik heb de prijzen vermeld die voor mij interessant zijn (variërend van €1,00 tot en met €4,00 in stappen van €0,25) in het bereik C11:C23. Daarna ben ik één kolom opzij en één rij omhoog gegaan vanaf de lijst met invoerwaarden en hier heb ik de formules vermeld die we door een gegevenstabel willen laten berekenen. Ik heb de formule voor winst ingevoerd in cel D10, de formule voor opbrengst in cel E10 en de formule voor variabele kosten in cel F10.
Selecteer nu het tabelbereik (C10:F23). Het tabelbereik begint één rij boven de eerste invoerwaarde; de laatste rij is de rij die de laatste invoerwaarde bevat. De eerste kolom in het tabelbereik is de kolom die de invoerwaarden bevat; de laatste kolom is de laatste kolom die een invoerwaarde bevat. Als u het tabelbereik hebt geselecteerd, geeft u het tabblad Gegevens weer van het lint. Klik in de groep Hulpmiddelen voor gegevens op 'Wat als'-analyse en klik vervolgens op Gegevenstabel. Vul nu het dialoogvenster Gegevenstabel in volgens Figuur 3.
Figuur 2 Gegevenstabel met één variabele met veranderende prijzen
Figuur 3 Een gegevevenstabel maken
Als de kolominvoercel gebruikt u de cel waarin u wilt dat de vermelde invoerwaarden — dat wil zeggen, de waarden die in de eerste kolom van het bereik van de gegevenstabel vermeld staan — worden toegewezen. Omdat de vermelde invoerwaarden prijzen zijn, kies ik D1 als de kolominvoercel. Nadat ik op OK heb geklikt, wordt de gegevenstabel met één variabele gemaakt die in Figuur 4 is te zien.
Figuur 4 Gegevenstabel met één variabele met veranderende prijzen
In het bereik D11:F11 worden de winst, de opbrengst en de variabele kosten berekend voor een prijs van €1,00. In de cellen D12:F12 worden de winst, de opbrengst en de variabele kosten berekend voor een prijs van €1,25, en zo verder voor alle prijzen in het bereik. De prijs die de maximale winst oplevert is €3,75. Een prijs van €3,75 zou een jaarlijkse winst opleveren van €58.125,00, een jaarlijkse opbrengst van €117.187,50 en jaarlijkse variabele kosten van €14.062,50.
Stel dat ik wil bepalen hoe de jaarlijkse winst verandert als de prijs verandert van €1,50 tot en met €5,00 (in stappen van €0,25) en de variabele kosten per eenheid veranderen van €0,30 tot en met €0,60 (in stappen van €0,05).
Omdat we twee invoerwaarden veranderen, hebben we een gegevenstabel met twee variabelen nodig. (Zie Figuur 5). Ik vermeld de waarden voor de ene invoerwaarde in de eerste kolom van het tabelbereik (ik gebruik het bereik H11:H25 voor de prijswaarden) en de waarden voor de andere invoerwaarde in de eerste rij van het tabelbereik. (In dit voorbeeld bevat het bereik I10:O10 de lijst met waarden voor de variabele kosten.) Een gegevenstabel met twee variabelen kan maar één uitvoerwaardecel hebben en de formule voor de uitvoerwaarde moet in de linkerbovenhoek van het tabelbereik worden geplaatst. Daarom heb ik de winstformule in cel H10 gezet.
Figuur 5 Gegevenstabel met twee variabelen die de winst laat zien als een functie van de prijs en de variabele kosten per eenheid
Ik selecteer het tabelbereik (cellen H10:O25) en geef het tabblad Gegevens weer. In de groep Hulpmiddelen voor gegevens klik ik op 'Wat als'-analyse en vervolgens op Gegevenstabel. Cel D1 (prijs) is de kolominvoercel en cel D3 (variabele kosten per eenheid) is de rij-invoercel. Dit zorgt ervoor dat de waarden in de eerste kolom van het tabelbereik als prijzen worden gebruikt en dat de waarden in de eerste rij van het tabelbereik als variabele kosten per eenheid worden gebruikt. Nadat ik op OK heb geklikt, zien we de gegevenstabel met twee variabelen in Figuur 5. Voorbeeld: in cel K19, wanneer we €3,50 rekenen en de variabele kosten per eenheid zijn €0,40, is onze jaarlijkse winst gelijk aan €58.850,00. Voor alle kosten per eenheid heb ik de prijs gemarkeerd die de maximale winst oplevert. U ziet dat naarmate de kosten per eenheid stijgen, de prijs die de maximale winst oplevert stijgt, omdat we een gedeelte van de kostenstijging doorberekenen aan onze klanten. Uiteraard kunnen we slechts garanderen dat de prijs die de maximale winst oplevert in de gegevenstabel binnen €0,25 ligt van de werkelijke prijs die de maximale winst oplevert.
Nog een paar opmerkingen over dit probleem:
- Als u de invoerwaarden in een werkblad wijzigt, wijzigen ook de waarden die worden berekend door gegevenstabel. Bijvoorbeeld, als we de vaste kosten verhogen met €10.000, worden alle winstcijfers in de gegevenstabel verminderd met €10.000.
- U kunt niet een gedeelte van een gegevenstabel verwijderen of bewerken. Als u de waarden in een gegevenstabel wilt opslaan, selecteert u het tabelbereik en kopieert u de waarden. Klik vervolgens met de rechtermuisknop en selecteer Plakken speciaal. Kies vervolgens Waarden uit het menu Plakken speciaal. Als u deze stap neemt, worden de berekeningen van de gegevenstabel niet langer bijgewerkt wanneer u wijzigingen aanbrengt in de invoerwaarden van uw werkblad.
- Als u gegevenstabel met twee variabelen opzet, zorg er dan voor dat u uw rij- en kolominvoercellen niet verwart. Anders krijgt u onzinnige resultaten.
- De meeste mensen stellen de berekeningsmodus voor hun werkblad in op Automatisch, Met deze instelling zorgt een wijziging in uw werkblad ervoor dat alle gegevenstabellen worden herberekend. Meestal is dit wat u wilt, maar als u grote gegevenstabellen hebt, kan het automatisch herberekenen zeer langzaam gaan. Als het voortdurend herberekenen van gegevenstabellen uw werk trager maakt, klik dan op de Microsoft Office-knop, klik op Opties voor Excel en klik vervolgens op het tabblad Formules. Selecteer Automatisch behalve voor gegevenstabellen. Wanneer Automatisch behalve voor gegevenstabellen is geselecteerd, worden uw gegevenstabellen alleen berekend wanner u op de toets F9 (herberekenen) drukt. U kunt ook op de knop Berekeningsopties klikken (in de groep Berekening op het tabblad Formules) en vervolgens klikken op Automatisch met uitzondering van gegevenstabellen.
Terug naar boven
De opdracht Doelzoeken
Vraag: Voor een bepaalde prijs, hoeveel glazen limonade moet een limonadewinkel per jaar verkopen om quitte te spelen?
Met de functie Doelzoeken in Office Excel 2007 kunt u een waarde berekenen voor een invoerwaarde in een werkblad waardoor de waarde van een bepaalde formule overeenkomt met het door u opgegeven doel. Bijvoorbeeld, in ons limonadewinkelvoorbeeld, stel dat we vaste overheadkosten hebben, vaste kosten per eenheid en een vaste verkoopprijs. Op basis van deze informatie kunnen we Doelzoeken gebruiken om het aantal glazen limonade te berekenen dat we moeten verkopen om quitte te spellen. In essentie voegt Doelzoeken een krachtige vergelijkingsoplosser toe aan uw werkblad. Als u Doelzoeken wilt gebruiken, moet u drie gegevens opgeven:
- Cel instellen Geeft op dat de waarde de formule bevat die informatie berekent die u zoekt. In het limonadevoorbeeld zou Cel instellen de formule voor winst bevatten.
- Op waarde Geeft de numerieke waarde op voor het doel dat wordt berekend in Cel instellen. In het limonadevoorbeeld zou Op waarde 0 zijn, omdat we het verkoopvolume willen berekenen dat het rentabiliteitspunt weergeeft.
- Door wijzigen van cel Geeft de invoercel op die verandert totdat Cel instellen het doel berekent at in de cel Op waarde is gedefinieerd. In het limonadevoorbeeld bevat Door wijzigen van cel de jaarlijkse limonadeverkoop.
Ons werk voor deze sectie is te zien in Figuur 6. Ik ben opnieuw uitgegaan van jaarlijkse vaste kosten van €45.000,00 en variabele kosten per eenheid van €0,45. Laten we uitgaan van een prijs van €3,00. De vraag is hoeveel glazen limonade moeten we ieder jaar verkopen om quitte te spelen.
Figuur 6 We gebruiken deze gegevens om de functie Doelzoeken in te stellen voor het uitvoeren van een rentabiliteitsanalyse
Voer als eerste een nummer in voor vraag in cel D2. Klik in de groep 'Wat als'-analyse op het tabblad Gegevens op Doelzoeken. Vul nu het dialoogvenster Doelzoeken in volgens Figuur 7.
Figuur 7 Het dialoogvenster Doelzoeken ingevuld met invoerwaarden voor een rentabiliteitsanalyse
Het dialoogvenster geeft aan dat we cel D2 (jaarlijkse vraag, of verkoop) willen veranderen totdat cel D7 (winst) een waarde van 0 bereikt. Nadat we op OK hebben geklikt, krijgen we het resultaat dat te zien is in Figuur 6. Als we ongeveer 17.647 glazen limonade per jaar (of 48 glazen per dag) verkopen, spelen we quitte. Om de waarde te vinden die we zoeken, wordt de vraag in cel D2 veranderd (waarbij er wordt gewisseld tussen hoge en lage waarden) totdat er een waarde wordt gevonden waardoor de winst gelijk wordt aan €0. Als een probleem meer dan één oplossing heeft, laat Doelzoeken toch maar één antwoord zien.
Terug naar boven
