605
.pdf
Ограничения:
фактический расход финансовых ресурсов по каждому проекту в году t (t =1, 2, 3) не превышает доступные; левые части ограничений (диапазон E2:G2) вычисляются как сумма произведений значений двоичного массива A6:A18 и массива E6:E18; правые части ограничений (диапазон E4:G4) — доступные финансовые ресурсы в каждом году t, заданы;
фактический расход трудовых ресурсов по каждому проекту в году t (t = 1, 2, 3), см. рис. 4.40, окончание, не превышает доступные; левые части ограничений (диапазон H2:J2) вы-
числяются как сумма произведений значений двоичного массива A6:A18 и массива H6:H18; правые части ограничений (диапазон H4:J4) — доступные трудовые ресурсы в каждом году t — заданы;
диапазон двоичных изменяемых ячеек A6:A18 задается при помощи ограничения $A$6:$A$18 = двоичное.
2. Выполните команду Сервис, Поиск решения. В диалоговом окне команды сформируйте математическую модель выбора инвестиционных проектов (рис. 4.41) и нажмите кнопку Выполнить. Если оптимальное решение существует и оно найдено, на экран выводится окно «Результаты поиска решения», в нем сообщение: «Решение найдено. Все ограничения и условия оптимальности выполнены» (рис. 4.42). Если в этом окне нажать кнопку ОК, то результаты решения будут сохранены на листе в соответствующих ячейках информационной модели (рис. 4.43).
Рис. 4.41. Математическая модель задачи выбора инвестиционных проектов
Рис. 4.42. Диалоговое окно «Результаты поиска решения»
3. Сверьте полученное решение с приведенным на рис. 4.43 и сделайте выводы.
81
82
Рис. 4.43. Результаты решения задачи выбора инвестиционных проектов
Контрольные задания
1. Решите транспортную задачу, условие которой приведено в табл. 4.4.
|
|
|
|
|
Таблица 4.4 |
|
|
|
|
|
|
Поставщики, их запасы ai, т |
|
Потребители, их потребности bj, т |
|
||
|
|
|
|
|
|
|
80 |
|
140 |
|
110 |
|
|
|
|
|
|
100 |
4 |
|
3 |
|
5 |
|
|
|
|
|
|
150 |
10 |
|
1 |
|
2 |
|
|
|
|
|
|
80 |
3 |
|
8 |
|
6 |
2. Компания решает судьбу 9 проектов. Чистая приведенная стоимость (ЧПС), приносимая каждым проектом, и средства в млн $, которые нужно выделить на каждый проект на протяжении двух следующих лет, приведены в табл. 4.5. Проект 1 увеличит чистую приведенную стоимость на $14 млн, однако потратить на него придется $12 млн в 1-й год и $3 млн во 2-й. На все проекты выделено $50 млн в 1-й год и $20 млн во 2-й год. Какие проекты надо реализовать, чтобы получить максимальную чистую приведенную стоимость инвестиции?
|
|
|
Таблица 4.5 |
|
|
|
|
Проект |
ЧПС |
Расходы, |
Расходы, |
|
|
1-й год |
2-й год |
|
|
|
|
Проект 1 |
14 |
12 |
3 |
|
|
|
|
Проект 2 |
17 |
54 |
7 |
Проект 3 |
17 |
6 |
6 |
Проект 4 |
15 |
6 |
2 |
|
|
|
|
Проект 5 |
40 |
30 |
35 |
|
|
|
|
Проект 6 |
12 |
6 |
6 |
|
|
|
|
Проект 7 |
14 |
48 |
4 |
|
|
|
|
Проект 8 |
10 |
36 |
3 |
|
|
|
|
Проект 9 |
12 |
18 |
3 |
|
|
|
|
5. МОДЕЛИРОВАНИЕ И ОЦЕНКА ЗАВИСИМОСТЕЙ В СРЕДЕ MS EXCEL. МОДЕЛИ ДЛЯ СОСТАВЛЕНИЯ
КРАТКОСРОЧНЫХ ЭКОНОМИЧЕСКИХ ПРОГНОЗОВ
5.1.Линейная зависимость. Разработка, оценка
ииспользование математической модели
Выше отмечалось, что переменные математической модели исследуемого экономического процесса характеризуют определенные экономические величины (показатели), а параметры — количественные связи между ними. Экзогенные (внешние) по отношению к моделируемому процессу переменные рассматриваются как входные, значения которых известны. Значения эндогенных (внутренних) по отношению к моделируемому процессу переменных должны быть найдены в результате исследования. Эндогенные переменные непосредственно используются в управлении процессом и делятся на независимые, изменяющие свое значение произвольным образом, и зависимые, являющиеся результатом изменения значений других переменных.
При логическом предположении о наличии зависимости между конкретными экономическими показателями следует установить форму зависимости, построить ее математическую модель, оценить качество полученной модели и, если оно хорошее, использовать модель.
Если показатели X и Y зависимы и математическая модель этой зависимости Y* = G(X ) известна, то, зная X (независимый показатель, факторный признак) и используя G-преобра- зование, можно предсказать значение зависимого показателя Y (Y* ~ Y, где Y* — вычисляемые по G-преобразованию значения Y) . Зависимый показатель Y называют еще результативным признаком.
83
Пусть Y — результативный признак, количественный. В этом случае регрессионный анализ является важнейшим инструментом построения математической модели Y* = G(X ) и оцен-
ки ее качества, при этом в классическом регрессионном анализе предполагается, что X — по- казатель-фактор является также количественным (однако это может быть и не так).
Если показатель-фактор X один (X = X1) , а зависимость между Y и X предполагается линейной, то для построения математической модели прямой линии Y* = a + b1X1 используются
инструменты линейного однофакторного регрессионного анализа (парная регрессия). Вид и параметры уравнения регрессии устанавливаются с помощью метода наименьших квадратов отклонений эмпирических данных от ожидаемых значений [9].
Для оценки зависимости между двумя переменными в среде моделирования MS Excel в случае парной регрессии удобно использовать метод диаграмм. Порядок использования этого метода рассмотрен в задании 23.
Задание 23
Управляющий заводом по выпуску небольших холодильников получает плановое задание по числу холодильников, которые надо выпускать в каждом месяце. Имеются фактические данные за 14 месяцев о произведенном объеме продукции и ежемесячных текущих расходах завода (табл. 5.1) [13].
Анализируя фактические данные, управляющий предполагает, что ежемесячные текущие расходы завода зависят от количества произведенных в этом месяце холодильников. Как определить эту зависимость? Как спрогнозировать ежемесячные расходы на основе произведенного объема продукции? Это поможет менеджеру завода определить текущий бюджет и затраты (с их детализацией) на производство холодильников.
Порядок выполнения задания
1. На листе рабочей книги MS Excel в диапазоне A1:C15 создайте таблицу с информацией об объеме производства продукции и текущих расходах завода по месяцам (см. табл. 5.1).
|
2. По данным табл. 5.1 постройте |
||
Таблица 5.1 |
точечную диаграмму (или |
график рас- |
|
Объем продукции и текущие расходы завода |
сеяния), |
отображающую |
независимые |
|
|
|
|
|
данные (количество произведенных хо- |
||
|
|||
|
лодильников) на оси x и зависимые (фак- |
||
|
тические ежемесячные текущие расходы |
||
|
завода) на оси y. При этом необходимо, |
||
|
чтобы столбец данных, который хотим |
||
|
отображать на оси x, располагался в таб- |
||
|
лице слева от столбца данных, которые |
||
|
показываются на оси y. |
|
|
|
Для создания графика необходимо |
||
|
выполнить следующие действия: |
||
|
• выделить данные табл. 5.1 в диа- |
||
|
пазоне |
B1:C15 (включая заголовки |
|
|
столбцов в ячейках B1 и C1); |
||
|
• щелкнуть кнопку Мастер диа- |
||
|
грамм на панели инструментов Стан- |
||
|
дартная; |
|
|
|
• в диалоге с Мастером диаграмм |
||
|
на первом шаге указать тип диаграммы |
||
|
«Точечная», выбрать вид диаграммы (по |
||
|
|||
умолчанию — первый) и нажать кнопку Далее;
•на втором шаге диалога еще раз нажать кнопку Далее;
•на шаге 3 на вкладке «Заголовки» подписать названия осей x (Количество произведенных холодильников) и y (Фактические текущие расходы завода, $ тыс.), на вкладке «Линии
84
сетки» для оси Y включить флажок «Промежуточные линии», на вкладке «Легенда» включить флажок «Добавить легенду» и нажать кнопку Далее;
• на шаге 4 указать, куда надо поместить диаграмму, и нажать кнопку Готово.
3. Расположите диаграмму под табл. 5.1 и сравните ее с рис. 5.1. Отредактируйте полученную диаграмму при необходимости.
Рис. 5.1. Точечная диаграмма «Выпуск холодильников и текущие расходы завода»
По данным графика рассеяния логично предположить, что между количеством произведенных холодильников и ежемесячными текущими расходами существует линейная зависимость. Чтобы увидеть прямую линию, "наилучшим образом аппроксимирующую" точки графика, следует добавить на график линию тренда. Для этого необходимо выполнить следующие действия.
4. Щелкните график рассеяния левой клавишей мыши, чтобы выделить его, а затем щелкните конкретную точку на графике. Все точки окажутся выделенными золотым цветом.
5. Укажите мышью на любую выделенную точку графика и нажмите правую клавишу мыши. В контекстном меню выберите команду Добавить линию тренда. Появится диалоговое окно с заголовком
Рис. 5.2. Диалоговое окно «Линия тренда» «Линия тренда» (рис. 5.2).
6. На вкладке «Тип» выберите линию тренда Линейная; на вкладке «Параметры» установите флажки:
•показывать уравнение на диаграмме;
•поместить на диаграмму величину достоверности аппроксимации ( R2 );
85
• включить переключатель «Другое» и в текстовое поле ввести новое название для тренда: «Линейный тренд (ожидаемые текущие расходы завода)» и нажать кнопку OK.
Полученные результаты показаны на рис. 5.3. Обратите внимание на добавленные в уравнение тренда после запятой дополнительно 2 знака (по умолчанию — 2). Для добавления в уравнение тренда дополнительно двух знаков после запятой надо щелкнуть на графике уравнение тренда, нажать правую кнопку мыши, в контекстном меню выбрать команду Формат подписей данных. Затем в диалоговом окне «Формат подписей данных» выбрать вкладку «Число», на ней выбрать формат Числовой, ввести в поле Число десятичных знаков значение 4 и нажать кнопку ОК.
Рис. 5.3. Фактические ежемесячные расходы завода и Линейный тренд
7. Сохраните полученные результаты командой Файл, Сохранить.
Как MS Excel определяет линию "наилучшей аппроксимации"? Такой линией считается та, которая минимизирует (по сравнению со всеми другими построенными линиями) сумму возведенных в квадрат вертикальных расстояний от каждой точки до линии. Вертикальное расстояние от каждой точки до линии называется ошибкой, или разностью.
Линия, которую строит MS Excel, называется линией наименьших квадратов. Мы минимизируем сумму ошибок, возведенных в квадрат, а не просто сумму ошибок, поскольку при обычном сложении положительные и отрицательные ошибки могут гасить друг друга. Так, в нашем задании точка, находящаяся на $100 выше прямой, и точка, находящаяся на $ 100 ниже прямой, при сложении ошибок дадут 0. Если же мы возведем ошибки в квадрат, то для нахождения прямой, обеспечивающей наилучшую аппроксимацию, MS Excel будет использовать тот факт, что наши прогнозы относительно каждой точки были ошибочными.
Таким образом, уравнение прямой, обеспечивающей наилучшую аппроксимацию для прогнозирования ежемесячных текущих расходов в зависимости от количества произведенных холодильников, имеет вид:
Текущие_ расходы = 64,26878* Количество_ произведенных _ холодильников +
(5.1)
+37894,0956
Используем теперь это уравнение для вычисления ежемесячных прогнозируемых значений текущих расходов. Для этого создадим таблицу прогнозов текущих расходов завода и ошибок прогнозов по месяцам — табл. 5.2 (табл. 5.2, а — в режиме формул и 5.2, б — в режиме
86
значений). Скопируем в нее данные столбцов A, B, C из табл. 5.1 и добавим еще два столбца — D и E с соответствующими заголовками.
Таблица 5.2, а (в режиме формул)
Прогнозные значения текущих расходов завода по месяцам и ошибки прогнозов
Таблица 5.2, б (в режиме значений)
Прогнозные значения текущих расходов завода по месяцам и ошибки прогнозов
8. В клетку D2 табл. 5.2, а запишем формулу = 64,2687*B2+37894,0956, которая определяет прогнозируемое значение текущих расходов для месяца 1. Эта формула скопирована затем в диапазон D3:D15. В клетках E2:E15 вычисляются ошибки (или разности) для каждой точки как величина, на которую фактическое значение точки отклоняется от линии наименьших
87
квадратов. Для каждого месяца ошибка равна разности фактических и спрогнозированных издержек. Поэтому в клетку E2 табл. 5.2, а записана формула =C2-D2, которая затем скопирована в диапазон E3:E15. Положительная ошибка означает, что точка располагается выше линии наименьших квадратов, а отрицательная — ниже. В клетку E17 табл. 5.2, а записана формула = СУММ(E2:E15) для вычисления суммы ошибок диапазона E2:E17. Она получилась равной
— 0,0304 (см. табл. 5.2, б). На самом деле для линии наименьших квадратов сумма ошибок должна равняться 0. Значение –0,0304 получено потому, что уравнение тренда было округлено до 4 десятичных знаков после запятой (см. п. 6 указаний к выполнению задания). Сумма ошибок, равная 0, подразумевает, что линия наименьших квадратов обладает свойством деления значений точек пополам.
Таким образом, когда произведено 1260 холодильников в месяц, то прогнозируемые текущие расходы равны 118872,66.
Нельзя использовать линию наименьших квадратов для прогнозирования независимых значений, лежащих вне диапазона фактических данных. Полученную линию тренда следует использовать для прогнозирования текущих расходов завода при объемах производства примерно от 470 до 1296 холодильников в месяц.
Точку пересечения линии тренда (см. рис. 5.3) с осью y ($37894,0956) можно интерпре-
тировать как ежемесячные постоянные издержки. Поэтому, даже если завод в течение месяца не выпустит ни одного холодильника, мы оцениваем текущие расходы на работу завода в $37894,10. Наклон этой линии (64,2687) указывает, что каждый дополнительно произведенный холодильник увеличивает ежемесячные текущие расходы на $64,27. Таким образом, мы считаем, что переменные издержки на производство холодильника равны $64,27.
Насколько хорошо полученное уравнение тренда (см. рис. 5.3) объясняет ежемесячные колебания текущих расходов завода?
Понятно, что каждый месяц текущие расходы и количество произведенных холодильников меняются. Возникает вопрос, какой процент изменений текущих расходов объясняется изменением количества производимых холодильников в месяц? Ответ на этот вопрос дает значение R2 (0,6882), показанное на рис. 5.3.
Можно сказать, что найденная линейная зависимость объясняет 68,82 % изменений значений ежемесячных текущих издержек. Это означает, что 31,18 % изменений объясняются другими факторами, влияющими на текущие расходы. Установить такие факторы позволяют инструменты множественной регрессии.
Всегда возникает вопрос, какое значение R2 хорошее? Определенного ответа на данный вопрос нет. В случае парной регрессии большее значение R2 свидетельствует о лучшей аппроксимации данных, чем меньшее значение. Гораздо эффективнее в качестве критерия точности полученных прогнозов использовать стандартную ошибку регрессии.
Насколько точны полученные прогнозы?
Когда мы подбираем наилучшую линию тренда по эмпирическим значениям, то получаем стандартную ошибку регрессии, которая измеряет "разброс" значений вокруг линии наименьших квадратов. Стандартную ошибку, связанную с линией наименьших квадратов, можно вычислить с помощью функции СТОШУХ из категории Статистические.
Синтаксис функции:
СТОШУХ (известные_ значения _ у;известные_ значения _ х) , |
(5.2) |
где диапазон известные_значения_ y содержит зависимые значения, а диапазон |
извест- |
ные_значения_ x — независимые.
Для оценки и анализа параметров полученного линейного тренда создадим таблицу параметров линейного — табл. 5.3 (табл. 5.3, а — в режиме формул и 5.3, б — в режиме значений), используя данные табл. 5.2 и добавив в нее столбцы F и G.
Вклетке G5 табл. 5.3, а стандартная ошибка регрессии (СОР) вычисляется по формуле
=СТОШУХ (C2:C15;B2: B15) . Она равна 13771,9 (см. табл. 5.3, б).
88
Таблица 5.3, а (в режиме формул)
Оценка параметров линейного тренда
89
90
Таблица 5.3, б (в режиме знчений)
Оценка параметров линейного тренда