Érzékenységelemzés Excelben (minta adatlap)

A pénzügyek területén a folyamatok mindig összefüggenek egymással – az egyik tényező a másiktól függ, és azzal együtt változik. Kövesse nyomon ezeket a változásokat, és értse meg, mire számíthat a jövőben, esetleg Excel-függvények és táblázatkezelő módszerek segítségével.

Több eredmény elérése adattáblázattal

Az adatlap-szolgáltatások a mi lenne, ha elemzés elemei – gyakran a Microsoft Excel segítségével. Ez az érzékenységelemzés második neve.

Áttekintés

Az adattábla egyfajta cellatartomány, amely problémák megoldására használható egyes cellák értékeinek megváltoztatásával. Akkor jön létre, ha nyomon kell követni a képlet összetevőiben bekövetkezett változásokat, és frissítéseket kell kapni az eredményekről ezeknek a változásoknak megfelelően. Nézzük meg, hogyan használhatjuk fel az adattáblákat a kutatásban, és milyen típusairól van szó.

Az adattáblák alapjai

Az adattábláknak két típusa van, ezek az összetevők számában különböznek. Össze kell állítania egy táblázatot, amely az uXNUMXbuXNUMXb értékek számára összpontosít, amelyeket ellenőriznie kell.

A statisztikusok egyetlen változótáblázatot használnak, ha egy vagy több kifejezésben csak egy olyan változó van, amely megváltoztathatja az eredményt. Például gyakran használják a PMT funkcióval együtt. A képlet a rendszeres fizetés összegének kiszámítására szolgál, és figyelembe veszi a szerződésben meghatározott kamatlábat. Az ilyen számításoknál az egyik oszlopba a változókat, a másikba a számítások eredményeit írják. Példa egy 1 változós adattáblára:

Ezután tekintse meg a 2 változós lemezeket. Olyan esetekben használatosak, amikor bármely mutató változását két tényező befolyásolja. A két változó egy másik, a hitelhez kapcsolódó táblázatba kerülhet, amely alapján meghatározható az optimális törlesztési idő és a havi törlesztőrészlet összege. Ebben a számításban a PMT függvényt is használnia kell. Példa egy 2 változót tartalmazó táblázatra:

Adattábla készítése egy változóval

Tekintsük az elemzési módszert egy kis könyvesbolt példáján, ahol mindössze 100 könyv van raktáron. Egy részük drágábban (50 dollár) adható el, a többi kevesebbe kerül a vásárlóknak (20 dollár). Kiszámolják az összes áru eladásából származó teljes bevételt – a tulajdonos úgy döntött, hogy a könyvek 60%-át magas áron adja el. Meg kell találnia, hogyan nő a bevétel, ha nagyobb mennyiségű áru árát emeli – 70%-kal stb.

Figyelem! A teljes bevételt képlet segítségével kell kiszámítani, különben nem lehet adattáblát összeállítani.

1. Válasszon ki egy szabad cellát a lap szélétől, és írja be a képletet: =A teljes bevétel cellája. Például, ha a bevétel a C14-es cellába van írva (véletlenszerű megjelölés van feltüntetve), akkor ezt kell írni: =S14.
2. A cellától balra lévő oszlopba írjuk az áru mennyiségének százalékos arányát – nem alatta, ez nagyon fontos.
3. Kiválasztjuk azt a cellatartományt, ahol a százalékos oszlop és a teljes jövedelemre mutató hivatkozás található.

4. Az „Adatok” fülön megtaláljuk a „Mi lenne, ha elemzés” elemet, és rákattintunk – a megnyíló menüben válassza az „Adattábla” opciót.

5. Megnyílik egy kis ablak, ahol meg kell adnia egy cellát a kezdetben magas áron eladott könyvek százalékos arányával az „Értékek helyettesítése sorok szerint…” oszlopban. Ez a lépés a teljes bevétel újraszámítása érdekében történik, figyelembe véve a növekvő százalékot.

Az „OK” gomb megnyomása után abban az ablakban, ahol a táblázat összeállításához az adatokat megadtuk, a sorokban megjelennek a számítások eredményei.

Képlet hozzáadása egyváltozós adattáblázathoz

Egy olyan táblázatból, amely segített kiszámítani egy műveletet egyetlen változóval, kifinomult elemzőeszközt készíthet egy további képlet hozzáadásával. Egy már létező képlet mellé kell beírni – ha például sororientált a táblázat, akkor a meglévőtől jobbra lévő cellába írjuk be a kifejezést. Ha az oszloptájolás be van állítva, az új képletet a régi alá írjuk. Ezután kövesse az algoritmust:

1. Válassza ki újra a cellatartományt, de most már tartalmaznia kell az új képletet.
2. Nyissa meg a „mi lenne, ha” elemzési menüt, és válassza az „Adatlap” lehetőséget.
3. A megfelelő mezőbe sorokban vagy oszlopokban új képletet adunk, a lemez tájolásától függően.

Hozzon létre egy adattáblát két változóval

Egy ilyen táblázat eleje kissé eltér – a százalékos értékek felett el kell helyeznie egy linket a teljes bevételre. Ezután a következő lépéseket hajtjuk végre:

1. Írja be az árlehetőségeket egy sorba a jövedelemre mutató hivatkozással – minden árhoz egy cellát.
2. Válasszon ki egy cellatartományt.

3. Nyissa meg az adattábla ablakot, mint egy változós táblázat összeállításakor – az eszköztár „Adatok” fülén keresztül.
4. Helyettesítsen az „Értékek helyettesítése oszlopokkal in…” oszlopban egy cellát, amelynek kezdeti magas ára.
5. Adjon hozzá egy cellát a drága könyvek eladásának kezdeti százalékával az „Értékek helyettesítése sorok szerint…” oszlophoz, és kattintson az „OK” gombra.

Ennek eredményeként az egész táblázat tele van lehetséges bevételi összegekkel, az áruk értékesítésére vonatkozó eltérő feltételekkel.

Az adattáblázatokat tartalmazó munkalapok számításának felgyorsítása

Ha olyan gyors számításokra van szüksége egy adattáblázatban, amely nem váltja ki a teljes munkafüzet újraszámítását, néhány dolgot megtehet a folyamat felgyorsítása érdekében.

1. Nyissa meg a beállítások ablakot, válassza ki a „Képletek” elemet a jobb oldali menüben.
2. A „Számítások a munkafüzetben” részben válassza ki az „Automatikus, kivéve az adattáblázatokat” elemet.

3. Számítsuk ki manuálisan a táblázatban szereplő eredményeket. Ehhez válassza ki a képleteket, és nyomja meg az F billentyűt.

Egyéb eszközök az érzékenységelemzés végrehajtásához

A programban más eszközök is találhatók, amelyek segítik az érzékenységelemzést. Automatizálnak bizonyos műveleteket, amelyeket egyébként manuálisan kellene végrehajtani.

1. A „Paraméterválasztás” funkció akkor megfelelő, ha ismert a kívánt eredmény, és ismerni kell a változó bemeneti értékét az eredmény eléréséhez.
2. A „Megoldás keresése” egy kiegészítő a problémák megoldásához. Határokat kell felállítani és rájuk mutatni, ami után a rendszer megtalálja a választ. A megoldást az értékek változtatásával határozzuk meg.
3. Az érzékenységelemzés a Scenario Manager segítségével végezhető el. Ez az eszköz a mi lenne, ha elemzés menüjében található az Adatok lapon. Több cellában helyettesíti az értékeket – a szám elérheti a 32-t. A diszpécser összehasonlítja ezeket az értékeket, hogy a felhasználónak ne kelljen kézzel módosítania. Példa a szkriptkezelő használatára:

Beruházási projekt érzékenységi elemzése Excelben

A mi lenne a helyzet elemzése különösen hasznos olyan helyzetekben, amikor előrejelzésre van szükség, például beruházásra. Az elemzők ezzel a módszerrel kiderítik, hogyan változik a vállalat részvényeinek értéke egyes tényezők változása következtében.

Befektetési érzékenység elemzési módszer

A „mi lenne, ha” elemzésekor használjon felsorolást – kézi vagy automatikus. Az értékek tartománya ismert, és egyenként behelyettesítik a képletbe. Az eredmény egy értékkészlet. Válassza ki közülük a megfelelő számot. Nézzünk meg négy olyan mutatót, amelyekre érzékenységi elemzést végeznek a pénzügyek területén:

1. Nettó jelenérték – Úgy számítják ki, hogy a befektetés összegét levonják a bevétel összegéből.
2. Belső megtérülési ráta / nyereség – azt jelzi, hogy egy befektetésből mennyi nyereséget kell elérni egy évben.
3. A megtérülési arány az összes nyereség és a kezdeti befektetés aránya.
4. Diszkontált profitindex – a befektetés eredményességét jelzi.

Képlet

A beágyazási érzékenység a következő képlettel számítható ki: Kimeneti paraméter változása %-ban / Bemeneti paraméter változása %-ban.

A kimeneti és bemeneti paraméterek a korábban leírt értékek lehetnek.

1. Ismernie kell az eredményt normál körülmények között.
2. Cseréljük az egyik változót, és figyeljük az eredmény változásait.
3. Mindkét paraméter százalékos változását számítjuk ki a megállapított feltételekhez képest.
4. A kapott százalékokat beillesztjük a képletbe, és meghatározzuk az érzékenységet.

Példa egy beruházási projekt érzékenységelemzésére Excelben

Az elemzési módszertan jobb megértéséhez példára van szükség. Elemezzük a projektet a következő ismert adatokkal:

1. Töltse ki a táblázatot a rajta lévő projekt elemzéséhez.

2. A pénzáramlást az OFFSET függvény segítségével számítjuk ki. A kezdeti szakaszban az áramlás megegyezik a beruházásokkal. Ezután alkalmazzuk a képletet: =HA(ELTOLÁS(szám,1;)=2;SZUM(Beáramlás 1:Kiáramlás 1); SZUM(Beáramlás 1:Kiáramlás 1)+$B$ 5)
   A képlet cellajelölései a táblázat elrendezésétől függően eltérőek lehetnek. A végén hozzáadódik a kezdeti adatokból származó érték – a mentési érték.

3. Meghatározzuk azt az időszakot, ameddig a projekt megtérül. A kezdeti időszakban ezt a képletet használjuk: =SUMMESLY(G7: G17;»<0″). A cellatartomány a cash flow oszlop. A további időszakokra a következő képletet alkalmazzuk: =Kezdő periódus+IF(Első e.folyam>0; Első e.folyam;0). A projekt 4 év alatt a fedezeti ponton van.

4. Létrehozunk egy oszlopot azoknak az időszakoknak a számaihoz, amikor a projekt megtérül.

5. Kiszámoljuk a befektetés megtérülését. Olyan kifejezést kell készíteni, ahol az adott időszak nyereségét elosztjuk a kezdeti befektetéssel.

6. A diszkonttényezőt a következő képlettel határozzuk meg: =1/(1+lemez.%) ^Szám.

7. A jelenértéket szorzással számítjuk ki – a pénzáramlást megszorozzuk a diszkonttényezővel.

8. Számítsuk ki a PI-t (nyereségességi index). Az időbeli jelenértéket el kell osztani a projekt kezdeti befektetésével.

9. Határozzuk meg a belső megtérülési rátát az IRR függvény segítségével: =IRR (pénzforgalom tartománya).

Befektetési érzékenység elemzése adatlap segítségével

A beruházási területen megvalósuló projektek elemzésére más módszerek alkalmasabbak, mint az adattáblázat. Sok felhasználó zavart tapasztal a képlet összeállítása során. Ahhoz, hogy megtudja, hogy egy tényező függ a többi változásától, ki kell választania a megfelelő cellákat a számítások beviteléhez és az adatok kiolvasásához.

Tényező- és diszperzióanalízis Excelben számítási automatizálással

Az érzékenységelemzés másik tipológiája a faktoranalízis és a varianciaanalízis. Az első típus a számok közötti kapcsolatot definiálja, a második az egyik változó függőségét tárja fel a többitől.

ANOVA Excelben

Az ilyen elemzés célja az érték változékonyságának három összetevőre osztása:

1. Változékonyság más értékek befolyásának eredményeként.
2. Változások az arra ható értékek kapcsolata miatt.
3. Véletlenszerű változások.

Végezzünk varianciaanalízist az Excel „Adatelemzés” bővítményén keresztül. Ha nincs engedélyezve, akkor a beállításokban engedélyezhető.

A kiinduló táblának két szabályt kell követnie: minden értékhez tartozik egy oszlop, és a benne lévő adatok növekvő vagy csökkenő sorrendben vannak elrendezve. Ellenőrizni kell, hogy az iskolai végzettség milyen hatással van a konfliktusos viselkedésre.

1. Keresse meg az Adatelemző eszközt az Adatok lapon, és nyissa meg annak ablakát. A listában ki kell választania az egyirányú varianciaanalízist.

2. Töltse ki a párbeszédpanel sorait. A beviteli intervallum az összes cella, a fejlécek és a számok kivételével. Csoportosítás oszlopok szerint. Az eredményeket új lapon jelenítjük meg.

Mivel a sárga cellában lévő érték nagyobb, mint egy, a feltételezés hibásnak tekinthető – nincs kapcsolat az iskolázottság és a konfliktusban lévő magatartás között.

Faktorelemzés Excelben: példa

Elemezzük az adatok kapcsolatát az értékesítés területén – szükséges azonosítani a népszerű és nem népszerű termékeket. Kezdeti információ:

1. Ki kell derítenünk, hogy mely áruk iránt nőtt a legnagyobb kereslet a második hónapban. Új táblázatot készítünk a kereslet növekedésének és csökkenésének meghatározására. A növekedés kiszámítása a következő képlettel történik: =IF((2. igény - 1. igény)>0; 2. igény - 1. igény; 0). Csökkentési képlet: =IF(Növekedés=0; Igény 1- Igény 2;0).

2. Számítsa ki az áruk iránti kereslet növekedését százalékban: =HA(növekedés/2.eredmény =0; csökkenés/2.eredmény; 2. növekedés/eredmény).

3. Készítsünk diagramot az egyértelműség kedvéért – válasszon ki egy cellatartományt, és hozzon létre egy hisztogramot a „Beszúrás” fülön keresztül. A beállításoknál el kell távolítani a kitöltést, ezt a Format Data Series eszközön keresztül lehet megtenni.

Kétirányú varianciaanalízis Excelben

A varianciaanalízist több változóval végezzük. Tekintsük ezt egy példával: meg kell találnia, hogy a különböző hangerősségű hangokra adott reakció milyen gyorsan nyilvánul meg férfiakban és nőkben.

1. Megnyitjuk az „Adatelemzést”, a listában meg kell találnia az ismétlések nélküli kétirányú varianciaanalízist.
2. Beviteli intervallum – adatokat tartalmazó cellák (fejléc nélkül). Az eredményeket egy új lapon jelenítjük meg, és kattintsunk az „OK” gombra.

Az F érték nagyobb, mint az F-kritikus, ami azt jelenti, hogy a padló befolyásolja a hangreakció sebességét.

Következtetés

Ebben a cikkben részletesen tárgyaltuk az Excel-táblázatban végzett érzékenységelemzést, hogy minden felhasználó megértse annak alkalmazási módjait.