Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:

Студентам ИТ / 3 ЛП_ИТ / ИТ_прогнозирования / ИТ_фин_произв_бизнесом_Excel

.pdf
Скачиваний:
46
Добавлен:
14.02.2016
Размер:
1.15 Mб
Скачать

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РФ Московский государственный университет технологий и управления

им. К.Г. Разумовского

(Первый казачий университет)

___________________________________________

Кафедра Информационных технологий

Краснов А.Е., Сагинов Ю.Л., Феоктистова Н.А.

ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ УПРАВЛЕНИЯ ФИНАНСАМИ, ПРОИЗВОДСТВОМ И БИЗНЕСОМ

Лабораторный практикум в среде 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