Главная | Обратная связь

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

Тема: ЭКОНОМИЧЕСКИЕ РАСЧЕТЫ В MICROSOFT EXCEL

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

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

 

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

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

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

 

 

Рис. 15.1 Исходные данные для задания 15.1

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

 

Краткая справка. Присваивание имени ячейке или группе ячеек производится следующим образом:

1. выделите ячейку (группу ячеек), которой необходимо при­своить имя;

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

3.введите имя ячейки;

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

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

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

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

Формула для расчета:

А(n) = А(0) Н (1 + j/12)^(1-n)

в ячейке С6 наберите формулу =В6*(1+ставка/12)^(1-$А6).

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

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

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

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

Для расчета текущей стоимости покрытия скопируйте формулу из ячейки С6 в ячейку F6. В ячейке F6 должна быть формула

=Е6*( 1 +ставка/12)^( 1 -$А6).

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

 

Рис. 15.2. Рассчитанная таблица оценки рекламной кампании

 

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

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

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

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

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

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

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

 

6. Постройте графики по результатам расчетов (рис. 15.4): — «Сальдо дисконтированных денежных потоков нарастающим итогом» по результата расчетов колонки Н;

Рис. 15.4. Графики для определения точки окупаемости инвестиций

— «Реклама: доходы и расходы» по данным колонок D и G (диа­пазоны D5:D17 и G5:G17 выделяйте, удерживая нажатой клавишу [Ctrl]).

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

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

 

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

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

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

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

Рис. 15.5. Исходные данные для задания 15.2

 

3. Произведите расчеты А(n) двумя способами:

• с помощью формулы A(n)= А(0) Н (l+j)ⁿ (в ячейку D10 ввести формулу =$В$3*(1+$В$4)^А10 или использовать функцию СТЕПЕНЬ);

• с помощью функции БС (рис. 15.6).

 

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

Синтаксис функции БС: БС (ставка; кпер; плт; пс; тип), где ставка — это процентная ставка за период; кпер — это общее число периодов платежей по аннуитету; плт (плата) — это выплата, про­изводимая в каждый период, вводимая со знаком «-»; это значение не может меняться в течение всего периода выплат. Обычно плата состоит из основного платежа и платежа по процентам, но не вклю­чает других налогов и сборов; пс — это приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей. Если аргумент пс опущен, то он полагается равным 0. В этом случае должно быть указано значе­ние аргумента плата.

Тип — это число 0 или 1, обозначающее время, когда должна про­изводиться выплата. Если аргумент тип опущен, то он полагается рав­ным 0 (0 — платеж в конце периода, 1 — платеж в начале периода).

Все аргументы, означающие деньги, которые уплачены (напри­мер, депозитные вклады), представляются отрицательными числа­ми. Деньги, которые получены (например, дивиденды), представля­ются положительными числами.

Для ячейки С10 задание параметров расчета функции БС имеет вид как на рис. 15.6.

Конечный вид расчетной таблицы приведен на рис. 15.7.

Рис. 15.6. Задание параметров функции БС

 

Рис. 15.7. Результаты расчета накопления финансовых средств фирмы (задание 15.2)

 

Рис. 15.8. Подбор значения суммы вклада для накопления 250 ООО руб.

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

В результате подбора выясняется, что первоначальная сумма для накоп­ления 137 330,29 руб. позволит нако­пить заданную сумму 250 000 руб.