Звіт з лабораторної роботи
Інформаційні технології в антикризовому управлінні
1. Визначення вартості грошових коштів у часі
Завдання 1
Ваша компанія планує взяти кредит на суму 4 000 000 руб. на 6 років під 20% річних. Необхідно:
1. Визначити розмір виплат по кредитах, якщо: а) виплати здійснюються щорічно; б) виплати здійснюються щомісяця;
2. Розрахувати загальний обсяг виплат, суму переплати, а також порівняти її за двома варіантами виплат;
3. Скласти структуру виплат за щорічним платежах.
Рішення
Для початку вводимо в Excel вихідні дані для вирішення завдання, які приймуть наступний вигляд:
Таблиця 1. Вихідні дані
Сума кредиту | 4000 000,00 р. |
Ставка відсотка | 20% |
Термін погашення, років | 6 |
1. Для визначення обсягу щорічних виплат використовуємо функцію ПЛТ. Ця функція повертає суму періодичного платежу для ануїтету на основі сталості сум платежів і сталості процентної ставки: ПЛТ (ставка; кпер; пс; бс; тип).
Обсяг щомісячних виплат розраховується аналогічно, з урахуванням таких особливостей. Щоб отримати місячну відсоткову ставку, річну ставку необхідно розділити на 12. Щоб дізнатися кількість виплат, потрібно помножити кількість років кредиту на 12.
2. Для знаходження загальної суми, що виплачується протягом інтервалу виплат, необхідно помножити повертається функцією ПЛТ значення на «кпер».
І нарешті, обсяг переплати розраховується як різниця між обсягом загальної суми виплат за 6 років і сумою кредиту.
Отримані результати наведені в наступній таблиці:
Таблиця 2. Обсяг виплат по кредиту
Щорічна оплата | Щомісячна оплата | |
Обсяг виплат | 1202 822,98 р. | 95 811,30 р. |
Загальний обсяг платежів | 7216 937,90 р. | 6898 413,90 р. |
Переплата | 3216 937,90 р. | 2898 413,90 р. |
Як видно з табл. 2, обсяг переплати при щорічній виплаті кредиту значно перевищує відповідний показник при щомісячній оплаті.
3. Виплати за кредитом складаються з двох частин: виплат по основному боргу і виплат за відсотками.
Виплати по основному боргу в кожному з періодів визначаються за допомогою функції ОСПЛТ. Ця функція повертає величину платежу для погашення основної суми за інвестицією за даний період на основі постійних періодичних платежів і постійної відсоткової ставки: ОСПЛТ (ставка; період; кпер; пс; бс; тип).
Виплати за відсотками в кожному з періодів визначаються за допомогою функції ПРПЛТ. Ця функція повертає суму платежів відсотків за інвестицією за даний період на основі сталості сум періодичних платежів та сталості процентної ставки: ПРПЛТ (ставка; період; кпер; пс; бс; тип).
Залишок основного боргу в кожному з періодів визначається як різниця між сумою основного боргу на початок періоду та сумою виплати за основним боргом у розглянутому періоді.
Структура платежів по кредиту при здійсненні щорічних виплат представлена в наступній таблиці.
Таблиця 3. Структура щорічних виплат по кредиту
Період | Виплати по основному боргу | Виплати за відсотками | Залишок основного боргу |
0 | 4000 000,00 р. | ||
1 | 402 822,98 р. | 800 000,00 р. | 3597 177,02 р. |
2 | 483 387,58 р. | 719 435,40 р. | 3113 789,44 р. |
3 | 580 065,10 р. | 622 757,89 р. | 2533 724,34 р. |
4 | 696 078,12 р. | 506 744,87 р. | 1837 646,22 р. |
5 | 835 293,74 р. | 367 529,24 р. | 1002 352,49 р. |
6 | 1002 352,49 р. | 200 470,50 р. | 0,00 р. |
Згідно з даними табл. 3 на початкових періодах в структурі виплат переважають виплати за відсотками, тоді як у наступних періодах домінують виплати по основному боргу. Даний факт наочно проілюстровано на наведеному нижче рисунку:
Рис. 1 Структура щорічних виплат по кредиту в руб.
Чистий приведений дохід банку визначається за допомогою функції ПС. Ця функція повертає наведену (до поточного моменту) вартість інвестиції: ПС (ставка; кпер; ПЛТ; бс; тип). Наведена (нинішня) вартість являє собою загальну суму, яка на даний момент рівноцінна ряду майбутніх виплат. Наприклад, в момент позики його сума є наведеної (нинішньої) вартістю для позичальника. З урахуванням інфляції в 15% чистий приведений дохід банку в даній задачі буде становити 4552 062,76 р.
Завдання 2
Ви плануєте взяти в борг у одного 700 000 крб. і збираєтеся виплачувати щомісячно 30 000 руб. Скільки місяців займе виплата боргу, якщо ставка відсотка становить 15% річних?
Рішення
Вводимо в Excel вихідні дані для вирішення завдання, які приймуть наступний вигляд:
Таблиця 4. Вихідні дані
Сума кредиту, руб. | 700 000,00 р. |
Ставка,% | 15,00% |
Щомісячна виплата, руб. | 30 000,00 р. |
Термін погашення, місяців | ? |
Термін погашення кредиту розраховується за допомогою функції Кпер. Ця функція повертає загальна кількість періодів виплати для інвестиції на основі періодичних постійних виплат і постійної відсоткової ставки: Кпер (ставка; ПЛТ; пс; бс; тип). Вказавши на клітинки, які містять суму кредиту, процентну ставку і суму щомісячних виплат, одержуємо, що термін погашення кредиту дорівнює 28 місяців.
Завдання 3
Ви плануєте зарезервувати кошти для проекту, який буде здійснено через 3 роки. Для цього ви відкриваєте депозитний рахунок з початковим внеском 300 000 руб. і потім вносите додатково 20 000 руб. на початку кожного наступного місяця. Процентна ставка банку - 17%. Яка сума буде накопичена до початку реалізації проекту?
Рішення
Вихідні дані задачі приймуть наступний вигляд:
Таблиця 5. Вихідні дані
Початковий внесок | 300 000р. |
Додатковий щомісячний внесок | 20 000р. |
Ставка,% | 17,00% |
Термін депозиту, років | 3 |
Майбутня вартість депозиту | ? |
Майбутня вартість депозиту визначається за допомогою функції БС. Ця функція повертає майбутню вартість інвестиції на основі періодичних постійних (рівних по величині сум) платежів і постійної відсоткової ставки: БС (ставка; кпер; ПЛТ; пс; тип). Використовуючи дані задачі, отримуємо майбутню вартість депозиту, рівну тисячі чотиреста сорок-один 825,55 р.
2. Визначення показників діяльності мережі магазинів «Насолода»
Завдання 1
Ви - керуючий мережі магазинів, маєте інформацію про виручку кожного з 6 магазинів за 4 квартали. Необхідно визначити:
Обсяг виручки мережі за квартал;
Суму виручки і середню виручку кожного магазину за рік;
Частку кожного магазину в сумарній виручці за рік і ранг магазину в залежності від частки;
Кількість магазинів, що входять в той чи інший діапазон в залежності від суми виручки;
Розмір премії кожного магазину, що становить 10% від сумарної річної виручки магазину за умови перевищення середньої виручки за квартал на 20 000 руб. При цьому премія директору за 1-е місце - 30 000 руб., За 2-е місце - 15 000 руб., За 3-е місце - 10 000 руб.;
Зобразити тренд річної виручки мережі магазинів і спрогнозувати сумарну виручку мережі на 2 квартали наперед.
Мережа магазинів «Насолода займається виробництвом і продажем кондитерських виробів. Вихідні дані представлені в наступній таблиці:
Таблиця 6. Виручка мережі магазинів "Насолода"
Назва магазину | 1 квартал, руб. | 2 квартал, руб. | 3 квартал, руб. | 4 квартал, руб. |
"Солодке життя" | 100 000,00 | 80 000,00 | 95 000,00 | 100 000,00 |
"Райська насолода" | 50 000,00 | 65 000,00 | 70 000,00 | 80 000,00 |
"Смак" | 75 000,00 | 55 000,00 | 60 000,00 | 70 000,00 |
"Медовик" | 90 000,00 | 95 000,00 | 100 000,00 | 120 000,00 |
"Наполеон" | 110 000,00 | 75 000,00 | 80 000,00 | 90 000,00 |
"Казки Шахерезади" | 200 000,00 | 100 000,00 | 120 000,00 | 110 000,00 |
Рішення
1. Для визначення загального обсягу мережі за квартал використовуємо Автосуммирование значень по стовпцях табл. 6
2. Сума виручки кожного магазину за рік розраховується Автосуммирование відповідних значень по рядках табл. 6. Середня виручка кожного магазину розраховується за допомогою функції СР означає, що повертає середнє значення виділених клітинок.
3. Частка кожного магазину в сумарній виручці розраховується, як відношення річної виручки магазину до сумарної виручки мережі. Ранг магазину визначається за допомогою функції РАНГ. Ця функція повертає ранг числа у списку чисел. Ранг числа - це його позиція щодо інших значень у списку.
Результати рішення п.1 - 3 даного завдання представлені в табл. 7:
Таблиця 7
Назва / Квартали | 1 |