Анализа осетљивости у Екцел-у (пример листа са подацима)

Процеси у области финансија су увек међусобно повезани – један фактор зависи од другог и мења се са њим. Пратите ове промене и схватите шта можете очекивати у будућности, можда користећи Екцел функције и методе прорачунских табела.

Добијање више резултата помоћу табеле са подацима

Могућности листа са подацима су елементи анализе шта-ако—често се обавља преко Мицрософт Екцел-а. Ово је други назив за анализу осетљивости.

преглед

Табела података је тип опсега ћелија који се може користити за решавање проблема променом вредности у неким ћелијама. Креира се када је потребно пратити промене у компонентама формуле и примати ажурирања резултата, у складу са тим променама. Хајде да сазнамо како да користимо табеле података у истраживању и које су врсте.

Основе о табелама података

Постоје две врсте табела података, које се разликују по броју компоненти. Морате да саставите табелу са фокусом на број вредности уКСНУМКСбуКСНУМКСб које треба да проверите са њом.

Статистичари користе једну табелу променљивих када постоји само једна променљива у једном или више израза која може да промени њихов резултат. На пример, често се користи у комбинацији са ПМТ функцијом. Формула је дизајнирана да израчуна износ редовне уплате и узима у обзир каматну стопу која је наведена у уговору. У таквим прорачунима варијабле се уписују у једну колону, а резултати прорачуна у другу. Пример таблице са подацима са 1 променљивом:

Затим размотрите плоче са 2 варијабле. Користе се у случајевима када два фактора утичу на промену неког индикатора. Две варијабле могу завршити у другој табели повезаној са кредитом, која се може користити за одређивање оптималног периода отплате и износа месечне уплате. У овом прорачуну такође треба да користите функцију ПМТ. Пример табеле са 2 променљиве:

Креирање табеле података са једном променљивом

Размотрите метод анализе на примеру мале књижаре са само 100 књига на залихама. Неки од њих се могу продати скупље (50 долара), остали ће купце коштати мање (20 долара). Укупан приход од продаје све робе је израчунат – власник је одлучио да 60% књига прода по високој цени. Морате сазнати како ће се повећати приход ако повећате цену веће количине робе – 70% и тако даље.

Обратити пажњу! Укупан приход се мора израчунати помоћу формуле, иначе неће бити могуће саставити табелу са подацима.

1. Изаберите слободну ћелију даље од ивице листа и упишите формулу у њу: = Ћелија укупног прихода. На пример, ако је приход уписан у ћелију Ц14 (назначена је насумична ознака), потребно је да напишете ово: =С14.
2. Проценат запремине робе уписујемо у колону лево од ове ћелије – не испод, ово је веома важно.
3. Бирамо опсег ћелија у којима се налази процентуална колона и веза до укупног прихода.

4. На картици „Подаци“ налазимо ставку „Шта ако анализа“ и кликнемо на њу – у менију који се отвори изаберите опцију „Табела података“.

5. Отвориће се мали прозор где треба да наведете ћелију са процентом књига које су првобитно продате по високој цени у колони „Замени вредности по редовима у…“. Овај корак се ради како би се прерачунао укупан приход узимајући у обзир повећање процента.

Након клика на дугме „ОК“ у прозору у који су унети подаци за састављање табеле, резултати прорачуна ће се појавити у редовима.

Додавање формуле у табелу података са једном променљивом

Из табеле која је помогла да се израчуна радња са само једном променљивом, можете направити софистицирани алат за анализу додавањем додатне формуле. Мора се унети поред већ постојеће формуле – на пример, ако је табела оријентисана на ред, уносимо израз у ћелију десно од постојеће. Када је постављена оријентација колоне, нову формулу уписујемо испод старе. Затим следите алгоритам:

1. Поново изаберите опсег ћелија, али сада би требало да садржи нову формулу.
2. Отворите мени за анализу „шта ако“ и изаберите „Табелу са подацима“.
3. Додамо нову формулу у одговарајуће поље у редовима или колонама, у зависности од оријентације плоче.

Направите табелу података са две променљиве

Почетак такве табеле је мало другачији – потребно је да ставите везу до укупног прихода изнад процентуалних вредности. Затим изводимо ове кораке:

1. Напишите опције цена у једном реду са везом до прихода – једна ћелија за сваку цену.
2. Изаберите опсег ћелија.

3. Отворите прозор табеле података, као када састављате табелу са једном променљивом – преко картице „Подаци“ на траци са алаткама.
4. Замените у колони „Замени вредности колонама у…” ћелију са почетном високом ценом.
5. Додајте ћелију са почетним процентом продаје скупих књига у колону „Замени вредности по редовима у…“ и кликните на „ОК“.

Као резултат, цела табела је испуњена износима могућег прихода са различитим условима за продају робе.

Убрзајте прорачуне за радне листове који садрже табеле са подацима

Ако су вам потребни брзи прорачуни у табели података који не покрећу поновно израчунавање целе радне свеске, постоји неколико ствари које можете да урадите да бисте убрзали процес.

1. Отворите прозор са опцијама, изаберите ставку „Формуле“ у менију са десне стране.
2. Изаберите ставку „Аутоматски, осим за табеле са подацима“ у одељку „Прорачуни у радној свесци“.

3. Хајде да ручно поново израчунамо резултате у табели. Да бисте то урадили, изаберите формуле и притисните тастер Ф.

Други алати за извођење анализе осетљивости

Постоје и други алати у програму који ће вам помоћи да извршите анализу осетљивости. Они аутоматизују неке радње које би иначе морале да се раде ручно.

1. Функција „Избор параметара“ је погодна ако је жељени резултат познат, а потребно је да знате улазну вредност променљиве да бисте добили такав резултат.
2. „Тражи решење“ је додатак за решавање проблема. Неопходно је поставити границе и указати на њих, након чега ће систем пронаћи одговор. Решење се одређује променом вредности.
3. Анализа осетљивости се може урадити помоћу Сценарио Манагер-а. Овај алат се налази у менију за анализу шта ако на картици Подаци. Замењује вредности у неколико ћелија – број може да достигне 32. Диспечер упоређује ове вредности тако да корисник не мора да их мења ручно. Пример коришћења менаџера скрипти:

Анализа осетљивости инвестиционог пројекта у Екцел-у

Анализа „шта ако“ је посебно корисна у ситуацијама када је потребно предвиђање, као што је инвестиција. Аналитичари користе овај метод да открију како ће се променити вредност акција компаније као резултат промена неких фактора.

Метода анализе осетљивости улагања

Када анализирате „шта ако“ користите набрајање – ручно или аутоматско. Опсег вредности је познат и оне се замењују у формулу једна по једна. Резултат је скуп вредности. Од њих изаберите одговарајући број. Хајде да размотримо четири индикатора за које се врши анализа осетљивости у области финансија:

1. Нето садашња вредност – Израчунава се одузимањем износа инвестиције од износа прихода.
2. Интерна стопа приноса/профита – показује колико је профита потребно да се добије од инвестиције у години.
3. Коефицијент исплативости је однос свих добити и почетног улагања.
4. Индекс дисконтираног профита – указује на ефективност инвестиције.

Формула

Осетљивост уградње се може израчунати помоћу ове формуле: Промена излазног параметра у % / Промена улазног параметра у %.

Излазни и улазни параметри могу бити вредности описане раније.

1. Морате знати резултат под стандардним условима.
2. Заменимо једну од варијабли и пратимо промене у резултату.
3. Израчунавамо процентуалне промене оба параметра у односу на утврђене услове.
4. Добијене проценте убацујемо у формулу и одређујемо осетљивост.

Пример анализе осетљивости инвестиционог пројекта у Екцел-у

За боље разумевање методологије анализе потребан је пример. Хајде да анализирамо пројекат са следећим познатим подацима:

1. Попуните табелу да бисте анализирали пројекат на њој.

2. Израчунавамо новчани ток помоћу функције ОФФСЕТ. У почетној фази, ток је једнак инвестицијама. Затим примењујемо формулу: =ИФ(ОФФСЕТ(Број,1;)=2;СУМ(Прилив 1:Одлив 1);СУМ(Прилив 1:Одлив 1)+$B$ КСНУМКС)
   Ознаке ћелија у формули могу бити различите, у зависности од изгледа табеле. На крају се додаје вредност из почетних података – спасоносна вредност.

3. Одређујемо период за који ће се пројекат исплатити. За почетни период користимо ову формулу: =ВЕЛИКО(G7: Г17;»<0″). Опсег ћелија је колона новчаног тока. За даље периоде примењујемо ову формулу: =Почетни период+ИФ(Први е.ток>0; Први е.ток;0). Пројекат је на прагу рентабилности за 4 године.

4. Правимо колону за бројеве оних периода када се пројекат исплати.

5. Израчунавамо повраћај инвестиције. Потребно је направити израз где се добит у одређеном временском периоду дели са почетним улагањем.

6. Одређујемо фактор попуста користећи ову формулу: =1/(1+Диск.%) ^Број.

7. Садашњу вредност израчунавамо коришћењем множења – новчани ток се множи дисконтним фактором.

8. Хајде да израчунамо ПИ (индекс профитабилности). Садашња вредност током времена се дели са инвестицијом на почетку пројекта.

9. Хајде да дефинишемо интерну стопу приноса користећи ИРР функцију: =ИРР(Распон тока готовине).

Анализа осетљивости улагања помоћу листа са подацима

За анализу пројеката у области инвестиција, друге методе су погодније од табеле са подацима. Многи корисници доживљавају конфузију приликом састављања формуле. Да бисте сазнали зависност једног фактора од промена у другим, потребно је да изаберете исправне ћелије за унос прорачуна и за читање података.

Анализа фактора и дисперзије у Екцел-у са аутоматизацијом прорачуна

Друга типологија анализе осетљивости је факторска анализа и анализа варијансе. Први тип дефинише однос између бројева, други открива зависност једне променљиве од других.

АНОВА у Екцел-у

Сврха такве анализе је да подели варијабилност вредности на три компоненте:

1. Променљивост као резултат утицаја других вредности.
2. Промене због односа вредности које утичу на то.
3. Случајне промене.

Хајде да извршимо анализу варијансе кроз Екцел додатак „Анализа података“. Ако није омогућено, може се омогућити у подешавањима.

Почетна табела мора да прати два правила: за сваку вредност постоји једна колона, а подаци у њој су распоређени у растућем или опадајућем редоследу. Неопходно је проверити утицај нивоа образовања на понашање у конфликту.

1. Пронађите алатку за анализу података на картици Подаци и отворите њен прозор. На листи треба да изаберете једносмерну анализу варијансе.

2. Попуните линије дијалога. Интервал уноса су све ћелије, осим заглавља и бројева. Групирајте по колонама. Резултате приказујемо на новом листу.

Пошто је вредност у жутој ћелији већа од један, претпоставка се може сматрати нетачном – нема везе између образовања и понашања у конфликту.

Факторска анализа у Екцел-у: пример

Хајде да анализирамо однос података у области продаје – потребно је идентификовати популарне и непопуларне производе. Почетне информације:

1. Морамо да сазнамо за којом робом је потражња највише порасла током другог месеца. Састављамо нову табелу да бисмо утврдили раст и пад тражње. Раст се израчунава помоћу ове формуле: =ИФ((Захтев 2-Захтев 1)>0; Захтев 2- Захтев 1;0). Формула смањења: =ИФ(Раст=0; Потражња 1- Потражња 2;0).

2. Израчунајте раст потражње за робом у процентима: =ИФ(Раст/Исход 2 =0; Смањење/Исход 2; Раст/Исход 2).

3. Хајде да направимо графикон ради јасноће – изаберите опсег ћелија и креирајте хистограм преко картице „Инсерт“. У подешавањима морате уклонити попуњавање, то се може урадити помоћу алата Формат Дата Сериес.

Двосмерна анализа варијансе у Екцел-у

Анализа варијансе се врши са неколико варијабли. Размотрите ово на примеру: потребно је да сазнате колико брзо се реакција на звук различите јачине манифестује код мушкараца и жена.

1. Отварамо „Анализу података“, на листи треба да пронађете двосмерну анализу варијансе без понављања.
2. Интервал уноса – ћелије које садрже податке (без заглавља). Резултате приказујемо на новом листу и кликнемо на „ОК“.

Ф вредност је већа од Ф-критичке, што значи да под утиче на брзину реакције на звук.

Zakljucak

У овом чланку је детаљно размотрена анализа осетљивости у Екцел табели, тако да ће сваки корисник моћи да разуме методе њене примене.