Задача 10. Аналіз кредиту

Підприємець бере кредит на деяку суму під місячну ставку 6% і, зважаючи на свою щомісячну платоспроможність, повинен прийняти рішення, на скільки місяців його брати. Побудувати таблицю щомісячних виплат для різних термінів кредиту, наприклад, 4, 5, 6, 7 місяців і реальних сум, що повинні бути виплачені за кредит протягом усього терміну. Побудувати двовимірну таблицю щомісячних виплат з ура­хуванням двох параметрів: можливих сум позики і термінів позики.

Планування випуску продукції. Задача 9 є задачею ліній­ного програмування. Вона розв'язується за допомогою інстру­мента Пошук розв'язку (SOLVER).

Математична модель задачі. Позначимо через х, у, z шукані кількості виробів трьох видів. Потрібно визначити х, у, z, для яких досягається максимум функції прибутку f = 9х + 10y+ 16z за таких обмежень:

18х +15у + 12z< = 360 – п

6х + 4у + 8z < = 192

5х +3у + 3z < = 180 + п

х, у, z > = 0;

х, у, z — цілі.

Розв'язування. Потрібно виконати такий алгоритм:

1) клітинкам А1, В1, С1 присвоїти імена х, у, z командами Вставити - Ім'я -Присвоїти => Ввести х => ОК і т.д.;

2) у клітинку D1 ввести формулу =9*х+10*у+16*z;

3) запустити програму Пошук розв'язку з меню Сервіс, для цього потрібно натиснути Файл – Параметри - Надстройки, виділити Пошук розв'язку та перейти, нажати кнопку ОК.

 

4) задати адресу цільової клітинки D1 і зазначити дію до­сягнення максимуму функції (рис. 46);

5) задати клітинки, де має міститися розв'язок: х; у; z;

6) за допомогою кнопки Додати додати обмеження (рис. 47) у вигляді шести нерівностей (значення п підставити конкретне):

х < = (360 - п - 15*у - 12*z)/18

у < = (192 - 6*х - 8*z)/4

z < = (180 + п - 5*х - 3*y)/3

х > = 0; у > = 0; 2 > = 0

х — ціле; у — ціле; z — ціле;

 

Рисунок 46- вікно програми Пошук розв’язку для задачі 8

 

7) натиснути на кнопку параметри і задати, що модель лінійна;

8)отримати розв’язок, натиснувши на кнопку Виконати.

Для n=0 відповідь (у клітинках А1, B1, C1, D1) така: x=0, y=8, z=20, f=400.

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

На прикладі розв'язування задачі 10 розглянемо вплив зміни одного параметра (кількості періодів позики) на дві величини щомісячну і сумарну виплати за кредит.

Основною формулою розв'язування задачі є =ПЛТ(ПС, КП; сума кредиту), яку розглядали в попередній роботі. Виконайте такий алгоритм (рис. 48).

1. У діапазон А1: АЗ введіть вхідні дані: ставку (6%), кількість періодів (4) і суму кредиту, наприклад 3000.

2. У діапазон А5: А8 введіть можливі терміни позики: 4, 5, 6, 7.

3. У клітинку В5 введіть формулу =ПЛТ(А1; А2; АЗ). У клітинку С5 введіть формулу =В5*А2. Ці формули мають бути першими у своїх стовпцях.

4. Виокремте діапазон А5: С8 і застосуйте команду Дані - Таблиця підстановки (Таblе...). Параметром у цій задачі є кіль­кість періодів з клітинки А2. Тому в отриманому діалоговому вікні у друге поле Підставляти значення по рядках введіть А2. Натис­ніть на кнопку ОК. Отримаєте таблицю, придатну для прийняття рішень. Який термін позики вам найбільше підходить?

Для аналізу щомісячних виплат, залежних від двох пара­метрів (можливих сум і термінів позики), таблицю будують так: у клітинку Б1 вводять формулу =ПЛТ(А1; А2; АЗ). Клітинки праворуч Е1: Н1 заповнюють деякими можливими сумами пози­ки: 2000, 2500, 3000, 3500, а клітинки знизу (Б2: Б5) - можливими термінами 4, 5, 6, 7 місяців. Вибирають прямокутний діапа­зон D1-Н5 і виконують команду Дані => Таблиця підстановки. В отриманому діалоговому вікні у перше поле вводять АЗ, а в друге — A2. Отримаємо таблицю, аналіз якої дає змогу вибрати суму і термін позики, враховуючи щомісячну платоспроможність підприємця.

Рисунок 49-зразок розв’язування задачі 10

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

Розв'яжемо задачу 11. За зразком задачі № 1 про товар­ний чек створіть таблицю про купівлю шести видів товарів з такими назвами стовпців: Назва, Ціна, Кількість, Вартість (рис. 49). Заповніть таблицю довільними даними: конкрет­ними назвами товарів, цінами за одиницю кожного товару, кількостями кожного товару. Введіть формулу для обчислення вартості кожного товару Ціна*КІЛЬКІСТЬ і скопіюйте її в діапазон Вартість. Виберіть клітинку під цим діапазоном і натисніть на кнопки Автосума і вводу — отримаєте шукану сумарну вартість покупки. Отже, ця клітинка міститиме результат, що залежить від параметрів.

Щоб проаналізувати чотири варіанти покупки для різних кількостей товарів, застосуйте інструмент Сценарії. Виконайте: Команда Дані – Аналіз < => отримаєте вікно Диспетчер сценаріїв. За його допомогою можна додавати новий сценарій під деякою назвою до множини сценаріїв, вилучати невдалий чи редагувати його, виводити на екран результати застосування сценарію, створювати звіт за всіма сценаріями, скористатися сценаріями, створеними на інших сторінках. Натисніть на кнопку Додати і у новому вікні введіть назву першого сценарію, наприклад vаr1, зазначте діапазон клітинок, що містять параметри, які досліджуються (у нашому випадку — це клітинки зі стовпця Кількість, наприклад С2: С7) і натисніть на кнопку ОК. Отримаєте вікно зі значеннями клітинок – параметрів стартового варіанта покупки. Їх значення не змінюйте, натисніть ОК. Додайте новий сценарій з назвою vаr2, але значення клітинок-параметрів тепер поміняйте довільним чином.

 

Рисунок 50 – крок 1 «Зміна сценаріїв»

Рисунок 51 – крок 2 «Значення комірок сценарію» для відповідного варіанту

Рисунок 52 – крок 3 «Перелік встановлених сценаріїв»

 

Таким способом створіть усі чотири сценарії (рис. 50). Розглянемо, як використовувати створені сценарії. У вікні Диспетчер сценаріїв вибирайте по черзі назви сценаріїв і натискайте на кнопку Вивести — стежте за результатами обчислень згідно з цим сценарієм і переписуйте у свій звіт сумарні вартості покупок. Для якого сценарію сумарна вартість найбільша?

Рисунок 53-Сюжет одного сценарію

Нові варіанти сценарію (рисунок 50) заповнюються за даними рисунка 51

 

Результати застосування всіх сценаріїв можна подати у вигляді звіту. Для цього у вікні Диспетчер сценаріїв натисніть Н кнопку Звіт і в новому вікні виберіть тип звіту: Структура, вкажіть клітинку-результат (клітинку зі значенням сумарної " " і пості покупки) і натисніть на кнопку ОК. Звіт отримаєте на окремій сторінці. Перегляньте його і проекспериментуйте з кнопками «+» і «-» ліворуч, які дають змогу згортати чи розгортати певного звіту. Який варіант покупки вам найбільше підходить?

 

Рисунок 54- структура сценарія

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

Щоб розв'язати задачу 12, виберіть створену таблицю і вико­найте команди Вставка- Зведена таблиця. Виділіть дану таблицю, натисніть кнопку зведена таблиця, де створюється структура зведеної таблиці та виберіть вам потрібні дані.

 

Рисунок 55-зразок розв’язування задачі 12 (основна і зведена таблиця)

 

Щоб модифікувати зведену таблицю, знову виконайте команди Вставка - Зведена таблиці і перетягніть поле Дата в поле структури з назвою Стовпець – тепер дати з основної таблиці стануть заголовками стовпців у зведеній. У зведеній таблиці поле продавець є полем-списком. Якщо вибрати у ньому конкретне прізвище, зведена таблиця продемонструє ефективність роботи цього продавця протягом деякого періоду за критерієм кількості і сумарної вартості продажу.

Деякі задачі аналізу можна розв’язати іншим способом за допомогою функції СУММЕСЛИ. Функція обчислює суму тих значень з діапазону 2, для яких відповідні значення з діапазону 1 задовольняють умову. Наприклад, щоб визначити суму виторгу продавця Дацка, у деяку клітинку потрібно ввести формулу =СУММЕСЛИ(Покупець; «Деол»; Кількість). Застосуйте подібним способом цю функцію у своїй практичній роботі.