Пример.

Необходимо найти ежемесячные выплаты по займу размером 300 млн. руб. для различных сроков погашения и процентных ставок.

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

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

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

3. Ввести формулу для расчета на пересечении строки и столбца, содержащих два множества входных значений., т.е. в ячейку В7 = плт (С4/12;С3*12;С2)

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

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

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

Рис.7

Рис.8

ЗАДАНИЕ 2

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

Варианты заданий приводятся в табл. 1.

Рекомендации:

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

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

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

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

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

Для вычисления стоимости использовать функции ВПР или ЕСЛИ.

Почтовые расходы. С тоимость фиксирована (см. табл. 3.4).

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

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

Доход на одного респондента. Это фиксированное значение (табл. 1).

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

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

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

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

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

Требуется создать таблицу подстановки с двумя входами, которая позволит вычислять чистую прибыль при разных комбинациях количества разосланных материалов и предполагаемого процента полученных ответов. Параметр Разослано_материалов задавать от N1 до N2 с шагом H1, процент_ответивших задавать от K1 до K2 с шагом H2.

Рис.9

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

Таблица 1

Рис. 10

Отчет о выполнении работы должен представлять собой документ MS Word. Обязательно приводятся условие задач и исходные данные.

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

Графический материал (диаграммы и графики) должны быть расположены на том же листе.

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

Дата добавления: 2015-09-06