страница 1 | страница 2 страница 3 страница 4 | страница 5 | страница 6
Практическая работа № 9
Работа со сводными таблицами
Задание 1. Заполните таблицу 1 с данными по ценным бумагам эмитентов.
1 Стоимость предложения рассчитывается как произведение эмиссии и номинальной стоимости.
2 Стоимость спроса равна произведению спроса на ценные бумаги, номинала и курса данной ценной бумаги.
Таблица 1 - База данных ценных бумаг
Код вида ЦБ
|
Код эмитента
|
Номинал ЦБ
|
Эмиссия ЦБ
|
Спрос ЦБ
|
Курс ЦБ
|
Стоимость предложения
|
Стоимость спроса
|
А
|
П1
|
1000
|
10
|
10
|
1,05
|
|
|
А
|
П1
|
1500
|
2
|
2
|
0,07
|
|
|
А
|
П2
|
500
|
6
|
3
|
0,98
|
|
|
А
|
ПЗ
|
100
|
3
|
4
|
0,97
|
|
|
В
|
П1
|
5000
|
2
|
3
|
1,12
|
|
|
В
|
П2
|
10000
|
1
|
2
|
1,06
|
|
|
В
|
ПЗ
|
2000
|
1
|
1
|
1,09
|
|
|
В
|
ПЗ
|
15000
|
3
|
1
|
1,12
|
|
|
О
|
П1
|
5000
|
6
|
5
|
1,01
|
|
|
О
|
П2
|
500
|
3
|
4
|
1,02
|
|
|
О
|
ПЗ
|
1000
|
5
|
2
|
1,02
|
|
|
О
|
П2
|
2000
|
4
|
3
|
1
|
|
|
А
|
П2
|
5000
|
6
|
3
|
0,98
|
|
|
В
|
ПЗ
|
100
|
3
|
4
|
0,97
|
|
|
В
|
П1
|
5000
|
2
|
3
|
1,1
|
|
|
В
|
П2
|
500
|
1
|
2
|
1,06
|
|
|
О
|
ПЗ
|
500
|
5
|
1
|
1,07
|
|
|
О
|
П1
|
2000
|
6
|
5
|
1,04
|
|
|
О
|
ПЗ
|
500
|
3
|
4
|
1,02
|
|
|
А
|
ПЗ
|
1000
|
4
|
2
|
1,02
|
|
|
В
|
ПЗ
|
500
|
1
|
3
|
0,98
|
|
|
О
|
П1
|
500
|
5
|
1
|
1,15
|
|
|
О
|
П2
|
1000
|
5
|
5
|
1,04
|
|
|
3. Для построения сводной таблицы необходимо установить курсор в область исходной таблицы, зайти в меню Данные и выполнить команду Сводная таблица.
4. Далее вам нужно указать в качестве типа источника данных для построения сводной таблицы базу данных (список) Excel. На следующем шаге расположите поля базы данных ценных бумаг в областях макета следующим образом:
по строкам - код вида ценных бумаг;
по столбцам - код эмитента.
Область Данные должна содержать итоговое поле Стоимость предложения. Вам необходимо переименовать его в Предложение по ЦБ и применить операцию Сумма. Стоимость спроса переименовать в Спрос по ЦБ, и так же выбрать операцию Сумма.
5. Поместите созданную таблицу на новом листе Excel и присвойте ему имя «Сводная таблица».
6. Установите курсор в область готовой сводной таблицы и использую панель инструментов «Сводные таблицы» выполните команду Формулы, Вычисляемое поле. Укажите имя нового вычисляемого поля - Дефицит/Избыток.
Формула расчета: Стоимость предложения - Стоимость спроса.
7. Добавьте в сводную таблицу новые поля в область Данные:
Стоимость предложения - с именем Структура предложения по эмитентам, операция - сумма, дополнительные вычисления - Доля от суммы по строке;
Стоимость спроса - с именем Структура спроса на ЦБ эмитентов, операция - сумма, дополнительные вычисления - Доля от суммы по строке;
Стоимость предложения - с именем Структура предложений эмитента, операция - сумма, дополнительные вычисления - Доля от суммы по столбцу.
8. Включите следующие параметры сводной таблицы с помощью команды контекстного меню Параметры:
-
общая сумма по столбцам;
-
общая сумма по строкам;
-
автоформат;
-
сохранять форматирование;
-
сохранять данные с таблицей;
-
обновить при открытии.
Задание 2. Проанализируйте деятельность банков по городам и на основании таблицы 2 с помощью мастера сводных таблиц получите таблицу 3.
Таблица 2 - База данных по банкам
Город
|
Код банка
|
Чистая прибыль
|
Собственный капитал
|
Средняя сумма собственного капитала
|
Текущие активы
|
Средняя сумма текущих активов
|
Средняя стоимость основных фондов
|
Алматы
|
123
|
123000
|
688799
|
568493
|
152356
|
15499
|
15000
|
Астана
|
123
|
186500
|
700000
|
600000
|
160000
|
16000
|
15500
|
Астана
|
115
|
15600
|
521358
|
684929
|
2165652
|
356513
|
59656
|
Алматы
|
145
|
59696
|
566389
|
659878
|
2656233
|
596568
|
596523
|
Алматы
|
112
|
126000
|
798931
|
684521
|
296532
|
526532
|
265945
|
Москва
|
137
|
135005
|
846563
|
659779
|
549656
|
651226
|
154835
|
Астана
|
108
|
15862
|
568598
|
550000
|
2656235
|
546563
|
154512
|
Астана
|
107
|
123687
|
798956
|
780025
|
358989
|
326562
|
788952
|
Алматы
|
197
|
56900
|
635799
|
516782
|
2698565
|
56598
|
265953
|
Алматы
|
131
|
148900
|
598369
|
600000
|
6589235
|
187652
|
126556
|
Алматы
|
156
|
15687
|
574894
|
550000
|
2168562
|
184952
|
1548535
|
Астана
|
122
|
132567
|
635979
|
590489
|
6598779
|
489926
|
154562
|
Алматы
|
125
|
122680
|
568722
|
505050
|
4587963
|
265623
|
645879
|
Астана
|
147
|
98503
|
843689
|
845000
|
2154652
|
219856
|
656546
|
Москва
|
159
|
110369
|
589355
|
600050
|
2165935
|
595965
|
216562
|
Астана
|
146
|
125863
|
597997
|
560045
|
6597556
|
26562
|
956235
|
Астана
|
111
|
149024
|
587600
|
560421
|
9562326
|
748756
|
595624
|
Алматы
|
108
|
125679
|
890000
|
850000
|
7521356
|
265562
|
265626
|
Москва
|
156
|
45455
|
678000
|
666023
|
1265597
|
- 487987
|
565624
|
Москва
|
121
|
26876
|
955606
|
850000
|
2165856
|
216583
|
154623
|
Алматы
|
196
|
26583
|
669768
|
669120
|
3457953
|
216956
|
595622
|
Алматы
|
110
|
139813
|
780008
|
764035
|
9432523
|
454526
|
1545213
|
В анализе деятельности банка заемный капитал равен 10 % от собственного капитала.
Текущая задолженность составляет разницу между текущими активами и заемным капиталом.
Прибыльность текущих активов равняется частному от деления чистой прибыли на среднюю сумму текущих активов.
Рентабельность основных фондов определяется путем деления чистой прибыли на среднюю стоимость основных фондов.
Прибыльность собственного капитала равна чистой прибыли, деленной на собственный капитал.
Таблица 3 - Анализ деятельности банков по городам
Город
|
Москва
|
|
|
|
|
Код банка
|
Данные
|
Итог
|
121
|
Чистая прибыль
|
26876
|
|
Собственный капитал
|
955606
|
|
Заемный капитал
|
95560,6
|
|
Текущая задолженность
|
2070295,4
|
|
Прибыльность текущих активов
|
0,124090995
|
|
Рентабельность основных фондов
|
0,173816315
|
|
Прибыльность собственного капитала
|
0,028124562
|
137
|
Чистая прибыль
|
135005
|
|
Собственный капитал
|
846563
|
|
Заемный капитал
|
84656,3
|
|
Текущая задолженность
|
464999,7
|
|
Прибыльность текущих активов
|
0,207308983
|
|
Рентабельность основных фондов
|
0,871928182
|
|
Прибыльность собственного капитала
|
0,159474251
|
156
|
Чистая прибыль
|
45455
|
|
Собственный капитал
|
678000
|
|
Заемный капитал
|
67800
|
|
Текущая задолженность
|
1197797
|
|
Прибыльность текущих активов
|
0,093147973
|
|
Рентабельность основных фондов
|
0,080362573
|
|
Прибыльность собственного капитала
|
0,067042773
|
159
|
Чистая прибыль
|
110369
|
|
Собственный капитал
|
589355
|
|
Заемный капитал
|
58935,5
|
|
Текущая задолженность
|
2106999,5
|
|
Прибыльность текущих активов
|
0,185193761
|
|
Рентабельность основных фондов
|
0,509641581
|
|
Прибыльность собственного капитала
|
0,18727083
|
Итого Чистая прибыль
|
317705
|
Итого Собственный капитал
|
3069524
|
Итого Заемный капитал
|
306952,4
|
Итого Текущая задолженность
|
5840091,6
|
Итого Прибыльность текущих активов
|
0,162778639
|
Итого Рентабельность основных фондов
|
0,291033524
|
Итого прибыльность Собственного капитала
|
0,103503019
|
страница 1 | страница 2 страница 3 страница 4 | страница 5 | страница 6
|