Анализа на чувствителност во Excel (примерен лист со податоци)

Процесите во областа на финансиите се секогаш меѓусебно поврзани – еден фактор зависи од друг и се менува со него. Следете ги овие промени и разберете што да очекувате во иднина, можеби користејќи ги функциите на Excel и методите за табеларни пресметки.

Добивање повеќе резултати со табела со податоци

Способностите на листот со податоци се елементи на анализата што-ако - често се прави преку Microsoft Excel. Ова е второто име за анализа на чувствителност.

Преглед

Табелата со податоци е тип на опсег на ќелии што може да се користат за решавање проблеми со менување на вредностите во некои ќелии. Се создава кога е неопходно да се следат промените во компонентите на формулата и да се добиваат ажурирања на резултатите, според овие промени. Ајде да дознаеме како да ги користиме табелите со податоци во истражувањето и какви видови се тие.

Основи за табелите со податоци

Постојат два вида табели со податоци, тие се разликуваат по бројот на компоненти. Треба да составите табела со фокус на бројот на вредности * uXNUMXbuXNUMXb што треба да ги проверите со неа.

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

Следно, разгледајте ги плочите со 2 променливи. Тие се користат во случаи кога два фактори влијаат на промената на кој било индикатор. Двете променливи може да завршат во друга табела поврзана со заемот, која може да се искористи за да се одреди оптималниот рок на отплата и висината на месечната исплата. Во оваа пресметка, треба да ја користите и функцијата PMT. Пример за табела со 2 променливи:

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

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

Обрнете внимание! Вкупниот приход мора да се пресмета со формула, во спротивно нема да биде можно да се состави табела со податоци.

1. Изберете слободна ќелија подалеку од работ на листот и напишете ја формулата во неа: =Ќелија на вкупен приход. На пример, ако приходот е запишан во ќелијата C14 (наведена е случајна ознака), треба да го напишете ова: =S14.
2. Го запишуваме процентот на волуменот на стоката во колоната лево од оваа ќелија - не под неа, ова е многу важно.
3. Го избираме опсегот на ќелии каде што се наоѓа процентуалната колона и врската до вкупниот приход.

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

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

Откако ќе кликнете на копчето „OK“ во прозорецот каде што се внесени податоците за составување на табелата, резултатите од пресметките ќе се појават во редовите.

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

Од табела што помогна да се пресмета дејство со само една променлива, можете да направите софистицирана алатка за анализа со додавање дополнителна формула. Мора да се внесе до веќе постоечка формула - на пример, ако табелата е ориентирана кон редови, го внесуваме изразот во ќелијата десно од постоечката. Кога е поставена ориентацијата на колоната, ја пишуваме новата формула под старата. Следно, следете го алгоритмот:

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

Направете табела со податоци со две променливи

Почетокот на таквата табела е малку поинаков - треба да ставите врска до вкупниот приход над процентуалните вредности. Следно, ги извршуваме овие чекори:

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

3. Отворете го прозорецот за табела со податоци, како кога составувате табела со една променлива - преку табулаторот „Податоци“ на лентата со алатки.
4. Заменете во колоната „Заменете ги вредностите со колони во…“ ќелија со почетна висока цена.
5. Додајте ќелија со почетниот процент на продажба на скапи книги во колоната „Заменете ги вредностите по редови во…“ и кликнете „OK“.

Како резултат на тоа, целата табела е исполнета со износи на можни приходи со различни услови за продажба на стоки.

Забрзајте ги пресметките за работните листови кои содржат табели со податоци

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

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

3. Ајде рачно повторно да ги пресметаме резултатите во табелата. За да го направите ова, изберете ги формулите и притиснете го копчето F.

Други алатки за вршење на анализа на чувствителност

Во програмата има и други алатки кои ќе ви помогнат да извршите анализа на чувствителност. Тие автоматизираат некои дејства што инаку би требало да се направат рачно.

1. Функцијата „Избор на параметри“ е погодна ако е познат саканиот резултат и треба да ја знаете влезната вредност на променливата за да добиете таков резултат.
2. „Барај решение“ е додаток за решавање проблеми. Потребно е да се постават граници и да се укаже на нив, по што системот ќе го најде одговорот. Решението се одредува со промена на вредностите.
3. Анализата на чувствителноста може да се направи со помош на Управувачот со сценарија. Оваа алатка се наоѓа во менито за анализа што-ако под табулаторот Податоци. Ги заменува вредностите во неколку ќелии - бројот може да достигне 32. Диспечерот ги споредува овие вредности за корисникот да не мора рачно да ги менува. Пример за користење на менаџерот за скрипти:

Анализа на чувствителност на инвестициски проект во Excel

Што-ако анализата е особено корисна во ситуации кога е потребно предвидување, како што се инвестициите. Аналитичарите го користат овој метод за да дознаат како вредноста на акциите на компанијата ќе се промени како резултат на промени во некои фактори.

Метод за анализа на чувствителноста на инвестициите

Кога го анализирате „што ако“ користете набројување - рачно или автоматски. Опсегот на вредности е познат и тие се заменуваат во формулата еден по еден. Резултатот е збир на вредности. Изберете го соодветниот број од нив. Да разгледаме четири индикатори за кои се врши анализа на сензитивноста во областа на финансиите:

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

Формула

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

Излезните и влезните параметри може да бидат вредностите опишани претходно.

1. Треба да го знаете резултатот под стандардни услови.
2. Заменуваме една од променливите и ги следиме промените во резултатот.
3. Ја пресметуваме процентуалната промена на двата параметри во однос на утврдените услови.
4. Добиените проценти ги внесуваме во формулата и ја одредуваме чувствителноста.

Пример за анализа на чувствителност на инвестициски проект во Excel

За подобро разбирање на методологијата на анализа, потребен е пример. Ајде да го анализираме проектот со следните познати податоци:

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

2. Го пресметуваме готовинскиот тек користејќи ја функцијата OFFSET. Во почетната фаза, протокот е еднаков на инвестициите. Следно, ја применуваме формулата: =IF(OFFSET(Број,1;)=2;SUM(Прилив 1:Излез 1); SUM(Прилив 1:Излез 1)+$B$ 5)
   Ознаките на ќелиите во формулата може да бидат различни, во зависност од распоредот на табелата. На крајот се додава вредноста од првичните податоци – вредноста за спасување.

3. Го одредуваме периодот за кој проектот ќе се исплати. За почетниот период, ја користиме оваа формула: = РЕЗИМЕ (G7: Г17;»<0″). Опсегот на ќелиите е колона за готовински тек. За понатамошни периоди, ја применуваме оваа формула: =Почетен период+IF(Прв e.stream>0; First e.stream;0). Проектот е на рентабилна точка за 4 години.

4. Ние создаваме колона за броевите на тие периоди кога проектот се исплати.

5. Го пресметуваме повратот на инвестицијата. Неопходно е да се направи израз каде што добивката во одреден временски период се дели со почетната инвестиција.

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

7. Ја пресметуваме сегашната вредност користејќи множење - готовинскиот тек се множи со факторот на попуст.

8. Да го пресметаме PI (индекс на профитабилност). Сегашната вредност со текот на времето се дели со инвестицијата на почетокот на проектот.

9. Ајде да ја дефинираме внатрешната стапка на принос користејќи ја функцијата IRR: =IRR(Опсег на готовински тек).

Анализа на чувствителност на инвестициите со помош на лист со податоци

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

Факторска и дисперзивна анализа во Excel со автоматизација на пресметките

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

ANOVA во Excel

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

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

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

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

1. Најдете ја алатката за анализа на податоци во картичката Податоци и отворете го нејзиниот прозорец. Во списокот, треба да изберете еднонасочна анализа на варијанса.

2. Пополнете ги линиите од полето за дијалог. Влезен интервал е сите ќелии, со исклучок на заглавијата и броевите. Групирајте по колони. Резултатите ги прикажуваме на нов лист.

Бидејќи вредноста во жолтата ќелија е поголема од една, претпоставката може да се смета за неточна - нема врска помеѓу образованието и однесувањето во конфликт.

Факторска анализа во Excel: пример

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

1. Треба да откриеме за која стока побарувачката најмногу се зголемила во вториот месец. Составуваме нова табела за да го одредиме растот и падот на побарувачката. Растот се пресметува со оваа формула: =IF((Побарувачка 2-Побарувачка 1)>0; Побарувачка 2- Побарувачка 1;0). Формула за намалување: =IF(Раст=0; Побарувачка 1- Побарувачка 2;0).

2. Пресметајте го растот на побарувачката за стоки како процент: =IF(Раст/Исход 2 =0; Намалување/Исход 2; Раст/Исход 2).

3. Ајде да направиме графикон за јасност - изберете опсег на ќелии и креирајте хистограм преку табулаторот „Вметни“. Во поставките, треба да го отстраните пополнувањето, тоа може да се направи преку алатката Format Data Series.

Двонасочна анализа на варијанса во Excel

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

1. Отвораме „Анализа на податоци“, во списокот треба да најдете двонасочна анализа на варијанса без повторувања.
2. Влезен интервал – ќелии кои содржат податоци (без заглавие). Резултатите ги прикажуваме на нов лист и кликнете „OK“.

Вредноста F е поголема од F-критичната, што значи дека подот влијае на брзината на реакција на звукот.

Заклучок

Во овој напис, детално беше разгледана анализата на чувствителност во табела на Excel, така што секој корисник ќе може да ги разбере методите на нејзината примена.