Инструмент Подбор параметра

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

Например, в Ms Excel можно реализовать базовую компьютерную модель анализа «Затраты – Объем – Прибыль», которая предназначена для проведения расчетов по исследованию влияния различных вариантов (сценариев) изменения выручки от реализации, постоянных и переменных затрат на изменение прибыли и финансовой прочности.

Базовая модель анализа «Затраты – Объем – Прибыль» позволяет решать широкий круг задач. Прежде всего, благодаря ей можно проанализировать влияние изменения выручки от реализации на сумму прибыли.

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

Например, руководством предприятия рассматриваются четыре альтернативные решения.

Первое состоит в увольнении ряда сотрудников, получающих фиксированную зарплату. Это позволит сократить постоянные затраты на 650, но приведет к сокращению выручки от реализации на 10%.

Второе решение предполагает аренду транспорта с целью сокращения переменных затрат на закупку и реализацию товаров. Фиксированная арендная плата (увеличение постоянных затрат) составит 500, но при этом переменные затраты уменьшатся на 7 коп. с рубля реализации. При этом выручка от реализации не изменится.

Третья альтернатива предполагает аренду новых торговых площадей и наем новых продавцов, что потребует увеличения постоянных затрат на аренду и заработную плату на 1000. При этом нет точной оценки увеличения выручки от реализации. В среднем предполагается ее увеличение на 21%. В худшем случае выручка увеличится только на 18%, а в лучшем – на 25%.

Следующая альтернатива предполагает оказание дополнительных услуг клиентам. Например, по наладке и сервисному обслуживанию продаваемых товаров. Это потребует увеличения переменных затрат на рубль реализации в среднем на 5%. Однако, за счет предоставления дополнительного сервиса ожидается увеличение выручки от реализации, которое по пессимистичной оценке составит 4%, а по оптимистичной 11%.

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

Например, руководство предприятия может интересовать:

а) на сколько процентов может снизиться выручка от реализации при уменьшении постоянных затрат на заданную величину с тем, чтобы масса прибыли осталась той же, что и в базовом периоде;

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

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

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

Команда Подбор параметра меню Данные – Анализ «что-если» позволяет определить неизвестное значение (параметр), которое будет давать желаемый результат. Технология использования команды следующая:

– решить нужную задачу с каким-либо начальным значением параметра;

– выбрать команду Подбор параметра меню Данные – Анализ «что-если»;

– в окне диалога Подбор параметра в поле Установить в ячейке задать абсолютную ссылку на ячейку, содержащую расчетную формулу, а в поле Значение – то значение, которое следует получить в качестве результата формулы;

– в поле Изменяя значение ячейки ввести ссылку на ячейку с параметром;

– нажать кнопку ОК или клавишу Enter, на экране появится окно диалога Результат подбора параметра;

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

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

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

По умолчанию команда Подбор параметра прекращает вычисления, когда выполняется 100 итераций, или при получении результата, который находится в пределах 0,001 от заданного целевого значения.

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

Команда Подбор параметра находит только одно решение, даже если задача имеет несколько решений.

Процедура пакета программ Ms Excel намного ускоряется процесс нахождения конкретного значения параметра по сравнению с методом «проб и ошибок».