Информационные системы в экономике

2.1.1. Одна независимая  переменная

В задании 1 имеются данные, полученные опытным путем, которые  представлены в виде таблицы значений.

На основе этих данных требуется подобрать такую функцию y = f(x), которая с точки зрения некоторого критерия оптимальности наилучшим образом описывала бы экспериментальную зависимость.

Задача аппроксимации  распадается на две части. Сначала  необходимо установить вид зависимости y = f(x) и соответственно вид эмпирической формулы, то есть определить, является ли она линейной, квадратичной, логарифмической или какой либо другой. После этого определяются численные значения неизвестных параметров выбранной формулы, для которых приближение к заданной функции оказывается наилучшим. Для сглаживания экспериментальных зависимостей в МS Excel используются различные функции y = f(x): линейная, полиномиальная, логарифмическая, степенная, экспоненциальная.

Степень точности аппроксимации  данных в МS Excel оценивается коэффициентом детерминации (R²). Чем ближе этот коэффициент к значению 1, тем точнее приближение.

Задание 1       

Вложенные в производство средства дают прибыль:

Средства	3000	4000	5000	6000	7000	8000
Прибыль	850	900	1000	1300	2000	4000

 

1. Определить зависимость прибыли от вложенных средств
2. Вычислить прибыль для вложений, равных:

Вариант	Сумма вложенных средств
4	18900

 

Решение:

1. На основе данных  таблицы строим точечную диаграмму со значениями, соединенными сглаживающими линиями.

Рис.1. Зависимость прибыли от вложенных средств

 

Наводим курсор на одну из точек полученного графика и  из контекстного меню выбираем команду: Добавить линию тренда (рис. 2).

 

Линия тренда типа Логарифмическая

 

 

 

 

 

Линия тренда типа Линейная

 

Линия тренда типа Степенная

 

 

 

 

 

 

 

 

 

Линия тренда типа Экспоненциальная

Рис. 2. Аппроксимации экспериментальных данных

 

В данном случае результат  не является удовлетворительным. Наилучшей  в данном задании является полиномиальная функция, которая дает показатель достоверности R² = 0,9618, тогда как для логарифмической функции этот показатель равен 0,6057, для линейной функции этот показатель равен 0,7239, для степенной функции этот показатель равен 0,7566, для экспоненциальной функции этот показатель равен 0,8627 (рис. 3).

Рис. 3. Полиномиальная аппроксимация

 

Таким образом, искомая  аналитическая формула y = f(x) для установленной из опыта функциональной зависимости y = φ(x) примет вид:

y = 0,0002x² - 1,8338x + 4564,6

2. Чтобы вычислить прибыль для вложений, равных 18900, необходимо данное значение подставить в формулу вместо х. Подставив, получим следующее значение прибыли:

y = 0,0002x² - 1,8338x + 4564,6 = 71442 - 34658,82 + 4564,6 = 41347,78

Для вложений 18900 прибыль составит 41347,78.

 

2. Несколько независимых переменных

 

В тех случаях, когда аппроксимируемая переменная y зависит от нескольких независимых переменных y = f(x₁, x₂,…, x_n) используются следующие специальные функции МS Excel:

ЛИНЕЙН и ТЕНДЕНЦИЯ  для аппроксимации линейных функций  вида:

y = а₀+ а₁х₁ + а₂x₂ +… +а_nx_n.

ЛГРФПРИБЛ и РОСТ для аппроксимации показательных функций вида:

y = а₀а₁^х1а₂^x2…а_n^xn.

Функции ЛИНЕЙН и ЛГРФПРИБЛ имеют одинаковые параметры.

Функции ТЕНДЕНЦИЯ и РОСТ позволяют находить точки, лежащие на аппроксимирующих кривых. Обе функции имеют одинаковые параметры.

Задание 2

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

х1	х2	Прибыль %
120	20	32,5
100	25	28,3
130	20	33,7
100	15	33,1
110	23	30,5
105	26	39,1
112	16	38,4

 

Требуется построить  таблицу, отражающую динамику прибыли в зависимости от числа посетителей (от 100 до 150)  и числа покупок (от 15 до 30) с шагом 5.

Решение:

Организуем данные на листе Excel, оставив пустой диапазон для у (рис. 4).

 

Рис. 4. Исходные данные на листе Excel

Выделим диапазон G4:G47 для у, вызовем функцию ТЕНДЕНЦИЯ и укажем для неё параметры. Функция ТЕНДЕНЦИЯ возвращает значения в соответствии с линейной аппроксимацией по методу наименьших квадратов. Нажатием клавиш CTRL+SHIFT+ENTER получаем результат для столбца у.

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

 

Рис. 5. Функция тенденция

 

Таблица 2.1

Итоговое значение у

х1	х2	у
100	15	34,28
105	15	34,44
110	15	34,60
115	15	34,76
120	15	34,93
125	15	35,09
130	15	35,25
135	15	35,41
140	15	35,57
145	15	35,73
150	15	35,90
100	20	33,42
105	20	33,59
110	20	33,75
115	20	33,91
120	20	34,07
125	20	34,23
130	20	34,39
135	20	34,56
140	20	34,72
145	20	34,88
150	20	35,04
100	25	32,57
105	25	32,73
110	25	32,89
115	25	33,05
120	25	33,21
125	25	33,38
130	25	33,54
135	25	33,70
140	25	33,86
145	25	34,02
150	25	34,18
100	30	31,71
105	30	31,87
110	30	32,03
115	30	32,20
120	30	32,36
125	30	32,52
130	30	32,68
135	30	32,84
140	30	33,00
145	30	33,17
150	30	33,33

 

2.2. Модели линейной  оптимизации в МS EXCEL

2.2.1. Решение задач  линейного программирования в  МS EXCEL

Задание 3

Предприятие выпускает  телевизоры, стереосистемы и акустические системы, используя общий склад  комплектующих. Запасы шасси на складе составляют 450 шт., кинескопов – 250, динамиков – 800, блоков питания – 450, плат – 600. На каждое изделие расходуется определенное количество комплектующих (проставить произвольно), которое необходимо указать в таблице. Пример таблицы приведен ниже. При ее заполнении необходимо учитывать, что запасы на складе являются ограничивающей величиной.

Прибыль от производства одного телевизора составляет 90 у. е., одной стереосистемы – 50 и аудиосистемы – 45.

Необходимо найти оптимальное  соотношение объемов выпуска  изделий, при котором прибыль от производства всей продукции будет максимальной.

Решение:

1. Составим модель  задачи, заполнив ячейки для значений переменных (первоначально ячейки х1 и х2 заполняются числовыми значениями, например, 30), целевой функции и ограничений (рис. 6). Расход количества комплектующих проставляется самостоятельно.

Рис. 6. Параметры задачи

 

Целевая функция рассчитывается по формуле: 

Z=C9*C12+D9*D12+E9*E12.

Расходы:

 

Шасси =C4*C12+D4*D12+E4*E12;

Кинескопы =C5*C12+D5*D12+E5*E12;

Динамики =C6*C12+D6*D12+E6*E12;

Блоки питания =C7*C12+D7*D12+E7*E12;

Платы =C8*C12+D8*D12+E8*E12.

 

C помощью команды Сервис –> Поиск решения находим оптимальный план решения (рис. 7).

 

 

 

 

 

 

 

 

 

 

Рис. 7. Оптимальный план решения

 

В результате в ячейках  с переменными задачи появятся значения соответствующие оптимальному плану (150 телевизоров, 50 стереосистем, 100 акустических систем), а в ячейке для целевой функции – значение прибыли (20500 у.е.), соответствующее данному плану.

Таким образом, данное решение  является удовлетворительным, следовательно, сохраним оптимальный план по результатам решения на отдельный лист (рис. 8).

 

Рис. 7. Результаты поиска

 

2.2.2. Двойственная задача линейного программирования

 

Задание 4

Сформулировать и решить двойственную задачу к задаче из задания 3.

Предприятие выпускает  телевизоры, стереосистемы и акустические системы, используя общий склад комплектующих. Запасы шасси на складе составляют 450 шт., кинескопов – 250, динамиков – 800, блоков питания – 450, плат – 600. На каждое изделие расходуется определенное количество комплектующих. Запасы на сладе являются ограничивающей величиной.

Прибыль от производства одного телевизора составляет 90 у. е., одной стереосистемы – 50 и аудиосистемы – 45.

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

Решение

Построим модель данной задачи:

у1 – цена за 1 шт. шасси; у2 – цена за 1 кинескоп; у3 – цена за 1 динамик; у4 – цена за 1 блок питания; у5 – цена за 1 плату.

 

Рис. 8. Модель двойственной задачи линейного программирования

 

Цены у1,…,у5 характеризуют  степень ценности ресурса для  производителя и называются теневыми ценами.

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

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

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

 

Рис. 9. Результаты поиска решения

 

Поиск решения дает следующий  результат: теневая цена на шасси  – 0 у.е., на кинескопы – 6 у.е., на динамики – 19 у.е., на блоки питания – 8,75 у.е., на платы – 0 у.е.

Данное решение является удовлетворительным, следовательно, сохраним оптимальный план по результатам решения на отдельный лист (рис. 10).

 

 

Рис. 10. Отчет о результатах поиска

 

ЗАКЛЮЧЕНИЕ

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