Оценка «4» ставится при выполнении 4-х заданий.

Оценка «3» ставится при выполнении 3-х заданий.

Практическая работа «Оценка рентабельности рекламной компании»

Время выполнения – 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 и скопируйте ее на весь столбец вниз.

Задание 2.  Задать условное форматирование диапазона, сделать расчет по  функциям.

1. Проведите условное форматирование результатов расчета колонки Н: отрицательных чисел - синим курсивом, положительных чисел красным цветом шрифта.

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

3. В ячейке H20 произведите расчет количества месяцев, в которых сумма покрытия больше 100000 руб. (используйте функцию СЧЕТЕСЛИ, указав в качестве диапазона «Значение» интервал ячеекE7:E14, а в качестве условия>100000).

Задание 3. Построить диаграммы, подготовить документ к печати.

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

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

- «Реклама: доходы и расходы» - по данным колонок D и G (диапазоны D5:D17, G5:G17 выделяйте, удерживая нажатой клавишу Ctrl. Графики дают наглядное представление об эффективности расходов на рекламу. Они графически показывают, что точка окупаемости инвестиций приходится на июль месяц.

Задание 4. Произвести расчеты с использованием функций.

1. На листе 2  создайте таблицу с результатами сдачи вступительных экзаменов с по трем предметам (рис. 20).

2. Рассчитайте суммарный балл по предметам; средний балл по предмету;

3. Рассчитайте количество обучающихся, сдавших экзамены на «5» и на «3» по каждому предмету;

4. Рассчитайте количество зачисленных, если проходной балл равен 13.

Задание 5. Выполните фильтрацию данных по указанным ниже параметрам. Подготовить документ к печати.

1. Отфильтруйте данные об обучающихся, имена которых начинаются на букву М. Результаты сохраните на следующем листе.

2. Отфильтруйте данные об учениках, возраст которых превысил 16 лет. Результаты сохраните на следующем листе.

3. Вставьте в нижний колонтитул информацию о исполнителе: ФИО, номер телефона, в верхний колонтитул текущую дату и время.

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

5. Сохранить документ в своей папке.

Рисунок 20

ОБРАЗЕЦ ВЫПОЛНЕНИЯ ЗАДАНИЙ

Задание 1-2

Задание 3

 

КРИТЕРИИ ОЦЕНКИ

Все задания выполняются строго по порядку.

За каждое задание студент получает 1 балл.

Оценка «5» ставится при выполнении всех заданий.