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

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

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

Можливості таблиці даних є елементами аналізу "що, якщо", який часто виконується через Microsoft Excel. Це друга назва аналізу чутливості.

огляд

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

Основи таблиць даних

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

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

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

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

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

Звернути увагу! Загальний дохід необхідно розрахувати за формулою, інакше скласти таблицю даних буде неможливо.

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

4. Знаходимо на вкладці «Дані» пункт «Що, якщо аналіз» і натискаємо на нього – в меню, що відкрилося, вибираємо опцію «Таблиця даних».

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

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

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

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

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

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

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

1. Напишіть цінові опціони в один рядок із посиланням на дохід – по одній клітинці для кожної ціни.
2. Виділіть діапазон клітинок.

3. Відкрийте вікно таблиці даних, як і при складанні таблиці з однією змінною – через вкладку «Дані» на панелі інструментів.
4. Підставте в стовпець «Підставити значення по стовпцях в …» осередок з початковою високою ціною.
5. Додайте осередок з початковим відсотком продажів дорогих книг в стовпець «Підставити значення по рядках в …» і натисніть «ОК».

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

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

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

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

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

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

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

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

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

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

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

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

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

Formula

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

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

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

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

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

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

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

3. Визначаємо термін, за який проект окупиться. Для початкового періоду ми використовуємо таку формулу: =SUMMESLY(G7: Г17;»<0″). Діапазон клітинок – це стовпець грошових потоків. Для подальших періодів ми застосовуємо цю формулу: =Початковий період+IF(Перший електронний потік>0; Перший електронний потік;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). Формула зменшення: =ЯКЩО(Зростання=0; Попит 1- Попит 2;0).

2. Розрахуємо зростання попиту на товар у відсотках: =ЯКЩО(Зростання/Результат 2 =0; Зменшення/Результат 2; Зростання/Результат 2).

3. Для наочності зробимо діаграму – виділіть діапазон комірок і створіть гістограму через вкладку «Вставити». В налаштуваннях потрібно прибрати заливку, це можна зробити через інструмент Формат ряду даних.

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

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

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

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

Висновок

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