Описание и программирование решения задач оптимизации средствами Excel

 

Вывод: За период 10 лет  с начальным капиталом 10310 млн. руб. наибольшие отчисления в бюджет составляют 372743 млн. руб. при налоговой ставке 30%.

Двухфакторный имитационный анализ.

Зависимость поступлений  в бюджет от ставки налога и величины начального капитала за период 10 лет.

Начальный капитал	7010	8010	9010	10010	10210	10310
Ставка налога	Сумма
Ставка налога 10%	175727	200795	225863	250932	255945	258452
Ставка налога 20%	244906	279843	314780	349716	356704	360197
Ставка налога 30%	253437	289590	325744	361897	369128	372743
Ставка налога 40%	230964	263912	296859	329807	336397	339692
Ставка налога 50%	195758	223683	251608	279534	285119	287912
Ставка налога 60%	158353	180943	203533	226122	230640	232899
Ставка налога 70%	124219	141940	159660	177380	180925	182697

 

Решение в Excel:

 

Таблица оптимальных ставок налога:

Начальный капитал	Оптимальная ставка налога
Начальный капитал 7010	30%
Начальный капитал 8010	30%
Начальный капитал 9010	30%
Начальный капитал 10010	30%
Начальный капитал 10210	30%
Начальный капитал 10310	30%

Анализ и вывод результатов.

При изменении начального капитала фирмы ставка налога остается неизменной, равной 30%. Чем больше начальный  капитал фирмы, тем больше отчисления в бюджет. Следовательно, государству  выгоднее облагать более высокими налогами.

 

Задача №2

Производственная  задача.

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

Цель работы.

Научиться составлять наилучший (оптимальный) план производства продукции  с учетом ограниченного обеспечения материальными ресурсами. Все необходимые расчеты выполнять на ЭВМ, используя общеизвестные программы.

Выделение проблемной системы.

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

Возможные объемы производства зависят от обеспеченности тремя  видами ресурсов: труд, машины и оборудование, материалы и комплектующие.

В данной задаче выбираем проблемную систему, включающую номенклатуру производства с искомыми объемами. Критерием производства принимаем  максимизацию прибыли. Из ресурсов будем  учитывать лишь ограничения по комплектующим узлам и деталям.

Постановка  задачи.

Туристическая фирма  в летний сезон обслуживает в  среднем 10000 туристов и располагает  флотилией из двух типов судов. В  месяц выделяются 9000 тонн горючего. Потребность в рабочей силе не превышает 1000 человек.

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

Дано:

Количество показателей  на месяц:

пассажировместимость 10000 человек, горючее – 9000 тонн, экипаж – 1000 человек.

Прибыль от эксплуатации судна:

первого типа – 20000000 рублей,

второго типа – 30000000 рублей.

Показатели по каждому  типу судов:

Показатели	Судно 1 типа	Судно 2 типа
Пассажировместимость, чел.	2000	1000
Горючее, тонны	700	800
Экипаж	100	100

Найти максимальную прибыль.

Ручной поиск  оптимального плана.

Цель: составить оптимальный план вручную

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

Судно 1 типа	2
Судно 2 типа	1
Расход на 1 продукцию			Расход по плану
Показатели	Судно 1 типа	Судно 2 типа	Судно 1 типа	Судно 2 типа	Расход показателей по плану	Количество показателей
Пассажировместимость	2000	1000	4000	1000	5000	10000
Горючее	700	800	1400	800	2200	9000
Экипаж	100	100	200	100	300	1000
Прибыль, руб.	20000000	30000000	40000000	30000000	70000000

Вывод:

Данный план является наилучшим планом с помощью программы Excel.

Программный поиск  оптимального решения.

1. Нахождение  оптимального решения с помощью  программы оптимизации в Excel:

 

Судно 1 типа	4
Судно 2 типа	2
Расход на 1 продукцию			Расход по плану
Показатели	Судно 1 типа	Судно 2 типа	Судно 1 типа	Судно 2 типа	Расход показателей  по плану	Количество показателей
Пассажировместимость	2000	1000	8000	2000	10000	10000
Горючее	700	800	2800	1600	4400	9000
Экипаж	100	100	400	200	600	1000
Прибыль, руб.	20000000	30000000	80000000	60000000	140000000

Excel получил результат:

Судов первого типа 4 штуки, судов второго типа 2 штуки.

Прибыль составляет 140000000 руб.

Отчет:

Целевая ячейка (Максимум)
	Ячейка	Имя	Исходное  значение	Результат
	$F$9	Прибыль, руб. Расход показателей по плану	0	140000000
Изменяемые ячейки
	Ячейка	Имя	Исходное  значение	Результат
	$B$2	Судно 1 типа	0	4
	$B$3	Судно 2 типа	0	2
Ограничения
	Ячейка	Имя	Значение	Формула	Статус	Разница
	$B$2	Судно 1 типа	4	$B$2=$B$3*2	не связан.	0
	$F$6	Пассажировместимость  Расход показателей по плану	10000	$F$6<=$G$6	связанное	0
	$F$7	Горючее Расход показателей по плану	4400	$F$7<=$G$7	не связан.	4600
	$F$8	Экипаж Расход показателей по плану	600	$F$8<=$G$8	не связан.	400
	$B$2	Судно 1 типа	4	$B$2>=0	не связан.	4
	$B$3	Судно 2 типа	2	$B$3>=0	не связан.	2

Анализ оптимального плана и решение менеджера.

Оптимальными планами  являются решения, полученные с помощью  программ Excel и Mathcad, которые получили одинаковый результат:

Судна первого типа – 4 штуки, судов второго типа – 2 штуки. Прибыль будет составлять 140000000 руб.

При нахождении оптимального плана пассажировместимость остается прежней, равной 10000 человек. Для дальнейшего  увеличения прибыли целесообразно увеличить пассажировместимость. Также остается неиспользованными 4600 тонн горючего и не задействованными 400 человек экипажа. В результате заморожены оборотные средства, оборотный капитал, увеличиваются расходы по хранению горючего на складе и не занятости экипажа, теряется прибыль. Завоз горючего можно сократить, а незанятое количество экипажа уволить.

 

Задача №3.

Транспортная  задача.

Определение проблемы: В современных условиях большие транспортные расходы связаны:

- с простоями в ожидании  погрузочно-разгрузочных работ;

- с нерациональными  перевозками;

- с затратами на  бензин;

- с порожними пробегами  и т. д.

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

Оптимизировать план перевозок груза можно по какому-либо экономическому показателю:

- финансовые затраты  на перевозку грузов;

- время, затрачиваемое  на перевозку.

Для решения таких  задач в линейном программировании разработаны следующие методы:

- Симплекс-метод;

- Метод потенциалов;

- Венгерский метод.

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

- затраты на транспортировку  ресурсов должны быть минимальными;

- возможности поставщиков  ограничены;

- нужно полностью выполнить  заявки каждого потребителя.

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

1. Имеются m пунктов  отправления (поставщиков грузов),

А1, А2, А3 …Аi, Аm

на которых сосредоточены  запасы како-либо груза в объемах  соответственно:

а1, а2, а3 …аi, аm

где величина аi – максимально  возможное количество груза в i пункте отправления. Тогда суммарный запас груза у всех поставщиков составляет:

2. Имеются n пунктов  назначения,

B1, B2, B3 …Bj, …Bn

которые подали заявки на поставку грузов в объемах соответственно:

b1, b2, b3, …bj, …bn

тогда суммарная величина заявок составляет:

3. Стоимость перевозки  1 единицы груза от поставщика  Аi к потребителю Bj обозначим ci,j (транспортный тариф).

Общая стоимость перевозок  составляет матрицу транспортных издержек С.

Критерием оптимальности  выберем суммарные затраты (издержки по перевозки груза).

Все исходные данные транспортной задачи записываются в виде таблицы (транспортной), в которой xi,j – значение объема перевозки грузов от поставщика Аi к потребителю Bj.

 

Пункты отправления	Пункты назначения				Запасы аi
	В1	В2	…Вj ...	Вn
А1	c11 x11	c12 x12	c1j x1j	c1n x1n	а1
А2	c21 x21	c22 x22	c2j x2j	c2n x2n	а2
…Аi…	ci1 xi1	ci2 xi2	cij xij	cin xin	аi
Аm	cm1 xm1	cm2 xm2	cmj xmj	cmn Xmn	аm
Заявки bj	b1	b2	bj	bn

Задача заключается  в определении плана перевозок  матрицы x, где

x (i = 1, m; j = 1, n ),

который удовлетворяет  следующим условиям:

1. План перевозок от i поставщика всем потребителям (сумма  по строке), ограничивается запасом  на складе j поставщика.

2. План поставок j потребителю  от всех поставщиков (сумма  по столбцу) должен быть не  меньше объема заявок j потребителя.

3. Из физического смысла  задачи определяем, что объемы  перевозок xij не могут быть  отрицательными.

xij ≥ bj; j = 1, n

4. Затраты на перевозку  равны:

m n

P(X) = ∑∑ cij* xij

i=1 j=1

X – план перевозок,  то есть матрица X.

Таким образом, экономико-математическая модель задачи можно записать в виде:

- Найти минимальное  значение целевой функции:

m n

P(X) = ∑∑ cij* xij  min

i=1 j=1

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

Условие: требуется минимизировать затраты на перевозку грузов от заводов поставщиков (А1, А2, А3) на торговые склады В1, В2, В3. Товары могут доставляться с любого завода на любой склад, при условии: