Аналіз чутливості фінансових показників в середовищі пакета
Microsoft Excel
Аналіз чутливості показників широко використовується в практиці фінансового менеджменту. У загальному випадку він зводиться до дослідження залежності деякого результуючого показника від варіації значень показників, що використовуються у його визначенні. Іншими словами, цей метод дозволяє одержати відповіді на питання виду: що буде з результуючою величиною, якщо зміниться значення деякої вхідної величини?
Проведення подібного аналізу припускає виконання наступних етапів.
Задається взаємозв'язок між вхідними і результуючими показниками у виді математичного рівняння або нерівності.
Визначаються найбільш ймовірні значення для вхідних показників і можливі діапазони їхніх змін.
Шляхом зміни значень вхідних показників досліджується їхній вплив на кінцевий результат.
Звичайна процедура аналізу чутливості припускає зміну одного вхідного показника, у той час як значення інших вважаються постійними величинами.
Табличний процесор Microsoft Excel надає користувачу широкі можливості по моделюванню подібних розрахунків. Для цього в ньому реалізовано спеціальний засіб Таблица подстановки.
Застосування таблиць підстановки дозволяє швидко розрахувати, переглянути і порівняти вплив на результат будь-якої кількості варіацій одного показника.
Приклад виконання практичного завдання «Аналіз чутливості фінансових показників в середовищі пакета Microsoft Excel»
Відомо, що підприємство займається виробництвом і продажем принтерів при наступних умовах:
Витрати праці на 1 принтер
|
35
|
Витрати на комплектуючі для 1 принтера
|
100
|
Витрати на рекламу
|
1000000
|
Кількість проданих принтерів
|
15000
|
Ціна принтера
|
249
|
Завдання:
Знайти загальний дохід і загальні витрати, а потім прибуток від продажу принтерів.
Варіюючи значення кількості проданих принтерів від 10000 до 20000 штук з кроком 2000 і, використовуючи таблицю підстановки, отримати значення прибутку.
На окремому аркуші створити точкову діаграму аналізу чутливості прибутку до кількості проданих принтерів.
Технологія використання інструменту «Таблиця підстановки»
Завантажити табличний процесор Microsoft Excel.
На Листі 1 ввести вхідні дані і розрахувати загальний дохід і загальні витрати (рисунок 1.5).
Рисунок 1.5 – Введення вхідних даних і розрахунок показників
Підготувати дані для таблиці підстановки (дивися рисунок 1.6).
Зверніть увагу на наступне:
чарунки, які містять варійовані значення та результати обчислень, повинні займати сусідні колонки або рядки. У нашому прикладі варійовані значення знаходяться в діапазоні D9:D14, а результати обчислень повинні будуть з'явитися в діапазоні чарунок E9:E14.
в першій чарунці стовпчика або рядка, що будуть містить результати обчислень, обов'язково повинна бути задана формула, яка зв'язує показники! Формула повинна прямо або опосередковано посилатися на одну й ту ж саму вхідну чарунку. У нашому прикладі формула знаходиться в чарунці E8 і побічно посилається на одну й ту ж саму чарунку B7 (кількість проданих принтерів).
Рисунок 1.6 – Підготовка даних для використання таблиці підстановки
Виконати розрахунок прибутку за допомогою таблиці підстановки:
Виділити діапазон чарунок D8: E14
Виконати команду Данные – Таблица подстановки
У вікні діалогу «Таблица подстановки» вибрати поле, як показано на рисунку 1.7, і вказати адресу чарунки B7, в яку Excel буде підставляти значення варьируемого показника.
Рисунок 1.7 – Вікно діалогу інструменту «Таблица подстановки»
Натиснути командну кнопку ОК. В результаті будуть отримані наступні результати (дивися рисунок 1.8, формульний вигляд цього документа - рисунок 1.9).
Рисунок 1.8 – Таблиця з підсумковими даними
Рисунок 1.9 – Формульний вид таблиці
На окремому листі створити точкову діаграму аналізу чутливості прибутку до кількості проданих принтерів (дивися рисунок 1.10).
Рисунок 1.10 – Графік залежності прибутку від кількості проданих принтерів
Використовуючи аналіз чутливості економічних показників за допомогою таблиці підстановки, дослідник може знайти граничні точки, в яких значення результуючого показника (у нашому випадку прибуток) дорівнює 0 і нижче яких підприємство буде зазнавати збитків.
Метод аналізу чутливості є хорошою ілюстрацією впливу окремих вхідних показників на результат. Він також показує напрямки подальших досліджень. Якщо встановлена сильна чутливість результуючого показника до змін деякого вхідного, останньому слід приділити особливу увагу.
Контрольні питання
Дайте поняття терміну «імітаційне моделювання»?
З яких етапів складається імітаційний експеримент?
Розкажіть про функцію СЛЧИС(): до якої категорії відноситься; призначення функції; структура функції.
Розкажіть про функцію НОРМОБР(): до якої категорії відноситься; призначення функції; структура функції.
З яких етапів складається аналіз чутливості показників?
Опишіть технологію використання інструменту «Таблиця підстановки».
Індивідуальні завдання за темою «Засоби пакета Microsoft Excel для імітаційного моделювання прийняття управлінських рішень
у сфері фінансів»
Варіант 1
-
Підприємство уклало з банком договір овердрафту. Побудуйте імітаційну модель кредитних відносин клієнта з банком. Розрахуйте суму овердрафту та процентний платіж по ньому, якщо відомо.
Назва показника
|
Значення показника
|
% овердрафта
|
35
|
Діапазон залишків грошових коштів на рахунку (млн.грн)
|
240 – 270
|
Діапазон терміну надходження грошових коштів на рахунок (дн)
|
10 - 13
|
Середня сума документів до сплати (млн.грн)
|
315
|
Стандартне відхилення суми документів до сплати (млн.грн)
|
45
|
-
Залишок грошових коштів на рахунку клієнта в банку - 270 млн грн. У банк поступили документи на оплату клієнтом угоди на суму - 315 млн грн. Відсоток овердрафту становить 35% річних. Надходження грошових коштів на рахунок клієнта відбувається через кожні 10 днів після оплати зазначеної угоди.
Визначити суму овердрафту.
Варіюючи значення залишків грошових коштів на рахунку від 200 до 300 млн.грн з кроком 20 млн.грн і, використовуючи таблицю підстановки, розрахуйте процентній платіж по овердрафту.
На окремому аркуші створити точкову діаграму аналізу чутливості процентного платежу по кредиту до залишку грошових коштів на рахунку.
Варіант 2
-
Банк «Форум» пропонує нову послугу для корпоративних клієнтів – факторинг. Підприємство уклало з банком угоду про надання факторингових операцій. Побудуйте імітаційну модель кредитних відносин клієнта з банком. Визначте ставку та суму плати за факторинг, якщо відомо:
Назва показника
|
Значення показника
|
Ставка по кредиту
|
25
|
Діапазон терміну оборотності коштів в розрахунках з покупцями (дн)
|
12 – 16
|
Середня сума документів до сплати (тис.грн)
|
300
|
Стандартне відхилення суми документів до сплати (тис.грн)
|
50
|
-
Ставка по кредиту становить 25% річних. Середній термін оборотності коштів в розрахунках з покупцями 14 днів. Постачальником надані рахунки фактури на суму 300 тис.грн.
Визначити ставку і суму оплати за факторинг.
Варіюючи значення середнього терміну оборотності коштів у розрахунках з покупцями від 10 до 16 днів з кроком 1 день і, використовуючи таблицю підстановки, розрахуйте процентній платіж по факторингу.
На окремому аркуші створити точкову діаграму аналізу чутливості процентного платежу по кредиту до середнього терміну оборотності коштів у розрахунках
Варіант 3
-
Побудуйте імітаційну модель ефективності інвестицій. Розрахуйте економічну рентабельність, диференціал фінансового важеля, плече фінансового важеля і ефект фінансового важеля, якщо відомо:
Назва показника
|
Значення показника
|
Діапазон суми прогнозованого прибутку від інвестицій (тис.грн)
|
200 – 400
|
Ставка податку на прибуток
|
20%
|
Діапазон величини ставки по кредиту у період інвестицій
|
12% – 18%
|
Діапазон суми інвестицій (млн.грн)
|
1,2 – 1,6
|
Середня сума позикових коштів (тис.грн)
|
500
|
Стандартне відхилення суми позикових коштів (тис.грн)
|
75
|
-
В результаті інвестування засобів в розмірі 1,5 млн.грн передбачається отримання прибутку в розмірі 300 тис.грн. Ставка податку на прибуток становить 20%, ставка по кредитам банку в період інвестицій дорівнює 12%. Розрахувати економічну рентабельність, диференціал фінансового важеля, плече фінансового важеля і ефект фінансового важеля якщо сума позикових коштів становить 500 тис.грн.
Визначити економічну рентабельність, диференціал фінансового важеля, плече фінансового важеля.
Варіюючи значення суми позикових коштів від 350 до 650 тис.грн з кроком 50 тис.грн і, використовуючи таблицю підстановки, розрахуйте ефект фінансового важелю.
На окремому аркуші створити точкову діаграму аналізу чутливості показника ефекту фінансового важелю до суми позикових коштів.
Варіант 4
-
Побудуйте імітаційну модель розрахунків за векселями. Визначте ставку дисконту, яка буде використовуватись банком при обліку векселя у разі передачі його в банк для обліку через 20 днів з моменту оформлення, а також суму, яку підприємство отримає від банку за обліковий вексель, якщо відомо:
Назва показника
|
Значення показника
|
Діапазон терміну погашення векселю (дн)
|
55 – 65
|
Діапазон величини ставки по кредитним операціям банку
|
18% – 22%
|
Середнє значення номінальної вартості векселю (тис.грн)
|
750
|
Стандартне відхилення номінальної вартості векселю (тис.грн)
|
100
|
Підприємство «Абсолют» реалізувало товар з відстрочкою платежу на умовах оформлення покупцем простого векселя номінальною вартістю 750 тис. грн. зі строком погашення 60 днів. Через 20 днів з моменту оформлення векселя підприємство передало його в банк для обліку. Процентна ставка за кредитними операціями комерційного банку складає 20%.
Визначити ставку дисконту, яка буде використовуватись банком при обліку векселя, а також суму, яку підприємство отримає від банку за обліковий вексель.
Варіюючи значення ставки по кредитним операціям комерційного банку від 18% до 24% з кроком 1% і, використовуючи таблицю підстановки, розрахуйте суму, яку підприємство отримає від банку за обліковий вексель.
На окремому аркуші створити точкову діаграму аналізу чутливості суми облікового векселю до величини ставки по кредитним операціям
Варіант 5
-
Побудуйте імітаційну модель ефективності інвестиційного проекту. Норма рентабельності інвестиційних витрат повинна бути не менше 80 відсотків. Визначте чистий прибуток інвестиційного проекту та оцініть його ефективність.
Назва показника
|
Значення показника
|
Діапазон суми амортизаційних відрахувань
|
4000 – 5000
|
Діапазон суми інвестиційних витрат
|
8500 – 12800
|
Діапазон суми податкових виплат
|
7500 – 8500
|
Середнє значення прибутку від операцій (тис.грн)
|
21108
|
Стандартне відхилення прибутку від операцій (тис.грн)
|
500
|
-
Норма рентабельності інвестиційних витрат повинна складати не менше 80 відсотків. Підсумкові показники такі: прибуток від операцій – 20000 тис.грн; амортизаційні відрахування – 4647 тис.грн; інвестиційні витрати – 9390 тис.грн; податкові виплати – 8105 тис.грн.
Визначте чистий прибуток інвестиційного проекту та оцініть його ефективність.
Варіюючи значення суми інвестиційних витрат від 7000 тис.грн до 10000 тис.грн з кроком 500 тис.грн і, використовуючи таблицю підстановки, розрахуйте показник норми рентабельності інвестицій.
На окремому аркуші створити точкову діаграму аналізу чутливості норми рентабельності інвестицій до величини інвестиційних витрат.
|