Методика выполнения работы

1. На рисунке 3.31 показана таблица, которую необходимо создать для проведения расчетов. В ячейках, где числа отображаются с двумя знаками после запятой, для установления этого формата воспользуйтесь кнопкой на панели инструментов «Увеличить разрядность». Для отображения двойной рамки вкладка Главная группа Шрифт кнопка Границы команда Другие границы.

Рисунок 3.31

Таблица содержит информацию, позволяющую подсчитывать выплату за покупку 10 видеомагнитофонов в кредит на 1 год по 12% годовых. Выплаты производятся в середине и конце каждого месяца, т.о. количество периодов выплаты равно 24. Задается цена за единицу товара, количество купленного товара, ежемесячный процент выплаты и срок кредита.

В ячейку F6 - «Итого» занесем формулу =F4*F5. В ячейку С4 - «Стоимость» занесем значение, полученное в F6. В ячейку С8 - «Выплата» заносится формула =ПЛТ(С5/12;С6;С4). Формулу в ячейку С8 можно внести с клавиатуры или воспользоваться мастером функций, финансовые функции. Функция ПЛТ используется для расчета величины выплаты за один период годового кредита.

Ответим на вопрос: Что произойдет, если мы закупим не 10, а 8 видеомагнитофонов? Для этого заменим Количество на 8.

2. Прогнозирование в таблице подстановки. Одна входная величина и одна формула.

При покупке видеомагнитофонов возник вопрос: Заказать их в кредит по телефону с 12% ставкой и не иметь проблем с доставкой или ехать самому, чтобы получить кредит с 10% ставкой? Что выгоднее?

Чтобы ответить на этот вопрос дополним нашу таблицу. Занесем в ячейки В11-В17 величину процента от 11% до 14% с шагом 0,5%. Отформатировать эти ячейки для отображения 1 знака после запятой. В ячейку В10 введите формулу =В5. Т.о. ячейке В10 будет присвоено имя «Процент». В ячейку С10 ввести формулу =С8.

Выделите всю таблицу данных, включая формулу и заголовок (В10:С17). На вкладке Данные в группе Работа с данными щелкаем на кнопке Анализ «что - если», в открывающемся меню выбираем команду Таблица данных. Откроется диалоговое окно. В строке Подставлять значения по строкамв ввести адрес $C$5. Щелкнуть ОК. В ячейках С11:С17 показаны новые выплаты (рис.3.32).

Рисунок 3.32

3. Использование нескольких формул для одной входной переменной.

В предыдущем примере мы использовали для одной входной переменной «Процент» одну формулу «Выплата». Дополним вычисления расчетом суммарного значения выплаты по процентам по истечению срока кредита. Для этого внесем в ячейку D10 новую формулу =(С10*С6)-С4. Т.е. выплату за период умножаем на срок и вычитаем исходную сумму покупки.

Выделим ячейки В10:D17. На вкладке Данные в группе Работа с данными щелкаем на кнопке Анализ «что - если», в открывающемся меню выбираем команду Таблица данных. В диалоговом окне «Таблица данных», в поле «Подставлять значения по строкам» набрать $С$5. Результат показан на рисунке 3.33.

Рисунок 3.33

4. Две входные переменные.

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

Рисунок 3.34

В ячейку В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. Результат вычислений показан на рисунке 3.35.

Рисунок 3.35

Проведем анализ чистой прибыли. Для этого в ячейки Е11:I11 скопируем содержимое ячеек Е2:I2, а в ячейки D12:D17 скопируем содержимое ячеек D3:D8.

Для ячеек Е12:I17 установить условное форматирование. Выделить этот блок ячеек выполнить Главная – Стили - Условное форматирование – Правила выделения ячеек. В открывшемся диалоговом окне задать, что если значения будут меньше нуля, формат их отображения будет красного цвета (рис. 3.36).

 

Рисунок 3.36

В ячейке Е12 разместим формулу =Е3-$B$13. Скопировать эту формулу в ячейки Е12:I17. Результат показан на рисунке 3.37.

Рисунок 3.37

5. Поиск решения с использованием средства «Подбор параметра».

Вернемся к первому нашему примеру «Покупка видеомагнитофонов в кредит» (рис. 3.32) расположенному на листе 1. В ячейке С8 содержится формула для расчета выплаты по кредиту. Если вы знаете, какую максимальную выплату можете сделать, то Excel сможет вычислить максимальную сумму кредита на покупку, которую вы можете себе позволить при заданном уровне процентной ставки и сроке погашения кредита. Поместив значение, максимально возможной выплаты в ячейку С8, вы удаляете формулу и вычисляете результат с использованием средства Подбор параметра.

Например, вы можете сделать выплату равную 200 руб. и хотите узнать, какой кредит вы можете взять.

В ячейку С4 внесем значение 3500. Сделаем активной ячейку С8 (содержащую формулу). Выполним Данные - Работа с данными - Анализ «что - если» - Подбор параметра. Откроется диалоговое окно, показанное на рисунке 3.38.

Рисунок 3.38

В этом окне в поле «Значение» набрать 200, в поле «Изменяя значение ячейки» набрать $C$4. Щелкнуть ОК. В появившемся окне, «Результат подбора параметра», вы можете принять новое значение, щелкнув ОК, или вернуться к исходным данным, нажав, Отмена.