Экономические расчёты в МС Excel

Практическая работа 14

Тема: Экономические расчёты в МС Excel

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

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

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

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

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

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

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

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

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

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

* Нажмите клавишу (Enter )

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

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

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

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

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

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

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

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

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

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

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

Далее формулу из ячейки G7 скопируем в ячейки G8/G17.

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

Сравнив значения в столбцах Д и Г , уже можно сделать вывод о рентабельности рекламной кампании, одна расчёт денежных потоков в течении года ( Колонка H ), вычисляемый как разница колонок Г и Д, показывает, в каком месяце была пройдена точка окупаемости инвестиций. В ячейке H6 введите формулу = Г6-Д6, и скопируйте её на всю колонку.

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

4.      4. В ячейке Е19 произведите расчёт кол-ва месяцев в которых сумма покрытия имеется ( используйте функцию Счёт (Вставка/Функция/Статистические), указав в качестве диапазона Значение 1 интервал ячеек Е7/Е14). После расчёта формула в ячейке Е19 будет иметь вид = Счёт( Е7/Е14 ).

5.      В ячейке Е20 произведите расчёт кол-ва месяцев, в которых сумма покрытия больше 100 000 р. ( используйте функцию Счетесли, указав в качестве диапазона Значение интервал ячеек Е20 будет иметь вид = Счетесли ( Е7/Е14) (рис. 14.3 )

6.      Постройте графики по результатам расчётов ( рис 14.4 )

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

Реклама: Расходы и доходы по данным колонок Д и Г ( диапазоны Д5/Д17 и Г5/Г17 выделяйте, удерживая нажатой клавишу Cntr.

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

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

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

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

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

2. Создайте таблицу констант и таблицу для расчёта наращенной  суммы вклада по образцу ( рис. 14.5 ) .

3.      Производите расчёты ! ( n) двумя способами: с помощью формулы А(n)=A(0)*(1+j)^n ( в ячейку Д10 ввести формулу =$B$3*(1+$B$4)^A10 или использовать функцию Степень.

с помощью функции Б3 ( см. рис 14.5 ).

Краткая справка. Функция Б3 возвращает будущее значение вклада на основе периодических постоянных платежей и постоянных процентной ставки.

Синтаксис функции Б3/Б3 (ставка; кпер ; плата ;тип), где ставка- это процентная ставка за период ; кпер- это общее число периодов выплати выплат годовой ренты; плата-это выплата, производимая в каждый период, вводится со знаком -, это значение не может меняться в течении всего периода выплат. Обычно плата состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов; нз- это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента.

Если аргумент нз опущен, то он полагается равным 0. В этом случае должно быть указано значение аргумента плата; тип- это число 0 или 1 , обозначающее, когда должна производиться выплата. Если аргумент тип опущен, то он полагается равным

4.  Используя режим Подбор параметра ( Сервис/Подбор параметра) рассчитайте, какую сумму надо поместить в банк на тех же условиях, чтобы через 6 лет накопить 250 000 р. Задание параметров подбора значения суммы вклада для накопления 250 000 р. Приведено на рис. 14.8. В результате подбора выясняется, что первоначальная сумма для накопления в 137 330,29 р. Позволит накопить заданную сумму в 250 000 р.

Дополнительное задание

Задание 14.3 Сравнить доходность размещения средств организации, положенных в банк на один год, если проценты начисляются в m раз в год, исходя из процентной ставки j= 9,5% годовых (рис 14.9 ); по результатам расчёта построить график изменения доходности инвестиционной операции от кол-ва раз начисления процентов в год.

Выясните, при каком значении j доходность составит 15%.

Краткая справка. Формула расчёта доходности

Доходность= (1+j/m)^m-1

Примечание. Установить формат значений доходности Процентный.

для проверки правильности ваших расчётов сравните полученный результат с правильным ответом:

для m=12 доходность =9,92%

Произведите обратный расчёт( используйте режим Подбора параметра) для выяснения, при каком значении j доходность ( при капитализации m=12) составит 15% ( рис 14.10 ) Правильный ответ: Доходность составить 15% при j=14,08%