Процеси в сфері фінансів завжди взаємопов'язані - один фактор залежить від іншого і змінюється разом з ним. Відстежити ці зміни і зрозуміти, чого варто очікувати в майбутньому, можливо за допомогою функцій Excel і табличних методів.
Отримання кількох результатів за допомогою таблиці даних
Можливості таблиць даних являють собою елементи аналізу «що якщо» - його нерідко проводять через Microsoft Excel. Це друга назва аналізу чутливості.
Загальні відомостіТаблиця даних - це тип діапазону комірок, за допомогою якого можна вирішувати виникаючі проблеми шляхом зміни значень в деяких осередках. Її складають, коли необхідно стежити за змінами компонентів формули і отримувати оновлення результатів, згідно з цими змінами. З'ясуємо, як застосовувати таблички даних в дослідженнях, і яких видів вони бувають.
Базові відомості про таблиці данихІснує два види таблиць даних, вони розрізняються за кількістю компонентів. Складати таблицю потрібно з орієнтацією на кількість значень, які потрібно перевірити з її допомогою.
Фахівці статистики застосовують таблицю з однією змінною, коли в одному або декількох виразах є тільки одна змінна, яка може вплинути на зміну їх результату. Наприклад, її часто використовують в зв'язці з функцією ПЛТ. Формула призначена для розрахунку суми регулярного платежу і враховує встановлену в договорі процентну ставку. При подібних обчисленнях змінні записують в одну колонку, а результати обчислень в іншу. Приклад таблички даних з 1 змінної:
1Далі розглянемо таблички з 2 змінними. Вони застосовуються в тих випадках, коли на зміну будь-якого показника впливають два фактори. Дві змінні можуть опинитися в іншій таблиці, пов'язаної з позикою, - з її допомогою можна виявити оптимальний термін виплати і суму щомісячного платежу. В такому розрахунку теж потрібно використовувати функцію ПЛТ. Приклад таблички з 2 змінними:
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_1](/userfiles/22/1235_1.webp)
Розглянемо метод аналізу на прикладі невеликого книжкового магазину, де всього 100 книг в наявності. Частина з них можна продати дорожче (50 $), інші обійдуться покупцям дешевше (20 $). Розрахований загальний дохід з продажу всіх товарів - власник вирішив, що продасть за високою ціною 60% книг. Необхідно з'ясувати, як виросте виручка, якщо підвищити ціну на більший обсяг товарів - 70% і так далі.
- Вибираємо вільну комірку на віддалі від краю листа і записуємо в ній формулу: = Осередок загальної виручки. Наприклад, якщо дохід записаний в осередку С14 (вказано випадкове позначення), необхідно написати так: = С14.
- Записуємо відсотки обсягу товару в стовпець зліва від цього осередку - не під нею, це дуже важливо.
- Виділяємо діапазон комірок, де розташовується стовпчик відсотків і посилання на загальний дохід.
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_2](/userfiles/22/1235_2.webp)
- Знаходимо на вкладці «Дані» пункт «Аналіз« що якщо »» і клікаєм по ньому - в меню потрібно вибрати опцію «Таблиця даних».
- Відкриється невелике вікно, де необхідно вказати клітинку з відсотком спочатку проданих за високою ціною книг в графі «Підставляти значення по рядках в ...». Цей крок роблять для того, щоб робити перерахунок загальної виручки з урахуванням зростаючого відсотка.
Після натискання кнопки «ОК» у вікні, де вводилися дані для складання таблиці, в рядках з'являться результати обчислень.
Додавання формули в таблицю даних з однієї змінноїЗ таблиці, яка допомагала розраховувати дію тільки з однією змінною, можна зробити ускладнений інструмент аналізу, додавши додаткову формулу. Її необхідно вписати поруч з уже існуючою формулою - наприклад, якщо таблиця орієнтована по рядках, вписуємо вираз в клітинку праворуч від вже існуючого. Коли встановлена орієнтація по стовпцях, записуємо нову формулу під старою. Далі слід діяти за алгоритмом:
- Знову виділяємо діапазон комірок, але тепер він повинен включати нову формулу.
- Відкриваємо меню аналізу «що якщо» і вибираємо «Таблицю даних».
- Додаємо нову формулу в відповідне поле по рядках або по стовпчиках залежно від орієнтації таблички.
Початок складання такої таблиці трохи відрізняється - потрібно помістити посилання на загальну виручку над значеннями відсотків. Далі виконуємо ці кроки:
- Записати варіанти ціни в один рядок з посиланням на дохід - кожній ціні один осередок.
- Виділити діапазон комірок.
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_3](/userfiles/22/1235_3.webp)
- Відкрити вікно таблиці даних, як при складанні таблички з однією змінною - через вкладку «Дані» на панелі інструментів.
- Підставити в графу «Підставляти значення за стовпцями в ...» осередок з початковою високою ціною.
- Додати в графу «Підставляти значення по рядках в ...» осередок з початковим відсотком продажів дорогих книг і натиснути «ОК».
В результаті вся табличка заповнена сумами можливого доходу з різними умовами продажу товару.
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_4](/userfiles/22/1235_4.webp)
Якщо потрібні швидкі обчислення в таблиці даних, що не запускають перерахунок книги цілком, можна виконати кілька дій для прискорення процесу.
- Відкриваємо вікно параметрів, вибираємо пункт «Формули» в меню праворуч.
- Вибираємо пункт «Автоматично, крім таблиць даних» в розділі «Обчислення в книзі».
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_5](/userfiles/22/1235_5.webp)
- Виконаємо перерахунок результатів в табличці вручну. Для цього потрібно виділити формули і натиснути клавішу F
У програмі є й інші інструменти, що допомагають виконувати аналіз чутливості. Вони автоматизують деякі дії, які в іншому випадку довелося б виконувати вручну.
- Функція «Підбір параметра» підійде, якщо відомий потрібний результат, і потрібно дізнатися вхідний значення змінної для отримання такого результату.
- «Пошук рішення» - це надбудова для вирішення завдань. Необхідно встановити обмеження і вказати на них, після чого система знайде відповідь. Рішення визначається шляхом зміни значень.
- Аналіз чутливості можна провести за допомогою диспетчера сценаріїв. Цей інструмент знаходиться в меню аналізу «що якщо» на вкладці «Дані». Він підставляє значення в кілька осередків - кількість може досягати 32-х. Диспетчер порівнює ці значення, і користувачеві не доведеться міняти їх вручну. Приклад застосування диспетчера сценаріїв:
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_6](/userfiles/22/1235_6.webp)
Аналіз чутливості інвестиційного проекту в Excel
Метод аналізу чутливості в сфері інвестиційПри аналізі «що якщо» використовують перебір - ручний або автоматичний. Відомий діапазон значень, і вони по черзі підставляються в формулу. У підсумку виходить набір значень. З них вибирають відповідну цифру. Розглянемо чотири показники, за якими ведеться аналіз чутливості в сфері фінансів:
- Чиста приведена вартість - обчислюється шляхом вирахування розміру вкладення з обсягу доходів.
- Внутрішня норма прибутковості / прибутку - вказує, який прибуток потрібно отримати з вкладення за рік.
- Коефіцієнт окупності - відношення всієї прибутку до початкового вкладення.
- Дисконтований індекс прибутку - вказує на ефективність інвестиції.
Чутливість вкладення можна обчислити за допомогою цієї формули: Зміна вихідного параметра в% / Зміна вхідного параметра в%.
Вихідним і вхідним параметром можуть бути величини, описані раніше.
- Необхідно дізнатися результат при стандартних умовах.
- Замінюємо одну з змінних і стежимо за змінами результату.
- Обчислюємо процентну зміну обох параметрів щодо встановлених умов.
- Вставляємо отримані відсотки в формулу і визначаємо чутливість.
Для кращого розуміння методики аналізу необхідний приклад. Проаналізуємо проект з такими відомими даними:
10- Заповнимо таблицю, щоб аналізувати проект по ній.
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_7](/userfiles/22/1235_7.webp)
- Обчислюємо грошовий потік за допомогою функції зміщений. На початковому етапі потік дорівнює вкладенням. Далі застосовуємо формулу: = ЕСЛИ (зміщений (Номер; 1;) = 2; СУММ (Приплив 1: Відтік 1); СУММ (Приплив 1: Відтік 1) + $ B $ 5) Позначення осередків у формулі можуть бути іншими, це залежить від розміщення таблиці. В кінці додається значення з початкових даних - ліквідаційна вартість.
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_8](/userfiles/22/1235_8.webp)
- Визначаємо термін, за який проект окупиться. Для початкового періоду використовуємо цю формулу: = СУММЕСЛИ (G7: G17; "0; Перший д.поток; 0). Проект виявляється в точці беззбитковості за 4 роки.
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_9](/userfiles/22/1235_9.webp)
- Створюємо стовпець для номерів тих періодів, коли проект окупається.
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_10](/userfiles/22/1235_10.webp)
- Обчислюємо рентабельність вкладень. Необхідно скласти вираз, де прибуток в конкретному відрізку часу ділиться на початкові вкладення.
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_11](/userfiles/22/1235_11.webp)
- Визначаємо коефіцієнт дисконтування за цією формулою: = 1 / (1 + Ставка диск.%) ^ Номер.
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_12](/userfiles/22/1235_12.webp)
- Обчислимо наведену вартість за допомогою множення - грошовий потік множиться на коефіцієнт дисконтування.
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_13](/userfiles/22/1235_13.webp)
- Розрахуємо PI (індекс рентабельності). Наведена вартість в відрізку часу ділиться на вкладення на початку розвитку проекту.
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_14](/userfiles/22/1235_14.webp)
- Визначимо внутрішню норму прибутку за допомогою функції ВСД: = ВСД (Діапазон грошового потоку).
Аналіз чутливості інвестицій за допомогою таблиці даних
Для аналізу проектів в сфері інвестування краще підходять інші способи, ніж таблиця даних. У багатьох користувачів виникає плутанина при складанні формули. Щоб з'ясувати залежність одного фактора від змін в інших, потрібно вибрати правильні осередки введення обчислень і для зчитування даних.Факторний і дисперсійний аналіз в Excel з автоматизацією підрахунків
Дисперсійний аналіз в ExcelМета такого аналізу - розділити мінливість величини на три компоненти:
- Мінливість в результаті впливу інших значень.
- Зміни через взаємозв'язку впливають на нього значень.
- Випадкові зміни.
Виконаємо дисперсійний аналіз через надбудову Excel «Аналіз даних». Якщо вона не включена, її можна підключити в параметрах.
Початкова таблиця повинна відповідати двом правилам: на кожну величину доводиться один стовпець, і дані в ньому розташовуються по зростанням або за спаданням. Необхідно перевірити вплив рівня освіти на поведінку в конфлікті.
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_15](/userfiles/22/1235_15.webp)
- Знаходимо у вкладці «Дані» інструмент «Аналіз даних» і відкриваємо його вікно. У списку потрібно вибрати однофакторний дисперсійний аналіз.
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_16](/userfiles/22/1235_16.webp)
- Заповнюємо рядки діалогового вікна. Вхідний інтервал - все осередки без урахування шапки і номерів. Групуємо за стовпцями. Виводимо результати на новий лист.
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_17](/userfiles/22/1235_17.webp)
Оскільки значення в жовтій осередку більше одиниці, можна вважати припущення невірним - залежності між освітою і поведінкою в конфлікті немає.
Факторний аналіз в Excel: прикладПроаналізуємо взаємозв'язок даних в сфері продажів - необхідно виявити популярні і непопулярні товари. Початкова інформація:
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_18](/userfiles/22/1235_18.webp)
- Потрібно з'ясувати, на які товари найбільше зріс попит протягом другого місяця. Складаємо нову таблицю для визначення зростання і зниження попиту. Зростання розраховується за цією формулою: = ЕСЛИ ((Попит 2-Попит 1)> 0; Попит 2 Попит 1; 0). Формула зниження: = ЕСЛИ (Зростання = 0; Попит 1 Попит 2; 0).
- Підрахуємо зростання попиту на товари у відсотках: = ЕСЛИ (Зростання / Підсумок 2 = 0; Зниження / Підсумок 2; Зростання / Підсумок 2).
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_19](/userfiles/22/1235_19.webp)
- Складемо діаграму для наочності - виділяємо діапазон комірок і створюємо гистограмму через вкладку «Вставка». В налаштуваннях потрібно прибрати заливку, це можна зробити через інструмент «Формат ряду даних».
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_20](/userfiles/22/1235_20.webp)
Дисперсійний аналіз проводять з декількома змінними. Розглянемо це на прикладі: потрібно з'ясувати, як швидко проявляється реакція на звук різної гучності у чоловіків і жінок.
26- Відкриваємо «Аналіз даних», в списку потрібно знайти двохфакторну дисперсійний аналіз без повторень.
- Вхідний інтервал - осередки, де містяться дані (без шапки). Виводимо результати на новий лист і тиснемо «ОК».
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_21](/userfiles/22/1235_21.webp)
Показник F більше, ніж F-критичне - це означає, що стать впливає на швидкість реакції на звук.
![Аналіз чутливості в Excel (приклад таблиці даних) 1235_22](/userfiles/22/1235_22.webp)
висновок
У даній статті був детально розглянутий аналіз чутливості в табличному процесорі Excel, завдяки чому кожен користувач зможе розібратися в методах його застосування.
Повідомлення Аналіз чутливості в Excel (приклад таблиці даних) з'явилися спочатку на Інформаційні технології.