Після закінчення виконання роботи студент повинен. ЗНАТИ: яким чином працює команда Подбор параметраменю Сервис.

ЗНАТИ: яким чином працює команда Подбор параметра меню Сервис.

ВМІТИ: вирішувати конкретні економічні задачі підбору параметрів за допомогою засобів MS Excel.

МАТИ УЯВЛЕННЯ: до яких задач можливе застосування команди Подбор параметра.

Завдання: Виконати розрахунок щомісячних виплат за кредитом на придбання техніки.

Вартість товару – 2000+N*500 грн (N – номер студента за списком).

Термін погашення кредиту – 12 місяців.

Початковий внесок – не менше 30%.

Комісія банку – 0,05% від вартості кредиту.

Річний відсоток – 25%.

Страхування товару – 0,1%.

Підібрати таке значення початкового внеску, щоб розмір щомісячного погашення кредиту не перевищував 200 грн.

Щомісячний внесок обчислюється за формулою:

Щомісячний внесок = сума кредиту/термін кредиту

Відсотки за кредитом обчислюються за формулою:

Відсотки за кредитом = залишок кредиту*(річний відсоток/12)

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

Для виконання підбору параметра потрібно виконати дії, які надані на рис.2.1.

Перший етап. Введення початкових даних:	Другий етап. Виконання початкових розрахунків.

Рис.2.1. Формування початкових даних

В таблиці 2.1. наведені формули для початкових розрахунків.

Таблиця 2.1.

 

 

За умовою задачі, початковий внесок повинен становити не менше 30%. Використовуємо умовне форматування для комірки „початковий внесок” за допомогою підменю „ Условное форматирование ” меню Формат. Якщо значення комірки буде менше 30% визначимо зміну фону кольору, для відображення помилки.

Третій етап. Розрахунок щомісячних виплат.

Після введення початкових даних та виконання початкових розрахунків переходимо до знаходження щомісячних виплат за кредитом. Формули для розрахунку щомісячних виплат за кредитом надані у таблиці 2.2.

Таблиця 2.2.

Якщо при розрахунку даних ми отримуємо значення, що дорівнює 0, то виконуємо умовне форматування до комірки. Значення у комірці дамо білого кольору, щоб не було відображене на екрані. Отримаємо значення, які надані на рис.2.2.

Рис.2.2. Розрахунок щомісячних виплат за кредитом

Для підбору необхідних параметрів, виконуємо наступні дії:

1. Виділити комірку, яка містить формулу обчислення результату. (щомісячний внесок, у нашому випадку це комірка G4)

2. Викликати команду Сервис/Подбор параметра, після чого відкриється діалогове вікно Подбор параметра. У полі Установить в ячейке автоматично вставляється адреса виділеної комірки.

3. У поле Значение вікна Подбор параметра потрібно ввести цільове значення результату (за умовою завдання це значення = 200). Потім слід перемістити курсор у поле Изменяя значение ячейки та виділити на робочому аркуші комірку, у якій зберігається відшуканий параметр (початковий внесок у %, у нашому випадку це – B4). Див. рис.2.3.

Рис.2.3. Виконання Подбора параметра

4. Після виконання всіх установок у вікні Подбор параметра слід натиснути кнопку ОК, унаслідок чого почнеться пошук потрібного значення. Результат розрахунку буде відображено в наступному діалоговому вікні Результат подбора параметра. Після натискання ОК у зазначеному вікні розраховане значення буде розташоване у таблиці підбору. Отримані результати подано на рисунку 2.4.

Рис.2.4. Результат виконання Подбора параметра при заданих умовах

5. Якщо пошук потрібного значення триває надто довго, то його можна тимчасово припинити за допомогою кнопки Пауза. Кнопка Шаг даєможливість переглянути проміжні результати розрахунку.