Лабораторная работа №8. Анализ «Что-Если»

 

Цель работы: освоить начальные навыки экономического анализа данных с помощью специальных инструментов Excel.

Задание

1. Рассчитать ежемесячную выплату при изменяющейся ставке и сумме кредита.

2. Применить к ячейкам с отрицательными значениями Условное форматирование.

3. С помощью подбора параметра определить, какой кредит надо взять, если известно, сколько в месяц вы сможете выплачивать в течение определенного срока.

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

1. Расчет ежемесячной выплаты.

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

Рисунок 5.82 – Вид начальной таблицы

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

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

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

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

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

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

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

 

 

Рисунок 5.83 – Окно таблицы данных и кнопка Анализ «что-если».

Рисунок 5.84

 

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

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

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

.

Рисунок 5.85 – Результат подстановки

 

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

Построим таблицу, которая будет отображать валовую прибыль после изменения объема продаж или процента прибыли. На новом рабочем листе заполним таблицу представленную на рисунке 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 – Результат подстановки