 |
|
Построение уравнения регрессии
Имеется набор наблюдений суммы выручки магазина за ряд периодов (длительность периода измеряется в месяцах). Необходимо построить уравнение регрессии с использованием средства «линия тренда». Найти и интерпретировать коэффициентдетерминации(величина достоверности аппроксимации регрессии). Спрогнозировать сумму выручки на следующий период времени.
Построим с помощью средства «линия тренда» уравнение регрессии (функцию S) для суммы выручки магазина за ряд периодов, зависящей от периода времени (t).
Для этого с помощью построителя диаграмм строим две точечные диаграммы для суммы выручки магазина 3. Результат построения изображен на рисунках 2а и 2б. Исходя из вида поведения кривой, в качестве лини тренда выберем, например полиномиальную модель третьей степени (рис. 2а) и экспоненциальную модель (рис. 2б).
Добавим к диаграмме линию тренда с отображением уравнения регрессии и величины достоверности его аппроксимации (рисунки 2а, 2б). Величина достоверности аппроксимации (R2) для полиномиальной модели составила 0,9647, а для экспоненциальной модели – 0,7946. Оба значения этого коэффициента близки к 1, но для полиномиальной модели величина R2 выше. Это говорит о том, что полиномиальная модель лучше (точнее) описывает реальные данные.
Рис. 2а. Уравнение полиномиальной регрессии выручки магазина
Рис. 2б. Уравнение экспоненциальной регрессии выручки магазина
Поэтому для прогноза выберем полиномиальную модель третьей степени. Заметим также, что, если использовать полином более высокой степени, то можно с точки зрения целевой задачи получить «абсурдный» результат прогноза, не смотря на высокое значение величины достоверности (более близкой к 1).
В общем случае уравнение полиномиальной регрессии третьей степени можно записать в следующем аналитическом виде:
S = а t3 +b t2 +c t + d, где а, b, c, d — коэффициенты, S – сумма выручки, t – номер наблюдения.
В результате получили следующие значения коэффициентов регрессии: a= -1,172; b=34,147; c= -306,46; d=5948,1.
На основе этого уравнения рассчитаем плановую сумму выручки указанного магазина на будущий период времени. Эта величина, как видно из таблицы 5, составит 5101,479 тыс. рублей.
Следовательно, можно сделать вывод, что на следующий период (сентябрь месяц) будет еще наблюдаться спад продаж и, соответственно, суммы выручки. Чтобы улучшить положение, необходимо предпринять ряд мероприятий, например, повысить качество и частоту рекламы, провести льготную распродажу не пользующихся спросом товаров и т.д.
Таблица 5. Расчет плановой суммы выручки магазина на сентябрь
| месяц | номер периода (t) | Планируемая сумма выручки (S) | Коэффициенты уравнения | |||
| а | в | с | d | |||
| сентябрь | 5101,479 | -1,172 | 34,147 | -306,46 | 5948,1 |
Расчет плана производства
С некоторой периодичностью (например, раз в квартал или месяц), используя средство «Поиск решения», необходимо составить оптимальный план производства компьютерных и письменных столов с учетом производственных мощностей, потребностей продукции и других ограничений. Сформулируем эту задачу следующим образом.
Пусть цех выпускает два вида столов: столы письменные и столы компьютерные.
Продукция обоих видов поступает в оптовую продажу. Для производства столов используются два исходных продукта (сырье) — ДСП и пластик. Максимально возможные месячные запасы этих продуктов составляют 100 листов ДСП и 20 рулонов пластика. Расходы ДСП и пластика на 1 стол приведены в таблице 6.
Таблица 6.Ограничения для задачи о производстве столов
| Исходный продукт | Расход исходных продуктов (в м.кв.) на 1 стол | Максимально возможный запас, шт. | |
| Стол письм. | Стол комп. | ||
| ДСП пластик | 3,5 3,5 | 4,5 4,5 |
Оптовые цены одного стола равны: 5 тыс. руб. для письменного стола и 8 тыс. руб. для компьютерного стола.
Какое количество краски каждого вида в месяц должен производить цех, чтобы доход от их реализации был максимальным?
Для решения этой задачи сначала построим математическую модель.
В нашем случае цеху необходимо спланировать объем производства столов так, чтобы максимизировать прибыль. Поэтому переменными являются
X1 — месячный объем производства письменных столов (в шт.);
X2 — месячный объем производства компьютерных столов (в шт.).
Суммарная суточная прибыль от производства x1 столов письменных и х2 компьютерных столов равна
S = (5000-3,5*1800/6-3,5*21000/30) х1+(8000-4,5*1800/6-4,5*21000/30) x2,
Т.е. S=1500x1+3500x2.
Целью цеха является определение среди всех допустимых значений х1 и x2 таких, которые максимизируют суммарную прибыль, т. е. целевую функцию S.
Перейдем к ограничениям, которые налагаются на х1 и x2. Объем производства столов не может быть отрицательным. Следовательно, х1, x2 > 0.
Расход исходного продукта для производства обоих видов красок не должен превосходить максимально возможный запас данного исходного продукта. Таким образом,
3,5x1+4,5 x2 <6*100,
3,5x1+ 4,5x2 <6*100.
Кроме того, ограничения количество письменных столов должно составлять 65% от обь=щего числа изготовляемых столов, т.е. имеет место следующее ограничение 0,35x1-0,65x2=0
Таким образом, математическая модель данной задачи имеет следующий вид.
Максимизировать:
S=1500x1+3500x2
при ограничениях:
3,5x1+4,5 x2 <6*100,
3,5x1+ 4,5x2 <6*100,
0,35x1-0,65x2=0
X1,х2>0.
Заметим, что данная модель является линейной, т. к. целевая функция и ограничения линейно зависят от переменных. Решим поставленную задачу с помощью средства «Поиск решения». На рисунке 3 изображены исходные данные задачи (ячейки а4:g7), целевая функция (ячейка c11), переменные и решение (ячейки а10 и в10), ограничения (ячейки а14:в17). На рисунке 4 приведены значения параметров средства «Поиск решения» для поставленной задачи.
В результате был найден оптимальный план производства столов (рисунок 5), дающий максимальную прибыль. Таким образом, оптимальным является производство в месяц 101шт письменных столов и 54шт компьютерных столов. Этот объем производства принесет 342857 руб. прибыли.
Рис. 3. Окно с исходными данными для расчета плана производства
Рис .4. Окно «Поиск решения» для расчета плана производства