Подбор параметра

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

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

1. На Лист1 введите данные калькуляции цены книги, приведенные в табл. 1.

Таблица 1

Константами должны быть:

количество экземпляров;

проценты накладных расходов;

затраты на зарплату;

затраты на рекламу;

цена продукции;

себестоимость продукции

(в таблице эти значения показаны на сером фоне жирным шрифтом). Остальные данные должны быть представлены в виде расчетных формул:

Доход= Цена продукции ^x Количество экземпляров;

Себестоимость реализованной продукции= Себестоимость продукции ^x Количество экземпляров;

Валовая прибыль= Доход – Себестоимость реализованной продукции;

Накладные расходы= Доход ^x Проценты накладных расходов;

Валовые издержки= Накладные расходы + Затраты на зарплату + Затраты на рекламу;

Прибыль от продукции= Валовая прибыль – Валовые издержки.

Введите формулы и сверьте результаты расчета по ним с данными, приведенными в табл. 1.

2. Переименуйте Лист1 вКалькуляция и скопируйте отлаженную таблицу с формулами в Лист2. Исследуйте информацию, представленную на таблице листа Калькуляция.Увеличение прибыли может быть достигнуто за счет изменения количества экземпляров, себестоимости, валовых издержек и уровня накладных расходов.

3. Подберите такую цену книги, чтобы прибыль от продукции составила 1500 000 руб.

Для этого:

· на вкладке Данные в группе Работа с данными выберите команду Анализ “что-если”, а затем выберите в списке пункт Подбор параметра;

· в диалоговом окне Подбор параметра в поле Установить в ячейке с помощью мыши укажите целевую ячейку, содержащую значение прибыли от продукции ($B$11), в поле Значение укажите то значение, которое должно быть достигнуто (1 500 000) и в поле Изменяя ячейку введите абсолютную ссылку на ячейку, содержащую значение цены ($B$14);

· нажмите кнопку ОК.

4. Ознакомьтесь с результатами выполнения операции подбора параметра в окне Результат подбора параметра и щелкните кнопку OK для изменения значений ячеек таблицы в соответствии с найденным решением.

5. Вернитесь к исходному состоянию таблицы, используя описанный в пунктах 3, 4 способ подбора параметра.

6. Самостоятельно определите, каков должен быть показатель процентов накладных расходов, чтобы прибыль за продукцию составила 1000 000 рублей.