Лабораторная работа № 6 Рентабельность рекламной кампании

Если Вы хотите достичь определенных высот в экономике, то без рекламы, без вынесения Вашей деятельности на суд общественности никак не обойтись. Однако это не означает, что средства на рекламу следует тратить непродуманно, во всем необходим трезвый расчет. Хотя зачастую результат проведения рекламной кампании нельзя измерить в деньгах (например укрепление имиджа фирмы в обществе), и к помощи Excel в этом случае прибегать бесполезно. Мы обратим Ваше внимание на рекламу, приносящую результат, выраженный в конкретных цифрах, а именно попытаемся подсчитать с помощью динамического инвестиционного учета рентабельность проведения рекламной кампании.

Задание для подготовки к лабораторной работе

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

Условие задачи

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

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

1. Создание таблицы

1.1. Откройте новую рабочую книгу и введите в ячейку А1 название таблицы (например Расчет рентабельности рекламной кампании), а также задайте соответствующее начертание и размер шрифта. Чтобы Вы имели представление об окончательном результате работы, представим на рисунке полностью заполненную и оформленную таблицу.

Рис. 6.1. Заполненная таблица расчета

1.2. Как уже упоминалось, в задаче нам необходимо знать значение рыночной процентной ставки. Поэтому укажите в ячейке А3 текст Ставка, а в ячейку В3 введите само значение годовой процентной ставки и сформатируйте ячейку процентным стилем с двумя десятичными знаками.

1.3. В пятой строке таблицы укажите заголовки столбцов. Введите последовательно в ячейках А5-Н5 заголовки столбцов: Месяц, Расходы, Текущая стоимость, Расходы (Итог), Сумма покрытия, Текущая стоимость, Доходы (Итог), Сальдо.

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

1.5. Теперь можно приступать к вводу данных. Вместо названий месяцев в столбце А укажите числовые значения от 1 до 12, что позволит впоследствии использовать эти значения в формуле. Для этого используйте механизм автозаполнения. Введите в ячейки А6 и А7 значения 1 и 2, выделите диапазон из двух ячеек, поместите курсор вплоть до ячейки А17.

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

1.7. Поскольку расходы на рекламу осуществлялись в течение нескольких месяцев, то в этом случае целесообразно вести вместо статического динамический инвестиционный учет. А это предполагает сведение всех будущих платежей и поступлений путем дисконтирования на сумму рыночной процентной ставки к текущему значению. Первый платеж, естественно, не должен быть дисконтирован. Если предположить, что платежи осуществляются в разные промежутки времени, то платеж за февраль следует дисконтировать как платеж первого периода, поэтому в ячейку С6 вставьте следующую формулу расчета текущей стоимости расходов на рекламу для каждого месяца:

=В6*(1+$В$3/12)^(-$А6+1)

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

1.9. Сделайте вывод по полученному результату.

1.10. В заключении нужно представить общий объем расходов на рекламу за год нарастающим итогом. Первый платеж включим в общую сумму с помощью формулы =С6 в ячейке D6. Задание формулы в этой ячейке в нужном нам виде приведет к появлению значения ошибки. В ячейке С7 укажите формулу

=D6+C7

1.11. Скопируйте вставленную формулу в ячейки D8–D17. Поскольку после мая расходов на рекламу не было, то в ячейках определения нарастающего итога для мая-декабря представлено одно и то же значение. Таким образом, мы более-менее разобрались с расходами на рекламную кампанию и теперь попытаемся подсчитать, какой она принесла доход.

1.12. В качестве ключевого показателя целесообразности инвестиций в рекламу можно выбрать сумму покрытия, которая представляет собой разность между ценой товара и переменными издержками. Таким образом, она определяет, сколько приносит продажа единицы товара в копилку возврата инвестиций. В нашем примере мы укажем общее значение за месяц. Естественно, при расчете окупаемости рекламной кампании в реальной жизни данный пример должен опираться на солидный материал первичного учета. При этом в задаче мы учитываем только сумму покрытия, полученную при увеличении сбыта в результате проведения рекламной кампании. В январе реклама не дала еще результатов, и показатель объема продаж находился на уровне предыдущих месяцев. Однако уже в феврале можно констатировать увеличение сбыта. Поскольку рекламная кампания была уже практически завершена в мае, то в ноябре и декабре последствия ее проведения уже не ощущались. Введите желаемые значения в ячейки Е7–Е15 для остальных месяцев.

1.13. Затем задайте формулы для расчета текущей стоимости поступающих доходов. Однако нет необходимости снова вводить формулу. Достаточно скопировать формулу из ячейки С6 в ячейку F6. Поскольку в формуле заданы смешанная и абсолютная ссылки, то сейчас нет необходимости производить в ней какие-либо изменения. Скопируйте с помощью функции автозаполнения формулу из ячейки F6 в ячейки F7–F17

1.14. В столбце G будет отображена сумма покрытия нарастающим итогом за год. Возможно, заголовок Доходы(Итог) не совсем соответствует нашему замыслу, однако оставьте его с целью использования в дальнейшем при построении диаграммы. Как и в случае с расходами на рекламу, значение из ячейки F6 представьте в ячейке G6 без изменений: в ячейке G6 укажите выражение =F6, нажатием клавиши [Enter] переведите указатель ячейки на ячейку G7 и введите формулу

=G6+F7

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

1.16. Сравнив значения в столбцах D и G, можно уже сейчас сделать главный вывод о рентабельности рекламной кампании. Однако неплохо было бы еще определить, как протекали денежные потоки в течение года, а также в каком месяце была пройдена точка окупаемости инвестиций. Для отображения этих данных зарезервирован столбец Н, заголовок которого (Сальдо) подразумевает представление в нем сальдо дисконтированных денежных потоков нарастающим итогом. Укажите в ячейке Н6 формулу

=F6-C6

1.17. Программа выполнит необходимые вычисления и представит Вашему вниманию результат. Ячейки в столбце Н следует сформатировать денежным стилем.

1.18. После этого необходимо перевести указатель ячейки в ячейку Н7 и ввести в нее формулу

=Н6+(F6-C6)

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

Естественно, Вы можете определить сальдо денежных потоков и как разность соответствующих ячеек столбцов G и D.

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

2. Создание диаграммы

2.1. Диаграмму для иллюстрации хода рекламной кампании постройте на отдельном листе на основе несмежных выделений.

Выделите в таблице диапазоны ячеек D5-D17 и G5-G17 и выберите в меню Вставка команду Диаграмма/На новом листе. Перед текущим рабочим листом будет вставлен отдельный лист диаграмм, а на экране появится первое диалоговое окно мастера диаграмм. Подтвердите в нем предлагаемый программой исходный диапазон нажатием кнопки Далее.

2.2. Во втором диалоговом окне выберите один из типов диаграммы. Советуем отдать предпочтение типу График, а затем, нажав кнопку Далее, продолжить работу. Среди предлагаемых в третьем диалоговом окне видов графика выберите вид под номером 4 и нажмите кнопку Далее.

2.3. В четвертом окне Вы сможете впервые увидеть создаваемую диаграмму. Изменять в ней в данном случае ничего не нужно, поэтому нажмите кнопку Далее.

2.4. В последнем диалоговом окне мастера диаграмм укажите название диаграммы, например Реклама: расходы и доходы, название оси Х – Месяц и название оси Y – Итог по месяцам. Не забудьте также задать в этом диалоговом окне добавление легенды. Через несколько мгновений после закрытия пятого диалогового окна нажатием кнопки Готово созданная диаграмма будет представлена в листе диаграмм (рис. 6.2).

Рис. 6.2. Созданная диаграмма

Созданная диаграмма дает наглядное представление об эффективности расходов на рекламу. Поэкспериментируйте с применением различных способов форматирования диаграммы.

2.5. Поэкспериментируйте с суммами вкладов на проведение рекламной кампании и выберите наиболее выгодный для фирмы.

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

2.7. Сделайте отчет по лабораторной работе.

Контрольные вопросы

1. В каких целях проводится рекламная кампания?

2. Как рассчитывается фирмой прибыль от проведения рекламной кампании?

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