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

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

Задание

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

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

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

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

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

На рисунке 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 – Результат подстановки

⇐ Предыдущая 1 2 3 456 Следующая ⇒