ИНФОРМАЦИОННЫЕ СИСТЕМЫ В ЭКОНОМИКЕ

ИНФОРМАЦИОННЫЕ СИСТЕМЫ В ЭКОНОМИКЕ

Лабораторный практикум

Лабораторный практикум содержит практический материал по освоению программ Microsoft Excel и Microsoft Access для решения финансово-экономических задач. Учебное пособие адресовано преподавателям и студентам вузов, обучающимся по экономическим специальностям.

СОДЕРЖАНИЕ:

ВВЕДЕНИЕ...................................................................................... 4

РАЗДЕЛ 1. Использование возможностей программы

MICROSOFT EXCEL при решении финансово-экономических задач 6

Тема 1. Построение таблиц и графическое представление

данных в программе Microsoft Excel.................................... 6

Тема 2. Работа с базами данных в Microsoft Excel............. 13

Тема 3. Прогноз с использованием линии тренда.............. 19

Тема 4. Режим Подбора параметров................................... 24

Тема 5. Логические функции Microsoft Excel..................... 32

Тема 6. Финансовые функции Microsoft Excel.................... 42

Тема 7. Решение задач с использованием Таблицы

подстановок.......................................................................... 56

Тема 8. Программа оптимизации «Поиск решения»......... 63

РАЗДЕЛ 2. Работа с базами данных в MICROSOFT ACCESS 75

Тема 1. Освоение функций программы Microsoft Access... 75

Тема 2. Создание таблиц в Microsoft Access...................... .77

· Создание таблиц в режиме «Мастер таблиц»........ 78

· Создание таблиц в режиме «Конструктор»............ 84

· Создание связей между таблицами......................... 85

· Использование свойств поля для настройки

параметров ввода данных в таблицы........................... .91

· Создание маски ввода.............................................. 91

· Создание списка для поля таблицы........................ 94

Тема 3. Создание запросов в Microsoft Access................... 98

РАЗДЕЛ 3. Создание макросов................................................ 102

Задания для проверки остаточных знаний
по пройденному материалу........................................................ 106

Задания для контрольных работ для студентов заочной формы обучения 116

Примерные вопросы к зачету и экзамену.................................. 131

Список литературы..................................................................... 134

ВВЕДЕНИЕ

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

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

Целью разработки настоящего учебного пособия является организация лабораторного практикума по дисциплине «Информационные системы в экономике» на базе программ Microsoft Excel, Microsoft Access.

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

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

Изучение программ Microsoft Excel и Microsoft Access начинается с простейших задач, в процессе решения которых студент знакомится с интерфейсом программ и с легкостью осваивает основные приемы работы в программах. Поэтапно задания приобретают экономическую направленность, это позволяет применять полученные знания для решения более сложных экономических задач, в разных областях управленческой сферы деятельности: бухгалтерской, банковской, налоговой, инвестиционной и др.

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

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

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

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

Лабораторный практикум рекомендуется преподавателям и студентам вуза всех форм обучения, изучающих курс «Информационные системы в экономике».

РАЗДЕЛ 1. ИСПОЛЬЗОВАНИЕ ВОЗМОЖНОСТЕЙ

ПРОГРАММЫ MICROSOFT EXCEL ПРИ РЕШЕНИИ ФИНАНСОВО-ЭКОНОМИЧЕСКИХ ЗАДАЧ

ТЕМА 1. ПОСТРОЕНИЕ ТАБЛИЦ И ГРАФИЧЕСКОЕ ПРЕДСТАВЛЕНИЕ ДАННЫХ В ПРОГРАММЕ MICROSOFT EXCEL

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ ПО ТЕМЕ 2

Задача 1. Создайте таблицу, содержащую следующие сведения о сотрудниках ор­ганизации (см.: рисунок 1.13).

Отсортируйте записи по: 1). алфавиту фамилий, 2). уменьшению разряда. Выдайте список сотрудников организации:

а). Проживающих в городе Пятигорске,

б). Чей телефон начинается на 34.

в). Проживающих в Пятигорске, старше 25 лет на момент осуществления поиска информации, принятых после 16.04.2000.

Рис. 1.13. Данные о сотрудниках

Задача 2.Предприятие «Альфа» осуществляет оптовую реализацию бытовой техники со складов. Имея сведения о количестве проданной продукции в феврале, определите суммы выручки предприятия за месяц. Создайте таблицу, отражающую реализацию кофеварок и миксеров за месяц. Создайте таблицу, показывающую все поставки предприятия «Бета» ООО «Авангард» с 15.02.03 на сумму превышающую 2000000 руб.

Рис. 1.14 Продажи предприятия «Альфа» за февраль

Пример.

Задание.Используя данные о доходах бюджета г. Пятигорска за 1999 – 2002 годы рассчитайте темп прироста доходов, общую сумму доходов за каждый год и структуру налоговых доходов по периодам. Спрогнозируйте итоговую сумму налогов на 2004 год (постройте линию тренда). Исходные данные смотрите в таблице на рис. 1.15.

Рис. 1.15. Доходы бюджета г. Пятигорска за 1999 – 2002 годы

Решение. Для того чтобы построить линию тренда необходимо сначала построить график, отражающий динамику итоговой суммы налогов за каждый год. Для этого на панели инструментов нажмите кнопку Мастер диаграмм, в появившемся окне установите курсор в строке Диапазон, нажмите клавишу CTRL и, удерживая ее, выделите ячейки, содержащие итоговые суммы налогов за каждый год и ячейки, содержащие обозначения годов. Нажмите ДАЛЕЕ, ГОТОВО. Получили график в виде восходящей кривой линии соединяющей четыре точки (рис. 1.16).

Рис. 1.16. Линия тренда

Для того, чтобы построить линию тренда, наведите курсор на кривую и нажмите правую клавишу мыши, в появившемся контекстном меню выберите. Добавить линию тренда. Выберите Линейный тип. Откройте закладку ПАРАМЕТРЫ. Установите Прогноз вперед на 2 периода, поставьте галочку напротив условия Поместить на диаграмму величину достоверности аппроксимации, ОК (рис. 1.17).

Рис. 1.17. Параметры линии тренда

На графике появилась прямая линия, показывающая, что в 2004 году в бюджет поступит около 990 000 тыс. рублей. Коэффициент достоверности аппроксимации составляет 0,97. Это свидетельствует о том, что вероятность получения спрогнозированной суммы налогов составляет 97% (рис. 1.16).

Контрольные вопросы

Для чего используется линия тренда?

Что показывает коэффициент достоверности аппроксимации?

Что необходимо сделать, прежде чем строить линию тренда?

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ ПО ТЕМЕ 3

Задача 1. Проанализируйте продажи макаронных изделий за 3 мес. с помощью программы Microsoft Excel. Исходные данные приведены в таблице на рис.1.18. Рассчитайте значения пустых ячеек. Определите объем продаж лапши в июне с помощью линии тренда, определите коэффициент достоверности прогноза.

Рис. 1.18. Продажи макаронных изделий за первый квартал

Задача 2. Проанализируйте расходы на коммунальные услуги за 3 мес. с помощью программы Microsoft Excel. Исходные данные приведены в таблице на рис. 1.19.

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

Рис. 1.19. Перечень расходов на коммунальные услуги

.

Задача 3. Определите товарооборот за год, рассчитайте сумму налога, которая равна 15% от суммы товарооборота. Определите чистую прибыль. Спрогнозируйте товарооборот предприятия за май 2004 года. Исходные данные смотрите в таблице на рис. 1.20.

Чистая прибыль _________

Рис. 1.20. Товарооборот предприятия по филиалам за год

Задача 4. Рассчитайте темпы прироста (фактический год/предыдущий) и структуру доходов (удельный вес каждого вида налога в общей сумме) для каждого года. Постройте график, показывающий изменения общей суммы налоговых доходов. Постройте линию тренда и определите, сколько поступит средств в бюджет в 2005 году.

Рис. 1.21. Налоговые поступления в бюджет

Примеры.

Задание 1. Используя режим Подбора параметра, определите, при каком зна­чении процента премии общая сумма месячной заработной платы всех сотрудников организации, предназначенная к выдаче, будет равна 250 000 руб.

Исходные данные этого примера приведены на рис. 1.22, где знаком «?» отмечены ячейки с расчетны­ми данными.

Решение. Создайте таблицу расчета заработной платы по образцу (рис. 1.22).

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

При расчёте столбца "Премия" используйте формулу Премия = Оклад * % Премии, (=$D$5*С6), обратите внимание, что ячейка D5 используется в виде абсолютной адресации и строки и столбца. После набора формулу следует скопировать вниз по столбцу автозаполнением.

Рис. 1.22. Ведомость начисления заработной платы

Формула для расчёта столбца «Всего начислено»: Всего на­числено = Оклад + Премия.

Формула для расчёта столбца «Удержания»: Удержания = Всего начислено * % Удержаний, в ячейке F6 наберите =$F$5*Е6. Далее эта формула копируется вниз по столбцу F автозаполнением.

Формула для расчёта столбца «К выдаче»: К выдаче = Все­го начислено — Удержания.

По столбцу К выдаче рассчитайте среднее значение, найдите максимальное значение и минимальное значение. Для этого воспользуйтесь функциями «СРЗНАЧ», «МАКС», «МИН» соответственно. Откройте окно Мастер функций, выберите категорию – СТАТИСТИЧЕСКИЕ, функцию СРЗНАЧ. В строку 1 введите диапазон - столбец К выдаче (без итоговой суммы). Аналогично работают функции «МАКС», «МИН».

Результаты работы и итоговый вид таблицы для расчёта за­работной платы представлены на рис. 1.23.

Из расчетов видно, что общая сумма к выдаче при указанных окладах и пре­мии в размере 27 % составляет 104 799,77 руб. Теперь осуществите подбор параметра командой, для чего установите курсор в ячейке об­щей суммы К выдаче и воспользуйтесь менюСЕРВИС - командой Подбор параметра. Откроется окно, как на рис. 1.24.

Рис.1.23. Ведомость начисления заработной платы

Рис. 1.24. Подбор параметра Рис. 1.25. Подбор параметра

В диалоговом окне «Подбор параметра» на первой строке Установить в ячейке в качестве подбираемого параметра должен нахо­диться адрес той ячейки, в которой необходимо получить заданное значение. В данном случае это общая итоговая сумма зарплаты к выдаче (ячей­ка G20, на которой установлен курсор). На второй строке у поля «Значение» диалогового окна «Подбор параметра» наберите желаемое значение параметра, в данном примере это число равно 250 000, на третьей строке в поле «Изменяя значение ячейки» укажите адрес подбираемого значения «% Премии» (ячейка D5), после чего нажмите кнопку ОК (рис. 1.25).

Произойдёт почти моментальный пересчёт всей таблицы, и от­кроется окно «Результат подбора параметра» (рис. 1.26), в котором дайте подтверждение подобранному параметру нажатием ОК.

Итак, произошёл обратный пересчёт «% Премии». Результа­ты подбора выглядят так: если сумма к выдаче равна 250 000 руб., то процент премии должен быть 203%.

Рис 1.26. Таблица расчета заработной платы

Задание 2. Используя режим подбора параметра, определите штатное распи­сание фирмы. Исходные данные приведены на рисунке 1.27.

Известно, что в штате фирмы состоят:

6 курьеров;

8 младших менеджеров:

10 менеджеров;

3 заведующих отделами:

1 главный бухгалтер;

1 программист;

1 системный аналитик:

1 генеральный директор фирмы.

Общий месячный фонд зарплаты составляет 100 000 руб. Не­обходимо узнать, какими должны быть оклады сотрудников фирмы.

Решение.Каждый оклад является линейной функцией от оклада курье­ра, а именно: зарплата равна А * Z + В,

где Z — оклад курьера;

А и В — коэффициенты, показывающие соответственно, во сколь­ко раз превышается значение Z и на сколько превышается значе­ние Z.

Создайте таблицу штатного расписания фирмы по приведён­ному на рис. 1.27 образцу. Введите исходные данные в рабочий лист электронной книги.

Рис. 1.27. Штатное расписание фирмы (таблица исходных данных)

В столбце Коэф. В указаны надбавки к окладам.

Выделите отдельную ячейку D17 для зарплаты курьера (пере­менная Z) и с учётом этого задайте все расчёты. В ячейку D17 вре­менно введите произвольное число, например 500.

В столбце Зарплата сотрудника введите формулу для расчёта заработной платы по каждой должности. Далее скопируйте формулу из ячейки E4 вниз по столбцу автозаполнением.

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

Произведите подбор зарплат сотрудников фирмы для сум­марной заработной платы в размере 100 000 руб. Для этого в строке МЕ­НЮ выберите СЕРВИС - ПОДБОР ПАРАМЕТРА.

В поле Установить в ячейке появившегося окна введите ссылку на ячейку G12, содержащую формулу расчёта фонда зара­ботной платы; в поле Значение наберите искомый результат — 100000; в поле Изменяя значение ячейки введите ссылку на из­меняемую ячейку D17, в которой находится значение зарплаты курьера, и щелкните на кнопке ОК. Произойдёт обратный расчет зарплаты сотрудников по заданному условию при фонде зарпла­ты, равном 100 000 руб. (рис. 1.28).

Рис. 1.28. Штатное расписание фирмы

Итог обработанного пересчета таков: при фонде зарплаты, равном 100 000 руб., зарплата курьера должна составлять 1289,66 руб.

Следует заметить, что если возникает необходи­мость копировать результаты, полученные подбо­ром параметра, то следует производить копиро­вание полученных данных в виде значений с ис­пользованием СПЕЦИАЛЬНОЙ ВСТАВКИ.

Для этого необходимо выделить ко­пируемые данные, произвести запись в буфер па­мяти(ПРАВКА > КОПИРОВАТЬ), установить курсор в соответствующую ячейку, задать режим специ­альной вставки(ПРАВКА >СПЕЦИАЛЬНАЯ ВСТАВКА),отметив в качестве объекта вставки "значения" (ПРАВКА — СПЕЦИАЛЬНАЯВСТАВКА — ЗНАЧЕНИЯ)(рис. 1.29).

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

Рис. 1.29. Специальная вставка

Контрольные вопросы

Для решения каких задач используется режим ПОДБОРА ПАРАМЕТРОВ?

Адрес какой ячейки должен находиться в диалоговом окне «Подбор параметра» в первой строке?

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

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ ПО ТЕМЕ 4

Задача 1. Имея исходные данные, рассчитайте сумму премии и сумму к выдаче. При каком проценте премии общая сумма к выдаче будет равна 100000 руб. Рассчитайте средний доход за месяц.

Рис. 1.30. Ведомость заработной платы

Задача 2. Рассчитайте сумму оклада (МРОТ*коэф*разряд), сумму премии (50% от оклада), удержаний в пенсионный фонд (28% от оклада + премия) и сумму к выдаче. Определите, каким должен быть коэффициент, чтобы итоговая сумма к выдаче составила 200000 руб. Исходные данные приведены в таблице на рис. 1.31.

Рис. 1.31. Штат сотрудников фирмы

Таблица 1

Аргументы финансовых функциц

q Функция БЗ. Определение будущей стоимости на основе постоянной процентной ставки.

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

Синтаксис БЗ (норма, число_периодов, выплата, нз, тип).

1) Будущую стоимость единой суммы вклада, по которой начисляются сложные проценты за определенное число периодов, можно рассчитать по формуле:

fv=pv*(1+r)ⁿ

где fv - будущая стоимость вклада или займа,

pv - текущая стоимость вклада или займа;

п - общее число периодов начисления процентов,

r - процентная ставка по вкладу или займу.

Для вычисления будущего значения единой суммы функция БЗ используется в виде:

=БЗ(норма, число_периодов, , нз).

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

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

Для расчета будущей стоимости серии фиксированных периодических платежей пренумерандо используется формула:

fv = ртt * (1+r) + pmt * (1+r)² + ... + pmt(1+r)ⁿ

= pmt * ((1+r)ⁿ –1)/r* (1+r)

где fv - будущая стоимость фиксированных периодических платежей,

pmt - фиксированная периодическая сумма платежа,

n - общее число периодов выплат;

r - постоянная процентная ставка .

Функция БЗ в данном случае используется в виде

=БЗ(норма, число_периодов, выплата, , 1).

Для расчета будущей стоимости серии фиксированных периодических платежей постнумерандо используется формула;

fv = ртt + ртt*(1+r) + ... + pmt (1+r)^n-1 = pmt*((1+r)ⁿ –1)/r

Функция БЗ в данном случае используется в виде:

=БЗ(норма, число_периодов, выплата, , 0)

или

=БЗ(норма, число_периодов, выплата).

Примеры.

Задание 1. Рассчитать, какая сумма окажется на счете, если 27 тыс. руб. положены на 33 года под 13,5% годовых. Проценты начисляются каждые полгода.

Решение. Произведите расчеты двумя способами: используя финансовую формулу и с помощью финансовой функции БЗ. Для того чтобы осуществить расчет по формуле в ячейку А1 введите следующее выражение:= 27* (1+0,135/2)^(33*2), в результате получите 2012.07 тыс. руб. Для того чтобы решить задачу используя функцию БЗ, наведите курсор на ячейку А2, на панели инструментов нажмите кнопку , откроется окно Мастер функций (рис. 1.46).

Рис 1.46. Окно мастера функций

Выберите категорию Финансовые, далее в поле Выберите функцию – функцию БЗ или БС. В открывшемся окне введите в соответствующие строки имеющиеся данные следующим образом:

Рис. 1.47. Функции БС

По условию задачи сказано, что проценты начисляются каждые полгода, поэтому необходимо значение годового процента разделить на два, в случае с периодом вклада все наоборот, количество лет надо умножить на два (рис. 1.47). Сумма первоначального вклада записывается со знаком минус, так как эта сумма является исходящим денежным потоком. В результате получили ответ = 2012,07 тыс. руб. Таким образом решение задачи двумя способами дало результат равный 2012,07 тыс.руб.

Задание 2. Есть два варианта инвестирования средств в течение 4 лет: в начале каж­дого года под 26% годовых или в конце каждого года под 38% годовых. Ежегодно вносит­ся 300 тыс. руб. Определить, сколько денег окажется на счете в конце 4-го года для каж­дого варианта.

Решение. Расчет ведется по формуле

fv = 300 *((1+0.26)^4-1)/0.26* (1+0,26) =2210.53 - для первого варианта и по формуле

fv = 300 *((1+0.38)^4-1)/0.38 =2073.74 - для второго

Используя функцию БЗ, получите:

Б3(26%, 4, -300, , 1) = 2210,53 тыс. руб. - для первого варианта,

Б3(38%,4,-300)=2073,74тыс.руб. - для второго варианта.

Расчеты показали, что первый вариант предпочтительнее.

q Определение текущей стоимости. Функция ПЗ.

Во многих задачах используется понятие текущей (современной) стоимости буду­щих доходов и расходов. Текущая стоимость получается как результат приведе­ния будущих доходов и расходов к начальному периоду времени (т.е. путем дисконтиро­вания).

Расчет при помощи функции ПЗ требует денежных потоков равной величины и рав­ных интервалов между операциями. Функция НПЗ допускает денежные потоки перемен­ной величины через равные периоды времени.

Синтаксис П3( норма, кпер, выплата, бс, тип).

Рассмотрим различные варианты использования этой функции при решении кон­кретных задач.

1) Допустим, известно будущее значение вклада (займа). Требуется определить текущее значение этого вклада, т.е. сумму, которую необходимо положить на счет сего­дня, чтобы в конце n-го периода она достигла заданного значения. Это значение можно получить из формулы:

pv=fv/(1+r)ⁿ

Такой же расчет при использовании функции ПЗ в общем виде запишется так

= П3(норма, кпер, , бс).

2) Расчет текущей стоимости серии будущих постоянных периодических плате­жей, производимых в начале каждого периода и дисконтированных нормой дохода r, ве­дется по формуле:

pv = ртt + ртt/(1+r) +... + pmt/(1+r)^n-1 =pmt(1-1/(1+r)ⁿ)/r*(1+r)

где pv - текущая стоимость серии фиксированных периодических платежей,

pmt - фиксированная периодическая сумма платежа,

п - общее число периодов выплат (поступлений),

r - постоянная процентная ставка.

Для расчета этой величины функция ПЗ используется в виде:

=ПЗ(норма, кпер, выплата, , 1).

3) Для расчета текущей стоимости постоянных периодических выплат, если они происходят в конце периода, используется формула:

pv = ртt/(1+r) + ртt/(1+r)² +...+ pmt/(1+r)ⁿ =pmt(1-1/(1+r)ⁿ)/r

Соответствующая этому расчету формула в EXCEL имеет вид:

=ПЗ(Hоpмa, кпер, выплата).

По умолчанию аргументтип равен 0, поэтому его можно не указывать.

Примеры.

Задание 1. Фирме потребуется 5000 руб. через 12 лет, В настоящее время фирма располагает деньгами и готова положить их на депозит единым вкладом, чтобы через 12 лет он достиг 5000 руб. Определить необходимую сумму текущего вклада, если ставка про­цента по нему составляет 12% в год.

Решение.Для расчета используем формулу (2.1) или следующую функцию:

П3( 12%, 12,,5000)= -1283,38руб.

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

Задание 2. Рассматривается два варианта покупки дома: заплатить сразу 99000 руб. или в рассрочку - по 940 руб. ежемесячно в течение 15 лет. Определить, какой вариант предпочтительнее, если ставка процента - 8% годовых.

Решение.В задаче необходимо сравнить, что выгоднее: заплатить сегодня указанную сумму или растянуть платежи на определенный срок. Для сравнения следует привести эти денежные потоки к одному периоду времени, т.е. рассчитать текущую стоимость будущих фиксированных периодических выплат. Допустим, что выплаты происходят в конце каж­дого расчетного периода, Расчет можно вести по формуле (2.3) или используя функцию ПЗ:

П3(8%/12, 15*12, -940) = 98362,16 руб.

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

□ Определение текущей стоимости. Функция НПЗ.

Функция НПЗ вычисляет чистую текущую стоимость (NPV) периодических плате­жей переменной величины как сумму ожидаемых расходов и доходов, дисконтированных нормой процента r.

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

Синтаксис НПЗ(норма, сумма1, сумма2,..., cyммaN).

Считается, что инвестиция, чистую текущую стоимость которой вычисляет функция НПЗ, начинается за один период до даты аргументасумма1 и заканчивается с последним значением в списке. Если первый денежный взнос приходится на начало первого периода, то первое значение следует добавить (вычесть, если это затраты) к результату функции НПЗ, но не включать в список аргументов

Примеры

Задание 1.Инвестиции в проект к концу первого года его реализации составят 10000 руб. В последующие три года ожидаются годовые доходы по проекту 3000 руб., 4200 руб., 6800 руб. Издержки привлечения капитала 10%. Рассчитать чистую текущую стоимость проекта.

Решение.Так как инвестиция размером 10000 руб. относится не к начальному мо­менту, на который производится расчет, то это значение следует включить в список аргу­ментов. Поскольку этот денежный поток движется "от нас", то сумма 10000 руб. записывается со знаком “-“. Остальные денежные потоки представляют доходы, поэтому имеют знак "+". Чистый текущий объем инвестиции составит:

НПЗ(10%, -10000, 3000, 4200, 6800) = 1188,44 руб

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

Задача 2.Допустим, затраты на проект в начальный момент его реализации состав­ляют 37000 руб., а ожидаемые доходы за первые пять лет: 8000 руб , 9200 руб., 10000 руб., 13900 руб. и 14500 руб. На шестой год ожидается убыток в 5000 руб. Цена капитала 8% годовых. Рассчитать чистую текущую стоимость проекта.

Решение.В этой задаче нет необходимости дисконтировать начальные затраты по проекту, так как они относятся к настоящему моменту, и их текущая стоимость равна 37000 руб. Для сравнения затрат с будущими доходами и убытками последние необходи­мо привести к начальному моменту. Если доходы ввести в ячейки В1:В5 соответственно, чистая текущая стоимость проекта составит:

НПЗ(8%, B1:B5, -5000) - 37000 = 3167.77 руб.

q Определение срока платежа. Функция КПЕР.

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

Синтаксис. КПЕР(норма, выплата, нз, бс, тип).

Функция может применяться в следующих расчетах.

1) Если рассчитывается общее число периодов начисления процентов, необходи­мых для того, чтобы начальная сумма размером НЗ достигла будущего значения БС, то функция используется в виде:

КПЕР(норма, , нз, бс).

2) Формула для расчета общего числа периодов, через которые совокупная величи­на фиксированных периодических выплат составит значение БС, получается в виде:

КПЕР(норма, выплата, , бс, 1)

если платежи производятся в начале каждого расчетного периода, и в виде:

КПЕР(норма, выплата, , бс,)

для выплат в конце периода.

3) При погашении займа размером НЗ равномерными постоянными платежами в конце каждого периода число периодов, через которое произойдет полное погашение, равно

КПЕР( норма, выплата, нз).

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

Примеры.

Задание 1. Рассчитать, через сколько лет вклад размером 1 тыс. руб. достигнет вели­чины 1 млн. руб., если годовая ставка процента по вкладу 16.79% и начисление процентов производится ежеквартально.

Решение.Функция КПЕР дает следующий резуль­тат:

КПЕР(16,79% / 4, , -1, 1000) = 168 - это число кварталов.

Число лет составит 168 / 4=42.

Задание 2.Для обеспечения будущих расходов создается фонд. Средства в фонд по­ступают в виде постоянной годовой ренты постнумерандо. Размер разового платежа 16 тыс. рублей. На поступившие взносы начисляется 11,18% годовых. Необходимо опреде­лить, когда величина фонда будет равна 100 тыс. рублей.

Решение. Для решения задачи необходимо вычислить величину n. В EXCEL этот расчет выглядит так

КПЕР(11.18%,-16,, 100)=5, т. е, через 5 лет совокупная величина выплат составит 100 тыс. руб.

Задание 3. Ожидается, что ежегодные доходы от реализации проекта составят 33 тыс. руб. Необходимо рассчитать срок окупаемости проекта, если инвестиции к началу посту­пления доходов составят 100 тыс руб., а норма дисконтирования 12.11%

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

КПЕР(12.11%,33,-100)=4 года.

q Расчет процентной ставки. Функция НОРМА.