Практическая работа № 14. Порядок работы

Практическая работа № 14

Тема:ЭКОНОМИЧЕСКИЕ РАСЧЕТЫ В MS EXCEL

Цель: изучение технологии экономических расчетов в табличном

процессоре.

Задание 1.Оценка рентабельности рекламной кампании фирмы.

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

1. Запустите редактор электронных таблиц MS Excel и создайте новую электронную книгу.

2. Создайте таблицу оценки рекламной кампании по образцу (рис.1). Введите исходные данные: Месяц, расходы на рекламу А(0) (р.), Сумма покрытия В(0) (р.), Рыночная процентная ставка (j) = 13,7%.

Выделите для рыночной процентной ставки, являющейся константой, отдельную ячейку – С3, и дайте этой ячейке имя «Ставка».

Рис. 1. Исходные данные для Задания 1

Краткая справка: присваивание имени ячейке или группе ячеек:

· выделите ячейку (группу ячеек или несмежный диапазон), которой необходимо присвоить имя;

· выполните команду Формулы/Определенные имена/Присвоить имя;

· введите имя;

· нажмите ОК.

Помните, что по умолчанию имена являются абсолютными ссылками.

1. Произведите расчеты во всех столбцах таблицы.

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

Формулы для расчета:

A(n) = A(0) * (1 + j/12)^(1-ⁿ⁾,

в ячейке С6 наберите формулу

=B6 * (1+ ставка/12)^(1-$A6)

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

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

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

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

Для расчета текущей стоимости покрытия скопируйте формулу из ячейки C6 в ячейку F6. В ячейке F6 должна быть формула

= Е6 * (1 * ставка/12)^(1 - $А6).

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

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

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

Рис. 2. Рассчитанная таблица оценки рекламной кампании

Сравнив значения в столбцах В и G, уже можно сделать вывод о рентабельности рекламной кампании, однако расчет денежных потоков в течение года (колонка H), вычисляемый как разница колонок G и D, показывает, в каком месяце была пройдена точка окупаемости инвестиций. В ячейке H6 введите формулу +G6 – D6, и скопируйте её на всю колонку.

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

4. В ячейке Е19 произведите расчет количества месяцев, в которых сумма покрытия имеется (используйте формулу «Счет» (Формулы/Другие функции/Статистические), указав в качестве диапазона «Значение 1» интервал ячеек Е7:Е14). После расчета формула в ячейке Е19 будет иметь вид =СЧЕТ(Е7:Е14).

5. В ячейке Е20 произведите расчет количества месяцев, в которых сумма покрытия больше 100 000 р. (используйте функцию СЧЕТЕСЛИ, указав в качестве диапазона «Значение» интервал ячеек Е7:Е14, а в качестве условия >100 000). После расчета формула в ячейке Е20 будет иметь вид = СЧЕТЕСЛИ(Е7:Е14) (рис.3).

6. Постройте графики по результатам расчетов (рис.4):

«Сальдо дисконтированных денежных потоков нарастающим итогом» по результатам колонки H;

«Реклама: расходы и доходы» по данным колонок В и G (диапазоны D5:D17 и G5:G17 выделяйте, удерживая нажатой клавишу [Ctrl]).

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

7. Сохраните файл в вашей папке.

Рис. 3. Расчет функции СЧЕТЕСЛИ

Рис. 4. Графики для определения точки окупаемости инвестиций