Изменяя ячейки: D3:D5;F3:F5

Ограничения:

B3=D3+F3 – стоимость вывоза с 1-го склада,

B4=D4+F4 – стоимость вывоза со 2-го склада,

B5=D5+F5 – стоимость вывоза с 3-го склада,

D6=D3+D4+D5 – стоимость доставки в 1-й пункт,

F6=F3+F4+F5 – стоимость доставки в 2-й пункт,

F3:F5>= 0 – условие положительности стоимости,

D3:D5>= 0 – условие положительности стоимости.

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

D3:D5=целое и F3:F5=целое.

Результаты в этом случае будут таковы: в первый пункт доставки направляются грузы в объеме 7 т, 30 т, 3 т с каждого из трех складов соответственно, во второй – 43 т, 0 т, 37 т. Значение целевой функции осталось как и прежде – 1300 руб.

5.1.13 Регрессии и прогнозирование состояний экономических объектов.

Пример 5.12. Воспользовавшись результатами примера 5.1.9, введите, как показано в таблице 5.1.25, в столбцы B и С потоки чистых доходов предприятия. При этом в столбце B отобразите результаты за период янв ¸ окт (NPROF полная), а в столбце C – лишьза периодянв ¸ июн (NPROF укороченная).

Постройте диаграмму чистых доходов, отобразив одновременно результаты столбцов B и C, как показано на рисунке5.1.14 а. После этого скопируйте рисунок и по данным столбца C постройте прогноз на 4 месяца вперед. Сравните этот прогноз (постпрогноз) с реальными данными столбца B.

Решение. На новой диаграмме наведите курсор мышки на ряд данных, относящихся к NPROF укороч, и щелкните правой кнопкой. В появившемся «висящем» меню активизируйте раздел Добавить линию тренда.

В открывшемся меню Линия тренда (закладка «Тип») необходимо выбрать раздел «Построение линии тренда (аппроксимация и сглаживания)» и активизировать полиномиальную зависимость щелкнув левой кнопкой мышки по окошку с изображением кривой (окошко становится затемненным). Необходимо также справа от окошка установить число 3, как показатель степени полиномиальной зависимости. В левом нижнем углу меню Линия тренда отображается опция «Построен на ряде», под которой в окне приведена затемненная надпись той зависимости, которая подлежит прогнозированию.

Таблица 5.1.25

	A	B	C
	Прогнозирование экономических зависимостей
		NPROF полная	NPROF укороч
	янв	-89 243,00р.	-89 243,00р.
	фев	-124 122,00р.	-124 122,00р.
	мар	32 673,00р.	32 673,00р.
	апр	-16 431,00р.	-16 431,00р.
	май	-29 077,00р.	-29 077,00р.
	июн	86 859,71р.	86 859,71р.
	июл	91 218,07р.
	авг	191 263,48р.
	сен	403 642,97р.
12	окт	903 247,60р.

Рис. 5.1.14 а

Рис. 5.1.14 б

Для непосредственного построения прогноза с помощью выбранной полиномиальной зависимости 3-ей степени необходимо активизировать закладку «Параметры» меню Линия тренда. Далее, в подразделе «Прогноз» следует установить прогноз вперед на 4 периода и установить метки в подразделах «показывать уравнение на диаграмме» и «поместить на диаграмму величину достоверности аппроксимации (R^2)» линией тренда реальной зависимости.

Как видно на новой диаграмме линия тренда недостаточно достоверно отражает реальную динамику чистой прибыли предприятия, т.к. коэффициент детерминации (множественной корреляции) R² = 0,632 (см. раздел «Критерии качества регрессионных моделей и прогнозирования» на стр. 61). В то же время прогноз для одного – двух периодов вперед достаточно правдоподобен.

В качестве эксперимента осуществите прогноз лишь на 2 периода вперед (перед этим снова скопируйте диаграмму рис. 5.1.14 а), выбрав полиномиальную зависимость 4 степени. Результат показан на рисунке 5.1.14 в

Рис. 5.1.14 в

Наглядно видно, что линия тренда стала достаточно достоверно отражать реальную динамику чистой прибыли предприятия (R² > 0,9) на интервале известных эмпирических значений, однако качество прогноза резко ухудшилось. Основное объяснение этому заключается в том, что данные интервала содержат циклическую компоненту, в то время как прогнозирование на основе полиномиальных зависимостей более подходит для относительно монотонных данных. Увеличивая степень полинома, мы все более достоверно аппроксимируем циклическую зависимость на интервале известных эмпирических значений, однако ухудшаем прогноз в той области, где зависимость близка к монотонной.

Одним из путей построения более точного прогноза является идентификация и прогнозирование самих коэффициентов РАР модели, описывающей эмпирические данные в столбце С таблицы 5.1.25и затем использование данных коэффициентов для прогнозирования зависимости. Здесь мы сталкиваемся, по существу, с проблемой структурной идентификации модели ЭО.

Пример 5.13. Пусть исследуемый бизнес процесс описывается (относительно количества произведенных и проданных товаров/услуг) нестационарным РАР уравнением типа (4.4.1)

Q_t = + B _t C_t + H_t.

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

Решение. Представим нестационарные коэффициенты уравнения в виде

A_t,r = A⁰ _r + A¹ _r * t + A²_r * t² + …, B_t = B⁰ + B¹ * t + B² * t,

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

Q_t = + B⁰ C_t + H_t.

Метод наименьших квадратов сводит задачу к известной (см. 4.4.20) системе уравнений

Далее, найденные из системы уравнений коэффициенты A⁰_r @ A^*_r и B⁰ = B^* нулевого приближения используются для отыскания методом последовательных приближений коэффициентов более высоких приближений A¹ _r, B¹, … A^k _r, B^k.

Для проверки метода воспользуемся примером 5.1.9, записав все данные (как показано в таблице 5.1.26а) и решив сначала прямую задачу. Нестационарные коэффициенты A и B пересчитывались в соответствие с формулой (4.4.1). Взяты лишь 5 коэффициентов A₁, A₂, … A₅ и один коэффициент B. Прямая задача построения РАР уравнения 5 -го порядка заключается в вычислении ряда значений Q_t (с января по декабрь включительно) при известных структурных параметрах. В качестве помехи H взят равномерно распределенный шум с максимальной амплитудой 1000,00.

В таблице 5.1.26б приведены расчеты всех перекрестных произведений и их сумм. Однако, в отличие от предыдущей таблицы, считается, что известны лишь 6 первых значений Q_t (с января по июнь включительно). Этого как раз достаточно, чтобы найти 6 неизвестных коэффициентов нулевого приближения – 5 коэффициентов A⁰₁, A⁰₂, … A⁰₅ и один коэффициент B₀.

В таблице 5.1.26в приведен стандартный метод решения системы уравнений с помощью инструмента Поиск решения.