Analiza de sensibilitate în Excel (Eșantion de foaie de date)

Procesele din domeniul financiar sunt întotdeauna interconectate – un factor depinde de altul și se modifică odată cu el. Urmăriți aceste modificări și înțelegeți la ce să vă așteptați în viitor, poate folosind funcțiile Excel și metodele foilor de calcul.

Obținerea mai multor rezultate cu un tabel de date

Capacitățile foilor de date sunt elemente ale analizei care se întâmplă – adesea realizate prin Microsoft Excel. Acesta este al doilea nume pentru analiza sensibilității.

Descriere

Un tabel de date este un tip de gamă de celule care poate fi folosit pentru a rezolva probleme prin modificarea valorilor din unele celule. Este creat atunci când este necesar să urmăriți modificările componentelor formulei și să primiți actualizări ale rezultatelor, conform acestor modificări. Să aflăm cum să folosim tabelele de date în cercetare și ce tipuri sunt acestea.

Noțiuni de bază despre tabelele de date

Există două tipuri de tabele de date, care diferă prin numărul de componente. Trebuie să compilați un tabel cu accent pe numărul de valori uXNUMXbuXNUMXbpe care trebuie să le verificați.

Statisticienii folosesc un singur tabel de variabile atunci când există o singură variabilă într-una sau mai multe expresii care le poate schimba rezultatul. De exemplu, este adesea folosit împreună cu funcția PMT. Formula este concepută pentru a calcula suma plății obișnuite și ia în considerare rata dobânzii specificată în acord. În astfel de calcule, variabilele sunt scrise într-o coloană, iar rezultatele calculelor în alta. Un exemplu de plăcuță cu date cu 1 variabilă:

Apoi, luați în considerare plăcile cu 2 variabile. Ele sunt utilizate în cazurile în care doi factori influențează modificarea oricărui indicator. Cele două variabile pot ajunge într-un alt tabel asociat împrumutului, care poate fi folosit pentru a determina perioada optimă de rambursare și valoarea plății lunare. În acest calcul, trebuie să utilizați și funcția PMT. Un exemplu de tabel cu 2 variabile:

Crearea unui tabel de date cu o variabilă

Luați în considerare metoda de analiză folosind exemplul unei mici librării cu doar 100 de cărți în stoc. Unele dintre ele pot fi vândute mai scumpe (50 USD), restul îi va costa pe cumpărători mai puțin (20 USD). Se calculează venitul total din vânzarea tuturor bunurilor – proprietarul a decis că va vinde 60% din cărți la un preț ridicat. Trebuie să aflați cum vor crește veniturile dacă creșteți prețul unui volum mai mare de mărfuri – 70% și așa mai departe.

Fii atent! Venitul total trebuie calculat folosind o formulă, altfel nu va fi posibilă compilarea unui tabel de date.

1. Selectați o celulă liberă departe de marginea foii și scrieți formula în ea: =Celula veniturilor totale. De exemplu, dacă venitul este scris în celula C14 (este indicată desemnarea aleatorie), trebuie să scrieți acest lucru: =S14.
2. Scriem procentul din volumul de mărfuri în coloana din stânga acestei celule – nu mai jos, acest lucru este foarte important.
3. Selectăm intervalul de celule în care se află coloana procentuală și legătura cu venitul total.

4. Găsim în fila „Date” articolul „What if analysis” și faceți clic pe el – în meniul care se deschide, selectați opțiunea „Data table”.

5. Se va deschide o fereastră mică în care trebuie să specificați o celulă cu procentul de cărți vândute inițial la un preț ridicat în coloana „Înlocuiește valori cu rândurile din …”. Acest pas se face in vederea recalcularii veniturilor totale tinand cont de procentul in crestere.

După ce faceți clic pe butonul „OK” din fereastra în care au fost introduse datele pentru compilarea tabelului, rezultatele calculelor vor apărea în rânduri.

Adăugarea unei formule la un singur tabel de date variabile

Dintr-un tabel care a ajutat la calcularea unei acțiuni cu o singură variabilă, puteți crea un instrument de analiză sofisticat adăugând o formulă suplimentară. Trebuie introdusă lângă o formulă deja existentă – de exemplu, dacă tabelul este orientat pe rând, introducem expresia în celula din dreapta celei existente. Când este setată orientarea coloanei, scriem noua formulă sub cea veche. Apoi, urmați algoritmul:

1. Selectați din nou intervalul de celule, dar acum ar trebui să includă noua formulă.
2. Deschideți meniul de analiză „Dacă” și selectați „Foaie de date”.
3. Adăugăm o nouă formulă câmpului corespunzător în rânduri sau coloane, în funcție de orientarea plăcii.

Creați un tabel de date cu două variabile

Începutul unui astfel de tabel este ușor diferit – trebuie să puneți un link către veniturile totale peste valorile procentuale. În continuare, efectuăm acești pași:

1. Scrieți opțiunile de preț într-o singură linie cu un link către venit – o celulă pentru fiecare preț.
2. Selectați un interval de celule.

3. Deschideți fereastra tabelului de date, ca atunci când compilați un tabel cu o variabilă - prin fila „Date” din bara de instrumente.
4. Înlocuiți în coloana „Înlocuiți valorile cu coloane în …” o celulă cu un preț inițial ridicat.
5. Adăugați o celulă cu procentul inițial de vânzări de cărți scumpe la coloana „Înlocuiți valorile cu rândurile din …” și faceți clic pe „OK”.

Ca urmare, întregul tabel este umplut cu sume de venituri posibile cu diferite condiții pentru vânzarea mărfurilor.

Accelerați calculele pentru foile de lucru care conțin tabele de date

Dacă aveți nevoie de calcule rapide într-un tabel de date care nu declanșează o recalculare a întregului registru de lucru, există câteva lucruri pe care le puteți face pentru a accelera procesul.

1. Deschideți fereastra de opțiuni, selectați elementul „Formule” din meniul din dreapta.
2. Selectați elementul „Automat, cu excepția tabelelor de date” din secțiunea „Calcule în registrul de lucru”.

3. Să recalculăm manual rezultatele din tabel. Pentru a face acest lucru, selectați formulele și apăsați tasta F.

Alte instrumente pentru efectuarea analizei de sensibilitate

Există și alte instrumente în program care vă ajută să efectuați o analiză de sensibilitate. Ei automatizează unele acțiuni care altfel ar trebui făcute manual.

1. Funcția „Selectarea parametrilor” este potrivită dacă rezultatul dorit este cunoscut și trebuie să cunoașteți valoarea de intrare a variabilei pentru a obține un astfel de rezultat.
2. „Căutați o soluție” este un supliment pentru rezolvarea problemelor. Este necesar să stabiliți limite și să le indicați, după care sistemul va găsi răspunsul. Soluția se determină prin modificarea valorilor.
3. Analiza de sensibilitate se poate face folosind Scenario Manager. Acest instrument se găsește în meniul de analiză ce se întâmplă în fila Date. Înlocuiește valori în mai multe celule – numărul poate ajunge la 32. Dispecerul compară aceste valori, astfel încât utilizatorul să nu fie nevoit să le schimbe manual. Un exemplu de utilizare a managerului de script:

Analiza de sensibilitate a unui proiect de investiții în Excel

Analiza ce se întâmplă dacă este utilă în special în situațiile în care este necesară prognoza, cum ar fi investițiile. Analiștii folosesc această metodă pentru a afla cum se va schimba valoarea acțiunilor unei companii ca urmare a modificărilor unor factori.

Metoda de analiză a sensibilității investițiilor

Când analizați „ce-ar fi dacă” utilizați enumerarea – manuală sau automată. Intervalul de valori este cunoscut și ele sunt înlocuite în formulă unul câte unul. Rezultatul este un set de valori. Alegeți numărul potrivit dintre ele. Să luăm în considerare patru indicatori pentru care se efectuează analiza de sensibilitate în domeniul finanțelor:

1. Valoarea actuală netă – Calculată prin scăderea sumei investiției din valoarea venitului.
2. Rata internă de rentabilitate/profit – indică cât de mult profit este necesar să fie primit dintr-o investiție într-un an.
3. Raportul de amortizare este raportul dintre toate profiturile și investiția inițială.
4. Indicele de profit redus – indică eficacitatea investiției.

Formulă

Sensibilitatea de încorporare poate fi calculată folosind această formulă: Modificare a parametrului de ieșire în % / Modificare a parametrului de intrare în %.

Parametrii de ieșire și de intrare pot fi valorile descrise mai devreme.

1. Trebuie să cunoașteți rezultatul în condiții standard.
2. Înlocuim una dintre variabile și monitorizăm modificările rezultatului.
3. Calculăm modificarea procentuală a ambilor parametri în raport cu condițiile stabilite.
4. Inserăm procentele obținute în formulă și determinăm sensibilitatea.

Un exemplu de analiză de sensibilitate a unui proiect de investiții în Excel

Pentru o mai bună înțelegere a metodologiei de analiză, este necesar un exemplu. Să analizăm proiectul cu următoarele date cunoscute:

1. Completați tabelul pentru a analiza proiectul pe acesta.

2. Calculăm fluxul de numerar folosind funcția OFFSET. La etapa inițială, fluxul este egal cu investițiile. În continuare, aplicăm formula: =IF(OFFSET(Număr,1;)=2;SUM(Intrare 1:Ieșire 1); SUM(Intrare 1:Ieșire 1)+$B$ 5)
   Denumirile celulelor din formulă pot fi diferite, în funcție de aspectul tabelului. La final, se adaugă valoarea din datele inițiale – valoarea de salvare.

3. Stabilim perioada pentru care proiectul va fi rentabil. Pentru perioada inițială, folosim această formulă: =SUMMESLY(G7: G17;»<0″). Intervalul de celule este coloana fluxului de numerar. Pentru perioade ulterioare, aplicăm această formulă: =Perioada inițială+IF(Primul e.stream>0; Primul e.stream;0). Proiectul este la pragul de rentabilitate în 4 ani.

4. Creăm o coloană pentru numerele acelor perioade în care proiectul dă roade.

5. Calculăm rentabilitatea investiției. Este necesar să se facă o expresie în care profitul într-o anumită perioadă de timp este împărțit la investiția inițială.

6. Determinăm factorul de reducere folosind această formulă: =1/(1+disc.%) ^Număr.

7. Calculăm valoarea actuală folosind înmulțirea – fluxul de numerar este înmulțit cu factorul de actualizare.

8. Să calculăm PI (indicele de rentabilitate). Valoarea actuală în timp este împărțită la investiția la începutul proiectului.

9. Să definim rata internă de rentabilitate folosind funcția IRR: =IRR (Intervalul fluxului de numerar).

Analiza sensibilității investițiilor folosind fișa de date

Pentru analiza proiectelor din domeniul investițiilor, alte metode sunt mai potrivite decât un tabel de date. Mulți utilizatori se confruntă cu confuzie atunci când compilează o formulă. Pentru a afla dependența unui factor de modificările altora, trebuie să selectați celulele corecte pentru introducerea calculelor și pentru citirea datelor.

Analiza factorială și de dispersie în Excel cu automatizare de calcul

O altă tipologie a analizei de sensibilitate este analiza factorială și analiza varianței. Primul tip definește relația dintre numere, al doilea dezvăluie dependența unei variabile de altele.

ANOVA în Excel

Scopul unei astfel de analize este de a împărți variabilitatea valorii în trei componente:

1. Variabilitatea ca urmare a influenței altor valori.
2. Modificări datorate relației dintre valorile care îl afectează.
3. Schimbări aleatorii.

Să efectuăm o analiză a varianței prin programul de completare Excel „Analiza datelor”. Dacă nu este activat, poate fi activat în setări.

Tabelul inițial trebuie să respecte două reguli: există o coloană pentru fiecare valoare, iar datele din acesta sunt aranjate în ordine crescătoare sau descrescătoare. Este necesar să se verifice influența nivelului de educație asupra comportamentului în conflict.

1. Găsiți instrumentul de analiză a datelor în fila Date și deschideți fereastra acestuia. În listă, trebuie să selectați analiza unidirecțională a varianței.

2. Completați rândurile casetei de dialog. Intervalul de intrare este pentru toate celulele, cu excepția antetelor și numerelor. Grupați după coloane. Afișăm rezultatele pe o foaie nouă.

Deoarece valoarea din celula galbenă este mai mare decât unu, ipoteza poate fi considerată incorectă – nu există nicio relație între educație și comportament în conflict.

Analiza factorială în Excel: un exemplu

Să analizăm relația datelor din domeniul vânzărilor – este necesar să identificăm produsele populare și nepopulare. Informații inițiale:

1. Trebuie să aflăm pentru care cererea de bunuri a crescut cel mai mult în a doua lună. Alcătuim un nou tabel pentru a determina creșterea și scăderea cererii. Creșterea se calculează folosind această formulă: =IF((Cerere 2-Cerere 1)>0; Cerere 2-Cerere 1;0). Formula de scădere: =IF(Creștere=0; Cererea 1- Cererea 2;0).

2. Calculați creșterea cererii de bunuri ca procent: =IF(Creștere/Rezultat 2 = 0; Scădere/Rezultat 2; Creștere/Rezultat 2).

3. Să facem o diagramă pentru claritate - selectați o serie de celule și creați o histogramă prin fila „Inserare”. În setări, trebuie să eliminați umplerea, acest lucru se poate face prin instrumentul Format Data Series.

Analiza bidirecțională a varianței în Excel

Analiza varianței se realizează cu mai multe variabile. Luați în considerare acest lucru cu un exemplu: trebuie să aflați cât de repede se manifestă reacția la un sunet de volum diferit la bărbați și femei.

1. Deschidem „Analiza datelor”, în listă trebuie să găsiți o analiză bidirecțională a varianței fără repetări.
2. Interval de intrare – celule care conțin date (fără antet). Afișăm rezultatele pe o foaie nouă și facem clic pe „OK”.

Valoarea F este mai mare decât F-critică, ceea ce înseamnă că podeaua afectează viteza de reacție la sunet.

Concluzie

În acest articol, analiza sensibilității într-o foaie de calcul Excel a fost discutată în detaliu, astfel încât fiecare utilizator să poată înțelege metodele de aplicare a acesteia.