ПРАКТИЧЕСКОЕ ЗАНЯТИЕ N 19

ТЕМА: Экономические расчеты в MS Excel.

ЦЕЛЬ: Изучение технологии экономических расчетов в табличном процессоре.

ВРЕМЯ НА ВЫПОЛНЕНИЕ: 2 часа.

МЕСТО ВЫПОЛНЕНИЯ РАБОТЫ  лаборатория «Информационных технологий»

ДИДАКТИЧЕСКОЕ И МЕТОДИЧЕСКОЕ ОБЕСПЕЧЕНИЕ: Персональный компьютер. Электронные методические указания.

1. Внеурочная подготовка

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

 

2. Работа в кабинете

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

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

1. Запустите Microsoft Excel и создайте новую электронную книгу.

2. Создайте таблицу оценки рекламной кампании по образцу

Введите исходные данные: Месяц, Расходы на рекламу А(0) (р.), Сумма покрытия В(0) (р.)> Рыночная процентная ставка G = 13,7%-

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

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

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

• Щелкните на поле Имя, которое расположено слева в строке формул.

• Введите имя ячеек.

• Нажмите клавишу [Enter].

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

Формулы для расчета: А(n) = А(0) * (1 + j/12)^(1-n),

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

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

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

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

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

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

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

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

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

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

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

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

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

kino

4. В ячейке Е19 произведите расчет количества месяцев, в которых сумма покрытия имеется (используйте функцию «Счет» (Формулы/Вставить функцию/ Статистические), указав в качестве диапазона интервал ячеек Е6:Е17). После расчета формула в ячейке Е19 будет иметь вид — СЧЕТ(Е6:Е17).

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

 

 

6. Постройте графики по результатам расчетов: «Сальдо дисконтированных денежных потоков нарастающим итогом» по результатам расчетов колонки Н; «Реклама: расходы и доходы» по данным колонок D и G (диапазоны D5:D17 и G5:G17 выделяйте, удерживая нажатой клавишу [Ctrl])

.

 

3. СОДЕРЖАНИЕ ОТЧЕТА

3.1. Номер практического занятия, тема и цель работы.

3.2. Макет созданных электронных таблиц с формулами и результатами расчета.

3.2. Протокол выполненных действий.