Функции Excel для финансовых расчетов по ценным бумагам (стр. 2 из 2)
Величина амортизации актива методом двойного уменьшения остатка за 14–23 кварталы – 0 грн. и за 45–66 месяц – 0 грн.
Величина амортизации актива методом фиксированного уменьшения остатка за 2‑й год и 5‑й год, если только в течение 11 месяцев в году происходит амортизационное отчисление.
За 2‑й год – ФУО (3000; 1800; 6; 2; 11) = 227509 грн.
За 5‑й год – ФУО (3000; 1800; 6; 5; 11) = 176006 грн.
2. Необходимо накопить 5600 тыс. грн. за два года, откладывая постоянную сумму в конце каждого квартала. Какой должна быть эта сумма, если норма процента по вкладу составляет 17% годовых.
=ПЛТ (17%/4; 8; 0; 5600; 0) = 602,36 грн.
Задание 3. Сортировка. Фильтрация. Команда итоги. Анализ данных с помощью диаграмм
Таблица Б.13 – Структура доходов коммерческого банка
| Статьи доходов | тыс. грн. | % к итогу |
| Начисленные и полученные проценты | 100354 | NPP% |
| Плати за кредитные ресурсы | 18157 | PKR% |
| Комиссионные за услуги и корреспондентские отношения | 37649 | KUKO% |
| Доходы по операциям с ценными бумагами и на валютном рынке | 3427 | DOCB% |
| Доходы от лизинговых операций | 512 | DLO% |
| Доходы от участия и деятельности предприятий, организаций и банков | 1973 | DUD% |
| Плата за юридические услуги | 2136 | PUU% |
| Итого: | 100% |
1 Выполнить сортировку документа по возрастанию наименований статей доходов коммерческого банка.
2 Выполнить сортировку таблицы в алфавитном порядке по наименованиям статей доходов и по убыванию % к итогу.
3 Выполнить фильтрацию сформированного документа, оставив в нем только статьи доходов, значения сумм в тыс. грн. которых больше среднего значения. Вернуть таблицу в исходное состояние.
5 С помощью средства Расширенный фильтр сформировать новый документ, в который поместить только те статьи доходов коммерческого банка, сумма каждой из которых меньше среднего значения этого показателя по всему исходному документу или равна 100 354 тыс. грн.
6 Добавьте в ведомость новую графу«Вид дохода», присвоить вид – «По операциям», для доходов коммерческого банка полученных от различных операций, а всем остальным присвоить вид «Иной». Создайте итоговые строки с подсчетом среднего удельного веса дохода по одинаковым видам статей.
7 Построить на новом рабочем листе EXCEL смешанную диаграмму, в которой представить в виде гистограмм суммы доходов банка, а их удельные веса показать в виде линейного графика на той же диаграмме. Вывести легенду и название графика «Анализ доходов коммерческого банка».
8 Показать в виде графика суммы доходов коммерческого банка, вывести линию тренда с уравнением.
Решение
Таблица 1. – Сортировка документа по возрастанию наименований статей доходов коммерческого банка
| Статьи доходов | тыс. грн. | % к итогу |
| Начисленные и полученные проценты | 100354 | NPP% |
| Комиссионные за услуги и корреспондентские отношения | 37649 | KUKO% |
| Плати за кредитные ресурсы | 18157 | PKR% |
| Доходы по операциям с ценными бумагами и на валютном рынке | 3427 | DOCB% |
| Плата за юридические услуги | 2136 | PUU% |
| Доходы от участия и деятельности предприятий, организаций и банков | 1973 | DUD% |
| Доходы от лизинговых операций | 512 | DLO% |
| Итого: | 100% |
Таблица 2. – Сортировка таблицы в алфавитном порядке по наименованиям статей доходов и по убыванию, % к итогу
| Статьи доходов | тыс. грн. | % к итогу |
| Плати за кредитные ресурсы | 18157 | 3546% |
| Плата за юридические услуги | 2136 | 417% |
| Начисленные и полученные проценты | 100354 | 19600% |
| Комиссионные за услуги и корреспондентские отношения | 37649 | 7353% |
| Итого: | 158296 | 100% |
| Доходы по операциям с ценными бумагами и на валютном рынке | 3427 | 669% |
| Доходы от участия и деятельности предприятий, организаций и банков | 1973 | 385% |
| Доходы от лизинговых операций | 512 | 100% |
3. Статьи доходов, значения сумм в тыс. грн. которых больше среднего значения
| Статьи доходов | тыс. грн. | % к итогу |
| Начисленные и полученные проценты | 100354 | 19600% |
| Итого: | 158296 | 100% |
5. С помощью средства Расширенный фильтр сформировать новый документ, в который поместить только те статьи доходов коммерческого банка, сумма каждой из которых меньше среднего значения этого показателя по всему исходному документу или равна 100 354 тыс. грн.
| Статьи доходов | тыс. грн. |
| Плати за кредитные ресурсы | 18157 |
| Плата за юридические услуги | 2136 |
| Комиссионные за услуги и корреспондентские отношения | 37649 |
| Доходы по операциям с ценными бумагами и на валютном рынке | 3427 |
| Доходы от участия и деятельности предприятий, организаций и банков | 1973 |
| Доходы от лизинговых операций | 512 |
Таблица 3. – Итоговые строки с подсчетом среднего удельного веса дохода по одинаковым видам статей
| Статьи доходов | тыс. грн. | % к итогу | Вид дохода |
| Плати за кредитные ресурсы | 18157 | 11% | иной |
| Комиссионные за услуги и корреспондентские отношения | 37649 | 23% | иной |
| Доходы по операциям с ценными бумагами и на валютном рынке | 3427 | 2% | иной |
| иной Среднее | 12% | #ДЕЛ/0! | |
| Начисленные и полученные проценты | 100354 | 61% | По операциям |
| Доходы от лизинговых операций | 512 | 0% | По операциям |
| Доходы от участия и деятельности предприятий, организаций и банков | 1973 | 1% | По операциям |
| Плата за юридические услуги | 2136 | 1% | По операциям |
| По операциям Среднее | 16% | #ДЕЛ/0! | |
| Общее среднее | 14% | #ДЕЛ/0! |
7. Построить на новом рабочем листе EXCEL смешанную диаграмму, в которой представить в виде гистограмм суммы доходов банка, а их удельные веса показать в виде линейного графика на той же диаграмме. Вывести легенду и название графика «Анализ доходов коммерческого банка».
8 Показать в виде графика суммы доходов коммерческого банка, вывести линию тренда с уравнением.
Рисунок 4. – График суммы доходов коммерческого банка, линию тренда с уравнением
Задание 4. Оптимизация решений
Кондитерская фабрика для производства трёх видов карамели «Дюшес», «Персик», «Клубничная» использует три вида основного сырья: сахарный песок, патоку и фруктовое пюре. Нормы расхода сырья каждого типа на производство 1 т карамелиданного вида приведены в таблице. В ней же указано общее количество сырья каждого вида, которое может быть использовано фабрикой, а также приведена прибыль от реализации 1т карамели каждого вида.
Определить оптимальный план производства продукции кондитерской фабрики, обеспечивающий максимальную прибыль от её реализации, если максимальный выпуск карамели вида «Барбарис» составляет 140 т., «Дюшеса» не менее 160 т.
| Вид ингредиента | Нормы расхода сырья, т. на 1т карамели | Общее количество сырья, т | ||
| «Барбарис» | «Дыня» | «Дюшес» | ||
| Сахарный песокПатокаФруктовое пюре | 0,550,20 | 0,50,250,3 | 0,70,60,51 | 470265250 |
| Прибыль, грн./т | 4000 | 4800 | 4100 | |
Решение
Итак, решение найдено: «барбариса» выпускаем 140 т, «дыни» – 561,33 т, «Дюшеса» – 160 т. Максимальная прибыль – 3 910 400 грн.