Оптимизация решений экономических задач с помощью Excel

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

1. ПОДБОР ПАРАМЕТРА. Надо научиться применять команду Подбор параметра для определения желаемых значений при решении различных задач. Подбор параметра - простейший метод нахождения оптимального желаемого решения за счет изменения одного из параметров.

Задание 1. Пусть предприятие выпускает один вид продукции, например, столы. Данные о ре-ализации продукции и затратах на ее производство имеют значения, представленные ниже. В ячей ках таблицы приведены формулы: C4 = C2*C3, C7 = C6*0,302, C9 = C5+C6+C7+C8, C10 = C4-C9.

Допустим, что мы поставили себе цель повысить уровень прибыли до 12 млн. рублей. Это можно осуществить тремя способами: • повысить цену, • увеличить объем производства; • снизить затраты и прежде всего заработную плату.

Попробуем найти такую цену реализации, чтобы прибыль была 12 млн. руб. Воспользуемся меню Сервис-Подбор параметра. В результате откроется диалог Подбора параметра. В первом поле необходимо установить адрес целевой ячейки, во втором - ее значение. В третьем поле необходимо указать адрес изменяемой ячейки, то есть той ячейки, в которой будет осуществляться подбор параметра. Вставим в соответствующие поля адрес С10, 10000000 и СЗ и нажмем ОК. В результате мы получим сообщение, что решение найдено и, нажав еще раз ОК, в исходной таблице получим значение прибыли в 12 млн. руб. в случае, если цену реализации повысим до полученной в итоге суммы.

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

При нулевой прибыли можно определить себестоимость продукции.

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

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

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

Задание 2. Подбор оптимального значения максимальной ссуды.

С помощью команды Подбор параметра меню Сервис можно определить неизвестное зна-чение, которое будет давать желаемый результат. Например, предположим, что вы хотите узнать, какую максимальную ссуду на 20 лет вы можете взять, если процентная ставка равна 7,5% и вы должны ограничить свои месячные выплаты 2000 руб. Чтобы использовать команду Подбор пара-метра для получения ответа на этот вопрос, выполните следующие действия:

1. Создайте новый файл в программе MS Excel. Постройте таблицу, образец приведен ниже.

2. В ячейку В4 с помощью Мастера функций (категория функций - финансовые, функция ПЛТ) введите следующую формулу: =ПЛТ(B2/12; B3*12;B1). При правильном наборе и вводе формулы в ячейке В4 отразится результат - 4 833,56р.

3. Выполненный расчет показывает, что при ссуде в 600000 руб. ежемесячные выплаты превышают 2000 руб. Сделайте ячейку с формулой, в данном случае В4, активной ячейкой.

4. В меню Сервис выберите команду Подбор параметра. В окне диалога Подбор параметра оставьте без изменений значение в поле Установить в ячейке (абсолютная ссылка на ячейку В4).

5. В поле Значение введите максимальное значение, которое вы хотите получить в качестве значения формулы, в данном случае - 2000. (Введите - 2000, потому что вы хотите выплачивать 2000 руб.)

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

7. Нажмите кнопку ОК или клавишу Enter. MS Excel выведет окно диалога Результат подбора параметра. Ответ на наш вопрос находится в ячейке, заданной ранее в поле Изменяя значение ячейки. В данном примере результат 248264 появляется в ячейке В1. Чтобы сохранить это значение, нажмите кнопку ОК в окне диалога Результат подбора параметра.

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

8. Сохраните выполненную работу на личном диске в файле под именем Параметр-2.

9. Аналогично решению задачи 2 самостоятельно выполните расчет на приобретение 1-2х-комнатной квартиры в Сочи или автомобиля при существующих банковских процентах.

10. Посчитайте, какую в итоге сумму Вам придется вернуть банку.

3. ДИСПЕТЧЕР СЦЕНАРИЕВ. При проведении вариантных расчетов полезно сохранять промежуточные результаты и выводить их в виде структурной или свободной таблицы для последующего ее анализа. Это можно проделать с помощью Диспетчера сценариев, диалоговое окно которого вызывается командами меню Сервис - Сценарии.

Нажав кнопку Добавить, мы переходим в диалоговое окно Добавление сценария, в котором указываются: • имя сценария; • изменяемые ячейки (одна или диапазон); • комментарии; • запрет изменений; • скрыть или нет сценарий.

В следующем окне Значения ячеек сценария указываются значения изменяемых клеток

Нажав кнопку Отчет, попадаем в окно Отчет по сценарию, в котором необходимо указать тип отчета: Структура или Свободная таблица, а также Ячейки результата. Нажатие клавиши ОК позволяет выдать структурированную итоговую или сводную таблицу.

В столбце D таблицы структуры сценария представлены текущие значения цены и объема производства. При этом прибыль составит 712 тыс. руб. В следующем столбце раскрыт сценарий «Цена». Если увеличить цену за одни стол до 870 тыс. рублей, то прибыль составит 1972 млн. рублей. В столбце F приведен сценарий «Объем производства». Если увеличить объем производства столов с 18000 до 20000, то прибыль составит 2312 тыс., руб.

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

Сохраните результаты в файле с именем Параметр-1.