Овчаренко_7
.pdf
Анализ кредитных вложений коммерческого банка
Задание №3
1. Рассчитать и показать на графике структуру привлеченных средств коммерческого банка (в млн. руб.).
Для решения задачи используется следующая входная информация:
•депозиты государственных предприятии;
•депозиты сельскохозяйственных (с/х) предприятий;
•депозиты СП;
• вклады населения;
•депозиты внебюджетных фондов;
•депозиты АО и ТОО;
•депозиты ИЧП;
•остатки на расчетных и текущих счетах клиентов;
•депозиты юридических лиц в иностранной валюте (в рублевом эквиваленте).
В результате решения задачи необходимо сформировать следующий выходной документ:
Структура привлеченных средств коммерческого банка
Привлеченные средства коммерческого |
Сумма (млн. руб.) |
Уд. вес (%) |
банка |
|
|
депозиты государственных предприятий |
2000 |
U(1) |
депозиты с/х предприятий |
850 |
U(2) |
депозиты СП |
700 |
U(3) |
вклады населения |
4000 |
U(4) |
депозиты внебюджетных фондов |
1000 |
U(5) |
депозиты АО и ТОО |
1200 |
U(6) |
депозиты ИЧП |
900 |
U(7) |
остатки на расчетных и текущих счетах |
8000 |
U(8) |
клиентов |
|
|
депозиты юридических лиц в валюте (в |
5000 |
U(9) |
руб.) |
|
|
ИТОГО |
SS |
100 % |
Формулы для расчета выходных показателей имеют следующий вид:
SS = SUM(S(I)),
где S(I) — сумма i-го привлеченного средства;
U(I) = (S(I) / SS) • 100,
где U(I) — удельный вес i-го привлеченного средства,
I = [I, N], N — количество видов привлеченных средств банка.
2. Выполнить сортировку документа по возрастанию наименований привлеченных средств коммерческого банка.
3.Выполнить фильтрацию сформированного документа, оставив в нем только депозиты коммерческого банка. Вернуть документ в исходное состояние, когда он содержал все привлеченные средства банка
4.Рассчитать общую сумму всех депозитов и сумму всех остальных привлеченных средств коммерческого банка с помощью команды Итоги. После сравнения полученных результатов вернуть документ в исходное состояние.
5.Построить на отдельном рабочем листе EXCEL круговую диаграмму, отражающую структуру сумм привлеченных средств коммерческого банка в виде соответствующих секторов. Показать на графике процентное соотношение привлеченных средств, выделить самый большой сектор, вывести легенду и название графика Структура привлеченных
средств коммерческого банка.
6.Построить на новом рабочем листе EXCEL смешанную диаграмму,
вкоторой представить в виде гистограмм суммы при влеченных средств
банка, а их удельные веса показать в виде линейного графика на той же диаграмме. Вывести легенду и название графика Анализ привлеченных
средств коммерческого банка.
7.Выполнить на том же графике прогноз привлечения новых средств банка на два периода вперед. Вывести уравнение кривой линии тренда.
8.С помощью средства Расширенный фильтр сформировать новый документ, в который поместить только те привлеченные средства
коммерческого банка, сумма каждого из которых больше среднего значения
этого показателя по всему исходному документу Поместить в новый документ все графы исходного документа.
9.На основании исходного документа Структура привлеченных средств коммерческого банка рассчитать и сформировать следующий документ только для депозитных средств банка.
Анализ депозитных средств коммерческого банка
Расчетная величина |
Значение |
Средняя сумма всех депозитных средств |
|
Количество депозитных средств |
|
Максимальная сумма депозитных средств |
|
Минимальная сумма депозитных средств |
|
10. Подготовить результаты расчетов и диаграммы к выводу на
печать.
Ответы к заданию №3
В результате расчетов выходных показателей и выполнения сортировки исходного документа должна быть получена следующая таблица:
Структура привлеченных средств коммерческого банка
Привлеченные средства коммерческого |
Сумма (млн. руб.) |
Уд. вес (%) |
|||
банка |
|
|
|
|
|
вклады населения |
|
4000 |
|
17% |
|
депозиты СП |
|
700 |
|
3% |
|
депозиты АО и ТОО |
|
1200 |
|
5% |
|
депозиты внебюджетных фондов |
|
1000 |
|
4% |
|
депозиты государственных предприятий |
2000 |
|
8% |
||
депозиты ИЧП |
|
900 |
|
4% |
|
депозиты с/х предприятий |
|
850 |
|
4% |
|
депозиты юридических лиц в валюте (в руб ) |
5000 |
|
21% |
||
остатки на расчетных и текущих счетах |
8000 |
|
34% |
||
|
|
|
|
|
|
ИТОГО |
|
23650 |
|
100% |
|
Результат фильтрации документа получит приведенный ниже вид, |
|||||
если воспользоваться следующей группировкой данных: |
|
|
|||
|
|
|
|
||
Привлеченные средства |
Сумма (млн. руб.) |
Уд. вес (%) |
Группа |
||
коммерческого банка |
|
|
|
|
|
вклады населения |
4000 |
|
17% |
|
вклады |
депозиты СП |
700 |
|
3% |
|
депозиты |
депозиты АО и ТОО |
1200 |
|
5% |
|
депозиты |
депозиты внебюджетных фондов |
1000 |
|
4% |
|
депозиты |
депозиты государственных |
2000 |
|
8% |
|
депозиты |
предприятий |
|
|
|
|
|
депозиты ИЧП |
900 |
|
4% |
|
депозиты |
депозиты с/х предприятий |
850 |
|
4% |
|
депозиты |
депозиты юридических лиц в |
5000 |
|
21% |
|
депозиты |
валюте (в руб. ) |
|
|
|
|
|
остатки на расчетных и текущих |
8000 |
|
34% |
|
остатки |
счетах клиентов |
|
|
|
|
|
ИТОГО |
23650 |
|
100% |
|
|
|
|
|
|
|
|
Для фильтрации можно воспользоваться командой Авто. фильтр:
Привлеченные средства |
Сумма (млн. руб.) |
Уд. вес (%) |
Группа |
коммерческого банка |
|
|
|
депозиты СП |
700 |
3% |
депозиты |
депозиты АО и ТОО |
1200 |
5% |
депозиты |
депозиты внебюджетных фондов |
1000 |
4% |
депозиты |
депозиты государственных |
2000 |
8% |
депозиты |
предприятий |
|
|
|
депозиты ИЧП |
900 |
4% |
депозиты |
депозиты с/х предприятии |
850 |
4% |
депозиты |
депозиты юридических лиц в |
5000 |
21% |
депозиты |
валюте (в руб.) |
|
|
|
Использование команды Итоги для расчета суммы всех депозитов и суммы остальных привлеченных средств коммерческого банка дает следующий результат:
Привлеченные средства коммерческого банка |
Сумма (млн. руб.) |
Группа |
вклады населения |
4000 |
вклады |
|
4000 |
вклады |
|
|
Всего |
депозиты СП |
700 |
депозиты |
депозиты АО и ТОО |
1200 |
депозиты |
депозиты внебюджетных фондов |
10W |
депозиты |
депозиты государственных предприятии |
2000 |
депозиты |
депозиты ИЧП |
900 |
депозиты |
депозиты с/х предприятий |
850 |
депозиты |
депозиты юридических лиц в валюте (в руб.) |
5000 |
депозиты |
|
11650 |
депозиты |
|
|
Всего |
остатки на расчетных и текущих счетах |
8000 |
остатки |
клиентов |
|
|
' |
8000 |
остатки |
|
|
Всего |
|
23650 |
Общий |
|
|
итог |
Использование команды Расширенный фильтр для формирования нового документа, содержащего только те привлеченные средства банка,
сумма каждого из которых больше среднего значения этого показателя по всему исходному документу (2628), дает следующий результат:
Привлеченные средства коммерческого банка |
Сумма (млн. руб.) |
|
|
вклады населения |
4000 |
депозиты юридических лиц в валюте (в руб.) |
5000 |
остатки на расчетных и текущих счетах клиентов |
8000 |
Анализ депозитных средств коммерческого банка с помощью функций EXCEL работы с базой данных дает следующий результат:
Расчетная величина |
Значение |
Средняя сумма всех депозитных средств |
1664,29 |
Количество депозитных средств |
7 |
Максимальная сумма депозитных средств |
5000 |
Минимальная сумма депозитных средств |
700 |
Смешанная диаграмма, в которой суммы привлеченных средств коммерческого банка представлены в виде гистограмм, а их удельные веса — в виде линейного графика, приведена ниже. На ней показан прогноз привлечения новых средств банка на два периода вперед, выведено уравнение линии тренда (прогноза), а также коэффициент аппроксимации R2.
Структура привлеченных средств коммерческого банка
Задание № 4
1. Сопоставить доходность акций по уровню дивидендов за 1996 год по отдельным эмитентам.
Исходные данные задачи следующие:
•номинал акции (NA);
•цена продажи (СР);
•дивиденды, объявленные в расчете на год (Div). В результате решения задачи должен быть сформирован следующий документ:
Доходность акций по уровню дивидендов
Эмитент |
Номина |
Цена |
Дивиденды, |
Доходность по |
|||
л акции |
продажи |
объявленные в |
|||||
дивидендам |
|||||||
|
(в руб.) |
(в руб.) |
расчете на год |
||||
|
|
|
|||||
|
NA |
СР |
в % Div |
в руб. |
к номи- |
факти- |
|
|
DivR |
налу |
ческая |
||||
|
|
|
|
DN |
DF |
||
|
|
|
|
|
|||
КБ |
10000 |
17780 |
400% |
|
|
|
|
Возрождение |
|
|
|
||||
Инкомбанк |
10000 |
22900 |
400% |
|
|
|
|
Торибанк |
5000 |
5600 |
320% |
|
|
|
|
Промстройбанк |
1000 |
2015 |
653% |
|
|
|
|
КБ |
1000 |
2482 |
736% |
|
|
|
|
С-Петербург |
|
|
|
||||
|
|
|
|
|
|
||
Уникомбанк |
1000 |
1000 |
325% |
|
|
|
|
|
|
|
|
|
|
|
|
Нефтехимбанк |
50000 |
27050 |
360% |
|
|
|
|
СКВ банк |
1000 |
1200 |
153% |
|
|
|
|
Формулы для расчета выходных показателей имеют следующий
вид:
DivR(i) = NA(i) * Div(i);
DN(i) = Div(i);
DF(i) = DivR(i) / CP(i),
где i = [1,N], N — число рассматриваемых эмитентов.
2.В выходном документе отсортировать записи в порядке
возрастания фактической доходности по дивидендам.
3.Выполнить фильтрацию таблицы, выбрав из нее только тех эмитентов, фактическая доходность которых больше средней по таблице. Результаты фильтрации поместить в новый документ, включив в него следующие графы:
•Эмитент;
•Номинал акции;
•Цена продажи;
•Доходность по дивидендам фактическая.
4.Построить на отдельном рабочем листе EXCEL круговую диаграмму, отражающую фактическую доходность по дивидендам каждого эмитента в виде соответствующего сектора. На графике показать значения доходности, вывести легенду и название графика Анализ
фактической доходности акций по уровню дивидендов.
5.Построить на новом рабочем листе EXCEL смешанную диаграмму, в которой представить в виде гистограмм значения
номиналов и цены продажи акций каждого эмитента, а их фактическую
доходность показать в виде линейного графика на тон же диаграмме. Вывести легенду и название графика Анализ доходности акций
различных эмитентов.
6. На основании исходного документа Доходность акций по уровню дивидендов рассчитать следующие значения:
•средняя цена продажи акций по всем эмитентам;
•максимальная цена продажи акций, наименование соответствующего эмитента;
•минимальная цена продажи акций, наименование соответствующего эмитента;
•максимальная фактическая доходность акций по уровню дивидендов, наименование соответствующего эмитента;
•минимальная фактическая доходность акций по уровню дивидендов, наименование соответствующего эмитента.
Результаты расчетов оформить в виде следующего документа:
Расчетная величина |
Значение |
Средняя цена продажи акций |
|
Максимальная цена продажи акций |
|
Минимальная цена продажи акций |
|
Максимальная фактическая доходность акций |
|
Минимальная фактическая доходность акций |
|
|
|
7. Подготовить результаты расчетов и диаграммы к вывода на печать.
Ответы к заданию №4
В результате расчетов выходных показателей и выполнения сортировки исходного документа должна быть получена следующая таблица:
Доходность акций по уровню дивидендов
Эмитент |
Номинал |
Цена |
Дивиденды, |
Доходность по |
|||
|
акции |
продажи |
объявленные в |
дивидендам |
|||
|
|
|
в % |
в руб. |
к |
факти- |
|
|
|
|
номиналу |
ческая |
|||
СКВ банк |
1000 |
1200 |
153% |
1530 |
153% |
1,28 |
|
Инкомбанк |
10000 |
22900 |
400% |
40000 |
400% |
1,75 |
|
КБ |
10000 |
17780 |
400% |
40000 |
400% |
2,25 |
|
Возрождение |
|||||||
|
|
|
|
|
|
||
Торибанк |
5000 |
5600 |
320% |
16000 |
320% |
2,86 |
|
КБ |
1000 |
2482 |
736% |
7360 |
736% |
2,97 |
|
С-Петербург |
|||||||
|
|
|
|
|
|
||
Промстрой- |
1000 |
2015 |
653% |
6530 |
653% |
3,24 |
|
банк |
|||||||
|
|
|
|
|
|
||
Уникомбанк |
1000 |
1000 |
325% |
3250 |
325% |
3,25 |
|
Нефтехим- |
50000 |
27050 |
360% |
180000 |
360% |
6,65 |
|
банк |
|||||||
|
|
|
|
|
|
Результат фильтрации документа с помощью команды Рас.
ширенный фильтр по эмитентам, чья фактическая доходность
больше средней по таблице, приведен ниже (средняя доходность составляет 3,03):
Эмитент |
Номинал акции |
Цена продажи (в |
Доходность по |
|
(в руб.) |
руб.) |
дивидендам |
||
|
||||
|
|
|
фактическая |
|
Промстройбанк |
1000 |
2015 |
3,24 |
|
Уникомбанк |
1000 |
1000 |
3,25 |
|
Нефтехимбанк |
50000 |
27050 |
6,65 |
Результаты расчетов выходных показателей на основании до-
кумента Доходность акций по уровню эмитентов приведены ниже:
Расчетная величина |
Значение |
Средняя цена продажи акций |
10003,38 |
Максимальная цена продажи акций |
27050 |
Минимальная цена продажи акций |
1000 |
Максимальная фактическая доходность акций |
6,65 |
Минимальная фактическая доходность акций |
1,28 |
Круговая диаграмма, отражающая фактическую доходность по дивидендам каждого эмитента, имеет следующий вид: