В задачах экономического анализа

Использования процедуры

Поиск решения» В Excel

в задачах экономического анализа

Несмотря на многообразие задач, встречающихся на каждом шагу и требующих оптимального принятия решения Excel предлагает единый мощный инструмент решения таких задач – Поиск решения. Программа Поиск решений (в оригинале Excel Solver) – дополнительная надстройка табличного процессора MS Excel, которая предназначена для решения определенных систем уравнений, линейных и нелинейных задач оптимизации.

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

В Excel для решения задач оптимизации используется команда СЕРВИС Þ ПОИСК РЕШЕНИЯ, реализующая методы линейного, дискретного (целочисленного) и нелинейного программирования.

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

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

После создания таблицы, содержащей исходные данные, целевую функцию и функции ограничений, можно активизировать программу СЕРВИСÞПОИСК РЕШЕНИЯ, которая позволит по заданному результату целевой функции (max., min., конкретное значение) найти значение переменных (параметров), удовлетворяющих значению целевой функции и введенным ограничениям.

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

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

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

Ø количество неизвестных (decision variable) – 200;

Ø количество формульных ограничений (explicit constraint) на неизвестные – 100;

Ø количество предельных условий (simple constraint) на неизвестные – 400.

По умолчанию в Excel надстройка Поиск решения отключена. Чтобы активизировать ее в Excel 2007:

Ø щелкните значок Кнопка Microsoft Office,

Ø щелкните Параметры Excel

Ø выберите категорию Надстройки.

Ø В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти.

Ø В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК.

В Excel 2003 и ниже:

Ø выберите команду Сервис/Надстройки,

Содержание задачи

Предприятие, обладая ограниченными ресурсами:

· трудовые ресурсы - 16

· сырье - 110

· финансы - 100

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

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

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

Выпишем математическую модель задачи:

Целевая функция (ЦФ) – определяет максимально возможную прибыль при оптимальном плане производства продукции.

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

C₁*X₁+ C₂*X₂ + C₃*X₃ + C₄*X₄ à max,

где С_i – прибыль от выпуска ед. продукции вида i;