Построение уравнения регрессии

Имеется набор наблюдений суммы выручки магазина 3 (таблица 4) за ряд периодов (длительность периода измеряется в месяцах). Необходимо построить уравнение регрессии с использованием средства «линия тренда». Найти и интерпретировать коэффициент детерминации (величина достоверности аппроксимации регрессии). Спрогнозировать сумму выручки на следующий период времени.

 

 

Таблица 3. Отчетная ведомость выручки от продаж (в тыс. руб.) сети магазинов компании

Магазин	Сумма выручки	Суммарная выручка	Рейтинг	Средняя выручка	Доля маг в общей выручке	Диапазон выручки	Кол.	Вознаграждения магазина
Июнь	Июль	Август	Комиссия магазина	Премия	Общая сумма вознаграждений
магазин 1						431,00	12,12%			6,465		6,465
магазин 2						478,00	13,44%			7,17		7,17
магазин 3						535,33	15,06%			8,03		8,03
магазин 4						702,67	19,76%			42,16	31,62	73,78
магазин 5						715,33	20,12%			42,92	42,92	85,84
магазин 6						693,33	19,50%			41,6	20,8	62,4
Итого

 

Таблица 4. Временной ряд суммы выручки магазина 3

Номер периода (t)
Сумма выручки в тыс. руб. (S)

Рис. 1. Диаграмма структуры суммарной выручки

 

Построим с помощью средства «линия тренда» уравнение регрессии (функцию S) для суммы выручки магазина 3, зависящей от периода времени (t).

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

Добавим к диаграмме линию тренда с отображением уравнения регрессии и величины достоверности его аппроксимации (рисунки 2а, 2в). Величина достоверности аппроксимации (R²) для полиномиальной модели составила 0,8836, а для экспоненциальной модели – 0,7921. Оба значения этого коэффициента близки к 1, но для полиномиальной модели величина R² выше. Это говорит о том, что полиномиальная модель лучше (точнее) описывает реальные данные.

 

Рис. 2а. Уравнение полиномиальной регрессии выручки магазина 3

Рис. 2б. Уравнение экспоненциальной регрессии выручки магазина 3

Поэтому для прогноза выберем полиномиальную модель третьей степени. Заметим также, что, если использовать полином более высокой степени, то можно с точки зрения целевой задачи получить «абсурдный» результат прогноза, не смотря на высокое значение величины достоверности (более близкой к 1).

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

S = а t³ +b t² +c t + d, где а, b, c, d — коэффициенты, S – сумма выручки, t – номер наблюдения.

В результате получили следующие значения коэффициентов регрессии: a= -5,5354; b=72,787; c= -221,68; d=435,21.

На основе этого уравнения рассчитаем плановую сумму выручки указанного магазина на будущий период времени. Эта величина, как видно из таблицы 5, составит 300,52 тыс. рублей.

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

 

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

месяц	номер периода (t)	Планируемая сумма выручки (S)	Коэффициенты уравнения
а	в	с	d
сентябрь		300,5204	-5,5354	72,787	-221,68	435,21

 

2.2.5. Расчет плана производства

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

Пусть цех выпускает два вида красок: П-115 для внутренних (обозначим ее через I) и наружных работ (E).

Продукция обоих видов поступает в оптовую продажу. Для производства красок используются два исходных продукта (сырье) — А («суспензия в пентафталевом лаке») и В («органический растворитель и специальные добавки»). Максимально возможные суточные запасы этих продуктов составляют 6 т и 8 т соответственно. Расходы А и В на 1 т соответствующих красок приведены в таблице 6.

 

Таблица 6.Ограничения для задачи о производстве красок

Исходный продукт	Расход исходных продуктов (в тоннах) на тонну краски	Максимально возможный запас, т
краска Е	краска I
А В

 

Изучение рынка сбыта показало, что суточный спрос на краску I никогда не превышает спроса на краску E более чем на 1 т. Кроме того, установлено, что спрос на краску I никогда не превышает 2 т. в сутки.

Оптовые цены одной тонны красок равны: 66 тыс. руб. для краски E и 60 тыс. руб. для краски I.

Какое количество краски каждого вида в сутки должен производить цех, чтобы доход от их реализации был максимальным?

Для решения этой задачи сначала построим математическую модель.

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

x_I — суточный объем производства краски I (в тоннах);

x_Е — суточный объем производства краски E (в тоннах).

Суммарная суточная прибыль от производства x_I краски I и х_Е краски E равна

S = 66000 х_Е +60000 x_I,.

Целью цеха является определение среди всех допустимых значений х_Е и x_I таких, которые максимизируют суммарную прибыль, т. е. целевую функцию S.

Перейдем к ограничениям, которые налагаются на х_Е и x_I. Объем производства красок не может быть отрицательным. Следовательно, х_Е, x_I > 0.

Расход исходного продукта для производства обоих видов красок не должен превосходить максимально возможный запас данного исходного продукта. Таким образом,

x_E +2 x_I <6,

2 x_E + x_I <8.

Кроме того, ограничения на величину спроса на краски имеют вид: x_I - х_Е <1, x_I <2.

Таким образом, математическая модель данной задачи имеет следующий вид.

Максимизировать:

S = 66000 х_Е +60000 x_I,.

при ограничениях:

x_E +2 x_I <6,

2 x_E + x_I <8,

x_I - х_Е <1,

x_I <2,

x_I, х_Е >0.

Заметим, что данная модель является линейной, т. к. целевая функция и ограничения линейно зависят от переменных. Решим поставленную задачу с помощью средства «Поиск решения». На рисунке 3 изображены исходные данные задачи (ячейки а4:g7), целевая функция (ячейка c11), переменные и решение (ячейки а10 и в10), ограничения (ячейки а14:в17). На рисунке 4 приведены значения параметров средства «Поиск решения» для поставленной задачи.

В результате был найден оптимальный план производства красок (рисунок 5), дающий максимальную прибыль. Таким образом, оптимальным является производство в сутки 3,3т краски Е и l,3т краски I. Этот объем производства принесет 300 тыс. руб. прибыли.

 

Рис. 3. Окно с исходными данными для расчета плана производства

 

Рис.4. Окно «Поиск решения» для расчета плана производства