Jautrumo analizė programoje „Excel“ (pavyzdinis duomenų lapas)

Procesai finansų srityje visada yra tarpusavyje susiję – vienas veiksnys priklauso nuo kito ir keičiasi kartu su juo. Stebėkite šiuos pakeitimus ir supraskite, ko tikėtis ateityje, galbūt naudodami „Excel“ funkcijas ir skaičiuoklės metodus.

Kelių rezultatų gavimas naudojant duomenų lentelę

Duomenų lapo galimybės yra „kas būtų, jei“ analizės elementai, dažnai atliekami naudojant „Microsoft Excel“. Tai antrasis jautrumo analizės pavadinimas.

Apžvalga

Duomenų lentelė yra langelių diapazono tipas, kurį galima naudoti problemoms spręsti pakeičiant kai kurių langelių reikšmes. Jis sukuriamas, kai reikia sekti formulės komponentų pokyčius ir gauti rezultatų atnaujinimus pagal šiuos pakeitimus. Išsiaiškinkime, kaip naudoti duomenų lenteles atliekant tyrimus ir kokių tipų jos yra.

Duomenų lentelių pagrindai

Yra dviejų tipų duomenų lentelės, jos skiriasi komponentų skaičiumi. Turite sudaryti lentelę, sutelkdami dėmesį į uXNUMXbuXNUMXb reikšmių skaičių, kurią turite patikrinti.

Statistikai naudoja vieną kintamųjų lentelę, kai vienoje ar keliose išraiškose yra tik vienas kintamasis, galintis pakeisti jų rezultatą. Pavyzdžiui, jis dažnai naudojamas kartu su PMT funkcija. Formulė skirta skaičiuoti eilinės įmokos sumą ir atsižvelgiama į sutartyje nurodytą palūkanų normą. Tokiuose skaičiavimuose kintamieji rašomi viename stulpelyje, o skaičiavimų rezultatai – kitame. Duomenų lentelės su 1 kintamuoju pavyzdys:

Toliau apsvarstykite plokštes su 2 kintamaisiais. Jie naudojami tais atvejais, kai kurio nors rodiklio pokyčiui įtakos turi du veiksniai. Du kintamieji gali atsidurti kitoje su paskola susijusioje lentelėje, pagal kurią galima nustatyti optimalų grąžinimo laikotarpį ir mėnesinės įmokos dydį. Šiame skaičiavime taip pat turite naudoti PMT funkciją. Lentelės su 2 kintamaisiais pavyzdys:

Duomenų lentelės su vienu kintamuoju kūrimas

Apsvarstykite analizės metodą, naudodami mažo knygyno, kuriame yra tik 100 knygų, pavyzdį. Kai kuriuos iš jų galima parduoti brangiau (50 USD), likusieji pirkėjams kainuos pigiau (20 USD). Skaičiuojamos bendros pajamos iš visų prekių pardavimo – savininkas nusprendė brangiai parduoti 60% knygų. Reikia pasidomėti, kaip padidės pajamos, jei padidinsite didesnės apimties prekių kainą – 70% ir pan.

Atkreipkite dėmesį! Bendros pajamos turi būti skaičiuojamos pagal formulę, kitaip nebus galima sudaryti duomenų lentelės.

1. Pasirinkite laisvą langelį nuo lapo krašto ir įrašykite formulę: =Visų pajamų langelis. Pavyzdžiui, jei pajamos rašomos langelyje C14 (nurodytas atsitiktinis žymėjimas), turite parašyti tai: =S14.
2. Prekių kiekio procentą rašome stulpelyje, esančiame šio langelio kairėje – ne žemiau, tai labai svarbu.
3. Mes pasirenkame langelių diapazoną, kuriame yra procentų stulpelis ir nuoroda į visas pajamas.

4. Skirtuke „Duomenys“ randame elementą „Kas būtų, jei analizė“ ir spustelėkite jį – atsidariusiame meniu pasirinkite parinktį „Duomenų lentelė“.

5. Atsidarys nedidelis langas, kuriame stulpelyje „Pakeisti reikšmes pagal eilutes …“ reikia nurodyti langelį su iš pradžių brangiai parduotų knygų procentine dalimi. Šis veiksmas atliekamas siekiant perskaičiuoti visas pajamas, atsižvelgiant į didėjantį procentą.

Lange, kuriame buvo įvesti duomenys lentelės sudarymui, paspaudus mygtuką „Gerai“, skaičiavimų rezultatai bus rodomi eilutėse.

Formulės įtraukimas į vieno kintamojo duomenų lentelę

Iš lentelės, kuri padėjo apskaičiuoti veiksmą naudojant tik vieną kintamąjį, galite sukurti sudėtingą analizės įrankį pridėdami papildomą formulę. Ją reikia įvesti šalia jau esančios formulės – pavyzdžiui, jei lentelė orientuota į eilutę, išraišką įvedame langelyje, esančiame dešinėje nuo esamos. Nustačius stulpelio orientaciją, naują formulę įrašome po senąja. Tada vykdykite algoritmą:

1. Dar kartą pasirinkite langelių diapazoną, bet dabar jame turėtų būti nauja formulė.
2. Atidarykite analizės meniu „kas būtų, jei“ ir pasirinkite „Duomenų lapas“.
3. Į atitinkamą lauką eilutėmis arba stulpeliais pridedame naują formulę, priklausomai nuo plokštės orientacijos.

Sukurkite duomenų lentelę su dviem kintamaisiais

Tokios lentelės pradžia yra šiek tiek kitokia – virš procentinių reikšmių reikia įdėti nuorodą į visas pajamas. Toliau atliekame šiuos veiksmus:

1. Kainų variantus parašykite vienoje eilutėje su nuoroda į pajamas – po vieną langelį kiekvienai kainai.
2. Pasirinkite langelių diapazoną.

3. Atidarykite duomenų lentelės langą, kaip ir sudarant lentelę su vienu kintamuoju – per įrankių juostos skirtuką „Duomenys“.
4. Stulpelyje „Pakeisti reikšmes stulpeliais …“ pakeiskite langelį su pradine aukšta kaina.
5. Pridėkite langelį su pradine brangių knygų pardavimo procentine dalimi prie stulpelio „Pakeisti reikšmes eilutėmis …“ ir spustelėkite „Gerai“.

Dėl to visa lentelė užpildoma galimų pajamų sumomis su skirtingomis prekių pardavimo sąlygomis.

Paspartinkite darbalapių, kuriuose yra duomenų lentelės, skaičiavimus

Jei reikia greitų duomenų lentelės skaičiavimų, kurie nesukelia visos darbaknygės perskaičiavimo, yra keletas dalykų, kuriuos galite padaryti, kad pagreitintumėte procesą.

1. Atidarykite parinkčių langą, dešinėje esančiame meniu pasirinkite elementą „Formulės“.
2. Skiltyje „Skaičiavimai darbaknygėje“ pasirinkite elementą „Automatinis, išskyrus duomenų lenteles“.

3. Rezultatus lentelėje perskaičiuokime rankiniu būdu. Norėdami tai padaryti, pasirinkite formules ir paspauskite klavišą F.

Kiti jautrumo analizės įrankiai

Programoje yra ir kitų įrankių, padedančių atlikti jautrumo analizę. Jie automatizuoja kai kuriuos veiksmus, kuriuos kitu atveju reikėtų atlikti rankiniu būdu.

1. Funkcija „Parametro pasirinkimas“ tinka, jei žinomas norimas rezultatas, o norint gauti tokį rezultatą reikia žinoti kintamojo įvesties reikšmę.
2. „Ieškoti sprendimo“ yra priedas, skirtas problemoms spręsti. Būtina nusistatyti ribas ir į jas nurodyti, po kurių sistema ras atsakymą. Sprendimas nustatomas keičiant reikšmes.
3. Jautrumo analizę galima atlikti naudojant scenarijų tvarkyklę. Šį įrankį rasite skirtuke Duomenys esančiame analizės meniu „kas būtų, jei“. Jis pakeičia reikšmes keliose langeliuose – skaičius gali siekti 32. Dispečeris lygina šias reikšmes, kad vartotojui nereikėtų jų keisti rankiniu būdu. Scenarijų tvarkyklės naudojimo pavyzdys:

Investicinio projekto jautrumo analizė Excel programoje

Ką daryti, jei analizė yra ypač naudinga situacijose, kai reikia prognozuoti, pavyzdžiui, investuoti. Analitikai šiuo metodu išsiaiškina, kaip pasikeitus kai kuriems veiksniams pasikeis įmonės akcijų vertė.

Investicijų jautrumo analizės metodas

Analizuodami „kas būtų, jei“ naudokite išvardinimą – rankinį arba automatinį. Reikšmių diapazonas yra žinomas, ir jie pakeičiami į formulę po vieną. Rezultatas yra vertybių rinkinys. Pasirinkite iš jų tinkamą skaičių. Panagrinėkime keturis rodiklius, kuriems finansų srityje atliekama jautrumo analizė:

1. Grynoji dabartinė vertė – apskaičiuojama iš pajamų sumos atėmus investicijų sumą.
2. Vidinė grąžos / pelno norma – nurodo, kiek pelno reikia gauti iš investicijos per metus.
3. Atsipirkimo koeficientas yra viso pelno ir pradinių investicijų santykis.
4. Diskontuoto pelno indeksas – parodo investicijos efektyvumą.

Formulė

Įterpimo jautrumą galima apskaičiuoti naudojant šią formulę: Išvesties parametro pokytis % / Įvesties parametro pokytis %.

Išvesties ir įvesties parametrai gali būti anksčiau aprašyti parametrai.

1. Jūs turite žinoti rezultatą standartinėmis sąlygomis.
2. Mes pakeičiame vieną iš kintamųjų ir stebime rezultato pokyčius.
3. Apskaičiuojame abiejų parametrų procentinį pokytį, palyginti su nustatytomis sąlygomis.
4. Gautus procentus įterpiame į formulę ir nustatome jautrumą.

Investicinio projekto jautrumo analizės Excel programoje pavyzdys

Norint geriau suprasti analizės metodiką, reikalingas pavyzdys. Išanalizuokime projektą su šiais žinomais duomenimis:

1. Norėdami išanalizuoti projektą, užpildykite lentelę.

2. Pinigų srautą apskaičiuojame naudodami funkciją OFFSET. Pradiniame etape srautas lygus investicijoms. Toliau taikome formulę: =IF(OFFSET(Skaičius,1;)=2;SUM(1 įplaukimas: 1 ištekėjimas); SUM(1 įplaukimas: 1 ištekėjimas)+$B$ 5)
   Formulės langelių pavadinimai gali skirtis, atsižvelgiant į lentelės išdėstymą. Pabaigoje pridedama pradinių duomenų reikšmė – gelbėjimo vertė.

3. Mes nustatome laikotarpį, per kurį projektas atsipirks. Pradiniam laikotarpiui naudojame šią formulę: =SUMMESLY(G7: G17;»<0″). Ląstelių diapazonas yra pinigų srautų stulpelis. Tolesniems laikotarpiams taikome šią formulę: =Pradinis periodas+IF(Pirmasis e.srautas>0; Pirmas e.srautas;0). Projekto lūžio taškas pasiekė 4 metus.

4. Sudarome stulpelį tų laikotarpių numeriams, kada projektas pasiteisina.

5. Skaičiuojame investicijų grąžą. Būtina padaryti išraišką, kur pelnas per tam tikrą laikotarpį yra padalintas iš pradinės investicijos.

6. Nuolaidos koeficientą nustatome naudodami šią formulę: =1/(1+Diskas.%) ^Skaičius.

7. Dabartinę vertę apskaičiuojame daugybos būdu – pinigų srautas dauginamas iš diskonto koeficiento.

8. Apskaičiuokime PI (pelningumo indeksą). Dabartinė vertė laikui bėgant yra padalinta iš investicijų projekto pradžioje.

9. Apibrėžkime vidinę grąžos normą naudodami IRR funkciją: =IRR (pinigų srautų diapazonas).

Investicijų jautrumo analizė naudojant duomenų lapą

Investicijų srities projektų analizei geriau tinka kiti metodai nei duomenų lentelė. Daugelis vartotojų susiduria su painiava sudarydami formulę. Norėdami sužinoti vieno veiksnio priklausomybę nuo kitų pokyčių, turite pasirinkti teisingus langelius skaičiavimams įvesti ir duomenims nuskaityti.

Faktorių ir dispersijos analizė Excel programoje su skaičiavimo automatika

Kita jautrumo analizės tipologija yra faktorinė analizė ir dispersinė analizė. Pirmasis tipas apibrėžia ryšį tarp skaičių, antrasis atskleidžia vieno kintamojo priklausomybę nuo kitų.

ANOVA programoje Excel

Tokios analizės tikslas – padalyti vertės kintamumą į tris komponentus:

1. Kintamumas dėl kitų vertybių įtakos.
2. Pokyčiai dėl jį įtakojančių vertybių santykio.
3. Atsitiktiniai pokyčiai.

Dispersijos analizę atlikime naudodami „Excel“ priedą „Duomenų analizė“. Jei jis neįjungtas, jį galima įjungti nustatymuose.

Pradinėje lentelėje turi būti laikomasi dviejų taisyklių: kiekvienai reikšmei yra po vieną stulpelį, o duomenys joje išdėstyti didėjančia arba mažėjančia tvarka. Būtina patikrinti išsilavinimo lygio įtaką elgesiui konflikte.

1. Skirtuke Duomenys suraskite duomenų analizės įrankį ir atidarykite jo langą. Sąraše turite pasirinkti vienpusę dispersijos analizę.

2. Užpildykite dialogo lango eilutes. Įvesties intervalas yra visi langeliai, išskyrus antraštes ir skaičius. Grupuoti pagal stulpelius. Rezultatus rodome naujame lape.

Kadangi reikšmė geltonoje langelyje yra didesnė už vienetą, prielaida gali būti laikoma neteisinga – nėra ryšio tarp išsilavinimo ir elgesio konflikte.

Faktorių analizė programoje „Excel“: pavyzdys

Išanalizuokime duomenų ryšį pardavimo srityje – būtina identifikuoti populiarias ir nepopuliarias prekes. Pradinė informacija:

1. Turime išsiaiškinti, kurių prekių paklausa antrąjį mėnesį išaugo labiausiai. Paklausos augimui ir mažėjimui nustatyti sudarome naują lentelę. Augimas apskaičiuojamas pagal šią formulę: =IF((2 paklausa - 1 paklausa)>0; 2 paklausa - 1 paklausa; 0). Sumažinimo formulė: =IF(augimas=0; Paklausa 1- Paklausa 2;0).

2. Apskaičiuokite prekių paklausos augimą procentais: =IF(augimas / 2 rezultatas =0; sumažėjimas / 2 rezultatas; augimas / 2 rezultatas).

3. Aiškumo dėlei sukurkime diagramą – pažymėkite langelių diapazoną ir sukurkite histogramą skirtuke „Įterpti“. Nustatymuose reikia pašalinti užpildymą, tai galima padaryti naudojant įrankį Format Data Series.

Dvipusė dispersijos analizė programoje „Excel“.

Dispersijos analizė atliekama naudojant kelis kintamuosius. Apsvarstykite tai pavyzdžiu: turite išsiaiškinti, kaip greitai vyrams ir moterims pasireiškia reakcija į skirtingo garsumo garsą.

1. Atsidarome „Duomenų analizė“, sąraše reikia rasti dvipusę dispersijos analizę be pasikartojimų.
2. Įvesties intervalas – langeliai, kuriuose yra duomenų (be antraštės). Mes parodome rezultatus naujame lape ir spustelėkite „Gerai“.

F vertė yra didesnė už F-kritinę vertę, o tai reiškia, kad grindys turi įtakos reakcijos į garsą greičiui.

Išvada

Šiame straipsnyje buvo išsamiai aptarta jautrumo analizė Excel skaičiuoklėje, kad kiekvienas vartotojas galėtų suprasti jos taikymo būdus.