математический оптимизация excel экономический

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

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

Общая постановка задачи

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

По технологическим требованиям предприятие с использованием этих ресурсов может изготавливать известное (p) количество видов продукции. Для единицы продукции каждого вида известна оптовая цена
(s₁, s₂, s₃, s_4, s₅, …, s_p). Для единицы продукции i-го вида известны удельные расходы ресурса j-го типа - a_ij.

Если предприятие не использует ресурс j-го типа полностью, то оно вынуждено потратить r_j денежных единиц на оплату хранения единицы ресурса j-го типа.

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

Вариант 1

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

Вариант 2

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

Вариант 3

Исходная постановка задачи соответствует варианту 1. Требуется найти такой оптимальный план по номенклатуре и объемам выпускаемых видов продукции, при котором прибыль предприятия будет равна заданной величине 1500.

Вариант 4

Исходная постановка задачи соответствует варианту 2. Требуется найти такой оптимальный план по номенклатуре и объемам выпускаемых видов продукции, при котором прибыль предприятия будет равна заданной величине 1500.

Исходные данные для варианта k=12

Сформируем исходные данные, задав конкретные значения параметрам исходной задачи, и представим их в табличном виде (Таблица 1).

Таблица 1. Исходные данные

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

1.3 Решение

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

На отдельном листе выполняется ввод исходных данных.

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

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

Рисунок 1 - Результаты задачи линейного программирования, вариант 1

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

В данном варианте отчет состоит из трех таблиц. Первые две таблицы показывают целевую функцию и переменные, третья таблица, названная Ограничения в столбце Значения показывает ресурсы, соответствующие каждому из ограничений, а в столбце Разница - разность между имеющимися и почти израсходованными ресурсами. В данной задаче разность не равна нулю только с третьим, десятым и тринадцатым ресурсом, первый равен 161,6, второй равен 65,6, четвертый равен 8, пятый равен 142,4, шестой равен 32, седьмой равен 56, восьмой равен 94,4, девятый равен 132,8, одиннадцатый равен 112, двенадцатый равен 320,8 равна 94,2, что означает, что ресурсы израсходованы не полностью (Рисунок 2).

Рисунок 2 - Отчет по результатам 1

Отчет по устойчивости дает нам сведения о допустимом увеличении / уменьшении коэффициентов целевой функции, при котором ее значение не изменится.

В отчете таблица Изменяемые ячейки фактически содержит результаты анализа на устойчивость структуры оптимального решения при изменении коэффициентов целевой функции. Значения в столбцах Допустимое увеличение и Допустимое уменьшение показывают величину возможного отклонения принятого значения коэффициента в столбце Целевой коэффициент.

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

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

Значения в столбцах Допустимое увеличение и Допустимое уменьшение показывают величину возможного отклонения принятого значения ресурса в столбце Ограничение правая часть.

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

В данной задаче Отчет по устойчивости показывает в итоге, какое количество ресурсов израсходовано при оптимальном решении. В таблице Ячейки переменных таким итогом служит количество ресурсов, которые следует закупать предприятию, чтобы получить при исходных данных максимальную прибыль. В данном случае, товар Х1 в количестве 6 единиц, товар Х3 в количестве 2,7 единиц, товар Х10 в количестве 0,8 единиц. Таблица с ограничениями показывает, сколько ресурсов израсходовано для достижения оптимального решения (Рисунок 3).

Рисунок 3 - Отчет по устойчивости 1

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

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

В данной задаче вторая и третья часть таблицы Целевое не совпадают, например, значения целевой функции товара Х1 минимальное равно -7824,64, а максимальное -4378,624. Вторая таблица показывает минимум, а третья - максимум. В этой задаче верхние значения не совпадают с оптимальным решением. Значения функции с целочисленным значением не совпадают со значениями функции с оптимальным решением (Рисунок 4).

Рисунок 4 - Отчет о пределах 1

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

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

Первый вариант необходимо скопировать с зажатой клавишей Ctrl.

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

Рисунок 5 - Результаты задачи линейного программирования, вариант 2

Отчет по результатам

В данном варианте отчет состоит из трех таблиц. Первые две таблицы показывают целевую функцию и переменные, третья таблица, названная Ограничения в столбце Значения показывает ресурсы, соответствующие каждому из ограничений, а в столбце Разница - разность между имеющимися и почти израсходованными ресурсами. В данной задаче разность нигде не равна нулю, первый равен 336, второй равен 290, четвертый равен 8, пятый равен 142,4, шестой равен 32, седьмой равен 56, восьмой равен 94,4, девятый равен 132,8, одиннадцатый равен 112, двенадцатый равен 320,8 равна 94,2, что означает, что ресурсы израсходованы не полностью (Рисунок 6).

Рисунок 6 - Отчет по результатам 2

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

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

Первый вариант необходимо скопировать с зажатой клавишей Ctrl.

Для решения задачи необходимо знать искомое значение прибыли. В данной задаче искомым значением является число 1500. Значение прибыли, равное исходному значению, получается вследствие уменьшения объема ресурсов (правой части), стоимости использования и стоимости хранения (Рисунок 7).

Рисунок 7 - Результаты задачи линейного программирования, вариант 3

Отчет по результатам

В данном варианте отчет состоит из трех таблиц. Первые две таблицы показывают целевую функцию и переменные, третья таблица, названная Ограничения в столбце Значения показывает ресурсы, соответствующие каждому из ограничений, а в столбце Разница - разность между имеющимися и почти израсходованными ресурсами. В данной задаче разность не равна нулю только с третьим, десятым и тринадцатым ресурсом, первый равен 161,6, второй равен 65,6, четвертый равен 8, пятый равен 142,4, шестой равен 32, седьмой равен 56, восьмой равен 94,4, девятый равен 132,8, одиннадцатый равен 112, двенадцатый равен 320,8 равна 94,2, что означает, что ресурсы израсходованы не полностью (Рисунок 8).

Рисунок 8 - Отчет по результатам 3

Отчет по устойчивости

В данной задаче Отчет по устойчивости показывает в итоге, какое количество ресурсов израсходовано при оптимальном решении. В таблице Ячейки переменных таким итогом служит количество ресурсов, которые следует закупать предприятию, чтобы получить при исходных данных максимальную прибыль. В данном случае, товар Х1 в количестве 2 единиц, товар Х3 в количестве 2 единиц, товар Х5 в количестве 4 единиц, товар Х12 в количестве 0,2 единиц. Таблица с ограничениями показывает, сколько ресурсов израсходовано для достижения оптимального решения (Рисунок 9).

Рисунок 9 - Отчет по устойчивости 3

Отчет по пределам

В данной задаче вторая и третья часть таблицы Целевое не совпадают, например, значения целевой функции товара Х1 минимальное равно -1641, а максимальное 1504. Вторая таблица показывает минимум, а третья - максимум. В этой задаче верхние значения не совпадают с оптимальным решением. Значения функции с целочисленным значением не совпадают со значениями функции с оптимальным решением (Рисунок 10).

Рисунок 10 - Отчет о пределах 3

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

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

Третий вариант необходимо скопировать с зажатой клавишей Ctrl.

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

Отчет по результатам

В данном варианте отчет состоит из трех таблиц. Первые две таблицы показывают целевую функцию и переменные, третья таблица, названная Ограничения в столбце Значения показывает ресурсы, соответствующие каждому из ограничений, а в столбце Разница - разность между имеющимися и почти израсходованными ресурсами. В данной задаче разность нигде не равна нулю, первый равен 336, второй равен 290, четвертый равен 8, пятый равен 142,4, шестой равен 32, седьмой равен 56, восьмой равен 94,4, девятый равен 132,8, одиннадцатый равен 112, двенадцатый равен 320,8 равна 94,2, что означает, что ресурсы израсходованы не полностью.

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

2. Оценка экономической эффективности от оптимизационных решений

Прибыль от реализации продукции (работ, услуг) составляет, как правило, наибольшую часть всей балансовой прибыли предприятия. Определяют ее как разность между выручкой от реализации продукции по оптовым ценам предприятия (без НДС) и ее полной себестоимостью. Если себестоимость продукции превышает ее стоимость в оптовых ценах, то результатом производственной деятельности предприятия будет убыток. Расчет прибыли от реализации продукции может быть представлен в виде формулы:

(4)

где Z - валовой доход (выручка) от реализации продукции (работ, услуг) в действующих оптовых ценах минус затраты на производство и реализацию продукции (полная себестоимость продукции);

S_n - сумма налога на добавленную стоимость (НДС).

P_r1 = -4754,94 + 4754,94*18% = -3899,05

P_r2 = 1983,74 - 1983,74*18% = -1626,67

P_r3 = 1504,08 - 1504,08*18% = 1233,34

P_r4 = -9780,51 + 9780,51*18% = -8020,02

Рассчитаем рентабельность R деятельности рассматриваемого предприятия по следующей формуле:

(5)

(6)

Где P_r - прибыль от реализации продукции;

Q_i - затраты на производство реализованной продукции.

Рентабельность деятельности предприятия после оптимизации:

R₁ = -4041,7/14111,104 = -27,6%

R₂ = -1686,18/13183,744 = -12,3%

R₃ = 1278,47/8741,82 = 14,1%

R₄ = -8313,44/9780,512 = -82%

Заключение

В результате проделанной работы был найден оптимальный план - вариант 3, при котором прибыль предприятия будет максимальной. Следует выпускать товар Х₁ в количестве 2,9 единиц, товар Х₃ в количестве 2,5 единиц, товар Х₅ в количестве 4 единиц, Х12 в количестве 0,2. При установленной стоимости реализации размер максимальной прибыли составит 1504 р.

По результатам расчетов экономической эффективности рентабельным является вариант 3, расчет рентабельности показал 14,1%.

Список используемой литературы

1) Гай Харт-Дэвис, Microsoft Office Excel 2003, изд.:ACT, 2005, 428 c.

2) Б. Куритский, Поиск оптимальных решений средствами Excel 7.0, изд.: BHV - Санкт - Петербург, 1997, 384 с.

3) М.А. Кораблин, Информатика поиска управленческих решений, изд.: Солон-Пресс, 2003, 192 с.

4) В.А. Грабуров. Информационные технологии для менеджеров. - М.: Финансы и статистика, 2001,386 с. (Прикладные информационные технологии).

5) Г.А. Титоренко. Учебное пособие для вузов. Информационные технологии управления. - М: Юнити-Дана, 2002, 280 c.

Размещено на Allbest.ru