Порядок работы. Тема: Экономические расчеты в MS EXCEL

Тема: Экономические расчеты в MS EXCEL

Лабораторная работа № 5

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

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

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

2. Создайте таблицу оценки рекламной кампании по образцу таблица 5.1. Введите исходные данные: Месяц, Расходы на рекламу А(0) (р.), Сумма покрытия В(0) (р.), Рыночная процентная ставка = 13,7%, Текущая стоимость расходов на рекламу А(n), текущая стоимость суммы покрытия В(n).

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

Таблица 5.1

Исходные данные для Задания 5.1

Месяц (n)	Расходы на рекламу А(0) (руб)	Аn (руб)	Расходы на рекламу нарастающим итогом	В(0) (руб)	В (n)	Сумма покрытия нарастающим итогом	Сальдо дисконтированных денежных потоков нарастающим итогом

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

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

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

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

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

Помните, что по умолчанию имена являются абсолютными ссылками.

3. Произведите расчеты во всех столбцах таблицы.

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

Формулы для расчета: А(n) = А(0) * (1 + j/12)(l-n), ячейке С6 наберите формулу = В6 * (1 + ставка/12)^(1 – $А6).

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

При расчете расходов на рекламу нарастающим итогом надо учесть,

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

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

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

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

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

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

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

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

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

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

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

Рис. 5.1 – Расчет функции СЧЕТЕСЛИ

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

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

1. Сохраните файл в папке вашей группы.

Задание 5.2. Фирма поместила в коммерческий банк 45000 р. на 6 лет под 10,5 % годовых. Какая сумма окажется на счете, если проценты начисляются ежегодно? Рассчитать, какую сумму надо поместить в банк на тех же условиях, чтобы через 6 лет накопить 250 000 р.?

Дата добавления: 2017-01-14