Контрольная работа по "Информационные технологии в экономике"

 Задание 3.

Написать алгоритм решения  задачи, используя финансовые функции  MS Excel.

1. Сопоставить доходность  акций по уровню дивидендов ____ год по отдельным элементам.

Исходные данные задачи следующие:

- номинал акции (NA);

- цена продажи (CP);

- дивиденды, объявленные  в расчете на год (Div).

В результате решения задачи должен быть сформирован следующий  документ:

 

Эмитент	Номинал акции (в  руб.)	Цена продажи (в  руб.)	Дивиденды, объявленные  в расчете на год		Доходность по дивидендам
	NA	CP	в % Div	в руб. DivR	к номиналу DN	фактическая DF
КБ Возрождение	10 000,00	17 780,00	400 %
Инкомбанк	10 000,00	22 900,00	400 %
Торибанк	5 000,00	5 600,00	320 %
Промстройбанк	1 000,00	2 015,00	653 %
КБ С-Петербург	1 000,00	2 482,00	736 %
Уникомбанк	1 000,00	1 000,00	325 %
Нефтехимбанк	50 000,00	27 050,00	360 %
СКБ банк	1 000,00	1 200,00	153 %

 

Формулы для расчета выходных показателей имеют следующий  вид:

 

 

 

где i – число рассматриваемых эмитентов.

2. В выходном документе  отсортировать записи в порядке  возрастания фактической доходности  по дивидендам.

3. Выполнить фильтрацию  таблицы, выбрав из нее только  тех эмитентов, фактическая доходность  которых больше средней по  таблице.

4. Построить на отдельном  рабочем листе Excel, круговую диаграмму, отражающую фактическую доходность по дивидендам каждого эмитента в виде соответствующего сектора. На графике показать значения доходности, вывести легенду и название графика «Анализ фактической доходности акций по уровню, дивидендов».

5. Построить на новом  рабочем листе Excel смешанную диаграмму, в которой представить в виде гистограмм значения номиналов и цены продажи акций каждого эмитента, а их фактическую доходность показать в виде линейного графика на той же диаграмме. Вывести легенду и название графика «Анализ доходности акций различных эмитентов».

6. На основании исходного  документа Доходность акций по  уровню дивидендов рассчитать следующие значения:

- средняя цена продажи  акций по всем эмитентам;

- максимальная цена продажи  акций, наименование соответствующего  эмитента;

- минимальная цена продажи акций, наименование соответствующего эмитента;

- максимальная фактическая  доходность акций по уровню  дивидендов, наименование соответствующего  эмитента;

- минимальная фактическая доходность акций по уровню дивидендов, наименование соответствующего эмитента.

Результаты  расчетов оформить в виде следующего

Расчетная величина	Значение
Средняя цена продажи акций
Максимальная цена продажи  акций
Минимальная цена продажи  акций
Максимальная фактическая  доходность акций
Минимальная фактическая  доходность акций

 

Алгоритм решения.

1. В редакторе MS Excel создаем таблицу, вводим данные, обрамляем таблицу. В ячейки дивиденды, объявленные в расчете на год», «Доходность по дивидендам» вводим формулы для расчета. С помощью маркера автозаполнения рассчитываем все столбцы. Заполненная и просчитанная таблица выглядит так:

Доходность акций  по уровню дивидендов

 

Эмитент	Номинал акции (в  руб.)	Цена продажи (в  руб.)	Дивиденды, объявленные  в расчете на год		Доходность по дивидендам
	NA	CP	в % Div	в руб. DivR	к номиналу DN	фактическая DF
КБ Возрождение	10 000,00	17 780,00	400	4 000 000,00	400,00	224,97
Инкомбанк	10 000,00	22 900,00	400	4 000 000,00	400,00	174,67
Торибанк	5 000,00	5 600,00	320	1 600 000,00	320,00	285,71
Промстройбанк	1 000,00	2 015,00	653	653 000,00	653,00	324,07
КБ С-Петербург	1 000,00	2 482,00	736	736 000,00	736,00	296,54
Уникомбанк	1 000,00	1 000,00	325	325 000,00	325,00	325,00
Нефтехимбанк	50 000,00	27 050,00	360	18 000 000,0	360,00	665,43
СКБ банк	1 000,00	1 200,00	153	153 000,00	153,00	127,50

 

 2. В выходном документе делаем сортировку в порядке возрастания фактической доходности: выделяем столбец «Фактическая доходность» - данные – сортировка - в порядке фактической доходности. Результат сортировки:

 

Эмитент	Номинал акции (в  руб.)	Цена продажи (в  руб.)	Дивиденды, объявленные  в расчете на год		Доходность по дивидендам
	NA	CP	в % Div	в руб. DivR	к номиналу DN	фактическая DF
СКБ банк	1 000,00	1 200,00	153	153 000,00	153,00	127,50
Инкомбанк	10 000,00	22 900,00	400	4 000 000,00	400,00	174,67
КБ Возрождение	10 000,00	17 780,00	400	4 000 000,00	400,00	224,97
Торибанк	5 000,00	5 600,00	320	1 600 000,00	320,00	285,71
КБ С-Петербург	1 000,00	2 482,00	736	736 000,00	736,00	296,54
Промстройбанк	1 000,00	2 015,00	653	653 000,00	653,00	324,07
Уникомбанк	1 000,00	1 000,00	325	325 000,00	325,00	325,00
Нефтехимбанк	50 000,00	27 050,00	360	18 000 000,0	360,00	665,43

 

3. Сначала нужно найти среднее значение в столбце «Фактическая DF». С помощью Панель формул          вкладка автосумма найдем среднее значение фактической доходности: оно равно – 302,9872. Теперь можно выполнить фильтрацию таблицы: выделяем столбец «Фактическая DF" – данные – фильтр - числовой фильтр - выше среднего (302,9872). Результат фильтрации помещаем в таблицу:

 

Эмитент	Номинал акции (в  руб.)	Цена продажи (в  руб.)	Дивиденды, объявленные  в расчете на год		Доходность по дивидендам
	NA	CP	в % Div	в руб. DivR	к номиналу DN	фактическая DF
Промстройбанк	1 000,00	2 015,00	653	653 000,00	653,00	324,07
Уникомбанк	1 000,00	1 000,00	325	325 000,00	325,00	325,00
Нефтехимбанк	50 000,00	27 050,00	360	18 000 000,0	360,00	665,43

 

4. Строим на отдельном  рабочем листе Excel круговую диаграмму, отражающую фактическую доходность по дивидендам каждого эмитента, соответствующего определенному сектору: Вставка – Диаграммы – Тип «Круговая диаграмма» - диапазон «Фактическая DF». Также строим график: Вставка – Диаграмма – Тип «График» - диапазон «Фактическая DF». Выводим легенду и название графика и круговой диаграммы.

 

 

 

5. Строим на отдельном  рабочем листе смешанную диаграмму.

а) Для построения смешанной диаграммы строим диаграмму со вспомогательными осями.

- Для этого строим сначала простую диаграмму: график с маркерами;

- Выбираем данные таблицы,  которые нужно показать на графике с маркерами («Цена продажи», «Номинал акции» и «Фактическая доходность DF»);

- На вкладке: Вставка – Диаграмма – График – График с маркерами;

- Меняем значения по  оси X на названия эмитентов (для этого правой кнопкой мыши щелкнуть по оси X – Выбрать данные. Далее в окне Выбор источника данных нажать на команду Изменить в окне Подписи по горизонтальной оси. В появившемся окне Подписи окна выбираем Диапазон подписей оси (название эмитентов));

- Введем вспомогательную  ось Y для значений «Фактическая доходность DF». Для этого выделяем линию графика «Фактическая доходность DF» и выбираем команду Формат ряда данных. Далее в окне Формат ряда данных в закладке Параметры ряда необходимо выбрать По вспомогательной оси.

б) Выделяем график «Цена продажи» щелкаем правой кнопкой мыши и выбираем команду Изменить тип диаграммы для ряда – Гистограмма с группировкой.

в) Выделяем график «Номинал акции» щелкаем правой кнопкой мыши и выбираем команду Изменить тип диаграммы для ряда – Гистограмма с группировкой.

Легенду диаграммы располагаем сверху. Добавляем название диаграммы «Анализ доходности различных эмитентов» (для этого выделяем Диаграмму – вкладка Макет – Название диаграммы).

 

6. Выполняем задание с  помощью Мастера функций.

а) В пустую ячейку под столбцом «Цена продажи акций»:

- устанавливаем курсор – Вставка – функция – выбираем категорию, функцию «Среднее» - ОК.

- устанавливаем курсор  – Вставка – функция – выбираем  категорию, функцию «Максимум» - ОК.

- устанавливаем курсор  – Вставка – функция – выбираем  категорию, функцию «Минимум»  - ОК.

б) В пустую ячейку под столбцом «Фактическая DF»:

- устанавливаем курсор – Вставка – функция – выбираем категорию, функцию «Максимум» - ОК.

- устанавливаем курсор – Вставка – функция – выбираем категорию, функцию «Минимум» - ОК.

7.Результаты расчетов заносим в таблицу:

 

Расчетная величина	Значение
Средняя цена продажи акций	10003,375
Максимальная цена продажи  акций	27050,000
Минимальная цена продажи  акций	1000,000
Максимальная фактическая  доходность акций	665,434
Минимальная фактическая  доходность акций	127,500

 

Решение в Excel.