Построим таблицу, которая будет отображать валовую прибыль после изменения объема продаж или процента прибыли. На новом рабочем листе заполним таблицу представленную на рисунке 5.86
.
Рисунок 5.86 – Начальные данные
В ячейку В5 ввести формулу =В3*В4. В ячейку В13 ввести формулу =СУММ(В7:В12). В ячейку В15 - формулу =В5-В13. В ячейку D2 поместим формулу вычислений, для этого туда надо занести ссылку =В5. Ячейки Е2:I2 заполнить значениями валового объема продаж варьируемого от 80000 до 160000 с шагом 20000. Ячейки D3:D8 заполнить значениями процента прибыли от 15% до 40% с шагом 5%.
Чтобы создать таблицу анализа данных выделите ее (D2:I8). Выберите команду ДанныеÞАнализ «что-если» Þ Таблица данных.В открывшемся диалоговом окне в поле Подставлять значения по столбцам набрать $B$3, а в поле Подставлять значения по строкам - $B$4. Результат вычислений показан на рисунке 5.87.
Рисунок 5.87 – Результат подстановки
Проведем анализ чистой прибыли. Для этого в ячейки Е11:I11 скопируем содержимое ячеек Е2:I2, а в ячейки D12:D17 скопируем содержимое ячеек D3:D8.
Для ячеек Е12:I17 установить условное форматирование. Выделить этот блок ячеек и выполнить команду Главная Þ Условное форматированиеÞПравила выделения ячеек, если значения в ячейках будут меньше нуля, формат их отображения должен стать полужирным красного цвета (рис. 5.88).
Рисунок 5.88 – Окно условного форматирования
В ячейке Е12 разместим формулу =Е3-$B$13. Скопировать эту формулу в ячейки Е12:I17. Результат показан на рисунке 5.89.
Рисунок 5.89 – Результат подстановки
5. Поиск решения с использованием средства «Подбор параметра».
Вернемся к первому нашему примеру «Покупка видеомагнитофонов в кредит» (рис. 5.82) расположенному на листе 1. В ячейке С8 содержится формула для расчета выплаты по кредиту. Если вы знаете, какую максимальную выплату можете сделать, то Excel сможет вычислить максимальную сумму кредита на покупку, которую вы можете себе позволить при заданном уровне процентной ставки и сроке погашения кредита. Поместив значение, максимально возможной выплаты в ячейку С8, вы удаляете формулу и вычисляете результат с использованием средства Подбор параметра.
Например, вы можете сделать выплату равную 200 руб. и хотите узнать, какой кредит вы можете взять.
В ячейку С4 внесем значение 3500. Сделаем активной ячейку С8 (содержащую формулу). Выполним команду ДанныеÞАнализ «что-если» Þ Подбор параметра. Откроется диалоговое окно, показанное на рисунке 5.90.
Рисунок 5.90 – Окно подбора параметра
В этом окне в поле Значение набрать 200, в поле Изменяя значение ячейки набрать $C$4. Щелкнуть ОК. В появившемся окне, «Результат подбора параметра», вы можете принять новое значение, щелкнув ОК, или вернуться к исходным данным, нажав, Отмена.