Лаб_работа №8

Лабораторная работа №8

Тема: Анализ «что-если». Таблицы подстановки

Одной из очень привлекательных сторон MS Excel является возможность использования формул для построения динамических моделей, которые немедленно реагируют на изменение исходных данных. Анализ «что – если» - это процесс поиска ответов, например, на следующие вопросы: «Что будет, если цену на продукцию поднимем на 5%?» и т.д.

Типы анализа «что – если»:

1. Анализ «что – если» вручную.

2. Анализ «что – если» с использованием макросов.

3. Таблицы подстановки.

4. Диспетчер сценариев.

О первом методе много не скажешь. Он очень прост: есть одна или несколько ячеек для ввода информации, ссылки на которые используются в нескольких ячейках с формулами. Меняется информация входных ячеек – меняется результат.

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

Создание таблиц подстановки. Этот тип анализа используется тогда, когда нужно сравнить результаты нескольких вариантов решения.

Команда меню Данные – Таблица подстановки. Эта команда позволяет создавать удобные таблицы подстановки, которые помогают производить вычисления по формулам для одного из приведенных ниже случаев.

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

2. Имеется два набора данных для двух ячеек (две переменные), на которые ссылается одна формула. Создаваемая в этом случае таблица подстановки называется таблицей подстановки с двумя входами.

Таблица подстановки с одним входом

Макет таблицы подстановки с одним входом представлен на рис.1. Таблицу можно расположить в любом месте рабочего листа. Левый столбец содержит различные значения входного параметра. Верхняя строка содержит формулы или (чаще всего) ссылки на ячейки с формулами, по которым рассчитывается результат. Верхняя левая ячейка не используется.

Рис.1. Составные части таблицы подстановки с одним входом.

 ПРИМЕР. Требуется определить, какие ежемесячные выплаты необходимо вносить по ссуде размером 200 млн. Руб., выданной на 3 года при разных процентных ставках.

Предварительно подготовим исходные данные на рабочем листе Excel, как показано на рис.2.

Для заполнения таблицы необходимо выполнить следующую последовательность действий.

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

=плт(С4/12;С3*12;-С2)

2.Выделить диапазон ячеек, содержащий исходные значения процентных ставок и формулу для расчета - С7:D13. исходные данные в примере расположены в столбце С8:С13, поэтому результаты подстановки будут расположены в столбце D8:D13.

Рис.2. Подготовка исходных данных для использования Таблицы

3.Выбрать в меню Данные – Анализ «что-если» - Таблица данных. На экране появится диалоговое окно Таблицы подстановки. Это окно используется для задания рабочей ячейки, на которую ссылается формула расчета. В примере это ячейка С4, которую необходимо указать в поле Подставлять значения по строкам в диалогового окна в абсолютных координатах. Если исходные данные расположены в строке, то ссылку на рабочую ячейку необходимо ввести в поле Подставлять значения по столбцам в.

4. При нажатии кнопки ОК заполнится столбец результатов, как показано на рис. 3.

Рис. 3. Ежемесячные выплаты по займу для разных процентных ставок

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

 ПРИМЕР. В примере для расчета платежей по процентам за первый период для каждого значения процентной ставки в ячейку Е7 необходимо ввести формулу =ПРОЦПЛАТ(С4/12;1;С3*12;-С2) и повторить все описанные выше шаги. Результат представлен на рис.4.

Рис.4. Ежемесячные выплаты и платежи по процентам за первый месяц для разных процентных ставок

Для расчета выплат по процентам для остальных периодов (со 2 по 36) необходимо подставить формулы в следующие ячейки справа от последней.

Полученная таблица будет автоматически пересчитана при изменении суммы и срока займа, т.е. при внесении изменений в ячейки С2 и С3.

Задание

Составить электронную таблицу, которая обрабатывает информацию об ипотечной ссуде при изменении процентных ставок от 7% до 9% с шагом 0,25%, используя таблицу подстановки с одним входом.

Исходные данные: Цена - $201900, первый взнос – 20%, срок погашения ссуды 360, процентная ставка – 8%. Рассчитать размер ссуды, месячную плату, Общую сумму и сумму комиссионных. Все данные и результаты расчета приведены на рис.5. Допустим, необходимо ответить на вопросы:

Что, если смогу договориться о более низкой цене на имущество?

Что, если кредитор потребует 20% выплаты?

Что, если я смогу получить 40-летнюю ссуду?

Что, если процентная ставка снизится до 7,5%?

Рис.5. Данные для расчета ипотечной ссуды

Таблица подстановки для двух переменных

Таблица подстановки с двумя входами имеет макет, представленный на рис.6.

Рис.6. Макет Таблицы подстановки с двумя входами.

ПРИМЕР. Необходимо найти ежемесячные выплаты по займу размером 300 млн.руб. для различных сроков погашения и процентных ставок (рис.7).

Чтобы создать Таблицу подстановки для этой задачи, необходимо выполнить следующие действия.

1.Ввести первое множество входных значений (процентные ставки) в ячейки В8:В13 (см. рис.7).

2.Ввести второе множество входных значений (сроки погашения) в строку, расположенную выше и правее на одну ячейку, т.е. в ячейки С7:F7 (рис.7).

3.Ввести формулу для расчета на пересечении строки и столбца, содержащих два множества входных значений., т.е. в ячейку В7.

=пплат(С4/12;С3*12;С2)

4.Выделить диапазон таблицы данных , включающий все входные значения и формулу расчета, т.е. B7:F13.

5.Выбрать в меню Данные – Таблица подстановки и заполнить диалоговое окно: по столбцам- $С$3, по строкам – $С$4

6. Нажать кнопку ОК и получить результат, представленный на рис.8

Рис.8. Результат расчета таблицы подстановки с двумя переменными

Задание

Рассчитать эффективность проведения рекламной кампании с помощью рассылки материалов по почте путем вычисления чистой прибыли после продажи. Использовать для расчета инструмент Таблицы подстановки с двумя входами.

В модели будем использовать две ячейки для ввода информации: количество разосланных рекламных материалов и предполагаемый процент ответов. Область результата включает следующее(см.рис.9):

Стоимость печатных материалов. Изменяется в зависимости от количества:

0,20 – если количество экземпляров не превышает 200 000;

0,15 – если количество экземпляров превышает 200 000, но не более 300 000 экземпляров,

0,10 –если количество экземпляров превышает 300 000 экземпляров.

Стоимость определяется по следующей формуле:

=если(разослано_материалов<200000;0,20;если(разослано_материалов<300000; 0,15;0,10))

Почтовые расходы. Их стоимость фиксирована и составляет 0,32 за одно почтовое отправление.

Число респондентов. Количество ответов, которые предполагается получить. Оно определяется в зависимости от процента предполагаемых ответов и количества разосланных материалов. Формула для ячейки следующая:

=процент_ответивших*разослано_материалов

Доход на одного респондента. Это фиксированное значение. Компании известно, что за каждый заказ она получит прибыль 22.

Суммарный доход. Вычисляется по формуле:

=доход_на_одного_респондента*число респондентов

Суммарные расходы. В них входит стоимость печатных материалов и почтовых услуг:

=разослано_материалов*(стоимость_печатных_материалов+почтовые_расходы)

Чистая прибыль. Определяется как разница суммарных доходов и суммарных расходов.

Требуется создать таблицу подстановки с двумя входами, которая позволит вычислять чистую прибыль при разных комбинациях количества разосланных материалов и предполагаемого процента полученных ответов. Параметр Разослано_материалов задавать от 100 000 до 300 000 с шагом 25 000, процент_ответивших задавать от 1,50% до 3,00% с шагом 0,25%.

Рис.8. Расчет чистой прибыли после проведения рекламной кампании

По данным таблицы подстановки с двумя входами построить трехмерную диаграмму результатов анализа.