МИНОБРНАУКИ РОССИИ

ДАГЕСТАНСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ

 

 

 

 

 

Касимова Т.М.

 

 

МАТЕМАТИЧЕСКОЕ  И  ИМИТАЦИОННОЕ
МОДЕЛИРОВАНИЕ

 

 

Учебно-методическое пособие

для студентов очной формы обучения

по направлению 09.03.03 – «Прикладная информатика»

профили подготовки – «Прикладная информатика

в экономике», «Прикладная информатика в менеджменте», «Прикладная информатика в государственном

и муниципальном управлении»

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Махачкала

Издательство ДГУ

2017


 

УДК 519.876.5

ББК 32.81-65с

К 281

 

Касимова Т.М.

Математическое и имитационное моделирование: учебно-методическое пособие. – Махачкала: Издательство ДГУ, 2017. – 80 с.

 

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

 

Рецензенты:

Алиев М.А. – профессор кафедры экономической теории ФГБОУ ВО «Дагестанский государственный педагогический университет», доктор экономических наук, профессор;

Ишталбагомаев Ш.М. – начальник отдела информационных технологий ПАО СК «Росгосстрах» в Республике Дагестан

 

 

 

 

 

 

 

 

 

 

 

 

 

© Касимова Т.М., 2017

© Издательство ДГУ, 2017


 

Содержание

Введение

Лабораторная работа №1. Имитационный подход к моделированию

связей, зависимостей, динамических  тенденций в экономике

Лабораторная работа №2. Модели процессов массового

обслуживания в экономических системах

Лабораторная работа №3. Реализация метода Монте-Карло в Excel

Лабораторная работа №4. Модели экономических задач

на выполнение прямых математических расчетов

Лабораторная работа №5. Имитационная модель для обоснования

прогноза развития предприятия

Лабораторная работа №6. Игровые методы обоснования

экономических и управленческих решений

Лабораторная работа №7. Имитационный подход

к моделированию межотраслевых балансов

Лабораторная работа №8. Производственные функции

Лабораторные работы № 9-11.Моделирование динамики

экономической системы (Производственные функции:

Модель Солоу)

Лабораторная работа №12. Экономико-математическая

модель международной торговли

Список использованной литературы

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Введение

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

Новые направления в применении имитационного моделирования связаны с их использованием для решения задач прогнозирования и принятия решений в процессе управления сложными системами.

Целью изучения дисциплины «Математическое и имитационное моделирование» является ознакомление студентов с новейшими достижениями в теории и практике имитационного моделирования, применяемыми в управлении, методами описания и анализа процессов управления, с особенностями различных типов задач принятия решений как в условиях определенности, так и в условиях неопределенности; с методами построения компьютерных моделей экономических и социальных систем, планирования машинных экспериментов и анализа полученных результатов.

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

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

В процессе изучения данной дисциплины используются знания и навыки, полученные в рамках изучения дисциплин «Информатика и программирование», «Информационные системы», «Информационные технологии», «Компьютерное моделирование», «Проектирование информационных систем». Особый интерес и сложность при разработке данного учебного курса представляют лабораторные работы. Лабораторные работы выполняются студентами с использованием различных компьютерных программ.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Лабораторная работа №1. Имитационный подход к моделированию связей, зависимостей, динамических  тенденций в экономике

1.1. Связи и зависимости в экономике, их математическое  описание

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

Особенность эконометрической модели состоит в том, что она описывает связи и зависимости для статистической совокупности (а не для отдельного наблюдения) и представляет собой одно уравнение.

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

                                         Y=f(X1, X2, …, Xn),

где   Y – результативный показатель;

     Х1, Х2, …, Хn – показатели-факторы.

Вид эконометрической модели для выражения экономических связей и зависимостей в каждом конкретном случае заранее не известен. Он определяется в ходе постановки и решения задачи и зависит от объекта исследования, набора статистических наблюдений, количества показателей–факторов, включаемых в модели, от целей исследования и субъективного подхода автора и т.д.

Схематически любую эконометрическую модель можно свести к общему виду

Y = a0 + a1V1 + a2V2 +…+ anVn ,     (1)            

где Y – результативный признак,

       а0, а1, …, аn – параметры,

       V1, V2,…,Vn – простые переменные (показатели–факторы) или функции этих простых переменных.

Хотя вид эконометрической модели для конкретной задачи заранее не задается, должны быть известны математические аналоги (прототипы), на основе которых можно его определить и построить.

Примерами математических моделей, которые могут выступать в качестве аналогов (прототипов) эконометрических моделей, являются:

                        

         

                                 

                                 

             и т.д.

 

1.2. Методика построения эконометрических моделей. Выполнение расчетов на ПЭВМ

Построить эконометрическую модель  означает, в первую очередь, рассчитать ее параметры (a1, a2, .., an).

           Наиболее распространенным является метод наименьших квадратов. Согласно этому методу требуется найти такие значения a1, a2, .., an,  при которых ∑(Y-Yх)2 принимала бы минимальное значение (где Y – фактические значения результативного показателя, Yх – расчетные значения результативного показателя, получаемые на основе построенного эконометрического уравнения).

Для эконометрической модели (1) метод наименьших квадратов позволяет получит следующую систему  уравнений для расчета параметров:

где N – число наблюдений статистической совокупности.

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

 Анализ параметров и характеристик необходим для выявления степени тесноты корреляционной зависимости (индекс корреляции и детерминация), для экономической интерпретации численных значений параметров, для расчета предельных норм заменяемости одних факторов другими, определения доверительных  интервалов и др.

Численные значения параметров (их называют также коэффициентами регрессии) дают возможность ответить на вопрос: «На сколько изменится результативный показатель, т.е. Y, если тот или иной показатель-фактор хi увеличится на единицу».

Интересным является анализ предельной нормы замены одного фактора другим, показывающей сколько единиц одного фактора требуется, чтобы заменить одну единицу другого фактора.

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

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

Yрасч  - 2σуlx  ≤ Yпрогн ≤  Yрасч.  + 2σуlx,

с вероятностью 95 %, где Yрасч. – значение результативного показателя, полученного по построенному уравнению при фактических значениях показателей-факторов; σylx – стандартная ошибка.

Эконометрические модели применяются для прогнозирования. Для этого вне модели различными методами определяются численные значения показателей–факторов на прогнозируемый период; эти значения подставляются в построенное уравнение корреляционной зависимости и рассчитывается прогнозируемое значение результативного  показателя; определяются доверительные интервалы.

 

1.3. Имитационное моделирование связей, зависимостей и динамических тенденций

Эконометрические модели могут быть рассмотрены как имитационные, если в условие задачи включить требования об изучении влияния на параметры и характеристики эконометрических моделей изменения:

а) выборки совокупности статистических наблюдений;

б) вида эконометрической модели;

в) отбора (включения, исключения) показателей-факторов в модели;

г) исключения из выборки отдельных наблюдений.

Выборка совокупности статистических наблюдений. В случае традиционной постановки задачи изучение связей и зависимостей может быть осуществлено на основе данных всей совокупности или ее частей (например, по федеральным округам; по выборке из ¼, 1/3, ½ регионов; по 3-4 региона из каждого округа  и т.д.). В случае имитационного подхода в задачу включаются условия о том, как будут меняться связи и зависимости, если количество наблюдений в выборочной совокупности статистических данных будет меняться. Например, какие будут показатели связи для 87 регионов, 86, 85 и т.д.

Выбор вида эконометрической модели. В традиционном  варианте  постановки  задачи целью является подбор одного наиболее приемлемого  вида  эконометрической  модели на основе качественно-количественного   анализа. В случае имитационного  подхода задача сводится  к изучению характера  связей и зависимостей при выборе  различных видов эконометрических моделей.   

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

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

 

Задание 1. Заданы ВРП и стоимость основных фондов (млрд. руб.) регионов (см. таблицу 1). Требуется определить:

а) влияние каждого из регионов на тесноту связи между показателями и параметры, выражающие зависимость ВРП от среднегодовой стоимости ОФ в случае ее линейности;

б) имитировать особенности искомой зависимости за разные годы (2005, 2010, 2012, 2015).

Таблица 1

Валовой региональный продукт и стоимость основных фондов регионов ЮФО и СКФО за разные годы, млрд. руб.

регионы

2005

2010

2012

2015

ВРП

ОФ

ВРП

ОФ

ВРП

ОФ

ВРП

ОФ

Республика Адыгея

13,3

52

41,4

101

55,2

118

77,9

162

Республика Калмыкия

11,3

41

23,9

114

28,8

113

46

151

Краснодарский край

325,8

897

857,5

1870

1229,7

2471

1792,1

4209

Астраханская область

62,6

230

132,2

530

170,5

748

289

913

Волгоградская область

161,7

472

377,4

1116

499

1348

715,1

1735

Ростовская область

224,0

649

556,2

1331

761,8

1751

1000,2

2085

Республика Дагестан

76,1

257

265,1

610

327,0

823

538,3

1213

Республика Ингушетия

6,0

17

18,7

41

26,1

41

52,2

73

Кабар.-Балк. Республика

32,1

80

66,4

136

90,6

185

118,1

224

Карач.-Черк. Республика

14,5

60

38,6

112

49,6

126

69,2

166

Респ.СевОсетия - Алания

25,3

92

65,1

152

85,2

182

126,8

205

Чеченская Республика

 

 

64,1

208

86,3

301

141,3

414

Ставропольский край

132,8

395

277,5

799

399,9

980

541,2

1307

 

1.                Рассчитать коэффициент корреляции по формуле:

    ;

2.                Рассчитать стандартную ошибку по формуле:

  ;

3.                Рассчитать параметры уравнения регрессии линейного вида по формулам:

Для этого составить таблицу 2.

Таблица 2   

Расчетная таблица для вычисления коэффициента корреляции, стандартной ошибки и параметров линейной регрессии

n/n

Регион

y

x

1

 

 

 

 

 

 

 

 

2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

n

 

 

 

 

 

 

 

 

 

Cумма

 

 

0

0

 

 

 

 

Сред.

арифметич.

 

 

-

-

-

-

-

Например, таблица для расчета показателей и параметров, выражающих зависимость ВРП от среднегодовой стоимости ОФ по данным регионов ЮФО и СКФО за 2015 год представлена в таблице 3.

 

Таблица 3   

Расчетная таблица для вычисления коэффициента корреляции, стандартной ошибки и параметров линейной регрессии по данным регионов ЮФО и СКФО за 2015 год

региона

n/n

y

x

1

77,9

162

-827

-345,7

683929,0

119540,4

285932,1

2

46,0

151

-838

-377,6

702244,0

142616,6

316467,5

3

1792,1

4209

3220

1368,5

10368400,0

1872665,9

4406421,4

4

289,0

913

-76

-134,6

5776,0

18129,6

10233,1

5

715,1

1735

746

291,5

556516,0

84945,3

217424,6

6

1000,2

2085

1096

576,6

1201216,0

332414,3

631903,0

7

538,3

1213

224

114,7

50176,0

13145,5

25682,5

8

52,2

73

-916

-371,4

839056,0

137972,2

340244,7

9

118,1

224

-765

-305,5

585225,0

93358,5

233742,8

10

69,2

166

-823

-354,4

677329,0

125632,1

291709,2

11

126,8

205

-784

-296,8

614656,0

88117,6

232727,4

12

141,3

414

-575

-282,3

330625,0

79719,4

162349,0

13

541,2

1307

318

117,6

101124,0

13818,9

37382,1

сумма

5507,4

12857

0

0

16716272,0

3122076,4

7192219,3

среднее

423,6

989

-

-

-

-

-

 

Результаты вычисления характеристик и параметров линейного уравнения регрессии  привести в таблице 4. При этом для определения влияния каждого региона на тесноту связи между показателями и параметры, выражающие зависимость ВРП от стоимости ОФ исключить поочередно из исходной выборки каждый из регионов.

Таблица 4

Параметры и некоторые характеристики линейной зависимости ВРП от среднегодовой стоимости ОФ для различных выборок

Количество

регионов

n

 

 

 

 

n-1(исключен 1-й регион)

 

 

 

 

n-1(исключен 2-й регион)

 

 

 

 

 

 

 

 

n-1(исключен n-й регион)

 

 

 

 

 

Сделать выводы о влиянии каждого из регионов на тесноту связи между показателями и параметрами, выражающими зависимость ВРП от среднегодовой стоимости ОФ.

 

Результаты выполнения пункта б) лабораторной работы №1 привести в виде таблицы 5.

Таблица 5

Параметры и некоторые характеристики линейной зависимости ВРП от стоимости ОФ за разные годы

Год

2005

 

 

 

 

2010

 

 

 

 

2011

 

 

 

 

2012

 

 

 

 

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

 

Задание 2.

Заданы некоторые социально-экономические характеристики Центрального ФО РФ в разрезе регионов за 2015 год.

Таблица 6

Некоторые социально-экономические показатели ЦФО РФ за 2015 год

регион

ВРП,

млрд. руб.

ОФ,

млрд. руб.

Инвестиции,

млн. руб.

Ср. год. числ.

занятых в эк.,

тыс. чел.

Белгородская обл.

619,4

699,1

146,4

699,1

Брянская область

243

533,6

61,7

533,6

Владимирская  обл.

327,9

695,7

80,5

695,7

Воронежская область

709,1

1055,3

263,6

1055,3

Ивановская область

151,1

487,5

25,7

487,5

Калужская область

324,9

490,8

92,5

490,8

Костромская область

146,3

299,8

26,2

299,8

Курская область

297,4

567,1

70,4

567,1

Липецкая область

395,7

542,3

116,6

542,3

Московская область

2705,6

3040,5

640,3

3040,5

Орловская область

179,8

386,8

52,3

386,8

Рязанская область

297,3

494,1

54,1

494,1

Смоленская область

234,7

482,4

59,9

482,4

Тамбовская область

275,8

502,2

122,5

502,2

Тверская область

307,4

575,5

74,2

575,5

Тульская область

408,5

749,9

105,6

749,9

Ярославская область

388,1

627,4

69,1

627,4

г. Москва

12808,6

6778,4

1611,5

6778,4

 

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

Таблица 7

Расчетная таблица для построения моделей различных видов зависимости ВРП () от объема инвестиций () по данным регионов ЦФО за 2015 г.

региона

n/n

1

619,4

146,4

6,43

4,99

0,007

146,4

21433,0

2

243

61,7

5,49

4,12

0,016

61,7

3806,9

3

327,9

80,5

5,79

4,39

0,012

80,5

6480,3

4

709,1

263,6

6,56

5,57

0,004

263,6

69485,0

5

151,1

25,7

5,02

3,25

0,039

25,7

660,5

6

324,9

92,5

5,78

4,53

0,011

92,5

8556,3

7

146,3

26,2

4,99

3,27

0,038

26,2

686,4

8

297,4

70,4

5,70

4,25

0,014

70,4

4956,2

9

395,7

116,6

5,98

4,76

0,009

116,6

13595,6

10

2705,6

640,3

7,90

6,46

0,002

640,3

409984,1

11

179,8

52,3

5,19

3,96

0,019

52,3

2735,3

12

297,3

54,1

5,69

3,99

0,018

54,1

2926,8

13

234,7

59,9

5,46

4,09

0,017

59,9

3588,0

14

275,8

122,5

5,62

4,81

0,008

122,5

15006,3

15

307,4

74,2

5,73

4,31

0,013

74,2

5505,6

16

408,5

105,6

6,01

4,66

0,009

105,6

11151,4

17

388,1

69,1

5,96

4,24

0,014

69,1

4774,8

18

12808,6

1611,5

9,46

7,38

0,001

1611,5

2596932,3

 

 

 

 

 

Параметры линейной модели оцениваются с помощью встроенной функции ЛИНЕЙН MS EXCEL, а показательного вида – ЛГРФПРИБЛ. Остальные модели сводятся к линейному виду посредством логарифмизации или введения замены.

 

Методические указания к построению эконометрических уравнений на примере линейной регрессии

Встроенная статистическая функция ЛИНЕЙН определяет параметры линейной регрессии:

y=mx+b  или  y=m1x1 + m2x2 +...+ b,

где зависимое значение y является функцией независимого значения x.  Значения m - это коэффициенты,   соответствующие каждой независимой переменной x, а b - константа.

Синтаксис:

ЛИНЕЙН  (известные значения y; известные значения x; конст; статистика)

известные значения y - это множество значений y,   которые уже известны для соотношения y=mx+b.

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

Конст - это логическое значение,   которое указывает,   требуются ли,   чтобы константа b была равна нулю. Константа принимает одно из двух значений ИСТИНА или ЛОЖЬ.  Если конст имеет значение истина или опущено,   то b вычисляется,   если конст имеет значение ЛОЖЬ,   то b полагается равным 0.

Статистика -  это логическое значение,   которое указывает,   требуется ли вернуть дополнительную статистику по регрессии. 

Статистика также принимает одно из значений ИСТИНА или ЛОЖЬ.  В первом случае дополнительная статистика рассчитывается,   во втором случае не рассчитывается.

Дополнительные статистические характеристики функции ЛИНЕЙН приведены ниже Дополнительные статистические характеристики функции ЛИНЕЙН приведены ниже:

b, m1, m2,…mn – коэффициенты регрессии (параметры модели);

se1, se­2,...,sen - стандартные значения ошибок для коэффициентов m1,m2,...,mn;

seb - стандартное значение ошибки для постоянной b;

r2 - коэффициент детерминированности;

sey - стандартная ошибка для оценки y;

F - F-статистика, используемая для определения того, является ли наблюдаемая взаимосвязь между зависимой и независимой переменными случайной или нет;

df - степени свободы, используемые для нахождения F-критических значений в статистической таблице (для определения уровня надежности модели нужно сравнить значения в таблице с F-статистикой функции ЛИНЕЙН);

ssreg   - регрессионая сумма квадратов;

ssresid - остаточная сумма квадратов.

Характеристики выводятся на экран дисплея в виде приведенного ниже массива (таблицы):

mn

mn-1

m2

m1

b

sen

Sen-1

se2

se1

seb

r2

Seу

 

 

 

F

Df

 

 

 

ssreg

ssresid

 

 

 

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

1.  Вводятся исходные данные или открывается существующий файл,   содержащий исходные данные.

2.  В рабочем окне Excel выделяется диапазон ячеек 5×(n+1)  (5 число строк,   (n+1) - число столбцов, n – число показателей факторов) для вывода результатов расчета.

3.  Активизируется «Мастер функций» любым из способов:

а) в главном меню выбирается Вставка/Функция;

б) на панели инструментов Стандартная нажимается кнопка (fx)

4. В появившемся окне "Мастер функций шаг 1 из 2" среди категорий выбирается Статистические,  среди функций - ЛИНЕЙН шаг 1 из 2  (рис.  2)

Рис.  2.  Диалоговое окно «Мастер функций»

5.  В появившемся втором окне "Мастер функций"  (рис. 3) вводятся аргументы, т.е. указываются диапазоны ячеек рабочего окна EXCEL,   в которых находятся исходные данные для У и Х,  а также значения аргументов константа и статистика.

 

Рис. 3.  Второе диалоговое окно «Мастер функций»

 

6.    Нажимается кнопка ОК. В выделенном диапазоне рабочего окна  Excel появляется результат -  численное значение для коэффициента регрессии (b).  Чтобы вывести всю статистику следует нажать клавишу <F2>,   а затем  - комбинацию клавиш <Ctrl>+<Shift>+<Enter>.

 

Рис. 4.  Результаты построения эконометрических моделей различных видов

 

Результаты выполнения задания 2 привести в виде следующей таблицы 8.

 Таблица 8

Параметры и статистические характеристики эконометрических моделей различных видов

 

лин

показ

степ

гиперб

параб

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

В таблице 8  – стандартное значение ошибки для параметра ,
 - стандартное значение ошибки для постоянной ,
- коэффициент детерминированности, – стандартная ошибка для оценки ,  – F-критерий Фишера,  – средняя ошибка аппроксимации.

По результатам сделать выводы о характере зависимости ВРП от инвестиций при выборе каждого из построенных видов эконометрических моделей.

 

Задание 3. Определить влияние каждого из показателей-факторов и их комбинаций на параметры и характеристики линейной эконометрической модели. В качестве результативного показателя выбрать ВРП, показателей-факторов –  ОФ, инвестиции и среднегодовую численность занятых в экономике, заданные в таблице 6. Для этого необходимо составить таблицу 9.

Таблица 9

Параметры и статистические характеристики различных эконометрических моделей

 

 от

 от

 от

 от и

 от и

 от и

 от ,,

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Лабораторная работа №2. Модели процессов массового обслуживания в экономических системах

Задание 1 (одноканальная система с отказами).

Известно, что заявки на телефонные переговоры в телевизионном ателье поступают с интенсивностью λ заявок в час, а средняя продолжительность разговора по телефону  мин. Определить показатели эффективности работы СМО (телефонной связи) при наличии одного телефонного номера.

Задание 2  (многоканальная система с отказами).

Определить оптимальное число телефонных номеров в телевизионном ателье для условий задания 1, если оптимальным считать удовлетворение в среднем из каждых 100 заявок не менее N заявок на переговоры.

Необходимые для расчетов величины λ,  , N взять по варианту, заданному преподавателем, в таблице1.

   Таблица 1

варианта

К задаче 1

 

1

2

3

4

5

λ заявок/час

90

90

85

70

75

80

 , мин

2

1,5

2,5

2,0

2,5

1,5

К задаче 1

 

6

7

8

9

10

λ заявок/час

90

65

60

75

80

90

 , мин

2

2,0

2,5

2,5

2,0

1,5

К задаче 2

 

N

90

85

90

90

90

85

N

 

80

80

80

70

90

Составьте и заполните следующие таблицы

Таблица 2

Расчет характеристик одноканальной СМО с отказами при различных интенсивностях потока заявок и обслуживания

 

λ=

λ=

λ=

μ=

μ=

μ=

 

 

 

 

 

 

A

 

 

 

Таблица 3

Расчет характеристик многоканальной СМО с отказами при различных интенсивностях потока заявок и обслуживания

 

λ=      

λ=

λ=

 

μ=

μ=

μ=

 

n=

n=

n=

 

ρ

 

 

 

 

 

 

 

 

 

 

 

 

A

 

 

 

 

Q

 

 

 

 

 

 

 

 

 

Порядок выполнения работы:

В качестве показателей эффективности СМО с отказами будем рассматривать:

А - абсолютную пропускную способность СМО, т.е. среднее число заявок, обслуживаемых в единицу времени;

Q - относительную пропускную способность, т.е. среднюю долю пришедших заявок, обслуживаемых системой;

Ротк. - вероятность отказа, т.е. того, что заявка покинет СМО необслуженной;

* - среднее число занятых каналов (для многоканальной системы).

Рассмотрим задачу одноканальной системы обслуживания с отказами.

При этом предполагается, что все потоки событий, переводимые  СМО из одного состояния в другое, будут простейшими.  Пусть имеется один канал, на который поступает поток заявок с ин­тенсивностью. Поток обслуживаний имеет интенсивность . Требуется найти предельные вероятности состояний системы и показатели ее эффективности.

Система S имеет два состояния: S0 - канал свободен, S1 -  канал занят. Размеченный граф состояний представлен на рис. 1.

 

 


 

 

Рис. 1.

 

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

                               (1),

т.е. система вырождается в одно уравнение. Учитывая условие р0 + p1=1, найдем из (1) предельные вероятно­сти состояний

                      (2)

которые выражают среднее относительное время пребывания сис­темы в состоянии S0 (когда канал свободен) и S1 (когда канал занят), т.е. определяют соответственно относительную пропуск­ную способность Q системы, вероятность отказа Ротк., и абсолютную пропускную способность А:

 (3)          (4)        (5)

Пусть λ = 90 (1/ ч),  мин.

Интенсивность потока обслуживаний λ =1/t0б =1/2 = 0,5(1/мин) = 30(1/час).

Относительная пропускная способность СМО:

Q = 30 / (90 + 30) = 0,25, т.е. в среднем только 25% поступивших заявок осуществят переговоры по телефону. Соответственно вероятность отказа в обслуживании составит: Ротк = 0,75.

Абсолютная пропускная способность СМО:

А = 90 * 0,25 = 22,5 , т.е. в среднем в час будут обслужены 22,5 заявки на переговоры. Очевидно, при наличии только одного телефонного номера СМО будет плохо справляться с потоком заявок.

Граф состояний многоканальной СМО с отказами соответствует процессу гибели и раз­множения и показан на рис. 2.

 

 

 


Рис. 2.

 

Используя формулы процесса гибели и размножения, предельные вероятности состояния можно рассчитать по формулам

                              (6)

             (7)

 

где  - приведенная интенсивность потока заявок или интенсивность нагрузки канала.

Формулы (6) и (7) для предельных вероятностей полу­чили названия формул Эрланга (Эрланг А.К. (конец XIX в. - начало XX в.) - датский инженер, ма­тематик - основатель теории массо­вого обслуживания).

Вероятность отказа СМО, относительная и абсолютная пропускная способность, а также среднее число занятых каналов рассчитываются по формулам:  

                              (8)

где pk - предельные вероятности состояний (см. формулы (6) и (7)).

Среднее число занятых каналов можно найти и по формуле

                                                                         (9)

Например, при  n = 2

 =  = 0,118 = 0,12 ;

Q = 1 - (32 / 2 !) * 0,118 = 0,471 = 0,47 ;

A = 90 * 0,471 = 42,4 и т.п.

Для расчета вероятности того, что система свободна необходимо воспользоваться функцией  СУММЕСЛИ EXCEL. При изменении числа каналов необходимо задавать новый критерий используемой встроенной функции.

Значения характеристик СМО сведем в таблицу 4.

Таблица 4

Характеристики обслуживания

Обозначение

Число каналов телефонных номеров

1

2

3

4

5

6

Относительная пропускная способность

Q

0,25

0,47

0,65

0,79

0,90

0,95

Абсолютная пропускная способность

A

22,5

42,4

58,8

71,5

80,1

85,3

 

По условию оптимальности Q0,9, следовательно, в телевизионном ателье необходимо установить 5 телефонных номеров (в этом случае Q=0,90 - см. таблицу 1) . При этом в час будут обслуживаться в среднем 80 заявок (А = 80,1), а среднее число занятых телефонных номеров (каналов): К= 80,1/30=2,67 .

 

Задание 4 (одноканальная система с неограниченной очередью).

В порту имеется один причал для разгрузки судов. Интенсивность потока судов равна l (судов в сутки). Среднее время разгрузки одного судна составляет tоб суток. Предполагается, что очередь может быть неограниченной длины. Найти показатели эффективности работы причала, а также вероятность того, что ожидают разгрузки не более чем 2 судна.

 

Задание 5 (одноканальная система с ограниченной очередью).

В порту имеется один причал для разгрузки судов. Интенсивность потока  судов  равна  l  (судов в сутки).  Среднее  время разгрузки  одного судна составляет  суток. Предполагается, что очередь может быть ограниченной длины. Найти показатели эффективности работы причала, если в порту могут ожидать разгрузки не более чем 2 судна.

Необходимые для расчетов величины λ,  взять по варианту, заданному преподавателем, в таблице 5.

Таблица 5

варианта

1

2

3

4

l судов/сутки

0,4

0,25

0,3

0,2

 , сут

2

2

2

2

варианта

5

6

7

8

l судов/сутки

0,3

0,2

0,25

0,4

 ,сут

3

3

3

3

Составить таблицы для расчета показателей эффективности одноканальной СМО с неограниченной и ограниченной очередью аналогично таблицам 2 и 3 настоящей лабораторной работы.

                Порядок выполнения работы:

В качестве показателей эффективности СМО с ожиданием (или очередью) кроме четырех вышеперечисленных применяются  и следующие:

- среднее число заявок в системе (Lсист); 

- среднее время пребывания заявки в системе (Тcист.);

- среднее число заявок в очереди или длина очереди (Lоч.);

- среднее время пребывания заявки в очереди (Точ);

- вероятность того, что канал занят или степень загрузки канала (Рзан.).

Пусть имеется одноканальная СМО с неограниченной очередью. Поток заявок, поступающих в СМО, имеет ин­тенсивность λ, а поток обслуживаний - интенсивность μ. Требуется найти предельные вероятности состояний и показатели эффективности СМО.

Система может находиться в одном из состояний S0, S1, S2, ..., Sk (рис. 3), по числу заявок, находящихся в СМО: S0 - канал свободен; S1 - канал занят (обслуживает заявку), очереди нет; S2 - канал занят, одна заявка стоит в очереди; ... Sk - канал занят, (k-l) заявок стоят в очереди и т.д.

 

 

 


Рис. 3.

Предельные вероятности состояний можно определить по формулам:

(8)

 

Правая часть в этой формуле представляет собой геомет­рический ряд со знаменателем ρ < 1,  равный . Поэтому    p0 = 1-ρ.

Предельные вероятности р0, р1, p2, ..., рk, ... образуют убываю­щую геометрическую прогрессию со знаменателем <1, следова­тельно, вероятность p0 - наибольшая. Это означает, что если СМО справляется с потоком заявок (при <1), то наиболее ве­роятным будет отсутствие заявок в системе.

Среднее число заявок в системе Lсист. можно определить по формуле математического ожидания:

Lсист.=                    (9)

(суммирование от 1 до , так как нулевой член 0 р0=0).

При <1формула (9) преобразуется к виду

Lсист.=.                                               (10)

Среднее число заявок в очереди (Lоч) очевидно равно

Lоч.=Lсист.+Lоб,                                            (11)

где Lоб. - среднее число заявок, находящихся под обслужива­нием.

Среднее число заявок под обслуживанием представляет собой математическое ожидание числа заявок под обслуживани­ем, принимающего значения 0 (если канал свободен) либо 1 (если канал занят):

Lоб.=0

Откуда

Lоб. = 1-р0   или  Lоб. = ρ.                          (12)

На основе формул (10), (11) и (12) получим,

Lоч.=.                                                   (13)

Доказано, что при любом характере потока заявок, при любом распределении времени обслуживания, при любой дисциплине обслу­живания среднее время пребывания заявки в системе и в очереди можно рассчитать по формулам заявок в системе (в очереди), деленному на интенсив­ность потока заявок, т.е.

Tсист.=     Tоч.=                                    

Эти формулы  называются формулами Литтла. Подставляя в формулу Литтла (6) значения Lсист. и Lоч. из (10) и (13) получим формулы для определения времени пребывания заявки в системе  и среднего времени пребывания заявки в очереди -

Tсист.=    Tоч.=                       

Рассмотрим задачу многоканальной СМО с неограниченной очередью. Пусть имеется n-канальная СМО с неограниченной очередью. И пусть поток заявок, поступающих в СМО, имеет интенсивность , а по­ток обслуживаний -  интенсивность . Требуется найти предель­ные вероятности состояний СМО и показатели ее эффективности.

Доказано, что при /n < 1 предельные вероятности су­ществуют. Если /n1, очередь растет до бесконечности.

Показатели предельных вероятностей и эффективность в СМО с неограниченной очередью  можно определить по формулам:

а) вероятность того, что заявка окажется в очереди,

Pоч.=,                             

б) среднее число занятых каналов

                                        

в) среднее число заявок в очереди

Lоч.=                                 

г) среднее число заявок в системе

Lсист.=Lоч.+.                                   (14)

Среднее время пребывания заявки в очереди и среднее время пребывания заявки в системе находятся по форму­лам Литтла.

СМО с ограниченной очередью отличаются от рассмотренных выше задач лишь тем, что число заявок в очереди ограничено (не может превосходить некоторого заданного m). Если новая заявка поступает в момент, когда все места в очереди заняты, она покидает СМО необслуженной, т.е. получает отказ.

Для вычисления предельных вероятностей состоя­ний и показателей эффективности таких СМО может быть ис­пользован тот же подход, что и выше, с той разницей, что сумми­ровать надо не бесконечную прогрессию, а конечную.

Пусть l=0,4, .

Тогда получим следующие характеристики: Имеем   r = l / m = l t об = 0,4 * 2 = 0,8  .

Так как r = 0,8 < 1 , то очередь на разгрузку не может бесконечно возрастать, и предельные вероятности существуют. Найдем их.

Вероятность того, что причал свободен: р0=1–0,8=0,2, а вероятность того, что он занят, Рзан  = 1 - 0,2 = 0,8 .

Вероятности того, что у причала находится 1,2,3 судна (т.е. ожидают разгрузки 0,1,2 судна) равны:

р= 0,8 *(1 - 0,8) = 0,16;

р2 = 0,82 * (1 - 0,8) = 0,128;

р3 = 0,83 *(1 - 0,8) = 0,1024.

Вероятность того, что ожидают разгрузку не более чем 2 судна, равна

Р = р+ р+ р3 = 0,16 + 0,128 + 0,1024 = 0,3904.

Среднее число судов, ожидающих разгрузки,

 = 0,82 / ( 1- 0,8 ) = 3,2, а среднее время ожидания разгрузки: = 3,2 / 0,8 = 4 (суток). Среднее число судов, находящихся у причала,

 = 0,8 / (1 - 0,8) = 4 (суток) или  = 3,2 + 0,8=4. Среднее время пребывания судна у причала:

= 4 / 0,8 = 5 (суток).

Очевидно, что эффективность разгрузки судов невысокая. Для еѐ повышения необходимо уменьшить среднее время разгрузки судна  или увеличить число причалов n.

 

 

 

 

 

Лабораторная работа №3. Реализация метода Монте-Карло в Excel

Фирма рассматривает инвестиционный проект по производству продукта «А». В процессе предварительного анализа экспертами были выявлены три ключевых параметра проекта и определены возможные границы их изменений (табл. 1). Пусть все ключевые переменные имеют равномерное распределение вероятностей. Прочие параметры проекта считаются постоянными величинами в течение срока реализации проекта (табл. 2). В качестве результирующего показателя используется показатель чистого приведенного дохода (net present value, NPV).

Таблица 1

Ключевые параметры проекта по производству продукта «А»

Показатели

Сценарий

Наихудший

Наилучший

Вероятный

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

150

300

200

Цена за штуку – P

40

55

50

Переменные затраты – V

35

25

30

 

Таблица 2

Неизменяемые параметры проекта по производству продукта «А»

Показатели

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

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

500

Амортизация – A

100

Налог на прибыль – T

60%

Норма дисконта – r

10%

Срок проекта – n

5

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

2000

 

Проведение имитационных экспериментов по методу Монте-Карло в Excel можно осуществить двумя способами – с помощью встроенных функций и путем использования инструмента «Генератор случайных чисел» надстройки «Анализ данных».

 

1.  Имитационное  моделирование  с  применением  встроенных  функций Excel

Применение встроенных функций целесообразно лишь в том случае, когда вероятности реализации всех значений случайной величины считаются одинаковыми. Тогда для имитации значений требуемой переменной можно воспользоваться математическими функциями СЛЧИС() или СЛУЧМЕЖДУ(). Описание функций приведено в табл. 3.

 

Таблица 3

Математические функции Excel для генерации случайных чисел

Наименование функции

Формат функции

Описание

Английская

версия

Русская версия

RAND

СЛЧИС

 

СЛЧИС()  –  не

имеет

аргументов

Возвращает равномерно

распределенное

случайное вещественное

число, которое большее

или равно 0 и меньше 1.

Новое случайное

вещественное число

возвращается при

каждом вычислении

листа. Чтобы получить

случайное вещественное

число в диапазоне

между a и b, можно

использовать

следующую формулу:

СЛЧИС()*(b-a)+a

RANDBETWEEN

СЛУЧМЕЖДУ

СЛУЧМЕЖДУ

(нижн_граница;

верхн_граница)

Возвращает случайное

целое число,

находящееся в

диапазоне между двумя

заданными числами.

При каждом вычислении

листа возвращается

новое случайное целое

число.

Чтобы значения случайных чисел не изменялись при каждом пересчете листа, нужно отключить режим автоматических вычислений, выбрав на вкладке «Формулы» в раскрывающемся списке кнопки «Параметры вычислений» пункт «Вручную». Тогда пересчет листа будет осуществляться после нажатия клавиши F9.

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

На листе «Лр 3. Формулы» строим генеральную совокупность, задаем исходные значения и вводим формулы. Пример оформления листа приведен на рис. 1.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Рис. 1. Лист «Лр 3. Формулы»

 

Для ряда ячеек задаем имена, чтобы было их удобнее использовать при расчете: вкладка «Формулы», кнопка «Присвоить имя» (рис. 2).

Левая часть листа предназначена для ввода ключевых переменных, значения которых будут генерироваться в процессе проведения эксперимента. В ячейках $A$11:$A$510, $B$11:$B$510 и $C$11:$C$510 генерируют значения для соответствующих переменных с учетом заданных в ячейках В3:С5 диапазонов их изменений с помощью функции СЛУЧМЕЖДУ(нижн_граница; верхн_граница). Формулы в ячейках $D$11:$D$510 и $E$11:$E$510 вычисляют величину потока платежей и его чистую современную стоимость соответственно. Например, для ячейки D11 задается формула =(B11*(C11-A11)-Пост_затраты-Амортизация)*(1-Налог)+Амор-

тизация,  а для ячейки E11 – формула =ПС(Дисконт;Срок;-D11)-Нач_инвест.

Функция ПС (ставка;кпер;плт;бс;тип) возвращает приведенную (к текущему моменту) стоимость инвестиции. Приведенная (нынешняя) стоимость представляет собой общую сумму, которая на данный момент равноценна ряду будущих выплат. В функции ставка – процентная ставка за период, кпер – общее число периодов платежей по аннуитету, плт – выплата, производимая в каждый период и не меняющаяся на протяжении всего периода ренты, бс – требуемое значение будущей стоимости или остатка средств после последней выплаты, тип – число 0 или 1, обозначающее срок выплаты. Параметры бс и тип являются необязательными.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Рис. 2. Имена ячеек листа «Лр 3. Формулы»

В правой части листа кроме значений постоянных переменных содержатся также функции, вычисляющие параметры распределения изменяемых (Q, V, P) и результатных (NCF, NPV) переменных и вероятности различных событий. Соответствующие формулы приведены в табл. 4.

Здесь используются следующие формулы:

- СРЗНАЧ (число1, число2,...) возвращает среднее значение (среднее арифметическое) аргументов;

- СТАНДОТКЛОН (число1; число2; ...) оценивает стандартное отклонение по выборке., то есть меру того, насколько широко разбросаны точки данных относительно их среднего;

- МИН (число1;число2; ...) возвращает наименьшее значение в списке аргументов;

- МАКС (число1;число2; ...) возвращает наибольшее значение в списке аргументов;

- СЧЁТЕСЛИ (диапазон, критерий) подсчитывает количество ячеек в диапазоне, которые соответствуют одному указанному пользователем критерию;

- СУММЕСЛИ (диапазон, критерий, [диапазон_суммирования]) используется, если необходимо просуммировать значения диапазона, соответствующие указанным условиям. Если ячейки, по которым нужно выполнить суммирование, отличаются от ячеек, указанных в качестве диапазона, то их указывают в качестве необязательного диапазона суммирования.

Таблица 4

Формулы листа «Лр 3. Формулы»

Показатели

Переменные

Объем выпуска

Цена за штуку

Поступления

Чистый

 

затраты (V)

(Q)

(P)

(NCF)

приведенный

 

 

 

 

 

доход (NPV)

 

 

 

 

 

 

Среднее

=СРЗНАЧ(Пер

=СРЗНАЧ(Кол

=СРЗНАЧ(Це

=СРЗНАЧ(Пос

=СРЗНАЧ(ЧП

значение

ем_затраты)

ичество)

на)

тупления)

Д)

Стандартное

=СТАНДОТКЛ

=СТАНДОТКЛ

=СТАНДОТК

=СТАНДОТК

=СТАНДОТК

отклонение

ОН(Перем_зат

ОН(Количеств

ЛОН(Цена)

ЛОН(Поступл

ЛОН(ЧПД)

 

раты)

о)

 

ения)

 

Коэффициент

=H12/H11

=I12/I11

=J12/J11

=K12/K11

=L12/L11

вариации

 

 

 

 

 

Минимум

=МИН(Перем_

=МИН(Количе

=МИН(Цена)

=МИН(Поступ

=МИН(ЧПД)

 

затраты)

ство)

 

ления)

 

Максимум

=МАКС(Перем

=МАКС(Колич

=МАКС(Цена)

=МАКС(Посту

=МАКС(ЧПД)

 

_затраты)

ество)

 

пления)

 

Число

 

 

 

 

=СЧЁТЕСЛИ(

случаев

 

 

 

 

ЧПД;"<0")

NPV<0

 

 

 

 

 

Сумма

 

 

 

 

=СУММЕСЛИ

убытков

 

 

 

 

(ЧПД;"<0")

Сумма

 

 

 

 

=СУММЕСЛИ

доходов

 

 

 

 

(ЧПД;">0")

 

В рассматриваемом примере сделано предположение о независимости и равномерном распределении ключевых переменных Q, V, P. Однако какое распределение при этом будет показатель NPV заранее определить нельзя. Одно из возможных решений этой проблемы – попытаться аппроксимировать неизвестное распределение каким-либо известным. При этом в качестве приближения удобнее всего использовать нормальное распределение. Это связано с тем, что в соответствии с центральной предельной теоремой теории вероятностей при выполнении определенных условий сумма большого числа случайных величин имеет распределение, приблизительно соответствующее нормальному.

В                 прикладном анализе для целей аппроксимации широко применяется частный случай нормального распределения – так называемое стандартное нормальное распределение. Математическое ожидание стандартно распределенной случайной величины Е равно 0: M (E) = 0 . График этого распределения симметричен относительно оси ординат, и оно характеризуется всего одним параметром – стандартным отклонением s, равным 1.

Приведение случайной переменной E к стандартно распределенной величине Z осуществляется с помощью нормализации – вычитания средней и последующего деления на стандартное отклонение:

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

В Excel подобные вычисления осуществляются с помощью статистических функций НОРМАЛИЗАЦИЯ() и НОРМСТРАСП():

- НОРМАЛИЗАЦИЯ(x;среднее;стандартное_откл) нормализует значение x для распределения, характеризуемого средним и стандартным отклонением;

- НОРМСТРАСП(z) возвращает стандартное нормальное интегральное распределение для значения z. Это распределение имеет среднее, равное нулю, и стандартное отклонение, равное единице. Данная функция используется вместо таблицы площадей стандартной нормальной кривой.

Эти функции заданы в ячейках K21 и L21. В ячейке L21 отображается вероятность того, что чистый приведенный доход будет меньше некоторого значения X. В примере, отображаемом на рис. Х, риск получить отрицательную величину чистого приведенного дохода не превышает 8%. Сумма всех отрицательных значений NPV в полученной генеральной совокупности (ячейка L17) может быть интерпретирована как чистая стоимость неопределенности для инвестора в случае принятия проекта. Аналогично сумма всех положительных значений NPV (ячейка L18) может трактоваться как чистая стоимость неопределенности для инвестора в случае отклонения проекта. Несмотря на всю условность этих показателей, в целом они представляют собой индикаторы целесообразности проведения дальнейшего анализа.

 

2. Имитационное моделирование с применением инструмента «Генерация случайных чисел»

Инструмент «Генерация случайных чисел» применяется для заполнения диапазона случайными числами, извлеченными из одного или нескольких распределений. С помощью этой процедуры можно моделировать объекты, имеющие случайную природу, по известному распределению вероятностей. Использование этого инструмента возможно после установки надстройки «Анализ данных».

Рассмотрим ранее описанный пример. Также предположим, что распределение ключевых переменных является нормальным. Создайте копию листа «Лр 3. Формулы», переименуйте его в «Лр 3. Генерация», очистите диапазоны $A$11:$A$510, $B$11:$B$510 и $C$11:$C$510, а также внесите соответствующие изменения согласно рис. 3 и описанию ниже.

 

 

 

 

                         

 

 

 

 

 

 

 

 

 

 

 

 

Рис. 3. Лист «Лр 3. Генерация»

 

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

Таблица 5

Формулы для расчета вероятностей листа «Лр 3. Генерация»

Показате

Переменные

Объем

Цена за штуку

Поступления

Чистый

ли

затраты (V)

выпуска (Q)

(P)

(NCF)

приведенный

 

 

 

 

 

доход (NPV)

 

 

 

 

 

 

P(E<=0)

=НОРМРАСП

=НОРМРАСП(

=НОРМРАСП(0

=НОРМРАСП(0

=НОРМРАСП(0;

 

(0;I11;I12;1)

0;J11;J12;1)

;K11;K12;1)

;L11;L12;1)

M11;M12;1)

P(E<=min

=НОРМРАСП

=НОРМРАСП(

=НОРМРАСП(K

=НОРМРАСП(

=НОРМРАСП(M

(E))

(I14;I11;I12;1)

J14;J11;J12;1)

14;K11;K12;1)

L14;L11;L12;1)

14;M11;M12;1)

P(M(E)+s

=НОРМРАСП

=НОРМРАСП(

=НОРМРАСП(K

=НОРМРАСП(

=НОРМРАСП(M

<=E<=ma

(I15;I11;I12;1)-

J15;J11;J12;1)-

15;K11;K12;1)-

L15;L11;L12;1)-

15;M11;M12;1)-

x)

НОРМРАСП(I

НОРМРАСП(J

НОРМРАСП(K1

НОРМРАСП(L

НОРМРАСП(M1

 

11+I12;I11;I12;

11+J12;J11;J12

1+K12;K11;K12;

11+L12;L11;L12

1+M12;M11;M12

 

1)

;1)

1)

;1)

;1)

P(M(E)-

=НОРМРАСП

=НОРМРАСП(

=НОРМРАСП(K

=НОРМРАСП(

=НОРМРАСП(M

s<=E<=M

(I11;I11;I12;1)-

J11;J11;J12;1)-

11;K11;K12;1)-

L11;L11;L12;1)-

11;M11;M12;1)-

(E))

НОРМРАСП(I

НОРМРАСП(J

НОРМРАСП(K1

НОРМРАСП(L

НОРМРАСП(M1

 

11-

11-

1-

11-

1-

 

I12;I11;I12;1)

J12;J11;J12;1)

K12;K11;K12;1)

L12;L11;L12;1)

M12;M11;M12;1)

 

Обратите внимание на то, что для расчета стандартных отклонений используются формулы-массивы, то есть формулы, при помощи которых можно выполнять различные вычисления с одним или несколькими элементами в массиве (для ввода таких формул в рабочих книгах используется сочетание клавиш CTRL+SHIFT+ВВОД).

Таблица 6

Формулы для расчета параметров распределения листа «Лр 3. Генерация»

Показатели

Среднее

Отклонение

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

=СУММПРОИЗВ(B3:D3;$B$6:$D$6)

{=КОРЕНЬ(СУММПРОИЗВ((B3:D3- E3)^2;$B$6:$D$6))}

Цена за штуку – P

=СУММПРОИЗВ(B4:D4;$B$6:$D$6)

{=КОРЕНЬ(СУММПРОИЗВ((B4:D4- E4)^2;$B$6:$D$6))}

Переменные затраты – V

=СУММПРОИЗВ(B5:D5;$B$6:$D$6)

{=КОРЕНЬ(СУММПРОИЗВ((B5:D5- E5)^2;$B$6:$D$6))}

 

Функция НОРМРАСП (x; среднее; стандартное_откл; интегральная) возвращает нормальную функцию распределения для значения x для указанного среднего и стандартного отклонения. Параметр «интегральная» - логическое значение, определяющее форму функции. Если аргумент «интегральная» имеет значение ИСТИНА, функция НОРМРАСП возвращает интегральную функцию распределения; если этот аргумент имеет значение ЛОЖЬ, возвращается функция плотности распределения.

Задайте значения вероятностей: 0,5 для вероятного сценария и по 0,25 для каждого из наилучшего и наихудшего сценариев.

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

Рис. 4. Заполнение полей окна «Генератор случайных чисел»

 

При заполнении полей окна «Генератор случайных чисел» следует обратить внимание, что параметры «Среднее» и «Стандартное отклонение» можно задать только в виде констант. Использовать адреса ячеек и собственные имена не допустимо! Указание аргумента «Случайное рассеивание», равным 1, позволяет при повторных запусках генератора получать те же значения случайных величин, что и при первом.

Результаты (рис. 3) проведенного имитационного эксперимента ненамного отличаются от предыдущих (рис. 4). Величина ожидаемого чистого приведенного дохода (NPV) равна 3555,35 при стандартном отклонении 2679,49. Коэффициент вариации (0,75) немного выше, чем в предыдущем случае, но меньше 1, таким образом, риск данного проекта в целом ниже среднего риска инвестиционного портфеля фирмы. Результаты вероятностного анализа показывают, что шанс получить отрицательную величину NPV не превышает 9%. Общее число отрицательных значений NPV в выборке составляет 34 из 500. Таким образом, с вероятностью около 91% можно утверждать, что чистый приведенный доход проекта будет больше 0. При этом вероятность того, что величина NPV окажется больше чем М(NPV)+s, равна 16% (ячейка M22). Вероятность попадания значения NPV в интервал [М(NPV)-s; М(NPV)]  равна 34%.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Лабораторная работа №4. Модели экономических задач на выполнение прямых математических расчетов

Модель экономической задачи на выполнение прямых математических расчетов представляет собой совокупность формул, с помощью которых на основе заданных исходных экономических показателей рассчитываются зависимые от них промежуточные и конечные экономические показатели, характеризующие различные стороны хозяйственной деятельности.

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

-                         формулировка решаемой задачи;

-                         осуществление численного решения (построение алгоритма решения) задачи;

-                         ввод символьных обозначений для исходных, промежуточных и конечных расчетных показателей;

-                         запись в принятых обозначениях алгоритма решения задачи (совокупность формул).

Методику построения моделей прямых математических расчетов  рассмотрим на конкретном примере.

Пример 1. Формулировка решаемой задачи. На предприятии из трех видов сырья вырабатывается пять видов продукции (А, Б, В, Г, Д).

Объем вырабатываемой продукции в натуральном выражении (шт.), нормы расхода сырья (кг/шт.), цены сырья (руб./кг) и продукции (руб./шт.) приведены в таблице 1:

Таблица 1

 

Виды сырья

Виды продукции и нормы расхода сырья

Цена единицы сырья

А

Б

В

Г

Д

1

0,5

0,4

0,3

0,4

0,6

900

2

0,3

0,6

0,8

0,5

0,3

1200

3

0,6

0,2

0,3

0,4

0,5

700

Объем продукции

1700

3400

2100

1800

2700

 

Цена единицы продукции

1400

1000

1900

1300

1500

 

Требуется: 1) определить  потребность в сырье на производство всей продукции и материалоемкость продукции; 2) построить модель для выполнения прямых математических расчетов.

Численное решение (построение алгоритма) задачи.

Потребность в сырье равна:

первого вида – 1700∙0,5 + 3400∙0,4 + 2100∙0,3 + 1800∙0,4 + 2700∙0,6 = В1;

второго вида – 1700∙0,3 + 3400∙0,6 + +2100∙0,8 + 1800∙0,5 + 2700∙0,3 = В2;

третьего вида – 1700∙0,6 +  3400∙0,2 + +2100∙0,3 + 1800∙0,4 + 2700∙0,5= В3.

Для определения материалоемкости продукции рассчитываем суммарные затраты и объем продукции в денежном выражении:

      - суммарные материальные затраты – В1∙900 + В2∙1200 + В3∙700 = МЗ;

       - объем продукции  в денежном выражении – 1700∙1400 + 3400∙1000 + 2100∙1900 + 1800∙1300 + 2700∙1500 = ОП;

      - материалоемкость продукции – МЗ/ОП = Ме.

Построение модели:

1.    Ввод обозначений для исходных показателей:

А1, B1, C1, D1, E1 – нормы расхода первого вида сырья на производство продукции А, Б, В, Г, Д соответственно (кг);

А2, B2, C2, D2, E2 – нормы расхода второго вида сырья на производство продукции А, Б, В, Г, Д соответственно (кг);

А3, B3, C3, D3, E3 – нормы расхода третьего вида сырья на производство продукции А, Б, В, Г, Д соответственно (кг);

Z1, Z2, Z3 – цены единицы первого, второго и третьего вида сырья (руб./кг);

ZA, ZB, ZC, ZD, ZE – цены единицы продукции А, Б, В, Г, Д соответственно (руб./шт.);

VA, VB, VC, VD, VE – объем продукции А, Б, В, Г, Д  в натуральном выражении (шт.).

2.    Ввод обозначений для расчетных показателей:

PS1, PS2, PS3 – потребность в сырье первого, второго и третьего видов (кг);

MZ – суммарные материальные затраты (тыс. руб.);

V -  объем продукции в денежном выражении (тыс. руб.);

ME – материалоемкость продукции (руб.).

3. Математическая запись модели:

PS1 = VA∙ А + VBB1 + VCC1 + VDD1 + VEE1;

PS2 = VA∙ А + VBB2 + VCC2 + VDD2 + VEE2;

PS3 = VA∙ А + VBB3 + VCC3 + VDD3 + VEE3;

MZ = PS1Z1 + PS2Z2 + PS3Z3;

V = VA ZA+ VBZB + VCZC + VDZD + VEZE;

.

Решение на ПЭВМ в Microsoft Excel:

1.    Запуск MS Excel;

2.    Ввод в рабочее окно данных таблицы 1;

3.    Создание таблицы 2;

4.    Ввод формул в столбец “Величина показателя” таблицы 2;

5.    Сохранить результаты решения задачи 1;

6.    Выбор наиболее приемлемого варианта решения задачи.

 

Таблица 2

Потребность в сырье и материалоемкость продукции

№№

п/п

Наименование показателя

Единицы измерения

Величина показателя

1

Потребность в сырье:

 

 

 

а) первого вида

кг

 

 

б) второго вида

кг

 

 

в) третьего вида

кг

 

2

Стоимость сырья (материальных затрат)

тыс.руб.

 

3

Стоимость продукции

тыс.руб.

 

4

Материалоемкость продукции

руб.

 

 

Модели прямых экономических расчетов  могут рассматриваться как имитационные, если ставиться задачи с их помощью изучить изменения расчетных (итоговых) показателей в зависимости от изменения исходных показателей, принимаемых в качестве управляющих параметров. К управляющим обычно относятся такие показатели, которые могут изменятся под влиянием объективных или субъективных факторов и которые оказывают существенное влияние на итоговые (расчетные) экономические показатели.

В примере 1 к управляющим можно отнести цены на сырье, объем производства отдельных видов продукции в натуральном выражении, цены на продукцию. При этом цены на сырье формируются на рынке и от предприятия практически не зависит. Объем производства отдельных видов продукции является показателем, целиком зависящим от предприятия. Цены на различные виды продукции в значительной степени также зависят от предприятия. Однако на цены оказывают влияние и предприятия-конкуренты. Следовательно, задача предприятия, меняя управляющие параметры (цены на сырье, объем продукции в натуральном выражении, цены на продукции) разработать вариант плана производства продукции.

В случае имитационной постановки в формулировку задачи вводятся следующие изменения:

- вместо фиксированных цен на сырье вводятся диапазоны цены (например, цена 1-го вида сырья колеблется в пределах 900-1100 руб., 2-го вида сырья – 1200-1400 руб., 3-го вида сырья – 700-800 руб.);

- вместо фиксированного объема продукции в натуральном  выражении вводятся верхний и нижний предел ее производства (например, продукция вида А должно быть произведено не более 1800 и не менее 1700 и т.д.);

- вместо цены на продукцию также устанавливается возможный диапазон ее изменения (например, цена продукции А может колебаться в пределах 1400-1600 руб. и т.д.).

Заключительная часть задачи меняется полностью. Она формулируется следующим образом: Требуется: 1) построить имитационную модель для определения зависимости потребности в сырье на производство продукции и материалоемкости продукции от изменения цен на сырье и продукцию, а также от объема  производства отдельных видов продукции; 2) путем выполнения расчетов на ПЭВМ выявить зависимость потребности в сырье на производство продукции, величины материальных затрат, объема продукции в денежном выражении и материалоемкости продукции от изменения управляющих параметров.

Таблица, иллюстрирующая имитационный подход, приведена в таблице 3.

Таблица 3

Имитация определения потребности в сырье и материалоемкости продукции

№№

п/п

Наименование показателя

Единицы измерения

При изменении цены единицы сырья

1-го

2-го

3-го

1

Потребность в сырье:

 

 

 

 

 

а) первого вида

кг

 

 

 

 

б) второго вида

кг

 

 

 

 

в) третьего вида

кг

 

 

 

2

Материальные затраты

тыс.руб.

 

 

 

3

Стоимость продукции

тыс.руб.

 

 

 

4

Материалоемкость продукции

руб.

 

 

 

     

Продолжение таблицы 3

 

При изменении объема продукции

При изменении цены на единицу продукции

 

А