Лабораторная работа №11
Цель занятия: Изучение технологии экономических расчетов в табличном процессоре.
Задание 1: Оценка рентабельности рекламной компании фирмы.
Порядок работы:
  1. Запустите редактор электронных таблиц MS EXCEL и создайте новую электронную книгу.
  2. Создайте таблицу оценки рекламной компании по образцу:
Введите исходные данные: Месяц, Расходы на рекламу А(0) (р.), Сумма покрытия В(0) (р.), Рыночная процентная ставка (j) = 13,7%. Выделите для рыночной процентной ставки, являющейся константой, отдельную ячейку С3, и дайте этой ячейке имя «Ставка».
  1. Произведите расчеты во всех столбцах таблицы.
Краткая справка. Расходы на рекламу осуществлялись в течение нескольких месяцев, поэтому выбираем динамический инвестиционный учет. Это предполагает сведение всех будущих платежей и поступлений путем дисконтирования на сумму рыночной процентной ставки к текущему значению.
Формулы для расчета:
A(n)=A(0)*(1+j/12)(1-n), в ячейке C6 наберите формулу
=$В$6*(1+ставка/12)^(1-$A6).
Примечание. Ячейка А6 в формуле имеет комбинированную адресацию: абсолютную адресацию по столбцу и относительную по строке, и записывается в виде $A6.
При расчете расходов на рекламу нарастающим итогом надо учесть, что первый платеж равен значению текущей стоимости расходов на рекламу, значит в ячейку D6 введем =С6, но в ячейке D7 формула примет вид =D6+C7. Далее формулу ячейки D7 скопируйте D8:D17.
Обратите внимание, что в ячейках нарастающего итога с мая по декабрь будет находиться одно и тоже значение, поскольку после мая месяца расходов на рекламу не было.
Выберем сумму покрытия в качестве ключевого показателя целесообразности инвестиций на рекламу. Она определяет, сколько приносит продажа единицы товара в копилку возврата инвестиций.
Для расчета текущей стоимости покрытия скопируйте формулу из ячейки С6 в ячейку F6. В ячейке F6 (=F6), а в G7 введем формулу =G6+F7.
Далее формулу из ячейки G7 скопируем в ячейки G8:G17. В последних трех ячейках столбца будет представлено одно и то же значение, ведь результаты рекламной компании за последние три месяца на сбыте продукции уже не сказывались.
Сравнив значения в столбцах D и G, уже можно сделать вывод о рентабельности рекламной компании, однако расчет денежных потоков в течение года (колонка Н), вычисляемый как разница колонок G и D, показывает, в каком месяце была пройдена точка окупаемости инвестиций. В ячейке H6 введите формулу =G6-D6, и скопируйте ее на всю колонку.
Проведите условное форматирование результатов расчета колонки Н: отрицательных чисел – синим курсивом, положительных чисел – красным цветом шрифта. По результатам условного форматирования видно, что точка окупаемости приходится на июль месяц.
  1. В ячейке Е19 произведите расчет количества месяцев, в которых сумма покрытия имеется, используйте функцию «Счет» (Вставка/Функция/Статистические), указав в качестве диапазона «Значение 1» интервал ячеек Е7:Е14). После расчета формула в ячейке будет иметь вид =СЧЕТ(Е7:Е14).
  2. В ячейке Е20 произведите расчет количества месяцев, в которых сумма покрытия больше 100000 р. (используйте функцию СЧЕТЕСЛИ, указав в качестве диапазона «Значение» интервал ячеек Е7:Е14, а в качестве условия >100000). После расчета формула в ячейке Е20 будет иметь вид =СЧЕТЕСЛИ(Е7:Е14).
  1. Постройте графики по результатам расчетов:
«Сальдо денежных потоков нарастающим итогом» по результатам расчетов колонки Н;
«Реклама: расходы и доходы» по данным колонки D и G (диапазоны D5:D17 и G5:G17 выделяйте, удерживая нажатой клавишу [CTRL]).
Графики дают наглядное представление об эффективности расходов на рекламу и графически показывают, что точка окупаемости инвестиций приходится на июль месяц.
  1. Сохраните файл в папке вашей группы.
Задание 2: Фирма поместила в коммерческий банк 45000 р. На 6 лет под 10,5% годовых. Какая сумма окажется на счете, если проценты начисляются ежегодно? Рассчитать, какую сумму надо поместить в банк на тех же условиях, чтобы через 6 лет накопить 250000 р.?
Порядок работы:
  1. Запустите редактор электронных таблиц MS EXCEL и создайте новую электронную книгу или перейдите на новый лист книги, созданной в задании 1.
  2. Создайте таблицу констант и таблицу для расчета наращенной суммы вклада по образцу:
  1. Произведите расчеты A(n) двумя способами:
с помощью формулы A(n)=A(0)*(1+j)n, для этого в ячейку B10 ввести формулу =$B$3*(1+$B$4)^A10.
С помощью функции БC.

Краткая справка. Функция БС возвращает будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки.
Конечный вид расчетной таблицы приведен на рисунке:
  1. Используя режим Подбор параметра (Сервис/Подбор параметра) рассчитайте, какую сумму надо поместить в банк на тех же условиях, чтобы через 6 лет накопить 250000 р. Задание параметров подбора значения суммы вклада для накопления 250000 р. Приведено на рисунке:
В результате подбора выясняется, что первоначальная сумма для накопления 137330,29 р. Позволит накопить заданную сумму в 250000 р.
Задание 3: Сравнить доходность размещения средств организации, положенных в банк на один год, если проценты начисляются m раз в год, исходя из процентной ставки j=9,5% по результатам расчета построить график изменения доходности инвестиционной операции от количества раз начис­ления процентов в году (капитализации).
Порядок работы:
Выясните, при каком значении j доходность (при капитализа­ции m= 12) составит 15%.
Краткая справка. Формула для расчета доходности Доходность = (1 + j/m)m- 1.
Примечание. Установите формат значений доходности — «Процентный».
Для проверки правильности ваших расчетов сравните получен­ный результат с правильным ответом: для m = 12 доходность = 9,92 %.
Произведите обратный расчет (используйте режим Подбор па­раметра) для выяснения, при каком значении j доходность (при капитализации m = 12) составит 15%.

Правильный ответ: доходность составит 15 % при  j = 14,08 %.
Хостинг от uCoz