Студентам ИТ / 3 ЛП_ИТ / ИТ_прогнозирования / ИТ_фин_произв_бизнесом_Excel
.pdfМИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РФ Московский государственный университет технологий и управления
им. К.Г. Разумовского
(Первый казачий университет)
___________________________________________
Кафедра Информационных технологий
Краснов А.Е., Сагинов Ю.Л., Феоктистова Н.А.
ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ УПРАВЛЕНИЯ ФИНАНСАМИ, ПРОИЗВОДСТВОМ И БИЗНЕСОМ
Лабораторный практикум в среде EXСEL
для подготовки аспирантов
по специальностям:
05.13.01– «Системный анализ, управление и обработка информации»;
05.13.06– «Автоматизация и управление технологическими процессами»;
05.13.18 – «Моделирование, численные методы и
комплексы программ»,
атакже бакалавров и магистров, обучающихся по техническим,
технологическим и экономическим направлениям подготовки.
Москва – 2015
1
УДК 681.3.06 ББК 65.26с.я73
Краснов А.Е., Сагинов Ю.Л., Феоктистова Н.А. Информационные технологии управления финансами, производством и бизнесом. Лабораторный практикум
в среде Excel для подготовки аспирантов, бакалавров и магистров. - М.: МГУТУ им. К.Г. Разумовского, 2015. - 36 с.
Лабораторный практикум предназначен для подготовки, обучающихся по специальностям 05.13.01 – «Системный анализ, управление и обработка информации», 05.13.06 – «Автоматизация и управление технологическими процессами» и 05.13.18 – «Моделирование, численные методы и комплексы программ», а также бакалавров и магистров, обучающихся по направлениям подготовки «Информатика и вычислительная техника», «Прикладная информатика», «Инноватика», «Автоматизация технологических процессов и производств», «Управление в технических системах», «Продукты питания из растительного сырья», «Товароведение», «Экономика», «Менеджмент», «Бухгалтерский учет, анализ и аудит».
Лабораторный практикум позволяет обучающимся освоить численные методы управления реальными объектами в среде Excel на примере самых распространенных финансовых, производственных и коммерческих процессов и операций.
Рецензенты: Рябова Т.Ф., |
д.э.н., профессор МГУТУ; |
Магомедов М.Д., д.э.н., профессор МГУПП; |
|
Бородин А.В., |
д.т.н., профессор МГУПП. |
Редактор: Феоктистова Н.А., к.т.н., доцент МГУТУ
©Краснов А.Е., Сагинов Ю.Л., Феоктистова Н.А.
МГУТУ им. К.Г. Разумовского, 2015. 109004, Москва, Земляной вал, 73
2
Содержание
Стр.
Введение ………………………………………………………………………………… ……….4
Лабораторная работа № 1
Использование мастера функций Excel для управления финансовыми операциями, функции: БС, КПЕР, НОРМА, БЗРАСПИС, ЭФФЕКТ, НОМИНАЛ, ПЛТ,
ПРОЦПЛАТ, ОСНПЛТ, ОБЩПЛАТ, ОБЩДОХОД, АПЛ, ФУО, ДДОБ, ПУО ……. ……….5
Лабораторная работа № 2
Использование мастера функций Excel для управления инвестициями,
функции: ЧПС, ЧИСТНЗ, ВСД, ЧИСТВНДОХ, МВСД, ПС …………………………. ……….8
Лабораторная работа № 3
Оптимизационные методы в инвестициях: анализ пределов безопасности инвестиций, оптимизация инвестиционного портфеля ………………………………. ……...12
Лабораторная работа № 4
Решение оптимизационных задач управления ………………………………………... ……...16
Лабораторная работа № 5
Имитационное моделирование состояний производственных и коммерческих операций (управление состоянием и структурой бизнеса) …………………………... ……...20
Лабораторная работа № 6
Анализ чувствительности критериев эффективности ………………………………... ………24
Лабораторная работа № 7
Анализ чувствительности критериев эффективности ………………………………... ………28
Лабораторная работа № 8
Анализ рисков инвестиционного проекта с использованием инструмента Анализ данных …………………………………………………………………………………… ………32
Литература ……………………………………………………………………………… ………35
3
Введение
Лабораторный практикум «Информационные технологии управления финансами, производством и бизнесом» является важнейшей составной частью многомодульного курса, предназначенного для подготовки аспирантов и соискателей, обучающихся по специальностям 05.13.01 – «Системный анализ, управление и обработка информации», 05.13.06 – «Автоматизация и управление технологическими процессами» и 05.13.18 – «Моделирование, численные методы
икомплексы программ», а также бакалавров и магистров, обучающихся по техническим, технологическим и экономическим направлениям подготовки.
Целью выполнения практикума является освоение обучающимися практических методов управления финансовыми, производственными и коммерческими процессами и операциями в условиях неопределенности на базе единого системного подхода теории систем и системного анализа, теории управления.
Предполагается, что обучающиеся уже освоили общеобразовательные дисциплины: «Математика», «Теория вероятностей», «Информатика», а также – необходимые разделы пособия «Основы информационных технологий автоматизированного управления» и одноименного пособия «Информационные технологии управления финансами, производством и бизнесом».
Всвязи с тем, что в лабораторном практикуме затронут широкий круг вопросов, а объем материала в целом выходит за рамки часов, отведенных для конкретных тем, специальные акценты будут сделаны преподавателями на лекциях и лабораторных занятиях.
Влабораторном практикуме в центре внимания находится информационная технология управления финансовыми, производственными и коммерческими процессами и операциями в условиях помех. Напомним, что помехи обусловлены неполнотой и неточностью информации как об самом экономическом объекте, так
исреде, в которой он действует.
С формальных позиций информационных технологий, опирающихся на теорию систем и управления, предприятие в целом, производственный, финансовый или коммерческий процесс, или отдельная операция рассматриваются как некий экономический объект (ЭО), имеющий конкретную структуру, определяемую как внутренними свойствами самого объекта, так и его связями с внешним рынком. Важнейшим является выделение главных составляющих структуры и описание их связей с помощью совокупности различных параметров (констант и переменных). После описания структуры ЭО необходимо выделить основные переменные, которые при неизменной структуре объекта подвержены изменению в процессе его функционирования. Собственно, ради изменения этих переменных (например, количества продукции, прибыли) создается и поддерживается сама структура ЭО. Совокупность данных переменных полностью определяет динамическое состояние ЭО. Изменение динамического состояния в нужном направлении осуществляется с помощью управления - совокупности денежных, материальных и информационных средств. Замена начального состояния ЭО на требуемое его конечное состояние является целью управления. Однако в силу наличия помех и определенных ограничений, присущих конкретному ЭО, каждому управлению ставят в соответствие количественные критерии ограничения и критерии качества управления.
4
Лабораторная работа № 1
Использование мастера функций Excel
для управления финансовыми операциями, функции: БС, КПЕР, НОРМА, БЗРАСПИС, ЭФФЕКТ, НОМИНАЛ, ПЛТ, ПРОЦПЛАТ, ОСНПЛТ, ОБЩПЛАТ, ОБЩДОХОД, АПЛ, ФУО, ДДОБ, ПУО.
Мастер функций Excel.
Автоматизация вычислений в среде электронной таблицы Microsoft Excel for Windows удобно проводить с помощью встроенных функций. Данные функции вызываются с помощью кнопки-пиктограммы fx, расположенной на панели инструментов (обычно под строкой меню: Файл, Правка, Вид, …) «окна» Excel. При активизации кнопки (щелчок левой кнопкой мыши при наведенном курсоре) «всплывает» висящее окно: «Мастер функций – шаг 1 из 2», содержащее два поля: «Категория:» и «Функция:». В левом поле «Категория:» необходимо активизировать надпись «Финансовые», после чего в правом поле «Функция:» появится список всех доступных финансовых функций. Выбор нужной функции осуществляется наведением на нее курсора и однократным щелчком левой кнопки мыши. При этом в нижней части окна «Мастер функций – шаг 1 из 2» отображаются формат выбранной функции и ее назначение. Запуск выбранной функции осуществляется активизацией кнопки «ОК», расположенной в правом нижнем углу висящего окна. В минимальный комплект входит 15 финансовых функций. Для увеличения их количества до максимального числа необходимо закрыть висящее окно, активизировав кнопку «Отмена» в его правом нижнем углу. Затем следует активизировать опцию «Надстройки» программы «Сервис» строки меню. Во «всплывшем» окне «Надстройки» необходимо выбрать опцию «Пакет анализа», установить значок «v» слева соответствующей надписи и активизировать кнопку «ОК». Ниже рассмотрены форматы и назначения различных финансовых функций.
Управление финансовыми операциями.
1) БС(ставка;кпер;плт;пс;[тип]) = FVT(r;T;CF;PV;[тип]) – возвращает будущее значение (FVT) вклада с периодическими постоянными выплатами (CF) и постоянным процентом (r).
Аргумент [тип] – тип начисления процентов (1 – пренумерандо, 0 – постнумерандо). Квадратные скобки [] означают, что аргумент необязательный – по умолчанию [тип] = 0. Аргумент ставка может задаваться в десятичном и процентном виде (при установке процентного формата в соответствующей ячейке Excel), аргумент нз должен быть отрицательным для вкладчика (отток денег) и положительным для банка (увеличение пассивов). При начислении процентов m раз в году аргументы необходимо откорректировать: ставка = r/m, число_периодов = mT. При анализе элементарных потоков аргумент плт не используется (он имеет либо нулевое значение, либо пропуск).
Ввод данных (аргументов) в любую встроенную функцию Excel производится следующим образом (на примере функции БС). После активизации функции появляется висящее окно «БС» показанное на рис. 1.1.
Для ввода данных необходимо установить мигающий курсор в белое поле висящего окна, находящееся справа от имени аргумента. По умолчанию курсор устанавливается автоматически в первом (сверху) поле (в данном случае - в поле аргумента [норма]). Ввод данных производят, набирая с консоли число, либо адрес ячейки Excel, содержащей вводимое число, либо просто активизируя саму ячейку Excel курсором и левой кнопкой мыши. Если висящее окно закрывает необходимые ячейки, то его можно сдвинуть, либо «схлопнуть», активизируя пиктограмму в правом конце поля с вводимыми данными. «Расхлопывание» окна «БС» осуществляется с помощью той же пиктограммы. Значения вводимых данных и результат вычислений отображаются справа от знаков = . В ходе работы возможно получить контекстную подсказку, активизируя кнопку со значком ?. После ввода всех данных и активизации кнопки «ОК» вычисленное значение БС сохраняется в той ячейке Excel, в которой находился курсор до
5
вызова функции.
БС
Ставка |
= |
число |
|
||
Кпер |
= |
число |
|
||
Плт |
= |
число |
|
|
|
Пс |
= |
число |
Тип |
= |
число |
|
|
=
Возвращает будущее значение вклада с периодическими
Норма норма прибыли за период.
|
|
Значение: |
|
|
ОК |
|
|
|
Отмена |
|
? |
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Рис.1.1.
В таблице 1.1 приведен пример шаблона электронной таблицы Excel для реализации функции БС с примечаниями по заполнению висящего окна «БС» (столбец С).
2)КПЕР(ставка;плт;пс;бс;[тип]) = mT(r;CF;PV;FVT;[тип]) – возвращает общее количество периодов выплаты для данного вклада с постоянными выплатами и постоянным процентом.
При анализе элементарных потоков аргументу плт (выплата) присваивают нулевое значение. Аргументы пс и бс всегда должны иметь противоположные знаки!
3)СТАВКА(кпер;плт;пс;бс;[тип];[предположение]) = r/m – возвращает норму прибыли за один период при выплате ренты.
При анализе элементарных потоков аргументу плт присваивают нулевое значение. Аргументы пс и бс всегда должны иметь противоположные знаки! Аргумент [предположение] определяет начальное приближение (между 0 и 1) итерационно вычисляемой функции (по умолчанию ему присваивается значение 0).
|
|
|
|
|
Таблица 1.1 |
|
|
|
|
|
|
|
A |
|
|
B |
С |
|
|
|
|
||
1 |
Анализ операций с элементарными потоками платежей |
||||
2 |
|
|
|
|
|
|
|
|
|
|
|
3 |
Исходные данные : |
|
|
|
Примечания : |
|
|
|
|
|
|
4 |
Годовая процентная ставка, |
r = |
|
0,10 |
норма |
|
|
|
|
|
|
5 |
Количество начислений в году, |
m = |
|
4 |
|
|
|
|
|
|
|
6 |
Срок проведения операции (лет), T = |
|
5 |
число_периодов |
|
|
|
|
|
|
|
7 |
Начальное значение вклада, |
PV = |
|
1 000,00 р. |
нз |
|
|
|
|
|
|
8 |
|
|
|
|
|
9 |
Результаты вычислений : |
|
|
|
|
|
|
|
|
|
|
10 |
Будущее значение вклада, |
FVT = |
|
1 638,62р. |
БС(B4/B5;B5*B6; ;-B7; ) |
|
|
|
6 |
|
|
Годовую процентную ставку получают вычислением – r = СТАВКА * m.
4)БЗРАСПИС(первичное;план) = FV(PV; r1, r2, …) – возвращает будущее значение основного капитала после начисления сложных процентов по переменной ставке.
План задается укороченным списком адресов массива ячеек Excel (адрес первый: адрес последний), содержащих переменные процентные ставки. Например, =БЗРАСПИС(1000;B1:B4), где B1,B2,B3,B4 адреса массива.
5)ЭФФЕКТ(номинальная_ставка;кол_пер) = (1+r/m)m-1 – возвращает эффективную процентную ставку.
6)НОМИНАЛ(эффект_ставка;кол_пер) = r – возвращает номинальную годичную процентную ставку.
7)ПЛТ(ставка;кпер;пс;бс;[тип] ) = CF – возвращает величину выплаты за один период годовой ренты.
Часто используется совместно с БС и используется для разработки планов погашения кредитов.
Пример 1.1. Финансовая компания создает фонд путем помещения в банк суммы 50 000 р. с последующим ежегодным пополнением суммой по 10 000 р. Ставка по депозиту 10% годовых. Какова величина фонда к концу 4-го года?
=БС(0,1;4;-10000;-50000) 11 9615,00 р.
Пример 1.2. Решить обратную задачу – найти сумму ежегодного пополнения. = ПЛТ(0,1;4;-50000;119615,00) -10 000,00 р.
Пример 1.3. Банком выдан кредит в 10 000 р. на 5 лет под 15 % годовых, начисляемых один раз в конце каждого периода. Кредит должен быть погашен за 5 лет равными долями. Разработать план погашения кредита.
=ПЛТ(0,15;5;-10000) 2 983,16 р.
За 5 лет банк получит ПЛТ(0,15;5;-10000) * 5 = 14 915,80 р.
Часто функцию ПЛТ разбивают на две функции ПЛТ = ПРПЛТ + ОСПЛТ,
где составные функции определяются следующим образом
8) ПРПЛТ(ставка;период;кпер;пс;бс;[тип]) = CF1 – возвращает величину выплаты прибыли на вложения за данный период.
Так, для примера 3 п.7, получим = ПРПЛТ(0,15;1;5;-10000) 1 500,00 р.
9) ОСПЛТ(ставка;период;кпер;пс;бс;[тип]) = CF2 – возвращает величину выплат на основной капитал для вклада в данный период.
Для примера 3 п. 7, получим = ОСПЛТ (0,15;1;5;-10000) 1 483,16 р.
10) ОБЩПЛАТ(ставка;кол_пер;нз;нач_период;кон_период;[тип]) – возвращает общую выплату, проведенную между двумя периодическими выплатами. Это накопленная сумма процентов за период между двумя любыми выплатами.
Для примера 3 п.7, получим =ОБЩПЛАТ(0,15;5;-10000;1;5;0) 4 915,78 р.
11) ОБЩДОХОД(ставка;кол_пер;нз;нач_пер;кон_пер;[тип]) – возвращает общую выплату по займу между двумя периодами. Это удобный инструмент для определения накопленной суммы, поступающей в счет погашения основного долга по займу.
Для примера 3 п.7, получим =ОБЩДОХОД(0,15;5;-10000;1;5;0) 10 000 р. Отсюда видно, что ОБЩПЛАТ + ОБЩДОХОД = ППЛАТ * 5.
7
Лабораторная работа № 2
Использование мастера функций Excel для управления инвестициями, функции: ЧПС, ЧИСТНЗ, ВСД, ЧИСТВНДОХ, МВСД, ПС.
Пример 2.1. Проект рассчитан на два года (известны даты платежей) и требует начальных инвестиций в размере 10 000 руб. и имеет предполагаемые денежные поступления в размере: 25 000 руб., 30 000 руб. Рассчитать NPV проекта в предположении ставки 10 %.
Шаблон решения задачи представлен в таблице 2.1. Рассчитанное NPV находится в ячейке В9. Для решения задачи была использована функция ЧИСТНЗ.
ЧИСТНЗ(ставка;значения;даты) = NPV – возвращает чистую текущую стоимость инвестиции, вычисляемую на основе ряда периодических поступлений наличных и дисконтной ставки (таблица 2.1).
Таблица 2.1
A |
B |
С |
1 |
Анализ инвестиций |
|
2 |
|
|
3 |
Исходные данные : |
Результат : |
|
4 |
Дисконтная процентная |
|
|
|
ставка, |
r = |
0,10 |
5 |
|
|
|
6 |
Даты платежей: |
Суммы: |
|
7 |
|
30.01.98 |
-10 000,00р. =ЧИСТНЗ($B$4;B7;A7) |
8 |
|
30.01.99 |
25000р. =ЧИСТНЗ($B$4;B7:B8;A7:A8) |
9 |
|
30.01.00 |
30000р. =ЧИСТНЗ($B$4;B7:B9;A7:A9) |
|
|
|
|
Пример 2.2.Проект рассчитан на два года и требует начальных инвестиций в размере 10 000 руб. и имеет предполагаемые денежные поступления в размере: 25 000 руб., 30 000 руб. Рассчитать NPV проекта в предположении ставки 10 %.
Шаблон решения задачи представлен в таблице 2.2. Рассчитанное NPV находится в ячейке В9. Для решения задачи была использована функция ЧПС.
ЧПС (норма;значение1;значение2; …) = PV – возвращает чистый текущий объем вклада, вычисляемый на основе ряда последовательных поступлений наличных и нормы амортизации. Позволяет определять современную стоимость (PV) потока равномерно распределенных во времени платежей.
Таблица 2.2
A |
B |
С |
1 |
Анализ инвестиций |
|
2 |
|
|
3 |
Исходные данные : |
Результат : |
|
4 |
Дисконтная процентная |
|
|
|
ставка, |
r = |
0,10 |
5 |
|
|
|
6 |
|
Год: |
Суммы: |
7 |
|
0 |
-10 000,00р. =ЧПС($B$4;B7)+ $B$7 |
8 |
|
1 |
25000р. =ЧПС($B$4;B7:B8)+ $B$7 |
|
|
|
8 |
9 |
2 |
30000р. =ЧПС($B$4;B7:B9)+ $B$7 |
При решении задач анализа инвестиций также используют функции:
ВСД(значения;[предположения]) = IRR – возвращает внутреннюю норму доходности (скорость оборота) для ряда последовательных операций с наличными. Значения, задаваемые, как B7:B9 (см. таблицы 2.1, 2.2) должны включать по крайней мере одно положительное значение и одно отрицательное. Необязательный аргумент [предположение] это прогноз – величина, о которой предполагается, что она близка к результату вычислений. Начиная с прогноза, Excel делает итерационные вычисления с точностью 0,00001 %. Если после 20 попыток результат не достигается, то возвращается ошибка – « число!».
ЧИСТВНДОХ(значения;даты;[предположения]) = IRR – возвращает внутреннюю норму доходности для произвольного распределения во времени. Техника использования аналогична примеру 1.
МВСД(значения;финансовая_норма;реинвест_норма) = MIRR – возвращает модифицированную норму доходности с учетом реинвестирования дохода по норме «реинвест_норма».
Пример 2.3. Фирма собирается вложить средства в приобретение нового оборудования, стоимость которого вместе с доставкой и установкой составит 100 000 р. Ожидается, что внедрение оборудования обеспечит получение на протяжении 6 лет чистые доходы: 25 000 р., 30 000 р., 35 000 р., 40 000 р., 45 000 р., 50 000 р. Принятая норма дисконта равна 10 %. Имеется также возможность реинвестирования получаемых доходов по ставке 8 %. Какова экономическая эффективность проекта? Начиная с какого момента инвестиции окупаются? Решение приведено в таблице 2.3.
Таблица 2.3
|
|
A |
|
B |
|
С |
|
|
|
|
|
|
|
|
1 |
Оценка эффективности инвестиционного проекта |
||||
|
|
|
|
|
|
|
|
2 |
|
|
|
|
|
|
3 |
Исходные данные : |
|
Результаты : |
|
|
|
4 |
Финансовая норма доходности, |
|
|
|
|
|
|
|
d = |
0,10 |
|
|
|
5 |
Процентная ставка |
|
|
|
|
|
|
реинвестирования, |
r = |
0,08 |
|
|
|
6 |
Даты платежей: |
Суммы: |
=ЧИСТНЗ($B$4;B7:B13;A7:A13) |
|
|
|
7 |
|
30.01.98 |
-100 000,00р. |
-100 000,00р. |
|
|
8 |
|
30.01.99 |
25 000,00р. |
-77 272,73р. |
|
|
9 |
|
30.01.00 |
30 000,00р. |
-52 479,34р. |
|
|
10 |
|
30.01.01 |
35 000,00р. |
-26 190,19р. |
|
|
11 |
|
30.01.02 |
40 000,00р. |
1 123,22р. |
|
|
12 |
|
30.01.03 |
45 000,00р. |
29 057,38р. |
|
|
13 |
|
30.01.04 |
50 000,00р. |
57 273,71р. |
|
|
14 |
Используемые функции: |
|
Значения функций: |
|
|
|
|
|
|
Коммент.: |
|
|
|
|
|
|
|
|
|
|
15 |
=1-C13/B7 |
|
|
1,57 |
|
|
|
|
|
PI = |
|
|
|
|
|
|
|
|
|
|
16 |
=ЧИСТВНДОХ(B7:B13;A7:A13) |
IRR = |
25,50 % |
|
|
|
17 |
=МВСД(B7:B13;B4;B5) |
|
MIRR = |
18 % |
|
|
|
|
|
|
|
|
Из результатов видно, что проект стал окупаться к концу четвертого периода. При этом
9
модифицированная внутренняя норма доходности (MIRR= 18%) выше заданной (d = 10%). Поэтому проект можно считать прибыльным.
Пример 2.4.
Предполагаются два инвестиционных проекта, которые характеризуются предполагаемыми потоками платежей. Сравнить проекты на основе NPV и IRR.
Год |
Проект А |
Проект В |
0 |
-100 |
-100 |
1 |
50 |
20 |
2 |
40 |
40 |
3 |
40 |
50 |
4 |
30 |
60 |
Шаблон решения задачи представлен в таблице 2.4.
Таблица 2.4
Рассчитаем IRR для обоих проектов.
В В9 формулу =ВСД(В2:В6), формула скопируем в С9.
Рассчитаем NPV. Составим таблицу зависимости NPV от r для обоих проектов, построим график.
В E2 формулу = ЧПС($D2; B$3:B$6) + B$2. Эта формула скопируем в блок E2: F14. Для блока E2: F14 строим линейный график: зависимость NPV от дисконтной ставки. (рис. 2.1)
На диаграмме видим, что графики пересекаются при величине ставки 11%. Более точное значение определим при помощи команды Сервис, Подбор параметра. Для этого в Е17 запишем формулу: = ЧПС($D17; B$3:B$6) + B$2, скопируем ее в F17. В F18 запишем формулу: = Е17 - F17. В D17 помещаем начальное приближение 11 %. Точка пересечения графиков NPV проектов носит название точки Фишера. При r<11% выгоднее проект В, а при r> 11% выгоднее проект А.
10