«Структурирование листов. Подбор параметра. Поиск оптимального решения» Краткая теория:
Подбор параметра
Команда «Подбор параметра» пункта меню «Сервис» позволяет определить неизвестное значение, которое будет выдавать желаемый результат.
Поиск оптимального решения
Необходимость использования метода моделирования коммерческой деятельности определяется тем, что многие объекты или проблемы (поиск поставщика, получение максимальной прибыли при минимальных затратах и т.д.) непосредственно исследовать невозможно, или это исследование требует много времени и средств.
При решении этих проблем необходимо знать ответы на дополнительные вопросы, которые можно сгруппировать в две группы:
что будет, если…
что надо, чтобы…
Без ответа на поставленные вопросы невозможно принять обоснованного решения, которое требует:
постановки задачи;
выбор критерия;
анализ принимаемого решения.
Большинство задач коммерческой деятельности поиска оптимального решения относятся к классу задач линейного программирования.
Задачи линейного программирования записываются следующим образом:
,
,
,
Множество всех допустимых решений задачи линейного программирования называется областью допустимых решений (ОДР).
Допустимое решение задачи линейного программирования, максимизирующее или минимизирующее целевую функцию называется оптимальным.
Задачу линейного программирования можно решать аналитическими и графическими методами. Аналитические методы представляют собой последовательность вычислений по некоторым правилам, являются основой для решения задачи на компьютере. Их единственный недостаток заключается в том, что в отличие от графических методов, они совершенно не наглядны. Графические методы достаточно наглядны, но они пригодны лишь для решения таких задач, в которых число неизвестных переменных n=2, что дает возможность представлять задачу на плоскости.
В MS Excel существует возможность поиска оптимального решения с использованием команды «Поиск решения» пункта меню «Сервис». Подключение пакета «Поиск решения» выполняется аналогично пакету «Анализ данных».
Пример выполнения:
Задача 1. Торговая фирма для организации новой компании не имеет достаточно средств. Ей необходимо привлечь заемный капитал на 15 лет с месячной выплатой, не превышающей 100000 руб. и процентной годовой ставкой равной 5%.
Решение:
Построим таблицу данных (рис. 13):
|
A |
B |
C |
D |
1 |
Сумма заемного капитала, руб. |
Процентная ставка, % |
Срок заема, лет |
Ежемесячная выплата, руб. |
2 |
10000000 |
0,05 |
15 |
-79 079,36р. |
Рис. 13. Таблица данных
2. Предполагается, что на начальном этапе сумма заемного капитала задана в размере 10000000 руб.
3. Ввести в ячейку D2 формулу: =ПЛТ(В2/12;С2*12;А2);
4. Выбрать команду «Подбор параметра» пункта меню «Сервис».
5. Ввести входные параметры в окне диалога «Подбор параметра» (Рис. 14)
Рис. 14. Окно диалога «Подбор параметра»
в поле «Установить в ячейки» ввести $D$2:
в поле «Значение» ввести – 100000 (фирма должна выплатить 100000 руб.);
в поле «Изменяя значение ячейки» ввести ссылку или щелкнуть мышью на листе по ячейке, значение которой является неизвестным. В данном случае – ячейка $A$2.
После нажатия «Ок» появится диалоговое окно «Результат подбора параметра» и изменения в исходной таблице.
При подборе параметра MS Excel использует итерационный процесс: проверяется одно значение за другим для изменяемой ячейки, адрес которой указан в поле «Изменения значения ячейки», пока не получится нужное решение. Для прерывания вычисления при решении сложных задач подбора параметра используется кнопка «Пауза» в окне диалога «Результат подбора параметра», а для просмотра результата каждой последовательной итерации – кнопка «Шаг». Если задача решается в пошаговом режиме, то в окне диалога появится кнопка «Продолжить», которая предназначена для возврата в обычный режим подбора параметра.
По умолчанию команда «Подбор параметра» прекращает вычисления, когда выполняется 100 итераций, или при получении результата, который находится в пределах 0,001 от заданного целевого значения. Если нужна большая точность, то можно изменить используемые параметры. Для этого следует выбрать команду «Параметры» пункта меню «Сервис». В поле «Предельное число итераций» выбрать вкладку «Вычисления» и ввести значение больше 100, а в поле «Относительная погрешность» - значение меньше 0,001.
Команда «Подбор параметра» находит только одно решение, даже если задача имеет несколько решений. Команда «Подбор параметра» является удобной для решения задач, которые имеют точное целевое значение, зависящее от одного неизвестного параметра.
Задача 2. Фирма, выпускающая некоторую продукцию, осуществляет ее рекламу двумя способами: через радио и телевидение.