Новосибирская государственная академия водного транспорта

Омский филиал

КУРСОВАЯ РАБОТА

По информатике

Решение задач моделирования и оптимизации с помощью программ Excel и Mathcad

Выполнил студент:

Назарова Наталья Евгеньевна

группа ЭК-23

Проверила: Иванова Татьяна Леонидовна.

г. Омск-2006 г.

Содержание.

Лист заданий

Введение

Задача нахождения оптимальной ставки налога. Имитационное моделирование

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

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

Лист заданий

Задача №1 (билет №23К):

Исследовать средствами Excel и Mathcad зависимость оптимальной ставки налога от величины начального капитала предприятия.

Исходные данные:

Начальный капитал фирмы (млн. руб.): 7010, 8010, 9010, 10010, 10210, 10310.

Интервал моделирования 10 лет.

Рентабельность фирмы 80%.

Налоговая ставка: 10%, 20%, 30%, 40%, 50%, 60%, 70%.

Задача №2 (билет№47II):

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

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

Задача №3 (билет№59ТВ):

На заводах А1, А2, А3 имеется груз, поставляемый на склады В1, В2, В3. Запасы на заводах: 500, 600, 400 тонн соответственно. Нужно поставить груз на склады соответственно 500, 600, 500 тонн. Найти план перевозок груза, при котором затраты на перевозку были бы минимальными. Условия перевозки:

1. Минимальное количество поставляемого груза с завода А1 и завода А2 к каждому потребителю составляет 160 тонн.

2. С завода А2 на склад В1 нужно привезти не более 200 тонн, а с завода А3 на склад В1 – не менее 100 тонн.

Потребители	В1	В2	В3
Поставщики
А1	9	6	2
А2	4	15	15
А3	17	7	8

Введение.

Microsoft Excel.

Называть Excel просто «электронной таблицей» - все равно, что употребить в отношении Word термин «текстовый редактор». Вроде бы все сказано, а на самом деле ничего. Так как по количеству возможностей, скрытым и открытым функциям и кругу решаемых задач Excel, вероятно может обскакать тот же Word, не говоря уже об остальных программах.

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

Рабочее поле Excel поделено вертикальными и горизонтальными линиями на прямоугольные ячейки. Объединенные по вертикали ячейки составляют столбцы, а по горизонтали – строки. А заполнить эти клеточки можно чем угодно – текстом, численными значениями, даже графикой. Правда, для того, чтобы введенная вами информация обрабатывалась корректно, необходимо присвоить ячейке (а чаще – целому столбцу или строке) определенный формат:

Общий – это ячейки могут содержать как текстовую, так и цифровую информацию.

Числовой – для цифровой информации.

Денежный – для отображения денежных величин в заранее заданной пользователем валюте.

Финансовый – для отображения денежных величин с выравниванием по разделителю и дробной части.

Дата.

Время.

Процентный.

Дробный.

Экспоненциальный.

Текстовый.

Дополнительный – этот формат используется при составлении небольшой базы данных или списка адресов для ввода почтовых индексов, номеров телефонов, табельных номеров.

Операцию эту, как и многие другие, можно выполнить с помощью Контекстного меню ячейки или выделенного фрагмента таблицы. При этом объем информации, которую вы можете внести в клетки рабочего поля Excel, не ограничен видимой площадью клетки: как столбцы, так и строки обладают способностью услужливо растягиваться под напором пользовательской фантазии. Кроме того, «внутренности» каждой клетки могут иметь, куда большие размеры, чем видимая пользователю часть.

Рабочая книга – основной документ Microsoft Excel, состоящий из листов, максимальное количество которых составляет 255.

Создание новой книги.

Файл/ Создать.

Группировка листов.

Ряд операций (копирование, перемещение, удаление, параллельный ввод информации на нескольких листах) могут выполняться как для одного листа, так и для группы листов одновременно.

Создание группы из смежных листов.

Щелкнуть клавишей мыши на первом листе ярлыка.

Нажать и удерживать Shift.

При нажатой клавише Shift щелкнуть по последнему ярлыку листа.

Создание группы из несмежных листов.

Щелкнуть клавишей мыши на ярлыки первого листа и нажать Ctrl.

Прощелкать остальные ярлыки листа.

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

Для снятия выделенных листов нужно выполнить команду контекстного меню «Группировать листы».

Операции с листами.

Переименование: Формат/ Лист/ Переименовать (максимальная длина имени листа 31 символ).

Перемещение и копирование листа: Правка/ Переместить/ Скопировать.

Выделенные (сгруппированные) листы можно временно скрыть с помощью: Формат/ Лист/ Скрыть.

Для просмотра и открытия скрытых листов: Формат/ Лист/ Отобразить.

Примечание и индикатор.

Примечание и индикатор – маленький красный треугольник в верхнем правом углу ячейки.

Ввод примечания.

Выделить ячейки.

Команда Ставка/ Примечание.

Ввод текста.

Изменение примечания.

Вставка/ Изменить примечание.

Другие команды контекстного меню.

Удалить примечание.

Скрыть примечание.

Отобразить примечание.

Лист диаграммы.

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

Панель инструментов «Рисование» позволяет включить дополнительные элементы для диаграммы.

Форматирование ячеек.

Формат/ Ячейки.

Прогрессии.

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

Выделение блока смежных или несмежных ячеек.

Правка/ Заполнить/ Прогрессия или команда контекстного меню «Прогрессия».

Задание параметров прогрессии.

Типы адресации ячеек в Excel.

Ссылки – адреса ячеек.

Относительный тип адресации – способ, при котором адрес ячейки определяется как пересечение столбца и строки. В этом случае Excel заполняет расположение относительной текущей ячейки. Этот тип адресации применяется при заполнении больших таблиц формулами.

Абсолютная адресация. Используется при копировании формул, когда требуется сохранить ссылку на конкретную ячейку или область ячейки. При задании этой ссылки используется знак - $.

Смешанная адресация – адресация, при которой один параметр адреса изменяется, а другой нет.

Ошибки при вводе или редактировании формул.

##### - ширина ячеек недостаточна для отображения результата.

# ИМЯ ? – если программа не распознала имя функции или имя ячейки, которая используется в формуле.

# ЗНАЧ! – некорректное использование функции, например несоответствие данных установленному формату (вместо числа и даты в аргументе используется текст), либо если для функции, которая требует единственного значения аргумента, задан диапазон данных.

# ЧИСЛО! – возникают проблемы с представлением или использованием чисел, например функции с числовым аргументом используется аргумент нечислового формата.

# ССЫЛКА! – указывает на проблему с адресацией ячеек, участвующих в формуле, например, формула содержит ссылку на ячейку, которая уже удалена.

# ДЕЛ/0! – при попытке деления на 0 такая ситуация возникает из-за того что в качестве делителя используется ссылка на пустую ячейку или ячейку с нулем.

# ПУСТО! – в случае задания в ссылке пустого множества ячеек.

# Н/Д – сообщение говорит о наличии неопределенных данных.

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

Для отладки программы часто необходимо использовать команду: Сервис/ Зависимости/ Панель зависимостей.

Программа оптимизации в Excel.

С помощью данного оптимизатора можно находить множество значений переменных удовлетворяющих критериям оптимизации. Результаты работы оптимизатора могут быть оформлены в виде отчета. Можно полученные значения занести в таблицу.

Программа оптимизации в Excel вызывается командой Сервис /Поиск решения. В результате выполнения команды появляется диалоговое окно «Поиск решения».

В диалоговом окне «Поиск решения» поле «Установить целевую» служит для указания целевой ячейки, значение которой нужно максимизировать, минимизировать или задать равным какому-то числу.

В указанной целевой ячейке должна храниться формула.

В поле «Изменяя ячейки» указываются адреса ячеек значения, которых меняются до тех пор, пока не выполняются все ограничения и само условие оптимизации целевой функции. Изменяемые ячейки должны быть прямо или косвенно связаны с целевой ячейкой. В Excel допускается установка до 200 изменяемых ячеек.

Поле «Ограничения» вводим, изменяем, удаляем ограничения с помощью соответствующих кнопок, предварительно выделив нужное ограничение.

Команда «Выполнить» служит для запуска программы оптимизации.

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

Кнопка «Параметры» служит для настройки алгоритма и программы оптимизации.

Кнопка «Восстановить» служит для очистки полей диалогового окна, при этом принимаются параметры, принятые по умолчанию.

Ввод и редактор ограничений.

В окне «Поиск решения» щелкнуть по кнопке «Добавить», в результате появляется еще одно окно.

В поле «Ссылка на ячейку» вводится адрес на ячейку или диапазон ячеек на значение которых вводится ограничения.

В поле «Ограничения» вводится адрес ячейки или диапазон ячеек, в которых содержится или вычисляется ограничиваемое значение.

Кнопка «Добавить» нужна для добавления следующего ограничения.

При нажатии «ОК» возврат в окно «Поиск решения».

Настройка параметров алгоритма и программы.

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

В окне «Параметры поиска решения» задаются:

В поле «Максимальное время» устанавливаются ограничения на время время решения задачи. По умолчанию время решения задачи равно 100 секунд. Максимальное время – 32767 секунд.

В поле «Относительная погрешность» задается точность рения задачи. В поле должно содержаться число в интервале от 0 до 1. Чем меньше введенное число, тем выше точность результата. С другой стороны, чем выше точность результата, тем больше требуется времени на нахождение решения. По умолчанию относительная погрешность равна 0,000001.

Поле «Сходимость» необходимо для решения нелинейных задач.

Поле «Допустимое отклонение» используется для решения целочисленных задач.

Кнопки «Оценка», «Разность», «Метод поиска» служат для выбора алгоритмов оптимизации и их параметров.

Запуск и результаты работы оптимизатора.

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

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

Отчет «Устойчивость» содержит сведения о чувствительности решения к малым изменениям в формуле модели или в формулах ограничений.

Задача №1 (билет №23К).

Задача нахождения оптимальной ставки налога. Имитационное

моделирование.

Определение проблемы: Государство стремится увеличить налоги, чтобы наполнить бюджет для выполнения социальных и оборонных функций. Бизнес жалуется, что налоги высоки. Экономисты утверждают, что высокие налоги сдерживают развитие экономики, то есть накопление бюджета в будущем.

Проблема: Теория и практика не знают величину применяемой для всех налоговой ставки.

Задача: В определении и обосновании величины налоговой ставки.

Рабочая гипотеза: Поступление в бюджет за определенный период времени будут наибольшими не при максимальной налоговой ставке, а при каком-то значении, называемым оптимальным, то есть с увеличением налоговой ставки ожидаем, что поступления в бюджет сначала увеличатся, а затем уменьшатся.

Цель работы: Исследовать зависимость налоговых поступлений в бюджет за фиксированный период времени от величины налоговой ставки на прибыль.

Постановка задачи. Словесная модель.

Источником развития бизнеса налогового накопления бюджета является прибыль предприятия. Государство объявляет ставку налога на прибыль и получает от фирмы отчисления в бюджет. Фирмы обладают собственным капиталом, производят прибыль и по налоговой ставке отчисляют в бюджет.

Постналоговая прибыль (все, что осталось у фирмы от прибыли) полностью включается в собственный капитал фирмы, то есть в данной задаче считаем что никаких других отчислений от прибыли не производится, таким образом, вся прибыль распределяется на два потока:

в бюджет

в остаток в собственный капитал фирмы.

Математическая модель.

Исходные данные задачи:

1. Величина налоговой ставки Сn;

2. Рентабельность фирмы Rent;

3. Начальный капитал фирмы Кнач.;

4. Интервал моделирования t;

П – прибыль;

Sбюдж./год – отчисления в бюджет за год;

П = Кнач. * Rent;

Пналог = П * Сn;

Пост. = П * (1 – Сn) = П – Пналог;

Пкапитализ. = Пост.;

Ксальдо = Кнач. + Пост.;

Sбюдж./год = Пналог;

t

Sбюдж./период = ∑ Sбюдж./год;

1

В данной задаче эта математическая модель реализуется виде имитационной табличной модели в Excel, то есть создается таблица, где для фирмы с заданными значениями рентабельности, начального капитала и ставки налога, по формулам рассчитывается сумма отчислений в бюджет за заданный период времени t

Однофакторный имитационный анализ.

Время	год	0	1	2	3	4	5	6	7	8	9	10
Начальный капитал	млн. руб.		7010	12057,2	20738,38	35670,02	61352,44	105526,2	181505	312189	536964,5	923579
Ставка налога	%	10	10	10	10	10	10	10	10	10	10	10
Рентабельность	%	80	80	80	80	80	80	80	80	80	80	80
Прибыль	млн. руб.		5608	9645,76	16590,71	28536,02	49081,95	84420,95	145204	249751	429571,6	738863,2
Прибыль в налог	млн. руб.		560,8	964,576	1659,071	2853,602	4908,195	8442,095	14520,4	24975,1	42957,16	73886,32
Прибыль в остаток	млн. руб.		5047,2	8681,184	14931,64	25682,41	44173,75	75978,86	130684	224776	386614,5	664976,9
Капитализированная прибыль	млн. руб.		5047,2	8681,184	14931,64	25682,41	44173,75	75978,86	130684	224776	386614,5	664976,9
Капитал сальдо	млн. руб.	7010	12057,2	20738,38	35670,02	61352,44	105526,2	181505	312189	536965	923579	1588556
Поступление в бюджет за год	млн. руб.		560,8	964,576	1659,071	2853,602	4908,195	8442,095	14520,4	24975,1	42957,16	73886,32
Поступление за период	млн. руб.	0	560,8	1525,376	3184,447	6038,048	10946,24	19388,34	33908,7	58883,8	101841	175727,3

Отчисления в бюджет при разных налоговых ставках для фирм с начальным капиталом 7010 млн. руб.Период	1	2	3	4	5	6	7	8	9	10
Ставка налога	Сумма
10%	560,8	1525,38	3184,45	6038,048	10946,24	19388,34	33908,74	58883,84	101841	175727
20%	1121,6	2961,02	5977,68	10924,99	19038,59	32344,89	54167,22	89955,84	148649	244906
30%	1682,4	4306,94	8401,23	14788,32	24752,18	40295,81	64543,86	102370,8	161381	253437
40%	2243,2	5563,14	10476,6	17748,63	28511,17	44439,73	68014,01	102903,9	154541	230964
50%	2804	6729,6	12225,4	19919,62	30691,46	45772,05	66884,87	96442,81	137824	195758
60%	3364,8	7806,34	13669,2	21408,1	31623,49	45107,8	62907,1	86402,17	117416	158353
70%	3925,6	8793,34	14829,3	22313,99	31594,95	43103,33	57373,73	75069,03	97011,2	124219

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

Отчисления в бюджет при разных налоговых ставках для фирм с начальным капиталом 8010 млн. руб.

Период	1	2	3	4	5	6	7	8	9	10
Ставка налога	Сумма
10%	640,8	1743	3638,72	6899,4	12507,76	22154,15	38745,94	67283,8	116369	200795
20%	1281,6	3383,4	6830,42	12483,5	21754,51	36959	61894,35	102788	169854	279843
30%	1922,4	4921,3	9599,7	16897,9	28283,17	46044,14	73751,26	116974	184402	289590
40%	2563,2	6356,7	11971,2	20280,5	32578,39	50779,21	77716,43	117584	176587	263912
50%	3204	7689,6	13969,4	22761,2	35069,7	52301,58	76426,22	110201	157485	223683
60%	3844,8	8919,9	15619,1	24462	36134,68	51542,58	71881,01	98727,7	134165	180943
70%	4485,6	10048	16944,8	25497,2	36102,07	49252,17	65558,29	85777,9	110850	141940

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

Отчисления в бюджет при разных налоговых ставках для фирм с начальным капиталом 9010 млн. руб.

Период	1	2	3	4	5	6	7	8	9	10
Ставка налога	Сумма
10%	720,8	1960,6	4092,99	7760,74	14069,3	24920	43583,1	75683,8	130897	225863
20%	1441,6	3805,8	7683,15	14042	24470,4	41573,1	69621,5	115621	191060	314780
30%	2162,4	5535,7	10798,2	19007,5	31814,1	51792,5	82958,7	131578	207424	325744
40%	2883,2	7150,3	13465,7	22812,4	36645,6	57118,7	87418,9	132263	198633	296859
50%	3604	8649,6	15713,4	25602,8	39447,9	58831,1	85967,6	123959	177146	251608
60%	4324,8	10034	17569,1	27516	40645,9	57977,4	80854,9	111053	150915	203533
70%	5045,6	11302	19060,3	28680,3	40609,2	55401	73742,8	96486,7	124689	159660

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

Отчисления в бюджет при разных налоговых ставках для фирм с начальным капиталом 10010 млн. руб.

Период	1	2	3	4	5	6	7	8	9	10
Ставка налога	Сумма
10%	800,8	2178,2	4547,26	8622,09	15630,8	27685,8	48420,3	84083,8	145425	250932
20%	1601,6	4228,2	8535,89	15600,5	27186,3	46187,2	77348,6	128453	212265	349716
30%	2402,4	6150,1	11996,6	21117,1	35345,1	57540,8	92166,1	146181	230445	361897
40%	3203,2	7943,9	14960,2	25344,3	40712,8	63458,2	97121,3	146943	220678	329807
50%	4004	9609,6	17457,4	28444,4	43826,2	65360,7	95508,9	137716	196807	279534
60%	4804,8	11147	19519	30569,9	45157,1	64412,1	89828,8	123379	167665	226122
70%	5605,6	12557	21175,7	31863,5	45116,3	61549,8	81927,4	107196	138528	177380

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

Отчисления в бюджет при разных налоговых ставках для фирм с начальным капиталом 10210 млн. руб.

Период	1	2	3	4	5	6	7	8	9	10
Ставка налога	Сумма
10%	816,8	2221,7	4638,12	8794,36	15943,1	28238,9	49387,8	85763,8	148330	255945
20%	1633,6	4312,7	8706,43	15912,2	27729,5	47110	78894	131020	216506	356704
30%	2450,4	6273	12236,3	21539,1	36051,3	58690,5	94007,5	149102	235050	369128
40%	3267,2	8102,7	15259,1	25850,7	41526,3	64726,1	99061,8	149879	225088	336397
50%	4084	9801,6	17806,2	29012,7	44701,8	66666,6	97417,2	140468	200739	285119
60%	4900,8	11370	19909	31180,7	46059,3	65699,1	91623,6	125844	171015	230640
70%	5717,6	12807	21598,8	32500,1	46017,7	62779,6	83564,3	109337	141296	180925

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

Отчисления в бюджет при разных налоговых ставках для фирм с начальным капиталом 10310 млн. руб.

Период	1	2	3	4	5	6	7	8	9	10
Ставка налога	Сумма
10%	824,8	2243,5	4683,54	8880,5	16099,3	28515,5	49871,5	86603,8	149783	258452
20%	1649,6	4354,9	8791,71	16068	28001,1	47571,4	79666,8	132303	218627	360197
30%	2474,4	6334,5	12356,2	21750	36404,4	59265,3	94928,3	150563	237352	372743
40%	3299,2	8182	15408,6	26103,9	41933	65360	100032	151347	227292	339692
50%	4124	9897,6	17980,6	29296,9	45139,7	67319,5	98371,3	141844	202705	287912
60%	4948,8	11481	20104	31486,1	46510,4	66342,6	92521	127077	172690	232899
70%	5773,6	12933	21810,4	32818,4	46468,5	63394,5	84382,8	110408	142680	182697

Вывод: За период 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%

Решение в Mathcad:

Cn – ставка налога;

Kn7010 – начальный капитал, равный 7010 млн. руб.;

Kn8010 – начальный капитал, равный 8010 млн. руб.;

Kn9010 – начальный капитал, равный 9010 млн. руб.;

Kn10010 – начальный капитал, равный 100010 млн. руб.;

Kn10210 – начальный капитал, равный 10210 млн. руб.;

Kn10310 – начальный капитал, равный 10310 млн. руб.;

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

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

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

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

Задача №2 (билет № 47 II).

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

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

Цель работы.

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

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

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

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

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

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

Туристическая фирма в летний сезон обслуживает в среднем 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

2. Нахождение оптимального решения в системе Mathcad.

Введем обозначения:

Суда первого типа – s.

Суда второго типа – n.

Прибыль от реализации всей продукции – P.

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

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

Прибыль составляет 140000000 руб. Совпадает с результатом решения оптимизатора Excel.

Построение таблицы полученных решений.

№	План найденный вручную	План, найденный программным путем
	1	Excel	Mathcad
Прибыль, руб.	70000000	140000000	140000000

Вывод:

Наибольшая прибыль от реализации продукции получается при решении с помощью программы оптимизатора в Excel и в системе Mathcad.

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

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

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

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

Задача №3 (билет №59 ТВ).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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. Товары могут доставляться с любого завода на любой склад, при условии:

Минимальное количество поставляемого груза с завода А1 и завода А2 к каждому потребителю составляет 160 тонн.

С завода А2 на склад В1 нужно привезти не более 200 тонн груза, а с завода А3 на склад В1 – не менее 100 тонн.

Необходимо учесть:

- возможности поставщиков;

- максимально удовлетворить заявки склада.

Дано:

1. Потребности склада:

В1 – 500;

В2 – 600;

В3 – 400.

2. Мощность заводов:

А1 – 500;

А2 – 600;

А3 – 500.

3. Стоимость перевозки единицы груза:

Потребители	В1	В2	В3
Поставщики
А1	9	6	2
А2	4	15	15
А3	17	7	8

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

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

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

Стоимость перевозки единицы груза от завода к складу.
Потребитель
Поставщик	В1	В2	В3
А1	9	6	2
А2	4	15	15
А3	17	7	8
План перевозок от завода к складу
Потребитель				План поставок	Мощность завода
Поставщик	В1	В2	В3
А1	180	160	160	500	500
А2	160	180	160	500	600
А3	160	260	80	500	500
Поставлено по складу	500	600	400
Потребность складов	500	600	400
Стоимость перевозок по каждому складу				Трансп. расходы
	4980	5480	3360	13820
Стоимость перевозки единицы груза от завода к складу.
Потребитель
Поставщик	В1	В2	В3
А1	9	6	2
А2	4	15	15
А3	17	7	8
План перевозок от завода к складу
Потребитель				План поставок	Мощность завода
Поставщик	В1	В2	В3
А1	180	160	160	500	500
А2	180	160	160	500	600
А3	140	280	80	500	500
Поставлено по складу	500	600	400
Потребность складов	500	600	400
Стоимость перевозок по каждому складу				Трансп. расходы
	4720	5320	3360	13400
Стоимость перевозки единицы груза от завода к складу.
Потребитель
Поставщик	В1	В2	В3
А1	9	6	2
А2	4	15	15
А3	17	7	8
План перевозок от завода к складу
Потребитель				План поставок	Мощность завода
Поставщик	В1	В2	В3
А1	180	160	160	500	500
А2	160	200	160	520	600
А3	160	140	80	380	500
Поставлено по складу	500	500	400
Потребность складов	500	600	400
Стоимость перевозок по каждому складу				Трансп. расходы
	4980	4940	3360	13280
Стоимость перевозки единицы груза от завода к складу.
Потребитель
Поставщик	В1	В2	В3
А1	9	6	2
А2	4	15	15
А3	17	7	8
План перевозок от завода к складу
Потребитель				План поставок	Мощность завода
Поставщик	В1	В2	В3
А1	160	160	160	480	500
А2	160	160	160	480	600
А3	180	270	80	530	500
Поставлено по складу	500	590	400
Потребность складов	500	600	400
Стоимость перевозок по каждому складу				Трансп. расходы
	5140	5250	3360	13750
Стоимость перевозки единицы груза от завода к складу.
Потребитель
Поставщик	В1	В2	В3
А1	9	6	2
А2	4	15	15
А3	17	7	8
План перевозок от завода к складу
Потребитель				План поставок	Мощность завода
Поставщик	В1	В2	В3
А1	170	170	160	500	500
А2	170	170	170	510	600
А3	160	260	70	490	500
Поставлено по складу	500	600	400
Потребность складов	500	600	400
Стоимость перевозок по каждому складу				Трансп. расходы
	4930	5390	3430	13750

Вывод:

Наилучшим планом из составленных вручную с помощью программы Excel является план №3, т.к. при данном плане затраты на перевозку минимальные и составляют 13280 руб.

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

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

Стоимость перевозки единицы груза от завода к складу.
Потребитель
Поставщик	В1	В2	В3
А1	9	6	2
А2	4	15	15
А3	17	7	8
План перевозок от завода к складу
Потребитель				План поставок	Мощность завода
Поставщик	В1	В2	В3
А1	180	160	160	500	500
А2	200	160	160	520	600
А3	120	280	80	480	500
Поставлено по складу	500	600	400
Потребность складов	500	600	400
Стоимость перевозок по каждому складу				Трансп. расходы
	4460	5320	3360	13140

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

С завода А1 в склад В1 необходимо поставить 180 тонн груза, на В2 – 160 тонн, на В3 – 160 тонн. С завода А2 в склад В1 – 200 тонн, в В2 – 160 тонн, в В3 – 160 тонн. С завода А3 в склад В1 – 120 тонн, в В2 – 280 тонн, в В3 – 80 тонн.

Затраты на перевозку составляют 13140 руб.

Отчет.

Целевая ячейка (Минимум)
	Ячейка	Имя	Исходное значение	Результат
	$E$16	Трансп. расходы	0	13140
Изменяемые ячейки
	Ячейка	Имя	Исходное значение	Результат
	$B$10	А1 В1	0	180
	$C$10	А1 В2	0	160
	$D$10	А1 В3	0	160
	$B$11	А2 В1	0	200
	$C$11	А2 В2	0	160
	$D$11	А2 В3	0	160
	$B$12	А3 В1	0	120
	$C$12	А3 В2	0	280
	$D$12	А3 В3	0	80
Ограничения
	Ячейка	Имя	Значение	Формула	Статус	Разница
	$E$10	А1 План поставок	500	$E$10<=$F$10	связанное	0
	$E$11	А2 План поставок	520	$E$11<=$F$11	не связан.	80
	$E$12	А3 План поставок	480	$E$12<=$F$12	не связан.	20
	$B$13	Поставлено по складу В1	500	$B$13=$B$14	не связан.	0
	$C$13	Поставлено по складу В2	600	$C$13=$C$14	не связан.	0
	$D$13	Поставлено по складу В3	400	$D$13=$D$14	не связан.	0
	$B$10	А1 В1	180	$B$10>=0	не связан.	180
	$C$10	А1 В2	160	$C$10>=0	не связан.	160
	$D$10	А1 В3	160	$D$10>=0	не связан.	160
	$B$11	А2 В1	200	$B$11>=0	не связан.	200
	$C$11	А2 В2	160	$C$11>=0	не связан.	160
	$D$11	А2 В3	160	$D$11>=0	не связан.	160
	$B$12	А3 В1	120	$B$12>=0	не связан.	120
	$C$12	А3 В2	280	$C$12>=0	не связан.	280
	$D$12	А3 В3	80	$D$12>=0	не связан.	80
	$B$11	А2 В1	200	$B$11<=200	связанное	0
	$B$12	А3 В1	120	$B$12>=100	не связан.	20
	$B$10	А1 В1	180	$B$10>=160	не связан.	20
	$C$10	А1 В2	160	$C$10>=160	связанное	0
	$D$10	А1 В3	160	$D$10>=160	связанное	0
	$B$11	А2 В1	200	$B$11>=160	не связан.	40
	$C$11	А2 В2	160	$C$11>=160	связанное	0
	$D$11	А2 В3	160	$D$11>=160	связанное	0

2. Нахождение оптимального решения в системе Mathcad.

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

С завода А1 в склад В1 необходимо поставить 180 тонн груза, на В2 – 160 тонн, на В3 – 160 тонн. С завода А2 в склад В1 – 200 тонн, в В2 – 160 тонн, в В3 – 160 тонн. С завода А3 в склад В1 – 120 тонн, в В2 – 280 тонн, в В3 – 80 тонн.

Затраты на перевозку составляют 13140 руб.

Построение таблицы полученных решений.

План, найденный вручную						План, найденный программным путем
№	1	2	3	4	5	Excel	Mathcad
Трансп. расходы	13820	13400	13280	13750	13750	13140	13140

Вывод:

Наименьшие затраты на перевозку грузов получается при решении с помощью программы оптимизатора в Excel и в системе Mathcad.

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

Из графика видно, что наилучшим планом, составленным вручную является план №4 с транспортными расходами 13280 руб. Оптимальным планом является план полученный с помощью оптимизаторов в Excel и Mathcad. При этом транспортные расходы составляют 13140 руб.

По оптимальному плану мощность завода А1 задействована не полностью, а мощности заводов А2 и А3 полностью.

При долгосрочных планах можно предложить на будущее увеличить мощности заводов А1, а уменьшить мощность в заводах А2 и А3.