методичка по табличному процессору для магистров

ФГБОУ ВПО «Калининградский государственный технический университет»













Статистическая обработка данных и имитационное моделирование в среде табличного процессора Excel










Методические указания к лабораторным работам
для магистров экономического факультета специальности
080200 – Менеджмент









Калининград, 2012



Оглавление
13 TOC \o "1-3" \h \z \u 1413 LINK \l "_Toc339700653" 14Введение 13 PAGEREF _Toc339700653 \h 1431515
13 LINK \l "_Toc339700654" 14Лабораторная работа № 1 13 PAGEREF _Toc339700654 \h 1441515
13 LINK \l "_Toc339700655" 14Моделирование риска инвестиционного проекта с использованием 13 PAGEREF _Toc339700655 \h 1441515
13 LINK \l "_Toc339700656" 14встроенных функций ППП MS Excel 2007 13 PAGEREF _Toc339700656 \h 1441515
13 LINK \l "_Toc339700657" 14Лабораторная работа № 2 13 PAGEREF _Toc339700657 \h 14191515
13 LINK \l "_Toc339700658" 14Моделирование риска инвестиционного проекта с использованием 13 PAGEREF _Toc339700658 \h 14191515
13 LINK \l "_Toc339700659" 14инструмента «Генератор случайных чисел» MS Excel 13 PAGEREF _Toc339700659 \h 14191515
13 LINK \l "_Toc339700660" 14Лабораторная работа № 3 13 PAGEREF _Toc339700660 \h 14311515
13 LINK \l "_Toc339700661" 14Статистический анализ результатов имитационного эксперимента 13 PAGEREF _Toc339700661 \h 14311515
13 LINK \l "_Toc339700662" 14Лабораторная работа № 4 13 PAGEREF _Toc339700662 \h 14411515
13 LINK \l "_Toc339700663" 14Оптимизационный анализ в МS Excel 13 PAGEREF _Toc339700663 \h 14411515
13 LINK \l "_Toc339700667" 14Лабораторная работа № 5 13 PAGEREF _Toc339700667 \h 14481515
13 LINK \l "_Toc339700668" 14Прогнозирование данных в MS Excel 13 PAGEREF _Toc339700668 \h 14481515
13 LINK \l "_Toc339700669" 14Лабораторная работа № 6 13 PAGEREF _Toc339700669 \h 14521515
13 LINK \l "_Toc339700670" 14Зачетное задание 13 PAGEREF _Toc339700670 \h 14521515
13 LINK \l "_Toc339700671" 14Список литературы 13 PAGEREF _Toc339700671 \h 14561515
15

Введение

Данное пособие с лабораторным практикумом предназначено для студентов специальности 0802000 – «Менеджмент », изучающих дисциплину «Информационные технологии в менеджменте». Выбор программного продукта MS Excel 2007 обусловлен двумя причинами. Во-первых, данная программа является наиболее мощным и гибким средством обработки больших объемов цифровых данных со встроенными механизмами финансового и статистического анализа. Полученные в ходе выполнения лабораторных работ навыки будут в дальнейшем использованы магистрантами при подготовке выпускных магистерских диссертаций. Во-вторых, данный программный продукт очень широко распространен и доступен, поэтому каждый студент может успешно использовать его в дальнейшей профессиональной деятельности.
Пособие состоит из пяти лабораторных работ. В начале каждой работы приводятся краткие теоретические сведения, необходимые для выполнения работы. Далее описывается технология выполнения работы. Также для каждой работы предусмотрено конкретное задание по вариантам. Предложения, помеченные цифрами в тексте пособия, студент должен выполнять. Рассматриваемый перечень лабораторных работ может быть выполнен в течение одного семестра.
Лабораторные работы выполняются в компьютерных классах университета. Студентам рекомендуется иметь устройство для сохранения выполненных работ, т. к. задания в некоторых лабораторных работах предполагают использование результатов предыдущих работ.
При подготовке лабораторного практикума использованы некоторые примеры из литературы приведённого списка.



Лабораторная работа № 1


Моделирование риска инвестиционного проекта с использованием
встроенных функций ППП MS Excel 2007

Финансовый риск - уровень финансовой потери, выражающейся либо в возможности не достичь поставленной цели; либо в неопределённости прогнозируемого результата; либо в субъективности оценки прогнозируемого результата.
Статистические критерии риска следующие.
1. Вероятность (Р) события (Е) – отношение числа К случаев благоприятных исходов, к общему числу всех возможных исходов (М):
Р (Е)= К / М
2.Размах вариации (R) – разница между максимальным и минимальным значением фактора:
R=Xmax-Xmin
Этот показатель дает очень грубую оценку риску, т.к. он является абсолютным показателем и зависит только от крайних значений ряда.
3. Дисперсия – сумма квадратов отклонений случайной величины от ее среднего значения, взвешенных на соответствующие вероятности.
к=n

Vаr(Е) = ( рк (Хк - М(Е))2 ,

к=1

где М(Е) – среднее или ожидаемое значение (математическое ожидание) дискретной случайной величины Е
4. Математическое ожидание определяется как сумма произведений ее значений на их вероятности:
      к=n

М(Е)= ( Хкрк

      к=1

Это важнейшая характеристика случайной величины, т.к. служит центром распределения ее вероятностей. Смысл ее заключается в том, что она показывает наиболее правдоподобное значение фактора.
4. Среднее квадратическое отклонение ( (Е):

5. Коэффициент вариации (СV):
СV= ((E)/M (E)
Одним из способов оценки финансовых рисков служит имитационное моделирование. В общем случае под имитацией понимают процесс проведения на ЭВМ экспериментов с математическими моделями сложных систем реального мира.При анализе рисков инвестиционных проектов обычно используют в качестве базы для экспериментов прогнозные данные об объемах продаж, затратах, ценах и т.п. При проведении финансового анализа часто используются модели, содержащие случайные величины, поведение которых не детерминировано управлением или принимающими решения. Стохастическая имитация известна под названием "метод Монте-Карло".
Имитационное моделирование представляет собой серию численных экспериментов, призванных получить эмпирические оценки степени влияния различных факторов (исходных величин) на некоторые зависящие от них результаты (показатели).
В общем случае проведение имитационного эксперимента можно разбить на следующие этапы:
1. установить взаимосвязи между исходными и выходными показателями в виде математического уравнения или неравенства;
2. задать законы распределения вероятностей для ключевых параметров модели;
3. провести компьютерную имитацию значений ключевых параметров модели;
4. рассчитать основные характеристики распределений исходных и выходных показателей;
5. провести анализ полученных результатов и принять решение. Результаты имитационного эксперимента могут быть дополнены статистическим анализом, а также использоваться для построения прогнозных моделей сценариев.
Имитационное моделирование рисков может быть достаточно просто реализовано в среде EXCEL.
Исходные условия эксперимента.

Фирма рассматривает инвестиционный проект по производству продукта "А". В процессе предварительного анализа экспертами были выявлены три ключевых параметра проекта и определены возможные границы их изменений (таблица 1.1). Прочие параметры проекта считаются постоянными величинами (таблица 1.2).
Таблица 1.1 - Ключевые параметры проекта по производству продукта "А"
Показатель
Наихудший
Наилучший
Вероятный

Объем выпуска - Q
150
300
200

Цена за штуку - P
40
55
50

Переменные затраты - V
35
25
30


Таблица 1.2 - Неизменяемые параметры проекта по производству продукта "А"

Показатели
Наиболее вероятное значение

Постоянные затраты - F
500

Амортизация - A
100

Налог на прибыль - T
60%

Норма дисконта - r
10%

Срок проекта - n
5

Начальные инвестиции - I0
2000


Предположим, что используемым критерием оценки риска является чистая современная стоимость проекта NPV:
n
NPV=( ( NCFt / (1 + r)t - I0)
t=1
где:
NCFt - величина чистого потока платежей в периоде t.
По условиям примера, значения нормы дисконта r и первоначального объема инвестиций I0 известны и считаются постоянными в течение срока реализации проекта (таблица 1.2).
В целях упрощения будем полагать, что величина потока платежей NCF для любого периода t одинакова и может быть определена из следующего соотношения:
NCFt = Qt(Pt – Vt) – F – A)(1 – T) + A
Следующими этапом проведения анализа является выбор законов распределения вероятностей ключевых переменных.
По условиям примера ключевыми варьируемыми параметрами являются: переменные расходы V, объем выпуска Q и цена P. Диапазоны возможных изменений варьируемых показателей приведены в таблице 3.1. При этом будем исходить из предположения, что все ключевые переменные имеют равномерное распределение вероятностей.
Проведение имитационных экспериментов в среде ППП EXCEL можно осуществить двумя способами - с помощью встроенных функций и путем использования инструмента "Генератор случайных чисел" дополнения "Анализ данных" (Analysis ToolPack). Эти инструменты подключаются через пункты меню (Главная кнопка – Параметры - Надстройки – Пакет анализа – Перейти - ОК).
Если в ЭТ установлен режим автоматических вычислений, принятый по умолчанию, то возвращаемый функцией результат будет изменяться всякий раз, когда происходит ввод или корректировка данных. В режиме ручных вычислений пересчет всей ЭТ осуществляется только после нажатия клавиши [F9]. В нашем примере необходимо установить режим ручных вычислений (Главная кнопка – Параметры – Формулы – Параметры вычислений – Вручную).
В расчетах будут использоваться функции, описание которых приведено ниже.
Функция СЛУЧМЕЖДУ(нижн_граница; верхн_граница)
Как следует из названия этой функции, она позволяет получить случайное число из заданного интервала. При этом тип возвращаемого числа (т.е. вещественное или целое) зависит от типа заданных аргументов.
В качестве примера, сгенерируем случайное значение для переменной Q (объем выпуска продукта).
Введите в любую ячейку ЭТ формулу:
=СЛУЧМЕЖДУ(150; 300) (Результат: 210) .
Если задать аналогичные формулы для переменных P и V, а также формулу для вычисления NPV и скопировать их требуемое число раз, можно получить генеральную совокупность, содержащую различные значения исходных показателей и полученных результатов. После чего нетрудно рассчитать соответствующие параметры распределения и провести вероятностный анализ.
Продемонстрируем изложенный подход на решении приведенного выше примера. Перед тем, как приступить к разработке шаблона, целесообразно установить в ЭТ режим ручных вычислений.
Приступаем к разработке шаблона. С целью упрощения и повышения наглядности анализа выделим для его проведения в рабочей книге ППП EXCEL два листа.
Первый лист - "Имитация", предназначен для построения генеральной совокупности (рис. 1.1). Его необходимо создать в точном соответствии с рисунком. Далее необходимо задать имена соответствующим диапазонам ячеек. Учтите, что количество имитационных экспериментов 500, соответственно диапазоны ячеек включают строки с десятой по пятьсот десятую (выделяете диапазоны и затем используйте команду ФОРМУЛЫ – ПРИСВОИТЬ ИМЯ). Имена приведены в таблице 1.3.

Рис. 1.1 - Лист "Имитация"

Таблица 1.3 - Имена ячеек листа "Имитация"
Адрес ячейки
Имя
Комментарии

Блок A10:A510
Перем_расх
Переменные расходы

Блок B10:B510
Количество
Объем выпуска

Блок C10:C510
Цена
Цена изделия

Блок D10:D510
Поступления
Поступления от проекта NCFt

Блок E10:E510
ЧСС
Чистая современная стоимость NPV


После того, как создан первый лист «Имитация» и соответствующим диапазонам ячеек назначены имена, необходимо приступить к созданию второго листа «Результаты анализа». Шаблон этого листа приведен на рис. 1.2. Имена ячеек листа приведены в таблице 1.4.


Рис. 1.2 – Лист «Результаты анализа»

Таблица 1.4 - Имена ячеек листа "Результаты анализа"
Адрес ячейки
Имя
Комментарии

B2
Нач_инвест
Начальные инвестиции

B3
Пост_расх
Постоянные расходы

B4
Аморт
Амортизация

D2
Норма
Норма дисконта

D3
Налог
Ставка налога на прибыль

D4
Срок
Срок реализации проекта



После того, как два листа созданы и определены имена ячеек и диапазонов, необходимо приступить к вводу соответствующих формул. Эти формулы представлены в таблицах 1.5 и 1.6. Обратите внимание на то, что при указании нижней и верхней границы изменений используется абсолютная адресация ячеек.

Таблица 1.5 - Формулы листа "Имитация"
Ячейка
Формула

A10
=СЛУЧМЕЖДУ($B$3;$C$3)

B10
=СЛУЧМЕЖДУ($B$4;$C$4)

C10
=СЛУЧМЕЖДУ($B$5;$C$5)

D10
=(B10*(C10-A10)-Пост_расх-Аморт)*(1-Налог)+Аморт

E10
=ПС(Норма;Срок;-D10)-Нач_инвест


Примечание: Формулы в таблице 5 необходимо скопировать в соответствующие диапазоны до номера строки – 510.

Таблица 1.6 - Формулы листа "Результаты анализа"
Ячейка
Формула

B8
=СРЗНАЧ(Перем_расх)

B9
=СТАНДОТКЛОНП(Перем_расх)

B10
=B9/B8

B11
=МИН(Перем_расх)

B12
=МАКС(Перем_расх)

C8
=СРЗНАЧ(Количество)

C9
=СТАНДОТКЛОНП(Количество)

C10
=C9/C8

C11
=МИН(Количество)

C12
=МАКС(Количество)

D8
=СРЗНАЧ(Цена)

D9
=СТАНДОТКЛОНП(Цена)

D10
=D9/D8

D11
=МИН(Цена)

D12
=МАКС(Цена)

E8
=СРЗНАЧ(Поступления)

E9
=СТАНДОТКЛОНП(Поступления)

E10
=E9/E8

E11
=МИН(Поступления)

E12
=МАКС(Поступления)

F8
=СРЗНАЧ(ЧСС)

F9
=СТАНДОТКЛОНП(ЧСС)

F10
=F9/F8

F11
=МИН(ЧСС)

F12
=МАКС(ЧСС)

F13
=СЧЁТЕСЛИ(ЧСС;"<0")

F14
=СУММЕСЛИ(ЧСС;"<0")

F15
=СУММЕСЛИ(ЧСС;">0")

Е18
=НОРМАЛИЗАЦИЯ(D18;$F$8;$F$9)

F18
=НОРМСТРАСП(E18)


Лист "Результаты анализа" кроме значений постоянных переменных содержит также функции, вычисляющие параметры распределения изменяемых (Q, V, P) и результатных (NCF, NPV) переменных и вероятности различных событий. Приведем описание этих функций.
Функции МИН() и МАКС() вычисляют минимальное и максимальное значение для массива данных из блока ячеек, указанного в качестве их аргумента. Имена и диапазоны этих блоков приведены в таблице 3.6.
Функция СЧЕТЕСЛИ() осуществляет подсчет количества ячеек в указанном блоке, значения которых удовлетворяют заданному условию. Функция имеет следующий формат:
=СЧЕТЕСЛИ(блок; "условие").
В данном случае, заданная в ячейке F13, эта функция осуществляет подсчет количества отрицательных значений NPV, содержащихся в блоке ячеек ЧСС.
Механизм действия функции СУММЕСЛИ() аналогичен функции СЧЕТЕСЛИ(). Отличие заключается лишь в том, что эта функция суммирует значения ячеек в указанном блоке, если они удовлетворяют заданному условию. Функция имеет следующий формат:
=СУММЕСЛИ(блок; "условие").
Две последние формулы (ячейки Е18 и F18) предназначены для проведения вероятностного анализа распределения NPV .
Функция НОРМАЛИЗАЦИЯ(x; среднее; станд_откл)
Эта функция возвращает нормализованное значение Z величины x, на основании которого затем вычисляется искомая вероятность p(E
· x). Она реализует вышеприведенную формулу. Функция требует задания трех аргументов:
х - нормализуемое значение;
среднее - математическое ожидание случайной величины Е;
станд_откл - стандартное отклонение.
Полученное значение Z является аргументом для следующей функции -НОРМСТРАСП().
Функция НОРМСТРАСП(Z)
Эта функция возвращает стандартное нормальное распределение, т.е. вероятность того, что случайная нормализованная величина Е будет меньше или равна х. Она имеет всего один аргумент - Z, вычисляемый функцией НОРМАЛИЗАЦИЯ().
Нетрудно заметить, что эти функции следует использовать вместе. При этом наиболее эффективным и компактным способом их задания является указание функции НОРМАЛИЗАЦИЯ() в качестве аргумента функции - НОРМСТРАСП(), т.е.:
=НОРМСТРАСП(НОРМАЛИЗАЦИЯ(x; среднее; станд_откл)).
С целью повышения наглядности, в проектируемом шаблоне функции заданы раздельно (ячейки Е18 и F18).
Сформируйте данный шаблон и сохраните его на магнитном диске под именем SIMUL_1. Приступаем к имитационному эксперименту. Для его проведения необходимо выполнить следующие шаги.
1. Ввести значения постоянных переменных (табл. 1.2) в ячейки В2:В4 и D2:D4 листа "Результаты анализа".
2. Ввести значения диапазонов изменений ключевых переменных (табл. 1.1) в ячейки В3:С5 листа "Имитация".
3. Нажатием клавиши F9 провести расчет.
4. Перейти к листу "Результаты анализа" и проанализировать полученные результаты.
Результатом выполнения этих действий будет заполнение блока А10:Е510 случайными значениями ключевых переменных V, Q, P и результатами вычислений величин NCF и NPV. Фрагмент результатов имитации приведен на рис. 1.3. Соответствующие проведенному эксперименту результаты анализа приведены на рисунке 1.4.

Рис. 1.3- Результаты имитации

Рис. 1.4 - Результаты анализа
Сумма всех отрицательных значений NPV в полученной генеральной совокупности (ячейка F14) может быть интерпретирована как чистая стоимость неопределенности для инвестора в случае принятия проекта. Аналогично сумма всех положительных значений NPV (ячейка F15) может трактоваться как чистая стоимость неопределенности для инвестора в случае отклонения проекта. Несмотря на всю условность этих показателей, в целом они представляют собой индикаторы целесообразности проведения дальнейшего анализа.
В данном случае они наглядно демонстрируют несоизмеримость суммы возможных убытков по отношению к общей сумме доходов (-11691,92 и 1692669,76 соответственно).
На практике одним из важнейших этапов анализа результатов имитационного эксперимента является исследование зависимостей между ключевыми параметрами. Ограничимся визуальным (графическим) исследованием. На рисунке 1.5 приведен график распределения значений ключевых параметров V, P и Q, построенный на основании 75 имитаций.
Нетрудно заметить, что в целом, вариация значений всех трех параметров носит случайный характер, что подтверждает принятую ранее гипотезу об их независимости. Для сравнения ниже приведен график распределений потока платежей NCF и величины NPV (рисунок 1.6).



Рис. 1.5 Распределение значений параметров V, P и Q

Рис. 1.6 - Зависимость между NCF и NPV

Задание к лабораторной работе № 1

Постройте модель из теоретической части, проведите с ней эксперименты.
Используйте варианты индивидуальных заданий для постановки в построенную модель
Постройте отчет по индивидуальному заданию, куда включите фрагмент листа «Имитация» - первые 40 экспериментов, полностью лист «Результаты анализа». Прокомментируйте каждый показатель этого листа с точки зрения оценивания риска инвестиционного проекта.
Постройте график зависимости NCF и NPV для своего задания. Используйте первые 50 значений.





Варианты индивидуальных заданий для выполнения лабораторной работы № 1

Показатели
Сценарий


Наихудший
Наилучший
Вероятный

Вариант 1

Объем выпуска - Q
1000
1300
1200

Цена за штуку - P
130
150
140

Переменные затраты - V
145
125
130

Вариант 2

Объем выпуска - Q
1500
3000
2000

Цена за штуку - P
300
450
400

Переменные затраты - V
350
250
300


Вариант 3

Объем выпуска – Q
2150
2300
2200

Цена за штуку – P
240
250
245

Переменные затраты – V
250
230
220

Вариант 4

Объем выпуска - Q
800
900
850

Цена за штуку - P
70
85
75

Переменные затраты - V
80
60
50

Вариант 5

Объем выпуска - Q
500
600
550

Цена за штуку - P
45
55
50

Переменные затраты - V
50
40
30



Лабораторная работа № 2


Моделирование риска инвестиционного проекта с использованием
инструмента «Генератор случайных чисел» MS Excel

Этот инструмент предназначен для автоматической генерации множества данных (генеральной совокупности) заданного объема, элементы которого характеризуются определенным распределением вероятностей. При этом могут быть использованы 7 типов распределений: равномерное, нормальное, Бернулли, Пуассона, биномиальное, модельное и дискретное. Применение инструмента "Генератор случайных чисел", как и большинства используемых в этой работе функций, требует установки специального дополнения "Пакет анализа" (см лабораторную работу № 1).
Для демонстрации техники применения этого инструмента изменим условия примера, рассмотренного в предыдущей лабораторной работе, определив вероятности для каждого сценария развития событий следующим образом (таблица 2.1). Мы также будем исходить из предположения о нормальном распределении ключевых переменных. Количество имитаций оставим прежним - 500.

Таблица 2.1 - Вероятностные сценарии реализации проекта
Показатели
Наихудший
P = 0.25
Наилучший
P = 0.25
Вероятный
P = 0.5

Объем выпуска Q
150
300
200

Цена за штуку P
40
55
50

Переменные затраты V
35
25
30


Приступим к формированию шаблона. Как и в предыдущем случае, выделим в рабочей книге два листа: "Имитация" и "Результаты анализа".
Формирование шаблона целесообразно начать с листа "Результаты анализа" (рисунок 2.1).

Рис. 2.1. Лист "Результаты анализа" (шаблон II)

Как следует из рисунка 2.1, этот лист практически соответствует ранее разработанному для решения предыдущей задачи (см. лабораторную работу № 3). Отличие составляют лишь формулы для расчета вероятностей, которые приведены в таблице 2.2.

Таблица 2.2 - Формулы листа "Результаты анализа" (шаблон II)
Ячейка
Формула

1
2

В17
=НОРМРАСП(0;B8;B9;1)

В18
=НОРМРАСП(B11;B8;B9;1)

В19
=НОРМРАСП(B12;B8;B9;1)-НОРМРАСП(B8+B9;B8;B9;1)

В20
=НОРМРАСП(B8;B8;B9;1)-НОРМРАСП(B8-B9;B8;B9;1)


Окончание таблицы 2.2

1
2

С17
=НОРМРАСП(0;C8;C9;1)

С18
=НОРМРАСП(C11;C8;C9;1)

С19
=НОРМРАСП(C12;C8;C9;1)-НОРМРАСП(C8+C9;C8;C9;1)

С20
=НОРМРАСП(C8;C8;C9;1)-НОРМРАСП(C8-C9;C8;C9;1)

D17
=НОРМРАСП(0;D8;D9;1)

D18
=НОРМРАСП(D11;D8;D9;1)

D19
=НОРМРАСП(D12;D8;D9;1)-НОРМРАСП(D8+D9;D8;D9;1)

D20
=НОРМРАСП(D8;D8;D9;1)-НОРМРАСП(D8-D9;D8;D9;1)

E17
=НОРМРАСП(0;E8;E9;1)

E18
=НОРМРАСП(E11;E8;E9;1)

E19
=НОРМРАСП(E12;E8;E9;1)-НОРМРАСП(E8+E9;E8;E9;1)

E20
=НОРМРАСП(E8;E8;E9;1)-НОРМРАСП(E8-E9;E8;E9;1)

F17
=НОРМРАСП(0;F8;F9;1)

F18
=НОРМРАСП(F11;F8;F9;1)

F19
=НОРМРАСП(F12;F8;F9;1)-НОРМРАСП(F8+F9;F8;F9;1)

F20
=НОРМРАСП(F8;F8;F9;1)-НОРМРАСП(F8-F9;F8;F9;1)


Используемые в нем собственные имена ячеек также взяты из аналогичного листа предыдущего шаблона (см. лабораторную работу № 1).
Для быстрого формирования нового листа "Результаты анализа" выполните следующие действия.
1 Загрузите предыдущий шаблон SIMUL_1 и сохраните его под другим именем, например - SIMUL_2
2. Удалите лист "Имитация".
3. Перейдите в лист "Результаты анализа". Удалите строки 17-18. Откорректируйте заголовок.
4. Добавьте формулы из таблицы 2.1. Для этого введите соответствующие формулы в ячейки блока В17:В20 и скопируйте их в блок С17:F20. Введите соответствующие комментарии.
5. Сверьте полученную таблицу с рисунком 2.1.
6. Перейдите к следующему листу и присвойте ему имя "Имитация". Приступаем к его формированию (рис. 2.2).

Рис. 2.2. Лист "Имитация" (шаблон II)

Первая часть этого листа (блок ячеек А1:Е10) предназначена для ввода исходных данных и расчета необходимых параметров их распределений. Напомним, что нормальное распределение случайной величины характеризуется двумя параметрами - математическим ожиданием (средним) и стандартным отклонением. Для удобства определения формул и повышения их наглядности блоку ячеек Е3:Е5 присвоено имя "Вероятности" (см. таблицу 2.3). Остальные имена остаются как в предыдущей лабораторной работе. Формулы расчета указанных параметров для ключевых переменных модели заданы в блоках ячеек В7:D7 и B8:D8 соответственно (см. таблицу 2.4).

Таблица 2.3 - Имена ячеек листа "Имитация" (шаблон II)
Адрес ячейки
Имя
Комментарии

Блок Е3:Е5
Вероятности
Вероятность значения параметра

Блок A13:A512
Перем_расх
Переменные расходы

Блок B13:B512
Количество
Объем выпуска

Блок C13:C512
Цена
Цена изделия

Блок D13:D512
Поступления
Поступления от проекта NCF

Блок E13:E512
ЧСС
Чистая современная стоимость NPV


Таблица 2.4 - Формулы листа "Имитация" (шаблон II)
Ячейка
Формула

В7
=СУММПРОИЗВ(B3:B5; Вероятности)

В8
{=КОРЕНЬ(СУММПРОИЗВ((B3:B5 - B7)^2; Вероятности))}

С7
=СУММПРОИЗВ(C3:C5; Вероятности)

С8
{=КОРЕНЬ(СУММПРОИЗВ((C3:C5 - C7)^2; Вероятности))}

D7
=СУММПРОИЗВ(D3:D5; Вероятности)

D8
{=КОРЕНЬ(СУММПРОИЗВ((D3:D5 - D7)^2; Вероятности))}

E10
=B10+13 -1

D13
=(B13*(C13-A13)-Пост_расх-Аморт)*(1-Налог)+Аморт

E13
=ПС(Норма; Срок; -D13) - Нач_инвест


Обратите внимание на то, что для расчета стандартных отклонений используются формулы-массивы. Для формирования блока формул достаточно определить их для ячеек В7:В8 и затем скопировать в блок С7:D8.
Формула в ячейке Е10 по заданному числу имитаций (ячейка В10) вычисляет номер последней строки для блоков, в которых будут храниться сгенерированные значения ключевых переменных.
Ячейки D13:E13 содержат уже знакомые нам формулы для расчета величины потока платежей NCF и его чистой современной стоимости NPV.
Сформируйте элементы оформления листа "Имитация", определите необходимые имена для блоков ячеек (таблица 2.3) и задайте требуемые формулы (таблица 4.3). Сверьте полученную ЭТ с рисунком 2.2. Сохраните полученный шаблон под именем SIMUL_2.
Введите исходные значения постоянных переменных (лабораторная работа № 1) в ячейки В2:В4 и D2:D4 листа "Результаты анализа". Перейдите к листу "Имитация". Введите значения ключевых переменных и соответствующие вероятности (таблица 2.1). Полученная в результате ЭТ должна иметь вид, как рисунке 2.3.


Рис. 2.3. Лист "Имитация" после ввода исходных данных

Установите курсор в ячейку А13. Приступаем к проведению имитационного эксперимента.
1. Выберите команду ДАННЫЕ -"Анализ данных". Результатом выполнения этих действий будет появление диалогового окна "Анализ данных", содержащего список инструментов анализа.
2. Выберите из списка "Инструменты анализа" пункт "Генерация случайных чисел" и нажмите кнопку "ОК" (рисунок 2.4). На экране появится диалоговое окно "Генерация случайных чисел". Укажите в списке "Распределения" требуемый тип - "Нормальное". Заполните остальные поля изменившегося окна согласно рисунку 2.5 и нажмите кнопку "ОК". Результатом будет заполнение блока ячеек А13:А512 (переменные расходы) сгенерированными случайными значениями.

Рис. 2.4. Выбор инструмента "Генерация случайных чисел"
 
Рис.2.5. Заполнение полей окна "Генерация случайных чисел"

Приведем необходимые пояснения. Первым заполняемым аргументом диалогового окна "Генерация случайных чисел" является поле "Число переменных". Оно задает количество колонок ЭТ, в которых будут размещаться сгенерированные в соответствии с заданным законом распределения случайные величины. В нашем примере оно должно содержать 1, так как ранее мы отвели под значения переменной V (переменные расходы) в ЭТ одну колонку - "А". В случае, если указывается число больше 1, случайные величины будут размещены в соответствующем количестве соседних колонок, начиная с активной ячейки. Если это число не введено, то все колонки в выходном диапазоне будут заполнены.
Следующим обязательным аргументом для заполнения является содержимое поля "Число случайных чисел" (т.е. количество имитаций). Согласно условиям примера оно должно быть равно 500. При этом ППП EXCEL автоматически подсчитывает необходимое количество ячеек для хранения генеральной совокупности.
Необходимый вид распределения задается путем соответствующего выбора из списка "Распределения". Как уже отмечалось ранее, могут быть получены 7 наиболее распространенных в практическом анализе типов распределений, каждое из которых характеризуется собственными параметрами. Выбранный тип распределения определяет внешний вид диалогового окна. В рассматриваемом примере выбор типа распределения "Нормальное" повлек за собой появление дополнительных аргументов - его параметров "Среднее" и "Стандартное отклонение", рассчитанных ранее для исследуемой переменной V в ячейках В7 и В8 листа "Имитация". К сожалению эти аргументы могут быть заданы только в виде констант. Использование адресов ячеек и собственных имен здесь не допускается!
Указание аргумента "Случайное рассеивание" позволяет при повторных запусках генератора получать те же значения случайных величин, что и при первом. Таким образом, одну и ту же генеральную совокупность случайных чисел можно получить несколько раз, что значительно повышает эффективность анализа (сравните с предыдущим шаблоном!). В случае если этот аргумент не задан (равен 0), при каждом последующем запуске генератора будет формироваться новая генеральная совокупность. В нашем примере этот аргумент задан равным 1, что позволит нам оперировать с одной и той же генеральной совокупностью и избежать постоянных перерасчетов ЭТ.
Последний аргумент диалогового окна "Генерация случайных чисел" - "Параметры вывода" определяет место расположения полученных результатов. Место вывода задается путем установления соответствующего флажка. При этом можно выбрать три варианта размещения:
- выходной блок ячеек на текущем листе - введите ссылку на левую верхнюю ячейку выходного диапазона, при этом его размер будет определен автоматически и в случае возможного наложения генерируемых значений на уже имеющиеся данные на экран будет выведено предупреждающее сообщение;
- новый рабочий лист - в рабочей книге будет открыт новый лист, содержащий результаты генерации случайных величин, начиная с ячейки A1;
- новая рабочая книга - будет открыта новая книга с результатами имитации на первом листе.
В рассматриваемом примере для проведения дальнейшего анализа необходимо, чтобы случайные величины размещались в специально отведенные для них блоки ячеек. В частности для хранения 500 значений первой переменной ранее был отведен блок ячеек А13:А512. Поскольку для этого блока определено собственной имя - "Перем_расх", оно указано в качестве выходного диапазона. Отметим, что при увеличении либо уменьшении количества имитаций необходимо также переопределить и выходные блоки, предназначенные для хранения значений переменных.
Генерация значений остальных переменных Q и Р осуществляется аналогичным образом, путем выполнения шагов 1-3. Пример заполнения окна "Генерация случайных чисел" для переменной Q (количество) приведен на рисунке 2.6.


Рис.2.6. Заполнение полей окна для переменной Q

Для получения генеральной совокупности значений потока платежей и их чистой современной стоимости необходимо скопировать формулы базовой строки (ячейки D13:E13) требуемое число раз (499).
Полученные результаты решения примера приведены на рисунках 2.7 и 2.8.


Рис. 2.7. Результаты имитационного эксперимента (шаблон II)


Рис.2.8. Результаты анализа (шаблон II)
Задание к лабораторной работе № 2

Постройте модель из теоретической части, проведите с ней эксперименты.
Используйте варианты индивидуальных заданий для постановки данных в построенную модель
Постройте отчет, куда включите фрагмент листа «Имитация» - первые 40 экспериментов, полностью лист «Результаты анализа». Прокомментируйте каждый показатель этого листа с точки зрения оценивания риска инвестиционного проекта.


Лабораторная работа № 3


Статистический анализ результатов имитационного эксперимента

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

где X, Y - множества значений случайных величин размерности m; M(X) - математическое ожидание случайной величины Х; M(Y) - математическое ожидание случайной величины Y.
Как следует из формулы, положительная ковариация наблюдается в том случае, когда большим значениям случайной величины Х соответствуют большие значения случайной величины Y, т.е. между ними существует тесная прямая взаимосвязь. Соответственно отрицательная ковариация будет иметь место при соответствии малым значениям случайной величины Х больших значений случайной величины Y. При слабо выраженной зависимости значение показателя ковариации близко к 0.
Ковариация зависит от единиц измерения исследуемых величин, что ограничивает ее применение на практике. Более удобным для использования в анализе является производный от нее показатель - коэффициент корреляции R, вычисляемый по формуле:

Коэффициент корреляции обладает теми же свойствами, что и ковариация, однако является безразмерной величиной и принимает значения от -1 (характеризует линейную обратную взаимосвязь) до +1 (характеризует линейную прямую взаимосвязь). Для независимых случайных величин значение коэффициента корреляции близко к 0.
Определение количественных характеристик для оценки тесноты взаимосвязи между случайными величинами в ППП EXCEL может быть осуществлено двумя способами:
- с помощью статистических функций КОВАР() и КОРРЕЛ();
- с помощью специальных инструментов статистического анализа.
Если число исследуемых переменных больше 2, более удобным является использование инструментов анализа.
Инструмент анализа данных "Корреляция"
Определим степень тесноты взаимосвязей между переменными V, Q, P, NCF и NPV. При этом в качестве меры будем использовать показатель корреляции R.
1. Выберите "Анализ данных". Выберите из списка "Инструменты анализа" пункт "Корреляция" и нажмите кнопку "ОК" (рисунок 3.1). Результатом будет появление окна диалога инструмента "Корреляция".
2. Заполните поля диалогового окна, как показано на рисунке 3.2.
Вид полученной ЭТ после выполнения элементарных операций форматирования приведен на рисунке 3.3.


Рис.3.1. Список инструментов анализа (выбор пункта "Корреляция")

Рис.3.2. Заполнение окна диалога инструмента "Корреляция"



Рис. 3.3. Результаты корреляционного анализа

Результаты корреляционного анализа представлены в ЭТ в виде квадратной матрицы, заполненной только наполовину, поскольку значение коэффициента корреляции между двумя случайными величинами не зависит от порядка их обработки. Нетрудно заметить, что эта матрица симметрична относительно главной диагонали, элементы которой равны 1, так как каждая переменная коррелирует сама с собой.
Как следует из результатов корреляционного анализа, выдвинутая в процессе решения предыдущего примера гипотеза о независимости распределений ключевых переменных V, Q, P в целом подтвердилась. Значения коэффициентов корреляции между переменными расходами V, количеством Q и ценой Р (ячейки В3:В4, С4) достаточно близки к 0.
В свою очередь величина показателя NPV напрямую зависит от величины потока платежей (R = 1). Кроме того, существует корреляционная зависимость средней степени между Q и NPV (R = 0,548), P и NPV (R = 0,67). Как и следовало ожидать, между величинами V и NPV существует умеренная обратная корреляционная зависимость (R = -0,39).
Полезность проведения последующего статистического анализа результатов имитационного эксперимента заключается также в том, что во многих случаях он позволяет выявить некорректности в исходных данных, либо даже ошибки в постановке задачи. В частности в рассматриваемом примере, отсутствие взаимосвязи между переменными затратами V и объемами выпуска продукта Q требует дополнительных объяснений, так как с увеличением последнего, величина V также должна расти . Таким образом, установленный диапазон изменений переменных затрат V нуждается в дополнительной проверке и, возможно, корректировке.
Следует отметить, что близкие к нулевым значения коэффициента корреляции R указывают на отсутствие линейной связи между исследуемыми переменными, но не исключают возможности нелинейной зависимости. Кроме того, высокая корреляция не обязательно всегда означает наличие причинной связи, так как две исследуемые переменные могут зависеть от значений третьей.
При проведении имитационного эксперимента и последующего вероятностного анализа полученных результатов мы исходили из предположения о нормальном распределении исходных и выходных показателей. Вместе с тем, справедливость сделанных допущений, по крайней мере для выходного показателя NPV, нуждается в проверке.
Для проверки гипотезы о нормальном распределении случайной величины применяются специальные статистические критерии: Колмогорова-Смирнова,
· 2. В целом ППП EXCEL позволяет быстро и эффективно осуществить расчет требуемого критерия и провести статистическую оценку гипотез.
Инструмент анализа данных "Описательная статистика"
Чем больше характеристик распределения случайной величины нам известно, тем точнее мы можем судить об описываемых ею процессов. Инструмент "Описательная статистика" автоматически вычисляет наиболее широко используемые в практическом анализе характеристики распределений. При этом значения могут быть определены сразу для нескольких исследуемых переменных.
Определим параметры описательной статистики для переменных V, Q, P, NCF, NPV. Для этого необходимо выполнить следующие шаги.
1. "Анализ данных". Выберите из списка "Инструменты анализа" пункт "Описательная статистика".
2. Заполните поля диалогового окна, как показано на рисунке 3.4 и нажмите кнопку "ОК".
Результатом выполнения указанных действий будет формирование отдельного листа, содержащего вычисленные характеристики описательной статистики для исследуемых переменных. Выполнив операции форматирования, можно привести полученную ЭТ к более наглядному виду (рисунок 3.5).
Многие из приведенных в данной ЭТ характеристик вам уже хорошо знакомы, а их значения уже определены с помощью соответствующих функций на листе "Результаты анализа". Поэтому рассмотрим лишь те из них, которые не упоминались ранее.
Вторая строка ЭТ содержит значения стандартных ошибок
· для средних величин распределений. Другими словами среднее или ожидаемое значение случайной величины М(Е) определено с погрешностью
·
· .


Рис. 3.4. Заполнение полей диалогового окна "Описательная статистика"

Рис.3.5. Описательная статистика для исследуемых переменных

Медиана - это значение случайной величины, которое делит площадь, ограниченную кривой распределения, пополам (т.е. середина численного ряда или интервала). Как и математическое ожидание, медиана является одной из характеристик центра распределения случайной величины. В симметричных распределениях значение медианы должно быть равным или достаточно близким к математическому ожиданию.
Как следует из полученных результатов, данное условие соблюдается для исходных переменных V, Q, P (значения медиан лежат в диапазоне М(Е)
·
· , т.е. - практически совпадают со средними). Однако для результатных переменных NCF, NPV значения медиан лежат ниже средних, что наводит на мысль о правосторонней асимметричности их распределений.
Мода - наиболее вероятное значение случайной величины (наиболее часто встречающееся значение в интервале данных). Для симметричных распределений мода равна математическому ожиданию. Иногда мода может отсутствовать. В данном случае ППП EXCEL вернул сообщение об ошибке. Таким образом, вычисление моды не представляется возможным.
Эксцесс характеризует остроконечность (положительное значение) или пологость (отрицательное значение) распределения по сравнению с нормальной кривой. Теоретически, эксцесс нормального распределения должен быть равен 0. Однако на практике для генеральных совокупностей больших объемов его малыми значениями можно пренебречь.
В рассматриваемом примере примерно одинаковый положительный эксцесс наблюдается у распределений переменных Q, NCF, NPV. Таким образом графики этих распределений будут чуть остроконечнее, по сравнению с нормальной кривой. Соответственно графики распределений для переменных V и Р будут чуть более пологими, по отношению к нормальному.
Асимметричность (коэффициент асимметрии или скоса - s) характеризует смещение распределения относительно математического ожидания. При положительном значении коэффициента распределение скошено вправо, т.е. его более длинная часть лежит правее центра (математического ожидания) и обратно. Для нормального распределения коэффициент асимметрии равен 0. На практике, его малыми значениями можно пренебречь.
В частности асимметрию распределений переменных V, Q, P в данном случае можно считать несущественной, чего нельзя однако сказать о распределении величины NPV.
Осуществим оценку значимости коэффициента асимметрии для распределения NPV. Наиболее простым способом получения такой оценки является определение стандартной (средней квадратической) ошибки асимметрии, рассчитываемой по формуле:


где n - число значений случайной величины (в данном случае 500).
Если отношение коэффициента асимметрии s к величине ошибки
· as меньше трех (т.е.: s /
· as < 3), то асимметрия считается несущественной, а ее наличие объясняется воздействием случайных факторов. В противном случае асимметрия статистически значима и факт ее наличия требует дополнительной интерпретации. Осуществим оценку значимости коэффициента асимметрии для рассматриваемого примера.
Введите в любую ячейку ЭТ формулу:
= 0,763 / КОРЕНЬ(6*499 / 501*503) (Результат: 7,06).
Поскольку отношение s /
· as > 3, асимметрию следует считать существенной. Таким образом наше первоначальное предположение о правосторонней скошенности распределения NPV подтвердилась.
Для рассматриваемого примера наличие правосторонней асимметрии может считаться положительным моментом, так как это означает, что большая часть распределения лежит выше математического ожидания, т.е. большие значения NPV являются более вероятными.
Аналогичным способом можно осуществить проверку значимости величины эксцесса е. Формула для расчета стандартной ошибки эксцесса имеет следующий вид:

где n - число значений случайной величины.
Если отношение e /
· ex < 3, эксцесс считается незначительным и его величиной можно пренебречь.
Величина "Интервал" определяется как разность между максимальным и минимальным значением случайной величины (численного ряда). Параметры "Счет" и "Сумма" представляют собой число значений в заданном интервале и их сумму соответственно.
Последняя характеристика "Уровень надежности" показывает величину доверительного интервала для математического ожидания согласно заданному уровню надежности или доверия. По умолчанию уровень надежности принят равным 95%.
Для рассматриваемого примера это означает, что с вероятностью 0,95 (95%) величина математического ожидания NPV попадет в интервал 3412,14
· 224,88.
Вы можете указать другой уровень надежности, например - 98%, путем ввода соответствующего значения в поле "Уровень надежности" диалогового окна "Описательная статистика". Следует отметить, что чем выше принятый уровень надежности, тем больше будет величина доверительного интервала для среднего.
В заключении отметим, что имитационное моделирование позволяет учесть максимально возможное число факторов внешней среды для поддержки принятия управленческих решений и является наиболее мощным средством анализа инвестиционных рисков. Необходимость его применения в отечественной финансовой практике обусловлена особенностями российского рынка, характеризующегося субъективизмом, зависимостью от внеэкономических факторов и высокой степенью неопределенности.
Результаты имитации могут быть дополнены вероятностным и статистическим анализом и в целом обеспечивают менеджера наиболее полной информацией о степени влияния ключевых факторов на ожидаемые результаты и возможных сценариях развития событий.

Задание к лабораторной работе № 3

Выполните задания из теоретической части.
Используйте варианты индивидуальных заданий для получения показателей корреляции и описательной статистики.
Сформируйте отчет. Прокомментируйте каждый показатель с точки зрения оценивания риска инвестиционного проекта.


Лабораторная работа № 4


Оптимизационный анализ в МS Excel

В экономике оптимизационные задачи возникают в связи с многочисленностью различных вариантов функционирования экономического объекта, когда возникает ситуация выбора наилучшего варианта по некоторому критерию. Такие задачи называют задачами линейного программирования (ЗЛП). ЗЛП могут быть использованы в следующих случаях:
оптимальное использование ресурсов;
планирование производства;
оптимальное размещение денежных средств;
планирование штатного расписания.
Математическая модель ЗЛП в общем виде:
Найти min или max целевой функции
13 EMBED Equation.3 1415
при ограничениях
13 EMBED Equation.3 1415
где с0, сj, aij, bi - действительные числа.
Ограничения могут содержать как знаки равенства, так и неравенства.
Транспортная задача   математическая задача линейного программирования специального вида о поиске оптимального распределения однородных объектов из аккумулятора к приемникам с минимизацией затрат на перемещение. Для простоты понимания рассматривается как задача об оптимальном плане перевозок грузов из пунктов отправления в пункты потребления, с минимальными затратами на перевозки.
 
Когда суммарный объём предложений (грузов, имеющихся в пунктах отправления) не равен общему объёму спроса на товары (грузы), запрашиваемые пунктами потребления, транспортная задача называется несбалансированной (открытой).
 Когда суммарный объем предложения равен объему спроса, транспортная задача закрытого типа или называется закрытой.
 Транспортная задача (классическая)  задача об оптимальном плане перевозок однородного продукта из однородных пунктов наличия в однородные пункты потребления на однородных транспортных средствах (предопределённом количестве) со статичными данными и линеарном подходе (это основные условия задачи).
 Для решения таких задач Excel имеет специальный инструмент «Поиск решения». Надстройка «Поиск решения» в Microsoft Excel позволяет напрямую находить оптимальное решение транспортной задачи.
 Для добавления надстройки «Поиск решения», если на вкладке «Данные» этого пункта нет перейдите: Файл Параметры. Слева выберите меню «Надстройки». В основной части выделите «Поиск решения». Затем ниже, нажмите «Перейти». В открывшемся окне отметьте пункт «Поиск решения» и нажмите «Ok». Во вкладке «Данные» появился соответствующий одноименный пункт.
 
Общее условие транспортной задачи:
 Найти m*n неотрицательных чисел Xij– объем перевозок от i-ого поставщика к j-ому потребителю, минимизирующих транспортные затраты по перевозке однородных грузов поставщиков с мощностями (запасами) А1,А2Ам к потребителям с потребностями В1,В2Вn, если известны матрица издержек Сij – издержки перевозки единицы груза от i-ого поставщика к j-ому потребителю.
 
Математическая постановка задачи:
 
Целевая функция
[ Cкачайте файл, чтобы посмотреть картинку ]
 
Ограничения
[ Cкачайте файл, чтобы посмотреть картинку ]     для i=1,2.m
 
При этом необходимо, чтобы транспортная задача была закрытой - суммарная мощность поставщиков должна быть равна суммарной потребности потребителей.  
[ Cкачайте файл, чтобы посмотреть картинку ]    для j=1,2.n
 
Если задача открытого типа, для балансирования суммарных запасов и потребностей вводится или фиктивный поставщик, запасы которого равны превышению суммарных потребностей над суммарными запасами, или фиктивный потребитель, потребности которого равны превышению суммарных запасов над суммарными потребностями. При этом матрица издержек дополняется строкой или столбцом с нулевыми элементами.
 
Примеры решения оптимизационных задач в MS EXCEL

Постановка задачи о планировании производства
Небольшая фабрика выпускает 2 вида товаров. Для производства товаров используется два исходных продукта А и В. Максимально возможные суточные запасы этих продуктов составляют 6 и 8 тонн соответственно. Данные по производству товаров приведены в таблице 4.1.
Таблица 4.1 - Исходные данные задачи
Исходный продукт
расход исходного продукта на 1 тонну товара
Максимально возможный запас


товар 1
товар 2


А
1
2
6

В
2
1
8


Изучение рынка сбыта показало, что суточный спрос на товар 1 никогда не превышает спрос на товар 2 на 1 тонну. Установлено, что спрос на товар 1 никогда не превышает 2 тонны в сутки. Цена товара 1 – 3000 руб, товара 2 – 2000 рублей.
Какое количество товаров должна производить фабрика, чтобы максимизировать свою прибыль.

Математическая постановка задачи:
Переменные:
Х1 – количество товара 1, Х2 – количество товара 2 (в тоннах).
Целевая функция:
f(x) = 3000*X1 + 2000*X2 ( max
Ограничения:
Х1 + 2*Х2 ( 6;
2*Х1 + Х2 ( 8;
Х1, Х2 ( 0;
Х1 – Х2 ( 1;
Х1( 2.

Технология решения задачи
1. Разработайте шаблон для решения задачи. Для этого в ячейку А1 запишите “Переменные”; в ячейку А2 -“Х1”, в В2 – “Х2”; в А4 – “Функция цели”; в С4 – формулу “=3000*А3 + 2000*В3”; в А6 – “Ограничения”; в А7 – “=А3 + 2*В3”; А8 – “=2*А3 + В3”; А9 – “=В3-А3”; А10 – “=В3”; в В7 – “6”; в В8 – “8”; в В9 – “1”; в В10 – “2”.
2. Установите надстройку «Поиск решения» - Главная кнопка – Параметры - Надстройки – Поиск решения – Перейти - ОК. Выполните команду ДАННЫЕ – ПОИСК РЕШЕНИЯ, На экране отобразится диалоговое окно Поиск решения (рис.4.1). Заполните его в соответствии с приведенным рисунком.



Рис. 4.1. Решение задачи с помощью средства Поиск решения.

3. Нажмите кнопку Параметры. На экране отобразится диалоговое окно Параметры поиска решения. В диалоговом окне можно изменять условия и варианты поиска решения исследуемой задачи, а также загружать и сохранять оптимизируемые модели. В нашей задаче оставим все параметры, установленные по умолчанию, предварительно установив флажок Линейная модель.
4. Нажмите кнопку Выполнить. На экране отобразится окно Результаты поиска решения.
Поиск решения нашел оптимальный план производства товаров, дающий максимальную прибыль. Оптимальным является производство в сутки 3 1/3 товара 1 и 1 1/3 товара 2. Этот объем производства принесет 12 1/3 тыс. руб. прибыли.

Задача об оптимальном размещении денежных средств
Пусть собственные средства банка вместе с депозитами в сумме составляют 100 млн. долл. Часть этих средств, но не менее 35 млн. долл., должна быть размещена в кредитах, ценные бумаги должны составлять не менее 30% средств, размещенных в кредитах и ценных бумагах. Доходность кредитов – 6% годовых, доходность ценных бумаг – 3% годовых. Найти такое соотношение средств, размещенных в кредитах и ценных бумагах, чтобы максимизировать общий доход банка.
Математическая постановка задачи:
Переменные:
Х1 – средства (в млн. долл.), размещенные в кредитах;
Х2 – средства (в млн. долл.), размещенные в ценных бумагах.
Целевая функция:
f(x) = 0,06*X1 + 0,03*X2 ( mах
Ограничения:
Х1, Х2 ( 0;
Х1 + Х2 ( 100 – балансовое ограничение;
Х1 ( 35 – кредитное ограничение;
Х2 ( 0,3 * (Х1 + Х2) – ликвидное ограничение.
Задачу предлагается решить самостоятельно, используя навыки полученные при решении предыдущей задачи.

Задача оптимального использования ресурсов
Фабрика имеет в своем распоряжении определенное количество ресурсов: рабочую силу, денежные средства, сырье, оборудование и может выпускать товары четырех видов. Исходные данные для решения задачи приведены в таблице 4.2.

Таблица 4.2 - Исходные данные задачи

Ресурсы
нормы расхода на единицу товара
наличие ресурсов


товар 1
товар 2
товар 3
товар 4


Труд (чел/дней)
7
2
2
6
80

Сырье (кг)
5
8
4
3
480

Оборудование (станко/ч)
2
4
1
8
130

Цена (тыс. руб.)
3
4
3
1



Требуется найти такой план выпуска продукции, при котором общая стоимость продукции была максимальной.
Математическая постановка задачи:
Переменные:
Х1, Х2, Х3, Х4 – количество товаров каждого вида.
Целевая функция:
f(x) = 3*X1 + 4*X2 +3*Х3 + Х4 ( mах
Ограничения:
Х1, Х2, Х3, Х4 ( 0;
7*X1 + 2*X2 +2*Х3 + 6*Х4 ( 80 ;
5*X1 + 8*X2 +4*Х3 + 3*Х4 ( 480 ;
2*X1 + 4*X2 + Х3 + 8*Х4 ( 130 ;
Задачу предлагается решить самостоятельно, используя навыки, полученные при решении предыдущих задач.

Транспортная задача

Дано 5 производителей А1, А2, А3, А4, А5, мощность (запасы) которых соответственно равна(равны): 20, 45, 25, 30,20. И четыре потребителя В1, В2, В3, В4, потребность которых в продукте составляет соответственно: 45, 50, 20, 25.
Также известна матрица издержек  Сij – издержки перевозки единицы груза от i-ого поставщика к j-ому потребителю.
Ее можно представить таблицей:

12
9
10
4

4
7
7
6

7
11
5
8

9
6
9
9

10
11
6
5

 
Полностью, условие транспортной задачи, можно представить таблицей следующего содержания:


45
50
20
25

 20
12
9
10
4

45
4
7
7
6

25
7
11
5
8

30
9
6
9
9

20
10
11
6
5

 
 
Решение:
 
Введите исходные данные, начиная с ячейки А1
 
В ячейку Н1 введите формулу:     СУММПРОИЗВ(В2:E6;B9:E13) 
[ Cкачайте файл, чтобы посмотреть ссылку ]
 
В ячейку A9 введите формулу СУММ(B9:Е9) и растяните её до А13
[ Cкачайте файл, чтобы посмотреть ссылку ]
 
В ячейку B8  введите формулу СУММ(B9:B13) и скопируйте ее в диапазон от B8 до E8:
[ Cкачайте файл, чтобы посмотреть ссылку ]
 
Для решения задачи на панели вкладок выберите вкладку «Данные», а затем «Поиск решения»:
[ Cкачайте файл, чтобы посмотреть ссылку ]
 
Заполните открывшееся окно в соответствие с рисунком и нажмите Найти решение:
[ Cкачайте файл, чтобы посмотреть ссылку ]
 
 
В диапазоне B9:E13 Вы получите результат решения транспортной задачи (т.е. значение в ячейке соответствует количеству груза перевезенного от i-ого поставщика к j-ому потребителю).
 В диапазоне A9:A13 количество груза, которое необходимо вывезти от поставщиков.
 В диапазоне B8:E8 количество которое будет доставлено потребителям согласно найденному решению.
 В ячейке H1 значение целевой функции при найденном решении (минимально возможное - 765). Это значение получено в результате умножения стоимости перевозки от от i-ого поставщика к j-ому потребителю на количество единиц груза, которые необходимо перевезти между ними.
 
Оформим  полученный результат и получим следующее:
[ Cкачайте файл, чтобы посмотреть ссылку ]
 

Задания для самостоятельного выполнения.
1.Предприятие электронной промышленности выпускает две модели радиоприемников, причем каждая модель производится на отдельной технологической линии. Суточный объем производства первой линии – 60 изделий, второй линии – 75 изделий. На радиоприемник первой модели расходуются 10 однотипных элементов электронных схем, на радиоприемник второй модели – 8 таких же элементов. Максимальный суточный запас используемых элементов равен 800 единицам. Прибыль от реализации одного радиоприемника первой и второй моделей равна $30 и $20 соответственно. Определить оптимальный суточный объем производства первой и второй моделей.
2. Процесс изготовления двух видов промышленных изделий состоит в последовательной обработке каждого из них на трех станках (таблица 4.3). Время использования этих станков для производства данных изделий ограничено 10 ч в сутки. Найти оптимальный объем производства изделий каждого вида.
Таблица 4.3 – Исходные данные по задаче
Изделие
Время обработки одного изделия, мин
Удельная прибыль, $


Станок 1
Станок 2
Станок 3


1
10
6
8
2

2
5
20
15
3


3. Фирма производит два вида продукции – А и В. Объем сбыта продукции вида А составляет не менее 60% общего объема реализации продукции обоих видов. Для изготовления продукции А и В используется одно и то же сырье, суточный запас которого ограничен величиной 100 кг. Расход сырья на единицу продукции А составляет 2 кг, а на единицу продукции В – 4 кг. Цены продукции А и В равны $20 и $40 соответственно. Определить оптимальное распределение сырья для изготовления продукции А и В.
4. Фирма выпускает ковбойские шляпы двух фасонов. Трудоемкость изготовления шляпы фасона 1 вдвое выше трудоемкости изготовления шляпы фасона 2. Если бы фирма выпускала только шляпы фасона 1, суточный объем производства мог бы составить 60 шляп. Суточный объем сбыта шляп обоих фасонов ограничен диапазоном от 50 до 100 штук. Прибыль от продажи шляпы фасона 1 равна $8, а фасона 2 – $5. Определить, какое количество шляп каждого фасона следует изготавливать, чтобы максимизировать прибыль.
5. Изделия четырех типов проходят последовательную обработку на двух станках. Время обработки одного изделия каждого типа на каждом из станков приведено в таблице 4.4.

Таблица 4.4 – Исходные данные для задачи
Станок
Время обработки одного изделия, ч


Тип 1
Тип 2
Тип 3
Тип 4

1
2
3
4
2

2
3
2
1
2


Затраты на производство одного изделия каждого типа определяются как величины, прямо пропорциональные времени использования станков (в машино-часах). Стоимость машино-часа составляет $10 и $15 для станка 1 и 2 соответственно. Допустимое время использования станков для обработки изделий всех типов ограничено следующими значениями: 500 машино-часов – для станка 1 и 380 машино-часов для станка 2. Цены изделий типов 1, 2, 3 и 4 равны 65, 70, 55 и 45 соответственно. Составить план производства, максимизирующий чистую прибыль.
6. Имеются два склада готовой продукции: А1 и А2 с запасами однородного груза 200 и 300 т. Этот груз необходимо доставить трем потребителям: В1, В2 и В3 в количестве 100, 150 и 250 тонн соответственно. Стоимость перевозки 1 тонны груза из склада А1 потребителям В1, В2 и В3 равна 5, 3, 6 д.е., а из склада А2 тем же потребителям – 3, 4, 2 д.е соответственно. Составьте план перевозок, минимизирующий суммарные транспортные расходы.
7. Четыре оптовых склада каждый день обеспечивают однотипными товарами три магазина. Магазины подали заявки соответственно на 23, 15 и 42 тонны товаров. Склады имеют соответственно 20, 20, 15 и 25 тонн этого товара. Тарифы за перевозку 1 тонны в денежных единицах представлены в следующей таблице.
Склады
Магазины


1
2
3

1
2
7
4

2
3
2
1

3
5
6
2

4
3
4
7

Составьте план перевозок, минимизирующий суммарные транспортные расходы.
Лабораторная работа № 5

Прогнозирование данных в MS Excel


Цель работы: освоить технологию прогнозирования экономических показателей на основе метода линейной экстраполяции с использованием функции ТЕНДЕНЦИЯ.
Одной из наиболее часто используемых возможностей MS Excel является экстраполяция ряда данных, например, для анализа имеющихся фактических данных, оценки имеющейся тенденции их изменения и получения на этой основе краткосрочного прогноза на будущее. Чаще всего в подобной ситуации используется линейная экстраполяция данных на основе наименьшего квадратичного отклонения - отыскивается линейная зависимость данных, такая, которая бы минимизировала сумму квадратов разностей между имеющимися фактическими данными и соответствующими значениями на прямой линейного тренда. На основе найденной зависимости можно сделать вывод об ожидаемых будущих значениях изучаемого ряда данных. Но подобные методы годятся лишь для краткосрочных прогнозов при условии неизменности влияющих факторов.

Технология выполнения лабораторной работы

Спрогнозируем ежеквартальный объем таможенных платежей на ближайший год на основе имеющихся данных об объемах таможенных платежей за предыдущие годы. Исходные данные для прогнозирования представлены в таблице 5.1. (Примечание: ввод исходных данных начинать с ячейки А1 - Ежеквартальный объем таможенных платежей).



Таблица 5.1 Исходные данные для экстраполяции ежеквартального объема таможенных платежей

Факт

1 кв. 08 г
234300

2 кв. 08 г
269800

3 кв. 08 г
255900

4 кв. 08 г
275500

1 кв. 09 г
269800

2 кв. 09 г
276600

3 кв. 09 г
269100

4 кв. 09 г
288200

1 кв. 10 г
264000

2 кв. 10 г
278600

3 кв. 10 г
276100

4 кв. 10 г
305700


Выполнить обыкновенную линейную экстраполяцию проще всего с помощью выделения экстраполируемого ряда данных, а затем буксировки мышью маркера заполнения. (Выделите мышью интервал В3:В14, установите указатель мыши на маркер заполнения (квадратик в правом нижнем углу выделения) и, удерживая нажатой левую кнопку мыши, протащите его на четыре ячейки вниз.
Подобный образом можно выполнить не только линейную экстраполяцию, но и экспоненциальную экстраполяцию. Для этого нужно выполнить протаскивание маркера заполнения с помощью правой кнопки мыши. В этом случае будет раскрыто контекстное меню, в котором следует выбрать команду "Экспоненциальное приближение".
Таким образом можно легко получить интересующие численные значения. Однако, если потребуется вносить изменения в исходные данные, то для того, чтобы получить новые значения экстраполяции, потребуется заново повторить все описанные выше действия, ведь ячейки заполняются соответствующими числовыми значениями, а не формулами, связанными с исходными данными.
Если необходимо получить экстраполяционные значения, которые бы автоматически изменялись при изменении соответствующих исходных данных, необходимо использовать функции рабочего листа ТЕНДЕНЦИЯ для линейной экстраполяции или РОСТ для экспоненциальной экстраполяции.

Порядок выполнения задания:
1 заполнить ячейки С3:С18 элементами арифметической прогрессии (начальный член - 1 и шаг - 1), используя команды: Правка - Заполнить - Прогрессия;
2 выделить блок ячеек D3:D14, нажать кнопку мастера функций на панели инструментов, выбрать категорию "Статистические", имя функции "ТЕНДЕНЦИЯ";
3 в поле "изв_знач_у" ввести В3:В14. Нажать "Готово";
4 нажать , затем одновременно + + ;
5 выделить блок ячеек D15: D18, нажать кнопку мастера функций на панели инструментов, выбрать категорию "Статистические", имя функции "ТЕНДЕНЦИЯ";
6 в поле "изв_знач_у" ввести В3:В14, "изв_знач_х" ввести С3:С14; "нов_знач_х" ввести С15:С18. Нажать "Готово";
7 нажать , затем одновременно + + ;
8. Используя функцию РОСТ, аналогично спрогнозируйте ряд чисел в столбце Е, используя те же данные, что и для функции ТЕНДЕНЦИЯ.

А теперь в качестве иллюстрации вставим диаграмму с линией тренда.
Порядок выполнения задания:
1 вставка - Диаграмма - На этом листе;
2 исходный диапазон данных В3:В18;
3 тип диаграммы - график;
4 добавить легенду? - нет;
5 название диаграммы - линейный тренд;
6 войти в режим редактирования диаграммы, выделить щелчком вертикальную ось и отформатировать ее: Формат оси - Шкала - минимум:=200000;
7 добавить линию тренда. Для этого в области графика выделить одиночным щелчком ряд данных, затем выполнить следующие действия: Вставка - Линия тренда - тип: Линейная - параметры: Показывать уравнение на диаграмме.
Окончательный вид рабочего листа с результатами прогнозирования представлен на рис. 5.1.

Ежеквартальный объем таможенных платежей


Факт

Тенденция

1 кв. 08 г
234300
1
252905,1282

2 кв. 08 г
269800
2
256370,8625

3 кв. 08 г
255900
3
259836,5967

4 кв. 08 г
275500
4
263302,331

1 кв. 09 г
269800
5
266768,0653

2 кв. 09 г
276600
6
270233,7995

3 кв. 09 г
269100
7
273699,5338

4 кв. 09 г
288200
8
277165,2681

1 кв. 10 г
264000
9
280631,0023

2 кв. 10 г
278600
10
284096,7366

3 кв. 10 г
276100
11
287562,4709

4 кв. 10 г
305700
12
291028,2051

1 кв. 11 г
294493,9
13
294493,9394

2 кв. 11 г
297959,7
14
297959,6737

3 кв. 11 г
301425,4
15
301425,4079

4 кв. 11 г
304891,1
16
304891,1422



13 EMBED Excel.Chart.8 \s 1415

Рис. 5. 1 Окончательный вид рабочего листа с результатами
прогнозирования

Дополнительное задание:
Сделайте прогноз на следующие 5 периодов для исходных данных, используя экспоненциальное распределение:

Месяц
01.04.
01.05.
01.06.
01.05.
01.06.
01.07.
01.08.

Валовая прибыль, т.р.
17509
22122
8378
17509
5879
6349
17509



Лабораторная работа № 6

Зачетное задание
В соответствии с заданием варианта выбрать из исходных данных (таблицы 5.1 - 5.3) значения соответствующих показателей за 7 периодов. Перенести в Excel.
Сформировать на новом листе Excel таблицу на основе выбранных данных (путем копирования и вставки транспонированием) так, чтобы значения показателей из строк преобразовались в столбцы.
Установить пакет анализа (сервис – надстройки – пакет анализа – установить) и режим ручных вычислений (сервис – параметры – вычисления вручную – установить)
Используя Сервис-Анализ данных – корреляция построить корреляционную таблицу, характеризующую взаимосвязи между четырьмя показателями.
Используя Сервис – Анализ данных – Описательная статистика построить таблицу описательной статистики
8. Используя Сервис – Анализ данных – Генерация случайных чисел получить еще по двадцать значений каждого показателя. Снова построить корреляционную таблицу, сравнить с предыдущей.
6. Провести прогноз еще 10 значений каждого показателя. Построить диаграммы с линией тренда.
6. Подготовить отчет, в котором отразить все созданные таблицы, прокомментировать результаты статистических исследований выбранных данных.
Варианты заданий:
Даны документы финансовой отчетности некоторой организации за 7 месяцев. Необходимо исследовать соответствующие показатели.
Исходные данные для исследования: внеоборотные активы, долгосрочные финансовые вложения, запасы, краткосрочные финансовые вложения (таблица 5.1)
Исходные данные для исследования: основные средства, долгосрочные финансовые вложения, оборотные активы, незавершенное производство (таблица 5.1)
Исходные данные для исследования: внеоборотные активы, запасы, долгосрочные дебиторы, денежные средства (таблица 5.1)
Исходные данные для исследования: основные средства, долгосрочные финансовые вложения, краткосрочные финансовые вложения, денежные средства (таблица 5.1)
Исходные данные для исследования: собственный капитал, добавочный капитал, краткосрочные кредиты и займы, краткосрочная кредиторская задолженность перед поставщиками и подрядчиками (таблица 5.2)
Исходные данные для исследования: прибыль, краткосрочные обязательства, задолженность участникам (учредителям), собственный капитал (таблица 5.2)
Исходные данные для исследования: оборотные активы (таблица 5.1), прибыль , краткосрочные кредиты и займы, краткосрочная задолженность перед поставщиками и подрядчиками (таблица 5.2)
Исходные данные для исследования: чистая выручка, полная себестоимость, валовая прибыль, чистая прибыль (таблица 5.3)
Исходные данные для исследования: оборотные активы (таблица 5.1), собственный капитал (таблица 5.2), себестоимость, валовая прибыль (таблица 5.3)
Исходные данные для исследования: денежные средства (таблица 5.1), краткосрочные обязательства (таблица 5.2), себестоимость, чистая прибыль (таблица 5.3)

Таблица 5.1 - Активы аналитического баланса
Наименование показателя
01.04.
01.05.
01.06.
01.07.
01.08.
01.09.
01.10.

I. ВНЕОБОРОТНЫЕ АКТИВЫ
11948
7540
7486
21948
7487
7391
11898

Нематериальные активы
91
93
94
91
95
96
91

Основные средства
700
560
505
700
505
408
650

Продолжение таблицы 5.1
Вложения во внеоборотные активы
 
 
 
 
 
 
 

Долгосрочные финансовые вложения
11157
6887
6887
13157
6887
6887
11157

Прочие внеоборотные активы
 
 
 
8000
 
 
 

II. ОБОРОТНЫЕ АКТИВЫ
39455
54548
43204
39455
43438
41143
38855

Запасы
11615
17219
16255
11615
15800
15068
11615

в том числе
 
 
 
 
 
 
 

сырье и материалы
2200
7811
10790
2200
5633
5651
2200

незавершенное производство
9415
9408
5465
9415
10167
9417
9415

готовая продукция
 
 
 
 
 
 
 

товары отгруженные
 
 
 
 
 
 
 

расходы будущих периодов
 
 
 
 
 
 
 

НДС по приобретенным ценностям
5700
5998
5547
5700
5547
5147
5700

Долгосрочные дебиторы
5186
14005
3313
5186
3744
3744
5186

в том числе
 
 
 
 
 
 
 

покупатели и заказчики
4186
14005
3313
4186
3744
3744
4186

Краткосрочные дебиторы
5483
7921
9123
5483
10168
9559
5483

в том числе
 
 
 
 
 
 
 

покупатели и заказчики
3861
6399
7601
3861
8646
7037
3861

Краткосрочные финансовые вложения
4572
4572
4572
4572
4572
4572
4571

Денежные средства
6899
4831
4394
6899
3606
3053
6300


Таблица 5.2 – Пассивы аналитического баланса
Наименование показателя
01.04.
01.05.
01.06.
01.05.
01.06.
01.07.
01.08.

I. СОБСТВЕННЫЙ КАПИТАЛ (фактический)
17509
22122
8378
17509
5879
6349
17509

Уставный капитал (фактический)
532
532
532
532
532
532
532

Собственные акции, выкупленные у акционеров
14
14
14
14
14
14
14

Добавочный капитал
1982
1982
1982
1982
1982
982
1982

Целевое финансирование
2354
 
 
2354
 
 
2354

Прибыль, резервы (фактические)
12655
19622
5878
12655
3379
4849
12655

II. ДОЛГОСРОЧНЫЕ ОБЯЗАТЕЛЬСТВА
 
 
 
 
1360
1560
 

III. КРАТКОСРОЧНЫЕ ОБЯЗАТЕЛЬСТВА
33894
39966
42312
43894
43686
40625
33244

Краткосрочные кредиты и займы
4800
6255
8255
6800
6200
7130
5200

Краткосрочная кредиторская задолж.
28652
33271
33617
36652
37046
33054
27603

в том числе
 
 
 
 
 
 
 

перед поставщиками и подрядчиками
17484
24979
27143
25484
30166
25384
17484

перед персоналом организации
472
637
472
472
482
709
472

перед гос. внебюджетными фондами
1637
1546
1346
1637
1546
1546
1637

перед бюджетом
2505
1641
1568
2505
1714
2347
2505

по авансам полученным
2900
1800
420
2900
70
 
2900

перед прочими кредиторами
3654
2668
2668
3654
3068
3068
2605

Задолженность участникам (учредителям)
440
440
440
440
440
440
440

Таблица 5.3 – Отчет о прибылях и убытках
Наименование показателя
01.04.
01.05.
01.06.
01.07.
01.08.
01.09.
01.10.

I.ОСНОВНАЯ ДЕЯТЕЛЬНОСТЬ
 
 
 
 
 
 
 

Чистая выручка
3881
4680
5196
6457
2281
2962
3443

Себестоимость
1140
1500
6377
7586
2742
1963
2352

Валовая прибыль
2741
3180
-1181
-1129
-461
999
1091

Полная себестоимость
1140
1500
6377
7586
2742
1963
2352

в том числе:
 
 
 
 
 
 
 

коммерческие расходы
 
 
 
 
 
 
 

управленческие расходы
 
 
 
 
 
 
 

Результат от основной деятельности
2741
3180
-1181
-1129
-461
999
1091

II.ПРОЧАЯ ДЕЯТЕЛЬНОСТЬ
 
 
 
 
 
 
 

Прочие доходы
1100
100
800
1000
1000
605
893

Прочие расходы
716
94
3458
465
1093
663
595

Результат от прочей деятельности
384
6
-2658
535
-93
-58
298

Прибыль (убыток) до налогообложения
3125
3186
-3839
-594
-554
941
1389

Налог на прибыль и обязательные платежи
456
307
286
1468
 
103
172

Чистая прибыль (убыток)
2669
2879
-4125
-2062
-554
838
1217



Список литературы

Гарнаев А.Ю. Excel, VBA, Internet в экономике и финансах. – СПб.: БХВ-Петербург, 2003. – 816 с.
Емельянов А.А. Имитационное моделирование в управлении рисками – СПб: СПбГИЭА, 2000. – 376 с.
Г.Г. Арунянц. Моделирование экономических процессов. Практикум – Калининград: БИЭФ, 2009. – 223 с.









13 PAGE \* MERGEFORMAT 141715




Рисунок 47.gif (1212 bytes)Рисунок 8Рисунок 10Рисунок 11Рисунок 2Рисунок 6Рисунок 8Рисунок 17Рисунок 21Рисунок 23Root Entry

Приложенные файлы

  • doc 11192374
    Размер файла: 954 kB Загрузок: 0

Добавить комментарий