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

Процесите в областта на финансите винаги са взаимосвързани – един фактор зависи от друг и се променя с него. Проследете тези промени и разберете какво да очаквате в бъдеще, може би като използвате функции на Excel и методи за електронни таблици.

Получаване на множество резултати с таблица с данни

Възможностите на листа с данни са елементи от анализа на какво става – често се прави чрез Microsoft Excel. Това е второто име за анализ на чувствителността.

Overview

Таблица с данни е тип диапазон от клетки, който може да се използва за решаване на проблеми чрез промяна на стойностите в някои клетки. Създава се, когато е необходимо да се следят промените в компонентите на формулата и да се получават актуализации на резултатите, в съответствие с тези промени. Нека да разберем как да използваме таблици с данни в изследванията и какви типове са те.

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

Има два вида таблици с данни, те се различават по броя на компонентите. Трябва да съставите таблица с фокус върху броя на стойностите 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; Първи 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. Трябва да разберем за кои стоки търсенето се е увеличило най-много през втория месец. Съставяме нова таблица, за да определим ръста и спада на търсенето. Растежът се изчислява по следната формула: =АКО((Търсене 2-Търсене 1)>0; Търсене 2- Търсене 1;0). Формула за намаляване: =АКО(Растеж=0; Търсене 1- Търсене 2;0).

2. Изчислете ръста на търсенето на стоки като процент: =АКО(Растеж/Резултат 2 =0; Намаляване/Резултат 2; Растеж/Резултат 2).

3. Нека направим диаграма за по-голяма яснота – изберете диапазон от клетки и създайте хистограма чрез раздела „Вмъкване“. В настройките трябва да премахнете запълването, това може да стане чрез инструмента Format Data Series.

Двупосочен дисперсионен анализ в Excel

Дисперсионният анализ се извършва с няколко променливи. Помислете за това с пример: трябва да разберете колко бързо се проявява реакцията на звук с различна сила при мъжете и жените.

1. Отваряме „Анализ на данни“, в списъка трябва да намерите двупосочен анализ на дисперсията без повторения.
2. Интервал на въвеждане – клетки, които съдържат данни (без заглавка). Показваме резултатите на нов лист и щракнете върху „OK“.

Стойността F е по-голяма от F-критичната, което означава, че подът влияе върху скоростта на реакция на звука.

Заключение

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