Анализ данных: моделирование инвестиционного портфеля

ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ  
БЮДЖЕТНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ОБРАЗОВАНИЯ  
«ФИНАНСОВЫЙ УНИВЕРСИТЕТ ПРИ ПРАВИТЕЛЬСТВЕ  
РОССИЙСКОЙ ФЕДЕРАЦИИ»  
(ФИНАНСОВЫЙ УНИВЕРСИТЕТ) 
ВЛАДИКАВКАЗСКИЙ ФИЛИАЛ ФИНУНИВЕРСИТЕТА 

Кафедра «Математика и информатика»

А.Р. Цогоева, А.Ю. Цогоев, М.В. Волик

АНАЛИЗ ДАННЫХ:
МОДЕЛИРОВАНИЕ 
ИНВЕСТИЦИОННОГО 
ПОРТФЕЛЯ
 
Учебное пособие 

МОСКВА
2023

ISBN 978-5-00172-404-9

УДК 004.942:336.763
ББК 65.263−24
 
Ц 76
Авторы:
Цогоева Аида Руслановна, к.э.н., доцент, доцент кафедры 
«Математика и информатика», ФГОБУ ВО «Финансовый университет при Правительстве Российской Федерации», Владикавказский 
филиал;
Цогоев Алан Юрьевич, к.т.н., доцент, доцент кафедры 
«Информатики и моделирования», ФГОБУ ВО «Горский государственный аграрный университет»;
Волик Мария Владимировна, к.ф.-м.н., доцент кафедры 
«Математика и информатика», ФГОБУ ВО «Финансовый университет при Правительстве Российской Федерации», Владикавказский 
филиал.

Рецензенты:
Копышева Татьяна Николаевна, к.ф.-м.н., доцент, заведующий кафедрой ИиВТ, доцент кафедры ИиВТ, ФГБОУ ВО «Чувашский 
государственный педагогический университет им. И.Я. Яковлева»;
Басаева Елена Казбековна, к.ф.-м.н., доцент, заведующий кафедрой прикладной математики и информатики, ФГБОУ 
ВО «Северо-Осетинский государственный университет имени 
К.Л. Хетагурова».

Ц 76
Цогоева А.Р.
Анализ данных: моделирование инвестиционного 
портфеля: Учебное пособие / А.Р. Цогоева, А.Ю. Цогоев, 
М.В. Волик. — М.: Прометей, 2023. — 90 с.

ISBN 978-5-00172-404-9
В учебном пособии представлены теоретические основы моделирования инвестиционного портфеля с использованием надстройки 
«Поиск решения» MS Excel, а также ситуационные задания с описанием последовательности их выполнения и задания для самостоятельного выполнения. Учебное пособие составлено на основе учебного 
плана и рабочей программы дисциплины «Анализ данных» и предназначено для обучающихся высших учебных заведений по направлениям подготовки 38.03.01 «Экономика», 38.03.02 «Менеджмент», 
38.03.05 «Бизнес-информатика» и учебных заведений среднего 
профессионального образования по специальностям 38.02.01 «Экономика и бухгалтерский учет» (по отраслям), 38.02.06 «Финансы», 
а также для специалистов в области анализа и визуализации данных, 
изучающих различные методы анализа и обработки данных, моделирования экономических процессов с использованием электронных 
таблиц и инструментов MS Excel.

©  Цогоева А.Р., Цогоев А.Ю.,  
Волик М.В., 2023
© Издательство «Прометей», 2023

ОГЛАВЛЕНИЕ

Введение  ........................................................................................4

Технологии обработки и анализа информации  
в электронных таблицах ...............................................................8
Основные возможности табличных процессоров ............... 8
Рабочая книга и ее элементы ........................................10
Операции с рабочей книгой и ее элементами, 
изменение свойств элементов........................................13
Способы адресации: абсолютные и относительные адреса .16
Форматы данных ........................................................16
Инструменты автоматизации ввода данных ....................17
Синтаксис встроенных функций ....................................17
Визуализация данных: диаграммы ................................20
Визуализация данных: условное форматирование ...........24
Решение уравнений и задач оптимизации средствами 
табличного процессора ................................................26
Средства защиты табличных документов ........................30
Контрольные вопросы .................................................31

Практическая работа ..................................................................33

Задания для самостоятельной работы ......................................87

Литература...................................................................................88

ВВЕДЕНИЕ 

В современном мире принятие управленческих решений в экономических процессах невозможно себе представить без использования компьютерного моделирования и табличных процессоров. Массовое использование 
современных цифровых, информационных, прикладных 
и сетевых технологий и, в частности, электронных таблиц 
позволяют эффективно и своевременно решать профессиональные задачи разного уровня сложности. Экономисты, менеджеры, аналитики способны легко овладевать 
инструментами и методами решения задач в формате компьютерного моделирования, выполнять разработку алгоритмов и их реализацию на компьютерах, не обращаясь 
за помощью к специалистам. 
Решение задач управления или совершенствования 
элементов экономических процессов в формате компьютерного моделирования направлено на дополнение, 
а не замену собственного опыта и навыков сотрудников 
или владельцев компаний. Таким образом, применение 
средств компьютерного моделирования экономических 
процессов позволяет глубже разобраться в экономической ситуации, тем самым помогает будущим специалистам совершенствовать свои знания, умения, навыки, 
интуицию. Основываясь на классификации по функциональному признаку решаемых экономических и управленческих задач, а также на классификации особенностей инструментов бизнес-анализа и инвестиционного 
анализа данных, в учебном пособии представлены возможности моделирования, анализа и обработки статистических экономических данных на примере инвестиционного портфеля.
В предлагаемом учебном пособии для проведения 
расчетов, визуализации и анализа данных используется 
табличный процессор MS Excel. Однако, теоретические 
аспекты и практические задания моделирования инвестиционного портфеля сконструированы таким образом, что 

предлагаемая модель является универсальной и может 
быть составлена с использованием любого, доступного 
пользователям, табличного процессора.
В современных экономических условиях актуальность математического моделирования экономических 
процессов обусловлена внедрением и развитием прикладных инструментальных сред, автоматизирующие рутинные операции анализа и прогнозирования, предоставляя конечным пользователям результаты исследований 
в наглядном и доступном виде.
Табличный процессор MS Excel является незаменимым инструментом в работе экономистов, менеджеров, 
бизнес-аналитиков, а также рядовых сотрудников компаний разных масштабов и форм собственности. Электронные таблицы позволяют снизить затраты времени 
сотрудников в процессе анализа и визуализации данных, 
принятия управленческих, что позволит компаниям 
эффективнее достигать стратегических целей развития, 
автоматизировать функции и операции, повышать конкурентоспособность и инвестиционную привлекательность.
Табличный процессор MS Excel содержит инструментарий, который облегчает работу пользователя и состоит 
из четырех основных модулей: вычислительный (калькуляционный); презентационный (модуль диаграмм); 
модуль базы данных, позволяющий пересылать информацию из базы данных в Excel для обработки и обратно; 
модуль программирования, содержащий систему программирования Visual Basic for Applications. Презентационный модуль позволяет создавать презентационную 
графику и визуализировать результаты работы. Вычислительный модуль включает раздел Анализ данных 
и содержит такие инструменты, как Подбор параметра, 
Поиск решения, Диспетчер сценариев. Инструмент Подбор параметра используется для определения неизвестного значения, которое будет предоставлять желаемый 
результат. Например, определить тот объем производ

ства, при котором прибыль будет равна нулю, т.е. найти 
точку безубыточности. Инструмент надстройки MS Excel 
Поиск решения (Solver) применяется для решения оптимизационных задач. Он позволяет, изменяя значения 
переменных в диапазоне ячеек, максимизировать или 
минимизировать значение в целевой ячейке при выполнении ряда ограничений. Назначением инструмента 
Диспетчер сценариев является сохранение различных 
сценариев, созданных на основе одной модели, а затем 
сравнение результатов по разным сохраненным сценариям с выбором наилучшего сценария, используя графическую презентацию, которая создается в диспетчере 
автоматически. 
Следует отметить, что электронные таблицы достаточно легки в изучении, а решение задач для реализации работ, составляющих экономический процесс с элементами компьютерного моделирования, является очень 
интересным и чрезвычайно полезным занятием для специалистов различных направлений деятельности. 
Разработка, внедрение и использование математических и компьютерных моделей на базе электронных 
таблиц позволят повысить эффективность управления 
компанией в направлениях увеличения скорости повторяющихся задач, повышения качества работы, повышения точности управления, параллельного выполнения 
задач и принятия своевременных решений в типовых 
ситуациях. Для автоматизации многих бизнес-процессов 
не всегда требуется внедрение и использование коробочного прикладного решения. Существенное количество 
рутинных операций может быть автоматизировано собственными средствами специалистов путем разработки 
и внедрения электронных таблиц для моделирования, 
анализа и визуализации данных.
Цель учебного пособия — комплексное представление общих принципов разработки электронных таблиц 
с использованием инструментов Microsoft Excel для моделирования и анализа инвестиционного портфеля.

Учебное пособие может быть использовано обучающимися по программам бакалавриата и среднего профессионального образования, системными администраторами, 
экономистами, менеджерами, бизнес-аналитиками и другими специалистами в области анализа и визуализации 
данных, изучающих различные методы анализа и обработки данных, моделирования экономических процессов 
с использованием электронных таблиц и инструментов 
MS Excel.

ТЕХНОЛОГИИ ОБРАБОТКИ И АНАЛИЗА ИНФОРМАЦИИ 
В ЭЛЕКТРОННЫХ ТАБЛИЦАХ

Основные возможности табличных процессоров

В условиях цифровизации современной, актуальной 
и эффективной информационной технологией дня профессиональной обработки числовой информации и данных является электронная таблица (ЭТ), для управления 
которой используются прикладные программные инструменты табличных процессоров. Табличный процессор (ТП) 
представляет собой специализированный многофункциональный комплекс программных инструментов создания, 
обработки, редактирования, группировки, фильтрации, 
хранения, визуализации данных (риc. 1). ЭТ, по аналогии с обычными таблицами, состоят из строк и столбцов, 
образующих на пересечении ячейки, в которые вводятся 
данные разных типов: числовые, текстовые и формулы. 

Использование табличных процессоров целесообразно в следующих случаях:

числа, можно 
расположить в виде 
таблицы, т. е. в строках, 
столбцах, ячейках

числа в одних ячейках 
связаны с числами в 
других ячейках

предполагается 
использование 
математических 
вычислений над данными

предполагается 
статистическая обработка 
данных

возможно частое 
изменение информации

отслеживается большое 
число показателей

подготовка необходимого 
числа копий табличных 
документов

Риc. 1. Причины использования электронных таблиц

ТП построены по принципу диалоговых систем, 
позволяющих пользователю выбирать возможные действия из меню и обеспечивающих:
– ввод, редактирование, хранение больших объемов 
данных
– обновление результатов вычислений в автоматическом режиме после редактирования исходных данных;
– интуитивно-понятный интерфейс с пользовательским диалогом;
– естественное и наглядное представление документов пользователю на экране.
Функции, доступные пользователям, зависят от вида 
ТП. Основные функции представлены на риc. 2.

Для формул при
вычислениях
• Математические функции; логические функции типа IF (если);
статистические функции.

Текстовые
функции
• Позволяют обработать текстовые значения клеток. Например,
отразить только первые три символа текстовой строки.

Финансовые
функции
• Используются для выполнения основных финансовых
операций. Например, определение величины платежей и т.п.

Функции даты и
времени
• Предназначены для проверки корректности ввода даты и
времени, выполнения арифметических операций с ними.

• Сортировка, группировка, фильтрация  данных по различным
критериям.

Функции
сгруппированных
данных

Риc. 2. Базовые функции табличных процессоров 
для пользователей

На риc. 3 приведены виды табличных процессоров, 
классифицируемые по типу операционной системы (ОС), 
в которой они работают.
В условиях цифровизации операционная система MS 
DOS практически не используется. Табличные процессоры Excel, OpenOffi ce Calc, KSpread, Gnumeric отличаются друг от друга не существенно: интерфейсы и принципы работы схожи и интуитивно понятны.