На даному уроці ми навчимося практично застосовувати обчислювальний інструмент «Підбір параметр» в Excel. Спеціально для Вас підготовлено 3 практичних приклади, які можуть стати в нагоді Вам вже сьогодні.
Підбір параметра для банківських депозитів
Протягом 10-ти років ми хочемо накопичити 20 000 $. Свої заощадження будемо відкладати на банківський депозит по 5% річних. Гроші будемо вносити на банківський депозитний рахунок щорічно і однаковими частинами внесків. Який повинен бути розмір щорічного внеску, щоб за 10 років зібрати 20 000 $ при 5-т і відсотках річних?
Для вирішення даного завдання в Excel скористаємося інструментом «Підбір параметра»:
- Складіть таблицю як показано на малюнку:
- У осередок B5 введіть функцію: = БС (B1; B2; B3;)
- Залишаючись на осередку B5, виберіть інструмент: «Дані» - «Робота з даними» - «Аналіз що якщо» - «Підбір параметра»
- У вікні заповніть поля, так як на малюнку і натисніть ОК.
Корисна порада! Якщо Ви потрібно дізнатися розмір щомісячних внесків, тоді перед використанням інструменту «Підбір параметра» потрібно процентну ставку розділити на 12 (щоб перевести в щомісячний відсоток).
А кількість років потрібно перевести в кількість місяців помноживши на 12. Таким чином, в осередку B3 ми отримаємо необхідну суму щомісячного внеску для досягнення мети.
Пошук рішень підбором параметра при ціноутворенні
Стратегія для побудови виробничого плану випуску продукту:
- У поточному році продукт повинен бути проданий в кількості 10 000 шт.
- Виробничі витрати 1-ої штуки: 7,5 руб.
- Витрати на реалізацію: 450 000 руб.
Яку встановити роздрібну ціну, щоб рентабельність виробництва зберігалася на рівні 20%?
Рентабельність визначається як співвідношення прибутку до прибутку (прибуток розділити на дохід) і виражається тільки в процентах!
Знову вирішимо поставлену задачу в Excel за допомогою підбору параметра:
- Складіть таблицю з вихідними даними і формулами, так як вказано на малюнку нижче. Зверніть увагу! У стовпці D вказані, які саме потрібно вводити формули в відповідні комірки стовпчика B. А в осередку B1 вказана ціна 1 руб. щоб уникнути помилок у формулі B3 і B10 (ймовірна помилка ділення на 0). Не забудьте відформатувати всі осередки відповідним форматам: грошовий, загальний, процентний.
- Перейдіть в осередок B10 і виберіть інструмент: «Дані» - «Робота з даними» - «Аналіз що якщо» - «Підбір параметра»
- Заповніть поля в діалоговому вікні як на малюнку і натисніть ОК.
Як видно роздрібну ціну (B1) потрібно встановлювати в 2 рази вище виробничих витрат на 1-ну одиницю продукції. Тільки тоді ми зможемо утримати рентабельність виробництва на рівні 20% при таких витратах на реалізацію. В реальності буває і ще гірше.
Підбір параметра для банківських кредитів
Припустимо, Ви хочете придбати автомобіль в кредит. Максимальна сума щомісячного внеску, яку Ви можете собі дозволити, становить 700 $. Банк не може видати Вам кредит строком більш ніж на 3 роки, з процентною ставкою 5,5% річних. Чи можете ви собі дозволити при таких умовах кредитування придбати автомобіль вартістю в 30 000 $, а якщо немає, то на яку суму можна розраховувати?
Складіть таблицю умов кредитування в Excel як показано нижче на малюнку. Зверніть увагу! Осередок B4 містить формулу: = -ПЛТ (B3 / 12; B2; B1).
Як видно Ви не можете собі дозволити такий дорогий автомобіль. Тепер дізнаємося, яка максимальна вартість автомобіля відповідає Вашим фінансовим можливостям. Для цього перейдіть в осередок B4 і виберіть інструмент: «Дані» - «Робота з даними» - «Аналіз що якщо» - «Підбір параметра».
Заповніть поля в діалоговому вікні як показано вище на малюнку і натисніть ОК.
Як видно максимальна вартість автомобіля, на яку можна розраховувати становить при таких фінансових можливостях і умовах кредитування становить - 23 1812 $.
Увага! Якщо термін кредитування визначається кількістю місяців, а не років, то річну відсоткову ставку потрібно перевести в місячну. Тому в першому аргументі функції ПЛТ варто значення B3 / 12 (5,5% річних розділене на 12 місяців).
Використовуючи фінансові функції, слід пам'ятати про їх стандартах. Наприклад, сума позики завжди відображаються як негативне число. Тому перед функцією ПЛТ ми використовували знак мінус.