Работа в MS Excel

Аннотация

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

              Работа состоит из 17 страниц; имеется  13 рисунков;  1 таблица.

Содержание

Введение              2

1. Бизнес-план 1              2

1.1. Составлений бизнес плана-1 сельхоз предприятий « Василек»              5

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

1.1.2. Математическая постановка задачи              5

1.1.3. Составление электронной формы              6

1.1.4. Внесение формул в электронную форму              6

1.1.5. Работа в программе поиск решения              6

1.1.6. Автоматические отчеты              8

1.2. Заключение              9

2 Бизнес-план 2              9

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

2.2. Математическая постановка задачи              10

2.3. Составление  электронной формы для решения задачи              11

2.4.Ввод формул в электронную форму              11

2.5. Работа в программе Поиск решения              12

2.6. Результаты расчета Бизнес- план 2              13

2.7. Составление авто отчетов              13

2.7.1. Результаты              13

2.7.2.Устоичивость              13

2.7.3. Пределы              14

2.8. Заключения по Бизнес-плану 2              14

3.Заключение              14

Введение

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

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

Проблемы оптимизации присутствуют в самых различных процессах производства:

1.              поставка сырья;

2.              оптимальный выпуск продукции;

3.              оптимальное управление запасами ;

4.              оптимальное распределение ресурсов;

5.              планирования инвестиций;

6.              оптимальный рацион (смесь, сплав);

7.              оптимальная замена оборудования и т. д

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

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

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

1. Линейная оптимизация

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

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

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

Каждая задача оптимизации обязательно должна иметь три компоненты:

1.              неизвестные (что ищем, то есть, план);

2.              ограничение на неизвестные (область поиска);

3.              целевая функция (цель, для которой ищем экстремум).

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

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

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

Постановка задачи оптимизации предполагает существование конкурирующих свойств процесса, например:

- количество продукции - расход сырья

- количество продукции - качество продукции

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

При постановке задачи оптимизации необходимо:

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

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

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

4. Учет ограничений.

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

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

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

1. Бизнес-план 1

              В курсовой работе рассматривается хозяйство «Василек», занимающееся выращиванием и переработкой с/х продукции.

1.1. Составлений бизнес плана-1 сельхоз предприятий «Васи-лек»

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

В хозяйстве выращиваются культуры  А и В. На 1 га площади посева расходуется а1, а2, а3 кг и в1, в2, в3 кг соответственно минеральных удобрений трех наименований для культур А и В . Хозяйство, планирующее производство культур А и В, располагают запасами с1, с2, с3 кг минеральных удобрений каждого наименования. Прибыль по  культурам А и В составляют α и β денежных единиц соответственно. Определить оптимальный плана посева культур А и В, дающий максимальную прибыль хозяйству при полной реализаций его ресурсов.

Данные по хозяйству:

a1=20

a2=16

a3=20

в1=30

в2=10

в3=1

с1=1120

с2=700

с3=1000

α=15

β=20

1.1.2. Математическая постановка задачи

              Обозначим через Х1 и Х2 соответственно площади занятые под культуры А и В.

Прибыль вычисляется по формуле:

              При ограничениях:

При заданных значениях имеем:

– целевая функция

Ограничения:

1.1.3. Составление электронной формы

Рисунок 1. Электронная форма для расчета бизнес-плана 1

1.1.4. Внесение формул в электронную форму

Рисунок 2. Электронная форма с внесенными формулами

1.1.5. Работа в программе поиск решения

Меню Сервис → Поиск решения→ Окно Поиск решения:

1) Устанавливаем целевую функцию

2) Устанавливаем ячейки с переменными

3) Устанавливаем ограничения

Получаем:

Рисунок 3. Окно Поиск решения с введенными данными

→ Нажимаем на кнопку параметры, получаем

Рисунок 4. Окно Параметры Поиска решения

→ Ок в окне параметра Поиска решения  →  Выполнить в Окне Поиск решения.

Получаем:

Рисунок 5. Результаты расчета бизнес-плана1

1.1.6. Автоматические отчеты

В Окне результаты Поиска решения нажимаем на Результаты→ Получаем:

Рисунок 6. Автоматические результаты работы программы Поиск решения

В Окне результаты Поиска решения нажимаем на Устойчивость→ Полу-чаем:

Рисунок 7. Автоматические устойчивость работы программы Поиск решения

В Окне результаты Поиска решения нажимаем на пределы→ Получаем:

Рисунок 8 Автоматические Пределы работы программы Поиск решения

1.2. Заключение

              Из рисунка 5 и рисунков 6-8 видно что:

1)Максимальная прибыль хозяйства будет составлять  749 денежных единиц;

2) Количество гектаров под культурой А и В должно быть:

              Под культуру А-35га

              Под культуру В-14га

2 Бизнес-план 2

              В хозяйстве имеется три перерабатывающих предприятия, выпускающих два вида продукции А и В.

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

В производстве продукций А и В принимают участие три предприятия. При этом на изготовление единицы  изделия вида А первое, второе, третье предприятия затрачивают а1,а2 и а3 часов соответственно. На изготовление единицы изделия вида В эти же предприятия затрачивают в1, в2 и в3 часов соответственно. На производство всех изделий первое предприятие может затратить не боде t1 часов, второе предприятие- не боде t 2 часов третье предприятие не более чем t3  часов. От реализаций единицы готовой продукций вида А прибыль составляет β рублей. Определить единицы готовой продукций вида А прибыль составляет α рублей. Определить максимальную прибыль от всей производственной продукций.

Данные по хозяйству:

a1=8

a2=7

a3=7

b1=12

b2=9

b3=5

t1=612

t2=492

t3=562

α=11

β=9

2.2. Математическая постановка задачи

              Обозначим через ХА1, ХА2, ХА3 – количество продукций вида А изготовляемое на предприятиях 1, 2, 3 соответственно.

              Обозначим через ХВ1, ХВ2, ХВ3 – количество продукций вида А изготовляемое на предприятиях 1, 2, 3 соответственно.

Максимальная прибыль равна: 

Ограничениях:

С учетом данных по хозяйству  задача принимает вид:

Максимальная прибыль равна: 

Ограничениях:

2.3. Составление  электронной формы для решения задачи

Рисунок 9 Электронная форма для Бизнес- план 2

2.4.Ввод формул в электронную форму

Рисунок 10. Электронная форма сведенными формами

2.5. Работа в программе Поиск решения

Меню Сервис → Поиск решения→ Окно Поиск решения:

1) Устанавливаем целевую функцию

2) Устанавливаем ячейки с переменными

3) Устанавливаем ограничения

Получаем:

Рисунок 11. Окно Поиск решения Бизнес-план 2

Рисунок 12. Окно Параметры Поиска решения

2.6. Результаты расчета Бизнес- план 2

Рисунок 13. Результаты расчета Бизнес-план2

2.7. Составление авто отчетов

2.7.1. Результаты

Таблица 1. Авторезультаты по решению задачи для Бизнес-план2

2.7.2.Устоичивость

Отчет по устойчивости,  по целочисленным не применим

2.7.3. Пределы

Отчет по пределам по целочисленным не применим

2.8. Заключения по Бизнес-плану 2

Перерабатывающие заводы в хозяйстве «Василек» имеет максималь-ную прибыль равную 2616. При выпуске продукций:

Вида А:

На первом предприятий-76

На втором предприятий-70

На третьем предприятий-1

Вида В:

На первом предприятий– не выпускать

На втором предприятий–не выпускать