Время выполнения – 2 часа

Цель занятия.  Изучение информационной технологии организации расчетов с использованием встроенных функций, освоение методики создания сложных формул.

Вопросы для подготовки к работе:

1. Редактирование и форматирование данных.

2. Алгоритм работы с мастером функций.

3. Алгоритм построения диаграмм

Литература:   Теоретическая часть МУ (глава 1-4),[1.стр. с.241-271]

Порядок работы:

Задание 1. Произвести расчет рентабельности кампании.

Расходы на рекламу осуществлялись в течение нескольких месяцев, поэтому выбираем динамический инвестиционный учет. Это предполагает сведение всех будущих платежей и поступлений путем дисконтирования на сумму рыночной процентной ставки к текущему значению. Формула для расчета: А(n)=А(0) *(1 + j/12)(1-n).

1. Создайте таблицу по образцу Рисунок 19. Выделите для рыночной процентной ставки, являющейся константой, отдельную ячейку СЗ, и дайте ей имя «Ставка». Исходными данными для расчетов являются столбцы: A (месяц), B (Расходы на рекламу), Е (сумма покрытия), ячейка C3 (рыночная процентная ставка). Все остальные данные будут получены в процессе расчетов.

2. Произведите расчеты в остальных столбцах таблицы, результаты расчетов должны совпадать с данными в таблице.

3. В ячейку С6 введите формулу: =В6*(1 + ставка/12)^(1-$А6), Ячейка С6 в формуле имеет комбинированную адресацию: абсолютную адресацию по столбцу и относительную по строке и имеет вид $А6.

4. При расчете расходов на рекламу нарастающим итогом надо учесть, что первый платеж равен значению текущей стоимости расходов на рекламу, значит, в ячейку D6 введем значение = С6, но в ячейке D7 формула примет вид: D6 + С7.

5. Далее формулу ячейки D7 скопируйте в ячейки D8:D17.Обратите внимание, что в ячейках нарастающего итога с мая по декабрь будет находится одно и тоже значение, поскольку после мая месяца расходов на рекламу не было (см. таблицу).

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

7. Для расчета текущей стоимости покрытия скопируйте формулу из ячейки С6 в ячейку F6. В ячейке F6 должны быть формула: = Е6*(1+ставка/12)^(1-$А6).

8. С помощью маркера автозаполнения скопируйте формулу в ячейки F7:F17.

9. Сумма покрытия нарастающим итогом рассчитывается аналогично расходам на рекламу нарастающим итогом, поэтому в ячейку G6 поместим содержимое ячейки F6, а в G7 введите формулу = G6 + F7.

10. Далее формулу из ячейки G7 скопируем в ячейки G8:G17. В последних трех ячейках столбца будет представлено одно и тоже значение, т.к. результаты рекламной компании за последние три месяца на сбыте продукции уже не сказывались.

11. Сравнив значения в столбцах D и G, уже можно сделать вывод о рентабельности рекламной компании, однако расчет денежных потоков в течение года (колонка Н), вычисляемый как разница колонок G и D, показывает, в каком месяце была пройдена точка окупаемости инвестиций.

12. В ячейке Н6 введите формулу: = G6-D6 и скопируйте ее на весь столбец вниз.