По результатам расчетов сформулируйте выводы
ПРАКТИЧЕСКОЕ ЗАНЯТИЕ № 6
Тема: ЭКОНОМИЧЕСКИЕ РАСЧЕТЫВ MS EXCEL
Цель занятия. Изучение технологии экономических расчетов и определение окупаемости средствами электронных таблиц.
Перед началом занятия необходимо знать: теорию работы с экономическими расчетами в программе MS Excel..
После окончания занятия необходимо уметь: осуществлять экономические расчеты в программе MS Excel.
Оборудование: ПК, программа MS Excel.
Постановка задачи: изучить использование и применение экономических расчетов в программе MS Excel.в деятельности страховой компании.
Задание 1. Оценка рентабельности рекламной компании фирмы
Порядок работы
1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу.
2. Создайте таблицу оценки рекламной компании по образцу (рис. 1). Введите исходные данные: Месяц, Расходы на рекламу А(0), р., Сумма покрытия В(0) р., Рыночная процентная ставка
(j) = 13,7%.
Выделите для рыночной процентной ставки, являющейся константой, отдельную ячейку — СЗ, и дайте этой ячейке имя «Ставка».
Краткая справка. Для присваивания имени ячейке или группе ячеек выполните следующие действия:
• выделите ячейку (группу ячеек или несмежный диапазон), которой необходимо присвоить имя;
• щелкните поле Имя, которое расположено слева в строке формул;
• введите имя ячейки.
• нажмите клавишу [Enter].
Помните, что по умолчанию имена являются абсолютными ссылками.
3. Произведите расчеты во всех столбцах таблицы.
Краткая справка. Расходы на рекламу осуществлялись в течение нескольких месяцев, поэтому выбираем динамический инвестиционный учет. Это предполагает сведение всех будущих платежей и поступлений путем дисконтирования на сумму рыночной ночной процентной ставки к текущему значению.
Рис. 1. Исходные данные для Задания 1
Формула для расчета:
А(n) = А(0) х (1 +j/12)(1-n),
в ячейке С6 наберите формулу:
= В6 * (1 + ставка/12) л (1 - $А6).
Примечание. Ячейка А6 в формуле имеет комбинированную адресацию: абсолютную адресацию по столбцу и относительную по строке и имеет вид — $А6.
При расчете расходов на рекламу нарастающим итогом надо учесть, что первый платеж равен значению текущей стоимости расходов на рекламу, значит, в ячейку D6 введем значение = С6, но в ячейке D7 формула примет вид: = D6 + С7. Далее формулу ячейки D7 скопируйте в ячейки D8:D17.
Обратите внимание, что в ячейках нарастающего итога с мая по декабрь будет находиться одно и то же значение, поскольку после мая месяца расходов на рекламу не было (рис. 2).
Выберем сумму покрытия в качестве ключевого показателя Целесообразности инвестиций в рекламу. Она определяет, сколько Денежных средств приносит продажа единицы товара в копилку возврата инвестиций.
Для расчета текущей стоимости покрытия скопируйте формулу из ячейки С6 в ячейку F6. В ячейке F6 должна быть формула
= Е6 * (1 + ставка/12) л (1 - $А6).
Далее с помощью маркера автозаполнения скопируйте формулу в ячейки F7:F17.
Сумма покрытия нарастающим итогом рассчитывается аналогично расходам на рекламу нарастающим итогом, поэтому в ячейку G6 поместим содержимое ячейки F6 (= F6), а в G7 введем формулу:
= G6 + F7.
Рис. 2. Рассчитанная таблица оценки рекламной компании
Далее формулу из ячейки G7 скопируем в ячейки G8:G17. В последних трех ячейках столбца будет представлено одно и то же значение, ведь результаты рекламной компании за последние три месяца на сбыте продукции уже не сказывались.
Сравнив значения в столбцах D и G, уже можно сделать вывод о рентабельности рекламной компании, однако расчет денежных потоков в течение года (колонка Н), вычисляемый как разница колонок G и D, показывает, в каком месяце была пройдена точка окупаемости инвестиций. В ячейке Н6 введите формулу:
= G6 - D6,
и скопируйте ее вниз на весь столбец.
Проведите условное форматирование результатов расчета колонки Н: отрицательных чисел — синим курсивом, положительных чисел — красным цветом шрифта. По результатам условного форматирования видно, что точка окупаемости приходится на июль месяц.
4. В ячейке Е19 произведите расчет количества месяцев, в которых имеется сумма покрытия (используйте функцию «Счет» {Вставка/Функция/Статистические), указав в качестве диапазона «Значение 1» интервал ячеек Е7: Е14). После расчета формула в ячейке Е19 будет иметь вид = СЧЕТ(Е7:Е14).
Рис. 3. Расчет функции СЧЕТЕСЛИ
5. В ячейке Е20 произведите расчет количества месяцев, в которых сумма покрытия больше 100 000 р. (используйте функцию СЧЕТЕСЛИ, указав в качестве диапазона «Значение» интервал ячеек Е7:Е14, а в качестве условия > 100000) (рис. 3). После расчета формула в ячейке Е20 будет иметь вид = СЧЕТЕЕСЛИ(E7: Е14).
6. Постройте графики по результатам расчетов (рис. 4): «Сальдо дисконтированных денежных потоков нарастающим итогом» — по результатам расчетов колонки Н;
«Реклама: доходы и расходы» — по данным колонок D и G (диапазоны D5: D17 и G5: G17 выделяйте, удерживая нажатой клавишу [Ctrl]).
Графики дают наглядное представление об эффективности расходов на рекламу. Они графически показывают, что точка окупаемости инвестиций приходится на июль месяц.
7.Сохраните файл в папке вашей группы.
Рис. 4. Графики для определения точки окупаемости инвестиций