Побудова таблиці підстановки для двох змінних

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

Припустимо, що необхідно знайти щомісячні виплати по позиці розміром 300 тис. грн. для різних термінів погашення і процентних ставок. Щоб створити Таблицу подстановки для цієї задачі, необхідно виконати такі дії:

1. Ввести першу сукупність вхідних значень (процентні ставки) у колонку, наприклад, в клітинки В8:В13.

2. Ввести другу сукупність вхідних значень (терміни погашення) у рядок, розташований вище і правіше на одну клітинку від початку першого діапазону. У нашому прикладі це клітинки C7:F7.

3. Ввести формулу для розрахунку на перетині рядка і стовпця, що містять дві сукупності вхідних значень, тобто в клітинку В7. Якщо вхідні дані введені на робочому листі EXCEL так, як показано на рис. 7.6, то формула для розрахунку постійних періодичних виплат по позиці при повному її погашенні протягом терміну позики виглядає таким чином:

=ППЛАТ(С4/12, С3×12, С2).

Результат підготовки таблиці підстановки з двома змінними представлений на рис. 7.6.

Рис. 7.6. Підготовка таблиці підстановки з двома змінними

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

4. Далі необхідно виділити діапазон таблиці даних, що включає всі вхідні дані і формулу розрахунку, тобто B7:F13.

5. Виконати команду Данные Þ Таблица подстановки і заповнити діалогове вікно, як показано на рис. 7.7.

Рис. 7.7. Діалогове вікно таблиці підстановки для двох змінних

6. При натисканні кнопки OK EXCEL виконає розрахунок таблиці підстановки. Результати розрахунку наведені на рис. 7.8.

Рис. 7.8. Результат розрахунку таблиці підстановки з двома змінними

При зміні суми позики EXCEL автоматично перерахує всю таблицю.

7.5.3. Оцінка ефективності інвестицій на базі таблиці
  підстановки і функції НПЗ

Функція НПЗ призначена для знаходження чистої поточної вартості потоку майбутніх надходжень і витрат шляхом їх дисконтування. При цьому передбачається, що надходження і витрати можуть бути змінної величини, але відбуваються через рівні проміжки часу. Отримане значення являє собою абсолютний результат інвестиційної діяльності.

Оскільки розрахунок чистої поточної вартості зв'язаний з дисконтуванням, то найбільш важливим моментом тут є вибір ставки відсотка, по якій проводиться дисконтування. Існують різні методи визначення норми дисконтування. Тому при оцінці ефективності капіталовкладень важливо проаналізувати вплив різних процентних ставок на чисту поточну вартість проекту. В EXCEL такий розрахунок можна здійснити за допомогою Таблицы подстановки і функції НПЗ. Розглянемо наступний приклад.

Припустимо, що наприкінці року капіталовкладення по проекту складуть близько 1280 тис. грн. Очікується, що за наступні 4 роки проект принесе наступні доходи: 420, 490, 550, 590 тис. грн. Розрахуємо чисту поточну вартість проекту для різних норм дисконтування й обсягів капіталовкладень.

На робочому листі EXCEL вхідні дані представимо в такий спосіб (рис. 3.9.).

В клітинку D3 помістимо передбачувану величину початкових витрат по проекту (1280 тис. грн.) зі знаком "–", оскільки цей потік рухається протилежно очікуваним доходам. Це значення необхідно включити в список аргументів функції НПЗ, тому що чиста поточна вартість розраховується на початок року, а капіталовкладення, за умовою задачі, будуть здійснені наприкінці року. В клітинки C9:F9 помістимо різні обсяги капіталовкладень, як показано на рис. 3.9. Для розрахунку чистої поточної вартості візьмемо значення процентних ставок 13 %, 13.8 %, 15 %. У клітинку В9 за допомогою Мастера функций помістимо формулу для розрахунку:

Рис. 7.9. Вхідні дані задачі

=HП3(D2, D3, D4, D5, D6, D7).

Для побудови таблиці підстановки необхідно виділити діапазон клітинок B9:F12, у меню Дан ные вибрати команду Таблица подстановки, а діалогове вікно заповнити в такий спосіб:

Рис.7.10. Заповнення діалогового вікна для розв'язання задачі

Результати розрахунку наведені на рис. 7.11.

 

Рис. 7.11. Результати розрахунку

Отримані значення можна представити на графіку (рис. 7.12).

Рис. 7.12. Чиста поточна вартість проекту для різних
обсягів капіталовкладень і процентних ставок

 

Очевидно, що максимальна величина чистої поточної вартості досягається при мінімальних капіталовкладеннях і мінімальній ставці дисконтування. Аналізуючи отримані результати, можна відзначити, що деякі варіанти дають практично однакову величину чистої поточної вартості, наприклад, при капіталовкладеннях 1310 тис. грн. і нормі дисконтування 13.8 % досягається та ж величина NPV, що і при інвестиціях розміром 1270 тис. грн. і ставці 15 %.

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

 

⇐ Предыдущая1234567

Поделиться: