Аналіз чутливості в Excel (приклад таблиці даних)

Процеси в сфері фінансів завжди взаємопов'язані - один фактор залежить від іншого і змінюється разом з ним. Відстежити ці зміни і зрозуміти, чого варто очікувати в майбутньому, можливо за допомогою функцій Excel і табличних методів.

Отримання кількох результатів за допомогою таблиці даних

Можливості таблиць даних являють собою елементи аналізу «що якщо» - його нерідко проводять через Microsoft Excel. Це друга назва аналізу чутливості.

Загальні відомості

Таблиця даних - це тип діапазону комірок, за допомогою якого можна вирішувати виникаючі проблеми шляхом зміни значень в деяких осередках. Її складають, коли необхідно стежити за змінами компонентів формули і отримувати оновлення результатів, згідно з цими змінами. З'ясуємо, як застосовувати таблички даних в дослідженнях, і яких видів вони бувають.

Базові відомості про таблиці даних

Існує два види таблиць даних, вони розрізняються за кількістю компонентів. Складати таблицю потрібно з орієнтацією на кількість значень, які потрібно перевірити з її допомогою.

Фахівці статистики застосовують таблицю з однією змінною, коли в одному або декількох виразах є тільки одна змінна, яка може вплинути на зміну їх результату. Наприклад, її часто використовують в зв'язці з функцією ПЛТ. Формула призначена для розрахунку суми регулярного платежу і враховує встановлену в договорі процентну ставку. При подібних обчисленнях змінні записують в одну колонку, а результати обчислень в іншу. Приклад таблички даних з 1 змінної:

1

Далі розглянемо таблички з 2 змінними. Вони застосовуються в тих випадках, коли на зміну будь-якого показника впливають два фактори. Дві змінні можуть опинитися в іншій таблиці, пов'язаної з позикою, - з її допомогою можна виявити оптимальний термін виплати і суму щомісячного платежу. В такому розрахунку теж потрібно використовувати функцію ПЛТ. Приклад таблички з 2 змінними:

2 Створення таблиці даних з однієї змінної

Розглянемо метод аналізу на прикладі невеликого книжкового магазину, де всього 100 книг в наявності. Частина з них можна продати дорожче (50 $), інші обійдуться покупцям дешевше (20 $). Розрахований загальний дохід з продажу всіх товарів - власник вирішив, що продасть за високою ціною 60% книг. Необхідно з'ясувати, як виросте виручка, якщо підвищити ціну на більший обсяг товарів - 70% і так далі.

1. Вибираємо вільну комірку на віддалі від краю листа і записуємо в ній формулу: = Осередок загальної виручки. Наприклад, якщо дохід записаний в осередку С14 (вказано випадкове позначення), необхідно написати так: = С14.
2. Записуємо відсотки обсягу товару в стовпець зліва від цього осередку - не під нею, це дуже важливо.
3. Виділяємо діапазон комірок, де розташовується стовпчик відсотків і посилання на загальний дохід.

3

1. Знаходимо на вкладці «Дані» пункт «Аналіз« що якщо »» і клікаєм по ньому - в меню потрібно вибрати опцію «Таблиця даних».

4

1. Відкриється невелике вікно, де необхідно вказати клітинку з відсотком спочатку проданих за високою ціною книг в графі «Підставляти значення по рядках в ...». Цей крок роблять для того, щоб робити перерахунок загальної виручки з урахуванням зростаючого відсотка.

5

Після натискання кнопки «ОК» у вікні, де вводилися дані для складання таблиці, в рядках з'являться результати обчислень.

Додавання формули в таблицю даних з однієї змінної

З таблиці, яка допомагала розраховувати дію тільки з однією змінною, можна зробити ускладнений інструмент аналізу, додавши додаткову формулу. Її необхідно вписати поруч з уже існуючою формулою - наприклад, якщо таблиця орієнтована по рядках, вписуємо вираз в клітинку праворуч від вже існуючого. Коли встановлена ​​орієнтація по стовпцях, записуємо нову формулу під старою. Далі слід діяти за алгоритмом:

1. Знову виділяємо діапазон комірок, але тепер він повинен включати нову формулу.
2. Відкриваємо меню аналізу «що якщо» і вибираємо «Таблицю даних».
3. Додаємо нову формулу в відповідне поле по рядках або по стовпчиках залежно від орієнтації таблички.

Створення таблиці даних з двома змінними

Початок складання такої таблиці трохи відрізняється - потрібно помістити посилання на загальну виручку над значеннями відсотків. Далі виконуємо ці кроки:

1. Записати варіанти ціни в один рядок з посиланням на дохід - кожній ціні один осередок.
2. Виділити діапазон комірок.

6

1. Відкрити вікно таблиці даних, як при складанні таблички з однією змінною - через вкладку «Дані» на панелі інструментів.
2. Підставити в графу «Підставляти значення за стовпцями в ...» осередок з початковою високою ціною.
3. Додати в графу «Підставляти значення по рядках в ...» осередок з початковим відсотком продажів дорогих книг і натиснути «ОК».

В результаті вся табличка заповнена сумами можливого доходу з різними умовами продажу товару.

7 Прискорення обчислень для листів, що містять таблиці даних

Якщо потрібні швидкі обчислення в таблиці даних, що не запускають перерахунок книги цілком, можна виконати кілька дій для прискорення процесу.

1. Відкриваємо вікно параметрів, вибираємо пункт «Формули» в меню праворуч.
2. Вибираємо пункт «Автоматично, крім таблиць даних» в розділі «Обчислення в книзі».

8

1. Виконаємо перерахунок результатів в табличці вручну. Для цього потрібно виділити формули і натиснути клавішу F

Інші інструменти для виконання аналізу чутливості

У програмі є й інші інструменти, що допомагають виконувати аналіз чутливості. Вони автоматизують деякі дії, які в іншому випадку довелося б виконувати вручну.

1. Функція «Підбір параметра» підійде, якщо відомий потрібний результат, і потрібно дізнатися вхідний значення змінної для отримання такого результату.
2. «Пошук рішення» - це надбудова для вирішення завдань. Необхідно встановити обмеження і вказати на них, після чого система знайде відповідь. Рішення визначається шляхом зміни значень.
3. Аналіз чутливості можна провести за допомогою диспетчера сценаріїв. Цей інструмент знаходиться в меню аналізу «що якщо» на вкладці «Дані». Він підставляє значення в кілька осередків - кількість може досягати 32-х. Диспетчер порівнює ці значення, і користувачеві не доведеться міняти їх вручну. Приклад застосування диспетчера сценаріїв:

9

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

Метод аналізу чутливості в сфері інвестицій

При аналізі «що якщо» використовують перебір - ручний або автоматичний. Відомий діапазон значень, і вони по черзі підставляються в формулу. У підсумку виходить набір значень. З них вибирають відповідну цифру. Розглянемо чотири показники, за якими ведеться аналіз чутливості в сфері фінансів:

1. Чиста приведена вартість - обчислюється шляхом вирахування розміру вкладення з обсягу доходів.
2. Внутрішня норма прибутковості / прибутку - вказує, який прибуток потрібно отримати з вкладення за рік.
3. Коефіцієнт окупності - відношення всієї прибутку до початкового вкладення.
4. Дисконтований індекс прибутку - вказує на ефективність інвестиції.

Формула

Чутливість вкладення можна обчислити за допомогою цієї формули: Зміна вихідного параметра в% / Зміна вхідного параметра в%.

Вихідним і вхідним параметром можуть бути величини, описані раніше.

1. Необхідно дізнатися результат при стандартних умовах.
2. Замінюємо одну з змінних і стежимо за змінами результату.
3. Обчислюємо процентну зміну обох параметрів щодо встановлених умов.
4. Вставляємо отримані відсотки в формулу і визначаємо чутливість.

Приклад аналізу чутливості інвестиційного проекту в Excel

Для кращого розуміння методики аналізу необхідний приклад. Проаналізуємо проект з такими відомими даними:

10

1. Заповнимо таблицю, щоб аналізувати проект по ній.

11

1. Обчислюємо грошовий потік за допомогою функції зміщений. На початковому етапі потік дорівнює вкладенням. Далі застосовуємо формулу: = ЕСЛИ (зміщений (Номер; 1;) = 2; СУММ (Приплив 1: Відтік 1); СУММ (Приплив 1: Відтік 1) + $ B $ 5) Позначення осередків у формулі можуть бути іншими, це залежить від розміщення таблиці. В кінці додається значення з початкових даних - ліквідаційна вартість.

12

1. Визначаємо термін, за який проект окупиться. Для початкового періоду використовуємо цю формулу: = СУММЕСЛИ (G7: G17; "0; Перший д.поток; 0). Проект виявляється в точці беззбитковості за 4 роки.

13

1. Створюємо стовпець для номерів тих періодів, коли проект окупається.

14

1. Обчислюємо рентабельність вкладень. Необхідно скласти вираз, де прибуток в конкретному відрізку часу ділиться на початкові вкладення.

15

1. Визначаємо коефіцієнт дисконтування за цією формулою: = 1 / (1 + Ставка диск.%) ^ Номер.

16

1. Обчислимо наведену вартість за допомогою множення - грошовий потік множиться на коефіцієнт дисконтування.

17

1. Розрахуємо PI (індекс рентабельності). Наведена вартість в відрізку часу ділиться на вкладення на початку розвитку проекту.

18

1. Визначимо внутрішню норму прибутку за допомогою функції ВСД: = ВСД (Діапазон грошового потоку).

Аналіз чутливості інвестицій за допомогою таблиці даних

Для аналізу проектів в сфері інвестування краще підходять інші способи, ніж таблиця даних. У багатьох користувачів виникає плутанина при складанні формули. Щоб з'ясувати залежність одного фактора від змін в інших, потрібно вибрати правильні осередки введення обчислень і для зчитування даних.

Факторний і дисперсійний аналіз в Excel з автоматизацією підрахунків

Дисперсійний аналіз в Excel

Мета такого аналізу - розділити мінливість величини на три компоненти:

1. Мінливість в результаті впливу інших значень.
2. Зміни через взаємозв'язку впливають на нього значень.
3. Випадкові зміни.

Виконаємо дисперсійний аналіз через надбудову Excel «Аналіз даних». Якщо вона не включена, її можна підключити в параметрах.

Початкова таблиця повинна відповідати двом правилам: на кожну величину доводиться один стовпець, і дані в ньому розташовуються по зростанням або за спаданням. Необхідно перевірити вплив рівня освіти на поведінку в конфлікті.

19

1. Знаходимо у вкладці «Дані» інструмент «Аналіз даних» і відкриваємо його вікно. У списку потрібно вибрати однофакторний дисперсійний аналіз.

20

1. Заповнюємо рядки діалогового вікна. Вхідний інтервал - все осередки без урахування шапки і номерів. Групуємо за стовпцями. Виводимо результати на новий лист.

21

Оскільки значення в жовтій осередку більше одиниці, можна вважати припущення невірним - залежності між освітою і поведінкою в конфлікті немає.

Факторний аналіз в Excel: приклад

Проаналізуємо взаємозв'язок даних в сфері продажів - необхідно виявити популярні і непопулярні товари. Початкова інформація:

22

1. Потрібно з'ясувати, на які товари найбільше зріс попит протягом другого місяця. Складаємо нову таблицю для визначення зростання і зниження попиту. Зростання розраховується за цією формулою: = ЕСЛИ ((Попит 2-Попит 1)> 0; Попит 2 Попит 1; 0). Формула зниження: = ЕСЛИ (Зростання = 0; Попит 1 Попит 2; 0).

23

1. Підрахуємо зростання попиту на товари у відсотках: = ЕСЛИ (Зростання / Підсумок 2 = 0; Зниження / Підсумок 2; Зростання / Підсумок 2).

24

1. Складемо діаграму для наочності - виділяємо діапазон комірок і створюємо гистограмму через вкладку «Вставка». В налаштуваннях потрібно прибрати заливку, це можна зробити через інструмент «Формат ряду даних».

25 Двохфакторну дисперсійний аналіз в Excel

Дисперсійний аналіз проводять з декількома змінними. Розглянемо це на прикладі: потрібно з'ясувати, як швидко проявляється реакція на звук різної гучності у чоловіків і жінок.

26

1. Відкриваємо «Аналіз даних», в списку потрібно знайти двохфакторну дисперсійний аналіз без повторень.
2. Вхідний інтервал - осередки, де містяться дані (без шапки). Виводимо результати на новий лист і тиснемо «ОК».

27

Показник F більше, ніж F-критичне - це означає, що стать впливає на швидкість реакції на звук.

28

висновок

У даній статті був детально розглянутий аналіз чутливості в табличному процесорі Excel, завдяки чому кожен користувач зможе розібратися в методах його застосування.

Повідомлення Аналіз чутливості в Excel (приклад таблиці даних) з'явилися спочатку на Інформаційні технології.