Практическое занятие 4
Решения экономических оптимизационных задач. Подбор параметра. Диспетчер сценария.
Успех в бизнесе требует обладания многими важными навыками. Одно из самых ценных качеств менеджера умение строить модели «что-если» и на их основе составлять прогнозы на будущее. Сколько чашек кофе по $1,75 необходимо продать, чтобы общая прибыль составила $30000? Что случится с показателями, если понизить цену на кофе, но увеличить расходы на рекламу? К счастью, Ехсеl содержит несколько полезных средств планирования, которые помогут отыскать путь к светлому финансовому будущему. Будем пользоваться командой Подбор параметра для нахождения значения, приводящего к требуемому результату, надстройкой Поиск решения для расчета оптимальной величины по нескольким переменным и ограничениям, а также Диспетчером сценариев для создания и оценки наборов сценариев «что-если» с несколькими вариантами исходных данных.
Оптимизация с помощью команды Подбор параметра
Основной командой для решения оптимизационных задач в Ехсеl является команда Подбор параметра из меню Сервис. Эта команда определяет неизвестную величину, приводящую к требуемому результату, например, количество проданных компакт-дисков по $5, необходимое для достижения объема продаж в $1 000 . Простота работы с этой командой является следствием ее ограниченных возможностей для завершения итерационного цикла меняется всего одна переменная. Если задача подразумевает изменение дополнительных переменных (эффекта от рекламы или оптовых скидок), следует воспользоваться командой Поиск решения.
Для работы с командой Подбор параметра необходимо, чтобы в листе находились:
формула для расчета;
пустая ячейка для искомого значения;
все прочие величины, встречающиеся в формуле.
Ссылка на пустую ячейку должна присутствовать в формуле; она является той самой переменной, значение которой ищет Ехсеl.
Примечание
При выполнении команды Подбор параметра из меню Сервис ячейка с формулой должна указываться в текстовом поле Установить в ячейке.
Во время подбора параметра в переменную ячейку непрерывно заносятся новые значения, пока не будет найдено решение поставленной задачи. Такой процесс называется итерацией, и продолжается он до тех пор, пока Ехсеl не выполнит 100 попыток или не найдет решение, лежащее в пределах точности 0,001 от точного значения (чтобы настроить оба параметра, необходимо выполнить команду Параметры из меню Сервис и задать нужные значения на вкладке Вычисления). Команда Подбор параметра существенно экономит время по сравнению с методом, основанном на грубой силе, то есть на «ручном» переборе входящих в формулу чисел.
Оптимизация с помощью команды Подбор параметра выполняется так:
1. Создайте лист с формулой, пустой (переменной) ячейкой и любыми данными, которые могут понадобиться при вычислениях. Например, на рис. 23.1 изображен лист для определения количества чашек кофе по 5 р. 40 к., которые необходимо, продать для получения выручки в 30 тыс. р.
Рис. 1.
Для работы команды Подбор параметра необходима формула и пустая ячейка
Выделите ячейку листа, в которой содержится формула (в диалоговом окне Подбор параметра этой ячейке соответствует поле Установить в ячейке).
Выполните команду Подбор параметра из меню Сервис. Открывается диалоговое окно Подбор параметра, изображенное на приведенном ниже рисунке. Выделенная ячейка с формулой появляется в текстовом поле Установить в ячейке, а вокруг ячейки листа появляется пунктирная рамка. Снова щелкните на ячейке, чтобы подтвердить выбор.
Введите в текстовое поле Значение ту величину, которая должна быть возвращена формулой. Например, если интересующий объем продаж равен 30 000 р., следует ввести в поле Значение величину 30000.
5.Переместите курсор в текстовое поле Изменяя значение ячейки. При необходимости отодвиньте диалоговое окно Подбор параметра в сторону и выделите ту ячейку, в которой должен содержаться ответ (переменная ячейка). Ее содержимое будет подобрано и подставлено в формулу командой Подбор параметра. Выделенная ячейка (В7 в нашем примере) выделяется на листе рамкой, как показано наследующем рисунке.
Команда Подбор параметра вычисляет неизвестное значение
6. Щелкните на кнопке ОК, чтобы запустить поиск решения. После завершения итерационного цикла в диалоговом окне Результат подбора параметра появляется сообщение, а результат заносится в ячейку листа, как изображено на рис. 23.2. В нашем случае прогноз показывает, что для достижения выручки в 30 000 р. необходимо продать 5555 чашек кофе по 5 р. 40 к.
Рис. 2 Результат выполнения команды Подбор параметра выводится в пустой ячейке, выбранной на рабочем листе
7. Закройте диалоговое окно Результат подбора параметра, щелкнув на кнопке Ок.
СОВЕТ
При длительных вычислениях (например, при включении в формулу нескольких финансовых функций) можно остановить итерационный цикл кнопкой Пауза в диалоговом окне Результат подбора параметра или просматривать итерации в пошаговом режиме кнопкой Шаг.
Изменение условий оптимизационных задач
Наверное, самым большим достоинством решений, находимых с помощью команды Поиск решения, является простота, с которой они могут пересчитываться для учета новых целей и непредвиденных обстоятельств. Например, если вы вдруг решите, что желаете получать от продажи кофе ровно 2000 р. в неделю, то сможете вычислить оптимальную комбинацию с помощью команды Поиск решения. Установка целевого значения командой Поиск решения немного напоминает работу с командой Подбор параметра для определения неизвестной величины, но в данном случае можно пользоваться несколькими переменными.
Изменение условий задачи для получения определенного целевого значения происходит следующим образом:
Выполните команду Поиск решения из меню Сервис. Открывается диалоговое окно Поиск решения, в котором приведены переменные и ограничения из последнего решения. Их значения придется изменить для достижения новой цели.
Установите в группе Равной переключатель Значению и введите текстовое поле величину 2000. Данный переключатель задает для целевой ячейки конкретное числовое значение, а поиск решения состоит в определении набора переменных, при которых это значение будет достигнуто (в нашем примере переменные ячейки соответствуют проданным чашкам кофе). Диалоговое
окно должно выглядеть так
Щелкните на кнопке Выполнить, чтобы начать поиск ответа. После завершения работы щелкните на кнопке ОК, чтобы занести найденное решение в ячейки листа.
Рис. 5. Команда Поиск решения вычисляет оптимальное распределение продаж с учетом ограничений