Підбор параметру в Excel 2010 при прийнятті оптимальних рішень в банківській діяльності Досягнення оптимал
Работа добавлена на сайт samzan.net:
3.5 Використання надбудови «Підбор параметру» в Excel 2010 при прийнятті оптимальних рішень в банківській діяльності
Досягнення оптимального, найкращого результата є метою при прийнятті будь-яких управлінських рішень, тому вміння використовувати спеціальні можливості EXCEL для реалізації економічних завдань є важливим інструментом в руках кожного економіста.
Зокрема, для рішення широкого спектру завдань банківської діяльності можна застосовувати спеціальне програмне розширення, яке містять електронні таблиці Excel – аналіз «Що, коли», надбудова “Підбор параметру”. На прикладi чотирьох варіантів однiєї економiчної задачі розглянемо можливостi цієї надбудови пошуку оптимальних рiшень.
В першій задачі визначимо оптимальний банківській відсоток при інших фіксованих даних; в другій задачі – при фіксованому банківському відсотку визначимо оптимальну суму першого платежу; в третій задачі потрібно розрахувати оптимальний відсоток, на який змінюється кожний наступний платіж, при фіксованих сумі першого вкладу та відсотку банку, і нарешті в четвертій задачі потрібно розрахувати цільову функцію – оптимальну кількість місяців, впродовж яких вкладаються кошти.
В процесі пошуку оптимальних рішень економічних задач засобами процесора Excel стає зрозумілим, що особливістю електронних таблиць є те, що в них структурування інформації необхідно починати безпосередньо на етапі введення даних: із самого початку свого існування в машинній формі їх треба прив'язати до структурних підрозділів таблиці – комірок.
Хід роботи
Для того, щоб вивчити різні можливості використання надбудови Підбор параметру розглянемо прості банківські задачі.
Задача 1. Визначити, при якій щомісячній відсотковій ставці можна за рік накопити 5 000 грн., якщо вносити кожного місяця платіж на 5% більше попереднього, почавши з першого платежу 200 грн.
Для рішення цього завдання змоделюємо реальний потік щомісячних платежів, потім знайдемо накопичену під кінець року суму, з врахуванням того, що банк нараховує відсотки на кожен платіж за передбачуваною ставкою на відповідне число відсоткових періодів (перший платіж на 12 місяців вперед, другий – на 11 і т.д.); потім за допомогою Підбора параметру знайдемо істинне значення відсоткової ставки.
- На першому етапі рішення заповнюємо стовпчик А – надрукуємо назви місяців і стовпець В – вкажемо номер місяця по черзі платіжного періоду;
- потім в комірку С4 вносимо значення першого платежу 200, в комірку С5 вводимо формулу =С4*105% (тобто на 5% більше значення в С4) і копіюємо цю формулу по стовпцю С (підвівши курсор до правого нижнього кута комірки, натискаємо лівою клавішею миші і протягуємо по стовпчику С), як показано на рис.3.5.1. Рис.3.5.1
З кожним щомісячним вкладом платежів кількість періодів, які банк враховує для нарахування фіксованого (постійного) відсотка, зменшується.
- Відобразимо цю тенденцію зменшення періодів в стовпці D:
перший платіж - банківський відсоток нараховується на 12 місяців,
другий платіж – на 11 місяців і т.д (рис. 3.5.2).
Для того щоб розрахувати суму вклада, який періодично поповнюється та на нього нараховується постійний банківський відсоток, скористаємося вбудованою функцією для розрахунку майбутньої вартості БС (меню “Формули”/ Фінансові / БС).
- В комірці Е4 встановлюємо вбудовану функцію БС (рис. 3.5.3):
ставка – відсоток банку за період (оскільки це невідомий, розшукуваний параметр, то можемо в комірку F4 встановити будь-яку величину ставки у вигляді індекса, наприклад, 2% = 0,02);
Кпер – загальна кількість періодів нарахування відсотків на суму платежу;
Пс – сума внесеного в цьому періоді вкладником платежу вираховується, тому зі знаком “ – “.
Рис.3.5.3. Вікно фінансової функції БС для розрахунку майбутньої вартості вкладу
Рис.3.5.2
- Отримаємо результат і копіюємо цю формулу по стовпчику Е.
- В комірці Е16 підрахуємо суму значень по стовпчику Е (кнопка - автосума на панелі інструментів) як показано на рис.3.5.2.
Тепер за допомогою функції Підбор параметру з меню “Сервіс” одержимо розшукуєме значення ставки банку (вміст комірки F4), при якому вкладник може накопичити на кінець року 5000 грн.
- Встановлюємо в комірку Е16, в якій знаходиться сума всіх платежів з нарахованими відсотками;
- вводимо значення, тобто суму, яку хочемо накопити на рахунку: 5000 грн.;
- змінючи значення комірки F4, в якій відображується величина банківської ставки (поки що це будь-який відсоток, обраний нами навмання).
Натискаємо кнопку “Оk” та отримуємо наступне повідомлення про знайдений результат:
Результат рішення задачі – значення процентної ставки, яке дорівнює 0,0737694 або 0,07 (рис.3.5.4).
Рис.3.5.4
Отже, саме при величині банківської ставки 7% на кінець року вкладник може накопичити 5000 грошових одиниць, а також якщо буде вносити кожного місяця платіж на 5% більше попереднього, почавши з першого платежу 200 грн.
Тепер змінемо умови задачі (цільову функцію) та покажемо, як зміниться рішення.
Задача 2 Визначити, яку суму першого платежу необхідно вкласти в банк, якщо вносити кожного наступного місяця платіж на 5% більше попереднього,для того, щоб за рік накопичити 5 000 грн. Щомісячна відсоткова ставка банку 7%.
Оскільки сума першого платежу в цій задачі нам невідома, в комірку С4 ставимо 0. Отримуємо результати змінених даних, представлені на рис.3.5.5.
Рис.3.5.5
Тепер за допомогою надбудови Підбор параметру з вкладники горизонтального меню “Дані”/ Аналіз «Що, коли» одержимо розшукуєме значення першого платежу (вміст комірки С4), при якому вкладник може накопичити на кінець року 5000 грн.
- встановлюємо в комірку Е16, в якій знаходиться сума всіх платежів з нарахованими відсотками;
- вводимо значення, тобто суму, яку хочемо накопити на рахунку: 5000 грн.;
- змінючи значення комірки С4, в якій відображується величина першого платежу (поки що це просто 0).
Результат рішення задачі – заповнена комірка С4 = 200, а отже одразу можно побачити й розміри наступних платежів (рис. 3.5.6).
Рис.3.5.6
Отже, саме при величині першого внеску 200 грн., із збільшенням кожного місяця платежу на 5% більше попереднього, при величині банківської ставки 7% на кінець року вкладник може накопичити 5000 грн.
Таким чином, в першій задачі метою є визначення банківського відсотка при інших фіксованих даних, в другій – при фіксованому банківському відсотку визначення суми першого платежу.
Припустимо тепер, що фіксованими є сума першого вкладу та відсоток банку, а невідомий відсоток, на який змінюється кожний наступний платіж (цільова функція).
Задача 3 Визначити, на який відсоток необхідно вкладнику збільшувати свої щомісячні платежі на рахунок в банку, якщо сума першого платежу – 200 грн., та щоб за рік накопичити 5 000 грн., щомісячна процентна ставка банку – 7%.
Щоб вирішити цю задачу, необхідно створити комірку, яка б містила в собі значення розшукуємого відсотка збільшення платежів, нехай це буде комірка G4 (рис.3.5.7).
- Далі змінемо формули в комірках стовпця С. В комірку С5 введемо формулу =С4 х $G$4 та копіюємо її на решту комірок стовпця С (рис.3.5.7).
- Позначка $ в комірці $G$4 означає, що вказана абсолютна адреса комірки, тобто при копіюванні формули в інші комірки значення цієї комірки не буде змінюватись.
Рис.3.5.7
Тепер за допомогою вкладинки меню Дані, кнопки Аналіз «що, коли» / Підбор параметру одержимо розшукуєме значення відсотка, на який необхідно вкладнику збільшувати свої щомісячні платежі на рахунок в банку, (вміст комірки G4), при якому вкладник може накопити к кінцю року 5000 грн.
- встановлюємо в комірку Е16, в якій знаходиться сума всіх платежів з нарахованими відсотками;
- вводимо значення, тобто суму, яку хочемо накопити на рахунку: 5000 грн.;
- змінючи значення комірки G4, в якій відображується значення відсотка, на який необхідно вкладнику збільшувати свої щомісячні платежі (поки що це просто 0).
В результаті отримуємо рішення задачі (рис.3.5.8): індекс збільшення наступного платежу в порівнянні з попереднім – 1,05, тобто значення відсотка, на який необхідно вкладнику збільшувати свої щомісячні платежі - 5% .
Рис.3.5.8
Розв’яжіть самостійно задачу 4. Це варіант тієї самої задачі, але тепер розшукуєме значення – цільова функція – це кількість місяців, впродовж яких інвестують кошти.
Задача 4. На який період, на яку кількість місяців потрібно вкласти гроші, почавши з першого платежу 200 грн. та вносячи кожен місяць платіж на 5% більше попереднього, щоб накопичити 5 000 грн., щомісячна процентна ставка банку – 7%.
2. Практика показывает что написание цифр вызывает у школьников определённые трудности
3. Подари Рождество детям Место проведения акции- ТЦ КоронаУручье Дата- 21 декабря 2013 Продукты питания
4. а СТИЛЬ РУКОВОДСТВА в контексте управления это привычная манера поведения руководителя по отношению к п
5. B рис. 1. Между непрозрачными штрихами имеются одинаковые прозрачные щели ширины
6. варианты перевода
7. Задание 1 Определить де бройлеровскую длину волны протона кинетическая энергия которого равна энергии поко
8. В эпоху диктатуры и окруженные со всех сторон врагами мы иногда проявляли ненужную мягкость ненужную мягко
9. Работа с данными
10. Гален
11. Признак характерный для эпителиальной ткани 1 отсутствие базальной мембраны 2 низкая способность к
12. Приморская двигаться налево 680м в конец дома ~ 1015 минут пешком или 2 остановки на автобусе ежедневно в
13. От чего мы болеем
14. а Вищу освіту здобув у КиєвоМогилянській академії де почав писати власні твори
15. Реферат на тему- ldquo;Про графічний спосіб розв~язання задачrdquo; Нова програма з математики орієнтує вчите
16. Программы вступительных экзаменов по иностранным языкам в 2004г (МГУ)
17. Специфические черты культуры ХХ века- общая характеристика 2
18. темами своєю неповторною манерою письма зі своїм оригінальним підходом до традиційних тем
19. Реферат- Общественный контроль за деятельностью уголовно-исполнительной системы
20. а Возникновение электродного потенциала обусловлено переносом заряженных частиц через границу раздела ф