Построение математической модели и решение задачи в MS Excel - Информационные технологии в маркетинге

Вводная. Тракторный завод производит два вида продукции: трактора марки МТЗ-80 и Т-100. Мощность цеха № 1 (по сборке тракторов марки МТЗ-80) составляет 400 штук за плановый период, мощность цеха № 2 (по сборке тракторов марки Т-100) - 300 штук за плановый период. Механические цеха завода оснащены взаимозаменяемым оборудованием, и одна группа цехов может производить либо детали для 600 тракторов марки МТЗ-80, либо детали для 400 тракторов марки Т-100, либо любую допустимую их комбинацию. Другая группа механических цехов может выпускать детали либо для 480 тракторов марки МТЗ-80, либо для 640 тракторов марки Т-100, либо любую допустимую их комбинацию. Прибыль от продажи одного трактора марки МТЗ-80 cocтавляет 2 тыс. ден. ед., а от продажи одного трактора марки Т-100 - 4 тыс. ден. ед. найти оптимальный план производства тракторов различных марок, который обеспечит тракторному заводу наибольшую сумму прибыли.

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

Маркетинговый информация автоматизация управленческий

На листе "Вводные" вводим условия, содержащиеся в задаче

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

Для заполнения ячеек используем ссылки с листа "Вводные", в ячейке F9 с использованием функции "СУММПРОИЗВ" реализуется запись суммы произведений значений переменных.

Получаем следующую таблицу:

Прибыль равна 0 т. к. ячейки D8 и Е8 равны 0.

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

Для этого используем, включенную ранее надстройку "Поиск решения"

Поскольку в качестве критерия оптимизации нами выбрана максимальная прибыль, в поле "Установить целевую ячейку" вводим ссылку на ячейку, содержащую формулу расчета себестоимости F9. Чтобы максимизировать данное значение путем изменения значений влияющих ячеек (D8, E8), устанавливаем переключатель в положение максимальному значению.

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

Вводим ограничения:

Получаем следующий вид окна "Поиск решения"

Нажимаем "Выполнить". Появляется окно "Результаты поиска решения". Выбираем "Сохранить найденное решение", нажимаем "Ок"

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

Похожие статьи




Построение математической модели и решение задачи в MS Excel - Информационные технологии в маркетинге

Предыдущая | Следующая