В строке состояния будут отображаться сообщения процесса поиска решения, которые закончатся сообщением: Готово, после чего ячейки массива X будут заполнены данными найденного решения, и откроется окно Результаты поиска решения (Рис. 8).

Рис. 8. Результаты поиска оптимального решения

20. Сохранить сценарий найденного решения.

    1. Для этого:

       • В диалоговом окне Результаты поиска решения проверить установку переключателя в положение Сохранить найденное значение

       • Нажать кнопку Сохранить сценарий

       • В открывшемся диалоговом окне Сохранение сценария в поле Название сценария: ввести имя Сценарий и щелкнуть ОК. Произойдет возврат к окну Результаты поиска решения

21. Вывести отчет по результатам решения задачи.

    1. Для этого:

       • В списке Тип отчёта окна Результаты поиска решения выбрать строчку Результаты и нажать ОК. В результате будет создан новый лист с именем Отчёт по результатам 1.

22. Выполнить округление результатов до целых значений.

    1. Для этого:

       • Вернуться на лист Издержки и опт доставка, выделить в таблице поставок диапазон Bl1:F14 и удалить данные, нажав клавишу Del

       • В меню Сервис выбрать Сценарии и в открывшемся окне Диспетчер сценариев выделить имя сценария и нажать кнопку Изменить. Откроется окно Изменение сценария

       • В поле Изменяемые ячейки: этого окна должен быть указан диапазон Bl1:F14 и нажать кнопку ОК. Откроется окно Значения ячеек сценария

       • Листая в этом окне ячейки (с помощью вертикальной полоски прокрутки), округлить все данные до целых чисел и нажать ОК. Вновь откроется окно Диспетчер сценариев. Нажать в нём кнопку Вывести, и на месте старых значений массива X в таблице доставки вновь появятся значения вычисленных данных, но уже округлённые до целых чисел (Рис. 9). Щелкнуть на кнопке Закрыть

Рис. 9. Округленные результаты поиска оптимального решения

23. Создать отчет о результатах по сохраненному сценарию.

    1. Для этого:

       • Запустить Диспетчер сценариев (командой Сервис – Сценарии) и в окне Диспетчер сценариев нажать кнопку Отчет. Откроется окно Отчет по сценарию

       • Проконтролировать в этом окне положение переключателя Тип отчета (он должен быть в положении Структура)

       • Ввести в поле Ячейки результата: адрес В17 и нажать ОК. Будет вставлен лист с именем Структура сценария 1, на который будет выведен структурированный сценарий.

24. Построить диаграмму по результатам решения задачи оптимизации доставки товара.

    1. Для этого:

       • Открыть лист Издержки и опт доставка, выделить область оптимальных решений (массив X вместе с заголовками строк и столбцов, ячейки A10:F14) и запустить Мастер диаграмм (кнопкой Мастер диаграмм на панели инструментов Стандартная). Откроется окно первого шага работы Мастера

       • В этом окне выбрать (в панели Вид:) тип гистограммы – Гистограмма с накоплением, и нажать кнопку Далее. Откроется окно второго шага Мастера

       • На вкладке Диапазон данных этого окна проверить установку выделенного диапазона (в поле Диапазон:) и переключателя Ряды в: (должно быть установлено положение: строках) и нажать кнопку Далее. Откроется окно третьего шага работы Мастера

       • В этом окне ввести в поле Название диаграммы: Доставка товаров, в поле ось X (категорий): - Потребители (магазины). Нажать кнопку Далее. Откроется окно четвёртого шага Мастера

       • В этом окне проверить установку переключателя Поместить диаграмму на листе: (в положение отдельном:). Ввести в поле названия отдельного листа: Диаграмма и щелкнуть кнопку Готово. Появится диаграмма, представленная на Рис. 10.

Рис. 10. Диаграмма оптимальной доставки товаров

25. Сохранить изменения и выйти из Microsoft Excel.

Задание для самостоятельной работы Краткая справка: модель оптимизации использования кормов

Имеется п видов кормов себестоимостью в количестве . Обозначим оптимальное содержание j-гo корма в смеси – . Тогда общая стоимость j-гo корма в рационе животного составит , а задача будет сведена к определению значений , обеспечивающих минимум функции

при соблюдении условий полноценности питания.

Сформируем условие полноценности питания. Обозначим:

– необходимое количество i-гo элемента в полном рационе, – фактическое количество i-го элемента в единице j-го корма. Тогда условие полноценности можно записать так:

.

Оптимизируемая система имеет следующие ограничения:

• неотрицательность решения, то есть животные могут есть или не есть какой-то корм, но не могут его вырабатывать;

• количество потребляемых кормов не должно превышать установленного лимита, то есть ;

• количество видов кормов ;

• количество регламентируемых элементов питания .

Постановка задачи

Исходя из имеющихся ресурсов, частный предприниматель Иванов может использовать для кормления ослов не более 50 кг сена и не более 85 кг силоса в сутки. При этом рацион должен обладать определенной питательностью: общее число кормовых единиц – не менее 30, содержание белка – не менее 1000 г, кальция – не менее 100 г, фосфора – не менее 80 г.

Данные о питательной ценности и себестоимости одного килограмма сена и силоса представлены в Табл. 1.

Табл. 1. Данные о питательной ценности и себестоимости продуктов

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

Математическая модель

Введем переменные:

- количество сена в суточном рационе;

- количество силоса в суточном рационе.

Из Табл. 1 видно, что питательная ценность 1 кг сена составляет 0,5 ед., следовательно, питательная ценность всего сена в суточном рационе составляет . Аналогично питательная ценность силоса в суточном рационе – . Из условия следует, что суммарная ценность кормов должна быть не менее 30 ед., т. е. по количеству кормовых единиц можно записать неравенство:

.

Аналогично можно записать неравенства для белка, кальция и фосфора:

,

,

.

В задаче ограничены ресурсы кормов: расход сена не должен превышать 50 кг, а силоса – 85 кг, т. е.:

.

Целевая функция, определяющая стоимость суточного рациона:

.

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

.

Замечание. Рекомендуется оформить рабочий лист в следующей форме:

Отладка модели

В результате должны получиться значения, представленные на Рис. 11.

Рис. 11. Оптимальное решение задачи

Практическое занятие №5. Оптимальные технологии ввода данных. Подведение итогов. Сводные таблицы Технология работы

1. Открыть новую книгу Microsoft Excel. Сохранить ее под именем Задание 5 в личной папке.

2. На первом листе (назвать его База данных) сформировать список магазинов как показано ниже.

   	A
   1	Магазины
   2	Магазин 1
   3	Магазин 2
   4	Магазин 3
   5	Магазин 4
   6	Магазин 5

3. Присвоить диапазону А2:А6 имя Магазины (выделить диапазон, в меню Вставка – Имя выбрать Присвоить).

4. Ниже на листе База данных сформировать список товаров как показано ниже.

Замечание. Для диапазона А9:А12 выбрать текстовый формат ячеек, для диапазона С9:С12 – денежный (рублевый, без десятичных знаков).

5. Диапазону В9:В12 присвоить имя Товары, диапазону А9:С12 присвоить имя Справочник.

6. Второй рабочий лист назвать Таблица и заполнить его как показано ниже.

Замечание. Для диапазона В2:В11 выбрать текстовый формат ячеек, для диапазонов Е2:Е11 и F2:F11 – денежный (рублевый, без десятичных знаков).

7. Так как магазинов всего пять, то удобно вводить их названия, выбирая из списка. Такой ввод можно организовать с помощью инструмента Проверка.

Для этого:

• Выделить диапазон А2:А11 и в меню Данные выбрать Проверка

• В открывшемся диалоговом окне Проверка вводимых значений на вкладке Параметры ввода в части окна Условие проверки щелкнуть на стрелке в поле Тип данных: и выбрать Список. Далее щелкнуть в поле Источник: и нажать клавишу F3. В появившемся диалоговом окне Вставка имени щелкнуть на имени Магазины и нажать ОК во всех диалоговых окнах. После этого в каждой ячейке диапазона появились стрелки, которые позволяют выбрать нужное значение.

• Заполнить диапазон А2:А11, используя выбор из списка, как показано ниже.

Далее, было бы удобно, если наименования товаров и цена появлялись автоматически в соответствии с кодом товара. Это позволит сделать функция ВПР (вертикальный просмотр). Синтаксис:

Впр(искомое_значение;таблица;номер_столбца)

Аргументы:

• искомое_значение – это значение, которое должно быть найдено в первом столбце массива. Другими словами, это значение, определяющее остальные параметры (в нашем примере это будет код товара).

• таблица – это таблица с информацией, в которой ищутся данные. В нашем примере это таблица Справочник.

• номер_столбца – это номер столбца в массиве таблица, в котором должно быть найдено соответствующее значение. Если номер_столбца равен 1, то возвращается значение из первого столбца аргумента таблица; если номер_столбца равен 2, то возвращается значение из второго столбца аргумента таблица и так далее. Если номер_столбца меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если номер_столбца больше, чем количество столбцов в аргументе таблица, то функция ВПР возвращает значение ошибки #ССЫЛКА!.

8. В ячейку С2 ввести формулу

=ВПР(В2;Справочник;2)

Замечание. Здесь Справочник – это диапазон ячеек А9:С12 из листа База данных. Это имя можно ввести либо с клавиатуры, либо нажать клавишу F3 и в появившемся диалоговом окне Вставка имени выбрать требуемый диапазон.

9. Автозаполнить диапазон С3:С11.

10. В ячейку Е2 ввести формулу

=ВПР(В2;Справочник;3)

Замечание. См. замечание к п.8.

11. Автозаполнить диапазон Е3:Е11.

12. Заполнить столбец Стоимость (с учетом того, что стоимость = цена * количество).

Далее, подведем итоги по полученной таблице (например, по каждому из магазинов).

13. Скопировать лист Таблица и копию назвать Итоги.

14. Щелкнуть в любой ячейке таблицы на листе Итоги и отсортировать ее по полю Магазин по возрастанию. Затем в меню Данные выбрать Итоги.

15. Заполнить поля диалогового окна Промежуточные итоги как показано на Рис. 12. Щелкнуть ОК.

Рис. 12. Диалоговое окно Промежуточные итоги

Затем, построим две сводные таблицы, в которых сведем данные сначала по магазинам, а затем по наименованиям товаров.

16. Для построения первой сводной таблицы необходимо:

• Щелкнуть в любой ячейке списка на листе Таблица и в меню Данные выбрать Сводная таблица.

• В открывшемся диалоговом окне Мастер сводных таблиц и диаграмм – шаг 1 из 3 в части окна Вид создаваемого отчета: установить значение сводная таблица и щелкнуть Далее.

• В открывшемся диалоговом окне Мастер сводных таблиц и диаграмм – шаг 2 из 3 проконтролировать диапазон, содержащий исходные данные ($A$1:$F$11). Щелкнуть Далее.

• В открывшемся диалоговом окне Мастер сводных таблиц и диаграмм – шаг 3 из 3 выбрать вариант размещения таблицы на новом листе и щелкнуть на кнопке Макет. Создать макет первой сводной таблицы согласно Рис. 13.

Рис. 13. Макет первой сводной таблицы

• Щелкнуть ОК в диалоговом окне Мастер сводных таблиц и диаграмм – макет. В диалоговом окне Мастер сводных таблиц и диаграмм – шаг 3 из 3 щелкнуть Готово. В рабочей книге появится новый лист со сводной таблицей. Присвоить этому листу имя Сводная таблица 1.

17. Для построения второй сводной таблицы необходимо:

• Щелкнуть в любой ячейке списка на листе Таблица и в меню Данные выбрать Сводная диаграмма (со сводной таблицей)

• В открывшемся диалоговом окне Мастер сводных таблиц и диаграмм – шаг 1 из 3 в части окна Вид создаваемого отчета: установить значение сводная таблица и щелкнуть Далее.

• В открывшемся диалоговом окне Мастер сводных таблиц и диаграмм – шаг 2 из 3 проконтролировать диапазон, содержащий исходные данные ($A$1:$F$11). Щелкнуть Далее.

• В появившемся информационном окне (Рис. 14) щелкнуть Нет.

Рис. 14.

• В открывшемся диалоговом окне Мастер сводных таблиц и диаграмм – шаг 3 из 3 выбрать вариант размещения таблицы на новом листе и щелкнуть на кнопке Макет. Создать макет первой сводной таблицы согласно Рис. 15.

Рис. 15. Макет второй сводной таблицы

• Щелкнуть ОК в диалоговом окне Мастер сводных таблиц и диаграмм – макет. В диалоговом окне Мастер сводных таблиц и диаграмм – шаг 3 из 3 щелкнуть Готово. В рабочей книге появятся новые листы со сводной таблицей и диаграммой. Присвоить им имена Сводная таблица 2 и Сводная диаграмма соответственно.

18. Изменить отображаемые данные на сводной диаграмме.

Первоначальная сводная диаграмма представлена на Рис. 16.

Рис. 16. Сводная диаграмма

Очевидно, что бессмысленно в одной системе координат строить гистограмму по полю Количество и Стоимость (разный порядок значений и разные единицы измерения). Отобразим на диаграмме лишь данные по полю Количество. Для этого:

• На листе Сводная диаграмма щелкнуть на стрелке рядом с кнопкой Данные (она расположена внизу диаграммы) и в открывшемся окне выбрать Сумма по полю Количество (Рис. 17). Щелкнуть ОК

Рис. 17. Выбор данных для диаграммы

• Вызвать панель инструментов Сводные таблицы, на которой щелкнуть на кнопке Сводная диаграмма и выбрать команду Скрыть кнопки полей сводной диаграммы

• Щелкнуть правой кнопкой мыши в белом поле области диаграммы и выбрать Параметры диаграммы). Здесь:

• На вкладке Заголовки ввести названия осей: ось Х – Наименование товара, ось У – Количество

• На вкладке Линии сетки в части окна Ось Х (категорий) поставить флажок основные линии

• Щелкнуть ОК

• Щелкнуть правой кнопкой мыши на серой области построения диаграммы и в появившемся контекстном меню выбрать Очистить

• Щелкнуть два раза левой кнопкой мыши на числах вдоль вертикальной оси У. В появившемся диалоговом окне Формат оси на вкладке Шкала в поле максимальное значение установить значение 250. Щелкнуть ОК

Диаграмма примет вид (см. Рис. 18):

Рис. 18. Сводная диаграмма

19. Сохранить изменения и выйти из Microsoft Excel.

Практическое занятие №6. РЕШЕНИЕ ЗАДАЧ «ЧТО-ЕСЛИ»

Анализ поведения экономической модели требует инструментов, позволяющих варьировать параметры модели. Такие инструменты часто называют инструментами «что-если». Microsoft Excel предлагает несколько инструментов, относящихся к этой категории: Подбор параметра, Сценарии, Таблицы подстановки и Поиск решения. Рассмотрим эти инструменты (кроме Поиска решения – см. Задание 4).

Подбор параметра

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

Постановка задачи

Какую максимальную ссуду на год можно взять, если ограничиться ежемесячной выплатой а размере 1150 р., процентная ставка – простая, 8% годовых.

Замечание. Через n месяцев при простой ставке r% и начальной сумме s величина ежемесячных выплат p определяется формулой: .

Реализация

1. Открыть новую книгу Microsoft Excel и сохранить ее по именем Задание 6 в личной папке.

2. Первый рабочий лист назвать Подбор параметра.

3. Найдем величину ежемесячных выплат для произвольной величины ссуды. Для этого:

   • Заполнить диапазон А1:В5 как показано ниже

     	А	B
     1	Величина займа	10000
     2	Процентная ставка	0,08
     3	Срок (мес.)	12
     4
     5	Платеж

   • Для ячеек В1 и В5 установить денежный формат (рублевый, без десятичных знаков)

   • Для ячейки В2 установить процентный формат (без десятичных знаков)

   • В ячейке В5 ввести формулу для расчета выплаты по простой процентной ставке:

=(1+B2)*B1/B3

• Полученные данные представлены на Рис. 19.

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

4. Применим инструмент Подбор параметра для вычисления величины займи при ежемесячном платеже в размере 1150 р. Для этого:

   • Щелкнуть в ячейке В5 и в меню Сервис выбрать Подбор параметра

   • В открывшемся диалоговом окне Подбор параметра в поле Установить в ячейке: проконтролировать наличие адреса В5 (т.е. ячейки, содержащей формулу), в поле Значение: ввести число 1150 (так как это требуемое значение ежемесячной выплаты), в поле Изменяя значение ячейки: указать адрес В1 (здесь и будет подобрана величина займа). Щелкнуть ОК

   • В результате получим значения (Рис. 20):

Рис. 20. Результат подбора параметра

Таблица подстановки

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

таблица постановки с одной переменной

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

Постановка задачи

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

Реализация

1. Второй рабочий лист назвать Таблица подстановки 1.

2. Сформировать блок исходных данных как показано ниже.

Замечание. В ячейке В1 величина процентной ставки – произвольная. Пусть для начала она составляет, например, 10%.

3. Установить процентный формат для ячейки В1 и диапазона А7:А11, для ячейки В3 – денежный (рублевый).

4. В ячейку В6 ввести формулу для вычисления ежемесячных платежей (формула для расчета – см. Замечание к постановке предыдущей задачи):

=(1+B1)*B3/B2

5. Выделить диапазон А6:В11 и в меню Данные выбрать Таблица подстановки.

6. В появившемся диалоговом окне Таблица подстановки в поле Подставлять значения по строкам в: указать ячейку с начальным значением изменяемого параметра (это ячейка В1).

Замечание. Если процентные ставки расположены в строке, то ссылка на ячейку В1 вводиться в поле Подставлять значения по столбцам в:)

7. После щелчка на кнопке ОК будут выведены значения формулы вычисления платежа для каждой процентной ставки. Результат представлен на Рис. 21.

Рис. 21. Результат применения инструмента Таблица подстановки

Замечания.

1. При создании таблицы подстановки в каждой ячейке диапазона результатов (В7:В11) возникает формула массива {=ТАБЛИЦА(;B1)} (см. Рис. 21). Изменять содержимое ячеек в диапазоне результатов нельзя. При ошибке удаляется весь диапазон результатов.

2. Можно включить любое количество выходных формул при создании таблицы подстановки с одной переменной. Важно, чтобы все формулы использовали одну и ту же входную ячейку (в данном примере это ячейка В1). Если входной диапазон является столбцом, то вторая выходная формула вводится непосредственно справа от первой. Если входной диапазон является строкой, то вторая формула вводится непосредственно под первой.

• На рабочем листе Таблица подстановки 1 организовать столбец Сумма платежей справа от столбца Платежи, в котором в ячейке С6 ввести формулу:

=В6*В2

• Выделить диапазон А6:С11 и в меню Данные выбрать Таблица подстановки.

• В открывшемся диалоговом окне Таблица подстановки в поле Подставлять значения по строкам в: указать ячейку В1. Щелкнуть ОК.

• В результате получим таблицу Рис. 22:

Рис. 22. Таблица подстановки с двумя формулами

Таблица подстановки с двумя переменными

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

Постановка задачи

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

Реализация

1. Третьему листу рабочей книги присвоить имя Таблица подстановки 2.

2. Скопировать блок исходных данных А1:В3 с листа Таблица подстановки 1.

3. Заполнить диапазон А5:F11 как показано ниже

   	A	B	C	D	E
   5	Платежи
   6		12	18	24	30
   7	0,09
   8	0,1
   9	0,11
   10	0,12
   11	0,13

4. Для диапазона А7:А11 установить процентный формат.

5. В ячейке А6 ввести формулу для вычисления ежемесячных платежей (как и в предыдущей задаче):

=(1+B1)*B3/B2

6. Выделить блок А6:Е11 и в меню Данные выбрать Таблица подстановки.

7. В открывшемся диалоговом окне Таблица подстановки в поле Подставлять значения по столбцам в: указать ячейку В2, в поле Подставлять значения по строкам в: указать ячейку В1. Щелкнуть ОК.

8. В результате получим таблицу (Рис. 23):

Рис. 23. Таблица подстановки с двумя переменными

Сценарный подход к решению экономических задач

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

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

Перед вызовом команды Сценарии на рабочем листе необходимо смоделировать решение задачи при некотором произвольном наборе входных параметров.

Постановка задачи

По данным о расходах фирмы за прошлый год составить варианты расходов на следующий год.

Реализация

1. Четвертый лист рабочей книги назвать Сценарии.

2. Сформировать блок исходных данных как показано ниже.

   	А	В
   1	Арендная плата	120000
   2	Коммунальные услуги	100000
   3	Админ. расходы	25000
   4	Расходы

3. В ячейке В4 вычислить общую сумму расходов.

4. Для диапазона В1:В4 выбрать денежный формат (рублевый).

5. Сохранить текущие значения как первый сценарий. Для этого в меню Сервис выбрать Сценарии. В открывшемся диалоговом окне Диспетчер сценариев щелкнуть на кнопке Добавить. В диалоговом окне Добавление сценария:

• В поле Название сценария: ввести имя Прошлый год

• В поле Изменяемые ячейки: указать диапазон В1:В3

• Щелкнуть на кнопке ОК

• В открывшемся диалоговом окне Значения ячеек сценария оставить текущие значения и щелкнуть ОК (диалоговое окно Диспетчер сценариев не закрывать)

6. Смоделировать оптимистический прогноз расходов. Для этого в диалоговом окне Диспетчер сценариев щелкнуть на кнопке Добавить. В диалоговом окне Добавление сценария:

• В поле Название сценария: ввести имя Оптимистический прогноз

• В поле Изменяемые ячейки: указать диапазон В1:В3

• Щелкнуть на кнопке ОК

• В открывшемся диалоговом окне Значения ячеек сценария установить значения согласно Рис. 24. Щелкнуть ОК (диалоговое окно Диспетчер сценариев не закрывать)

Рис. 24. Оптимистический прогноз

Замечание. В каждом поле диалогового окна Значения ячеек сценария можно ввести константу или формулу (но формула не может содержать ссылки на ячейки, только константы).

7. Смоделировать пессимистический прогноз расходов. Для этого в диалоговом окне Диспетчер сценариев щелкнуть на кнопке Добавить. В диалоговом окне Добавление сценария:

• В поле Название сценария: ввести имя Пессимистический прогноз

• В поле Изменяемые ячейки: указать диапазон В1:В3

• Щелкнуть на кнопке ОК

• В открывшемся диалоговом окне Значения ячеек сценария установить значения согласно Рис. 25. Щелкнуть ОК

Рис. 25. Пессимистический прогноз

8. Для поочередного просмотра сценариев в диалоговом окне Диспетчер сценариев выбрать имя сценария и щелкнуть на кнопке Вывести.

9. Для создания отчетов по сценариям необходимо:

• В диалоговом окне Диспетчер сценариев щелкнуть на кнопке Отчет

• В диалоговом окне Отчет по сценарию выбрать тип отчета структура

• В поле Ячейки результата: указать адрес В4. Щелкнуть ОК.

• В результате получим лист Структура сценария со следующим содержимым (Рис. 26):

Рис. 26. Структура сценария

Замечание. Вместо указания на ячейки в столбце С целесообразно вставить названия исследуемых параметров из таблицы на листе Сценарии (это будут заголовки Арендная плата, Коммунальные услуги и Админ. расходы).

10. Сохранить изменения, закрыть рабочую книгу и выйти из Microsoft Excel.

Практическое занятие №7. Финансовые функции для расчетов то кредитам, займам и оценкам инвестиций (определение будущей стоимости) Краткая справка

В финансовой практике часто встречаются операции, характеризующиеся возникновением потоков платежей, распределенных во времени. Потоки платежей, при которых выплаты (поступления) денежных средств осуществляются равными суммами через одинаковые интервалы времени, называются обыкновенным аннуитетом. Такие потоки возникают при проведении кредитно-депозитных операций, формировании различных фондов, долгосрочной аренде и т. п.

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

• потоки платежей на конец (начало) периода известны;

• для всего срока проведения операции определена оценка в виде процентной ставки, в соответствии с которой средства могут быть инвестированы.

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

, где

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

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

r – процентная ставка за один период;

type – число 0 или 1, обозначающее, когда производится выплата (1 – в начале периода, 0 – в конце периода);

pv – текущая стоимость вклада (займа), по которому исчисляются проценты по ставке r% n-ое число периодов или текущая стоимость серии фиксированных периодических платежей;

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

Если процентная ставка за период начисления , то используется формула:

.

Определение будущей стоимости

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

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

• будущее значение инвестиции после исчисления сложных процентов при переменной процентной ставке (функция БЗРАСПИС).

Будущая стоимость – это параметр fv в предыдущих формулах.

Постоянная процентная ставка

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

БС(ставка; кпер; плт; [пс]; [тип])

Аргументы функции:

ставка – процентная ставка за период начисления процентов (ежегодно, раз в полгода, раз в квартал и т. д.) – см. Табл. 2;

кпер – количество периодов выплат – см. Табл. 2;

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

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

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

Табл. 2. Расчет основных величин при внутригодовом учете процентов

Замечания.

1. для расчета функция БС использует формулу: ;

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

Задача 1. Рассчитать, какая сумма окажется на счете, если 21 тыс. долларов США положить на 12 лет под 11% годовых. Проценты начисляются каждые полгода.

Решение.

1. Открыть новую книгу Microsoft Excel и сохранить ее под именем Финансы в личной папке.

2. Первый рабочий лист назвать Будущая стоимость.

3. Оформить диапазон A1:B5 как показано ниже:

   	A	B
   1	Задача 1
   2	Вложенная сумма	-21000
   3	Срок (в годах)	12
   4	Годовой процент	0,11
   5	Число периодов начисления
   6	Процент за период начисления
   7	Будущая сумма вклада

4. Для ячеек В2 и В7 установить долларовый формат без десятичных знаков.

5. Для ячеек В4 и В6 установить процентный формат (для В4 без десятичных знаков, для В6 – 1 десятичный знак).

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

7. В ячейке В6 вычислить полугодовой процент (годовой процент, деленный на 2).

8. Для вычисления будущей суммы вклада в ячейке В7 вызвать Мастер функций (комбинацией клавиш Shift+F3), выбрать финансовую функцию БС и заполнить диалоговое окно согласно Рис. 27.

Рис. 27. Диалоговое окно ввода аргументов функции БС

Упражнение. В ячейке С7 вычислить значение будущей стоимости по формуле и добавить к этой ячейке примечание (текст примечания – По формуле).

Задача 2. Фирма создает фонд для погашения долгосрочных обязательств, для чего перечисляет ежегодно в течение 4 лет платежи размером 100 тыс. р. в конце каждого года, на которые начисляются сложные проценты по ставке 18% годовых, начисляемых ежеквартально. Определить величину фонда к концу срока выплат.

Решение.

1. На первом рабочем листе Будущая стоимость оформить диапазон A9:B14 как показано ниже:

   	A	B
   9	Задача 2
   10	Срок (в годах)	4
   11	Размер ежегодного платежа	100000
   12	Годовая ставка	0,18
   13	Число периодов начисления
   14	Процент за период начисления
   15	Будущая величина фонда

2. Для ячеек В11 и В15 установить долларовый формат без десятичных знаков.

3. Для ячеек В12 и В14 установить процентный формат (для В12 без десятичных знаков, для В14 – 1 десятичный знак).

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

5. В ячейке В14 вычислить ежеквартальный процент (годовой процент, деленный на 4).

6. Для вычисления будущей величины фонда в ячейке В15 вызвать Мастер функций (комбинацией клавиш Shift+F3), выбрать финансовую функцию БС и заполнить диалоговое окно согласно Рис. 28.

Рис. 28. Диалоговое окно ввода аргументов функции БС (постнумерандо)

7. Добавить к ячейке В15 примечание. Текст примечания – Постнумерандо.

Упражнение. Проверить самостоятельно (в ячейке С15), что в случае аннуитета пренумерандо (выплаты в начале периода) результат был бы равен $2 374 171. К ячейке С15 добавить примечание. Текст примечания – Пренумерандо.

Переменная процентная ставка

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

Синтаксис:

БЗРАСПИС(первичное; план)

Аргументы функции:

первичное – это стоимость инвестиции на текущий момент;

план – это массив применяемых процентных ставок.

Замечание. Функция БЗРАСПИС вычисляется по формуле: , где - последовательные во времени значения процентных ставок; - длительность периодов, в течение которых используются соответствующие ставки.

Задача 3. По облигации номиналом 100 тыс. руб., выпущенной на 6 лет, предусмотрен следующий порядок начисления процентов: в первый год – 10 %, в два последующих года – 20 %, в оставшиеся три года – 25 %. Рассчитать будущую (наращенную) стоимость облигации по сложной процентной ставке.

Решение.

1. На первом рабочем листе Будущая стоимость оформить диапазон A17:С26 как показано ниже:

   	A	B	C
   17	Задача 3
   18	Вложенная сумма	100000
   19	Срок (в годах)	6
   20	Годовые процентные ставки	1-ый год	0,1
   21		2-ой год	0,2
   22		3-ий год	0,2
   23		4-ый год	0,25
   24		5-ый год	0,25
   25		6-ой год	0,25
   26	Наращенная стоимость

2. Для ячеек В18 и В26 установить рублевый формат без десятичных знаков.

3. Для диапазона С20:С25 установить процентный формат без десятичных знаков.

4. Для вычисления наращенной стоимости в ячейке В26 вызвать Мастер функций (комбинацией клавиш Shift+F3), выбрать финансовую функцию БЗРАСПИС и заполнить диалоговое окно согласно Рис. 29.

Рис. 29. Диалоговое окно ввода аргументов функции БЗРАСПИС

Упражнение. В ячейке С26 вычислить наращенную стоимость с использование формулы .

Задача 4. Исходя из плана начисления процентов, приведенного в Задаче 3, рассчитать номинал облигации, если известно, что ее будущая стоимость составила 1 546 880 р.

Решение.

1. На первом рабочем листе Будущая стоимость оформить диапазон A28:В30 как показано ниже:

   	А	В
   28	Задача 4
   29	Номинал облигации
   30	Будущая стоимость

2. В ячейку В30 ввести формулу

=БЗРАСПИС(B29;C20:C25)

3. Щелкнуть в ячейке В30 и в меню Сервис выбрать Подбор параметра. Заполнить диалоговое окно согласно Рис. 30.

Рис. 30. Диалоговое окно Подбор параметра

4. Установить для ячеек В29 и В30 рублевый формат без десятичных знаков.

В результате фрагмент рабочего листа будет выглядеть следующим образом (Рис. 31):

Рис. 31. Результат подбора параметра

5. Сохранить изменения.

Практическое занятие №8. Финансовые функции для расчетов то кредитам, займам и оценкам инвестиций (определение текущей стоимости) Краткая справка

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

Microsoft Excel содержит функции, которые позволяют рассчитать:

• текущую стоимость единой суммы вклада (займа) и фиксированных периодических платежей (функция ПС);

• чистую текущую стоимость будущих периодических расходов и поступлений переменной величины (функция ЧПС);

• чистую текущую стоимость нерегулярных расходов и поступлений переменной величины (функция ЧИСТНЗ).

Замечание. Расчеты с использованием функций ЧПС и ПС являются частными случаями вычисления текущей стоимости ожидаемых доходов и расходов, которые в общем случае могут быть переменной величины и происходить в разные периоды времени. Расчет при помощи функции ПС требует денежных потоков равной величины равных интервалов между операциями. Функция ЧПС допускает денежные потоки переменной величины через равные периоды времени. Наиболее общий расчет можно осуществить при помощи функции ЧИСТНЗ, которая позволяет вычислять чистую текущую стоимость переменных денежных потоков, являющихся нерегулярными.

Расчет текущей стоимости единой суммы вклада (займа) и фиксированных периодических платежей

Для таких расчетов используется функция ПС. Синтаксис:

ПС(ставка ;кпер;плт;[бс];[тип])

Аргументы функции:

ставка – процентная ставка за период;

кпер – общее число периодов платежей по аннуитету;

плт – выплата, производимая в каждый период и не меняющаяся за все время выплаты ренты;

бс – требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент опущен, он полагается равным 0 (будущая стоимость займа, например, равна 0);

тип – число 0 или 1, обозначающее, когда должна производиться выплата

Замечание. Значение, которое возвращает функция ПС – это аргумент pv формулы

^*.

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

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

.

В этом случае параметр pv возвращает функция ПС, имеющая следующий синтаксис:

ПС(ставка ;кпер;;бс)

Задача 1. Какую сумму необходимо положить в банк, выплачивающий 12% годовых, чтобы через 3 года получить 250 тыс. р.?

Решение.

1. Открыть рабочую книгу Финансы, сохраненную в Задании 7.

2. На втором листе (присвоить ему имя Текущая стоимость) заполнить блок ячеек А1:В5 как показано ниже:

   	А	В
   1	Задача 1
   2	Требуемая сумма	250000
   3	Срок (в годах)	3
   4	Годовая процентная ставка	0,12
   5	Сумма вклада

3. Для ячеек В2 и В5 установить рублевый формат (для ячейки В2 – без десятичных знаков, для ячейки В5 – с двумя десятичными знаками).

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

5. Для вычисления текущей суммы вклада в ячейке В5 вызвать Мастер функций, выбрать финансовую функцию ПС и заполнить диалоговое окно согласно Рис. 32.

Рис. 32. Диалоговое окно ввода аргументов функции ПС

Упражнение. В ячейке С5 вычислить значение текущей стоимости по формуле и добавить к этой ячейке примечание (текст примечания – По формуле).

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

ПС(ставка;кпер;плт;;[тип])

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

, где

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

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

n – общее число периодических выплат (поступлений);

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

Если выплаты производятся в конце каждого периода (постнумерандо), то параметр тип равен 0 (т. е. его можно не указывать), а функция ПС возвращает значение, которые может быть вычислено по формуле:

.

Задача 2. Платежи в фонд будут вноситься ежегодно по 200 тыс. р. в течение 4 лет с начислением на них сложных процентов по ставке 8% годовых. Определить современную сумму всех платежей с начисленными процентами.

Решение.

1. На втором рабочем листе Текущая стоимость заполнить блок ячеек А7:В11 как показано ниже:

   	А	В
   7	Задача 2
   8	Постоянная выплата	200000
   9	Срок (в годах)	4
   10	Годовая процентная ставка	0,08
   11	Современная сумма платежей с начисленными процентами

2. Для ячеек В8, В11 и С11 установить рублевый формат (для ячейки В8 – без десятичных знаков, для ячеек В11 и С11 – с двумя десятичными знаками).

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

4. В ячейке В11 вычислить современную сумму платежей постнумерандо. Для этого в этой ячейке вызвать Мастер функций, выбрать функцию ПС и заполнить диалоговое окно согласно Рис. 33.

Рис. 33. Диалоговое окно ввода аргументов функции ПС (постнумерандо)

5. Добавить примечание к ячейке В11. Текст примечания – Постнумерандо.

6. В ячейке С11 вычислить современную сумму платежей пренумерандо. Для этого в этой ячейке вызвать Мастер функций, выбрать функцию ПС и заполнить диалоговое окно согласно Рис. 34.

Рис. 34. Диалоговое окно ввода аргументов функции ПС (пренумерандо)

7. Добавить примечание к ячейке С11. Текст примечания – Пренумерандо.

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

Расчет чистой текущей стоимости будущих периодических платежей переменной величины

Функция ЧПС вычисляет чистую текущую стоимость NPV периодических платежей переменной величины как сумму ожидаемых доходов и расходов, дисконтированных^* нормой процента r. Эта функция возвращает значение, которое может быть вычислено по формуле:

, где

r – норма дисконтирования (ставка процента) или средняя цена капитала;

n – количество выплат и поступлений;

- значение выплат и поступлений.

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

Синтаксис функции ЧПС:

ЧПС(ставка ;значение1;[значение2]; [...])

Аргументы:

ставка – ставка дисконтирования за один период;

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

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

Задача 3. Инвестиции в проект составляют 800 тыс. р. В последующие 5 лет ожидаются следующие годовые доходы по проекту: 250; 320; 210; 400; 150 тыс. р. Издержки привлечения капитала 7%. Рассчитать чистую текущую стоимость проекта.

Решение.

1. На втором рабочем листе Текущая стоимость заполнить блок ячеек А13:В22 как показано ниже:

   	А	В
   13	Задача 3
   14	Инвестиции	-800000
   15	Годовые доходы по проекту
   16	1-ый год	250000
   17	2-ой год	320000
   18	3-ий год	210000
   19	4-ый год	400000
   20	5-ый год	150000
   21	Издержки	0,07
   22	Чистая текущая стоимость

2. Для ячеек В14, В16:В20 и В22 установить рублевый формат (для ячеек В14, В16:В20 – без десятичных знаков, для ячейки В22 – с двумя десятичными знаками).

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

4. Для вычисления чистой текущей стоимости в ячейке В22 ввести формулу согласно Рис. 35.

Рис. 35 Формула для вычисления чистой текущей стоимости

Замечание. Сумма инвестиций вычитается согласно замечанию перед этой задачей.

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

Решение.

1. На втором рабочем листе Текущая стоимость заполнить блок ячеек А24:В31 как показано ниже:

   	А	В
   24	Задача 4
   25	Инвестиции (1-ый год)	-10000
   26	Годовые доходы
   27	2-ой год	3000
   28	3-ий год	4200
   29	4-ый год	6800
   30	Издержки	0,1
   31	Чистая текущая стоимость

2. Для ячеек В25, В27:В29 и В31 установить рублевый формат (для ячеек В25, В27:В29 – без десятичных знаков, для ячейки В31 – с двумя десятичными знаками).

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

4. В ячейке В31 вычислить чистую текущую стоимость. Для этого в этой ячейке вызвать Мастер функций, выбрать функцию ЧПС и заполнить диалоговое окно согласно Рис. 36.

Рис. 36. Диалоговое окно ввода аргументов функции ЧПС

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

Решение.

1. На втором рабочем листе Текущая стоимость заполнить блок ячеек А33:В43 как показано ниже:

   	А	В
   33	Задача 5
   34	Инвестиции	-37000
   35	Ожидаемые доходы
   36	1-ый год	8000
   37	2-ой год	9200
   38	3-ий год	10000
   39	4-ый год	13900
   40	5-ый год	14500
   41	6-ой год	-5000
   42	Цена капитала	0,08
   43	Чистая текущая стоимость

2. Для ячеек В34, В36:В41 и В43 установить рублевый формат (для ячеек В34, В36:В41 – без десятичных знаков, для ячейки В43 – с двумя десятичными знаками).

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

4. Для вычисления чистой текущей стоимости в ячейке В43 необходимо ввести формулу согласно Рис. 37.

Рис. 37. Формула для вычисления чистой текущей стоимости

Расчет чистой текущей стоимости нерегулярных расходов и поступлений переменной величины

Функция ЧИСТНЗ позволяет рассчитать чистую текущую стоимость NPV нерегулярных переменных расходов и доходов. Для расчета используется формула:

, где

r – ставка процента (норма дисконтирования);

- дата i-ой операции;

- дата 0-й операции (начальная дата);

- сумма i-ой операции.

Синтаксис функции:

ЧИСТНЗ(ставка;значения;даты)

Аргументы функции:

ставка – это ставка дисконтирования, применяемая к денежным потокам;

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

даты – это расписание дат платежей, которое соответствует ряду денежных потоков.

Задача 6. Рассмотрим инвестицию размером 10 млн. р. 01.07.2006 г., которая принесет доходы: 2750 тыс. р. 15.09.2006 г., 4250 тыс. р. 01.11.2006 г., 5250 тыс. Р. 01.01.2007. Норма дисконтирования 6%. Определить чистую текущую стоимость инвестиции на 01.07.2006 г и на 01.05.2006 г.

Решение.

1. На втором рабочем листе Текущая стоимость заполнить блок ячеек А45:С53 как показано ниже:

   	А	В	С
   45	Задача 6
   46	Сумма операции	Дата	Чистая текущая стоимость
   47	0	01.05.2006
   48	-10000000	01.07.2006
   49	2750000	15.09.2006
   50	4250000	01.11.2006
   51	5250000	01.01.2007
   52
   53	Норма дисконтирования	0,09

2. Для ячеек А48:А51, и С47:С48 установить рублевый формат (для ячеек А48:А51– без десятичных знаков, для ячеек С47:С48 – с двумя десятичными знаками).

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

4. В ячейке С48 найдем чистую текущую стоимость инвестиции на 01.07.2006. Для этого в ячейке С48 вызвать Мастер функций, выбрать функцию ЧИСТНЗ и заполнить диалоговое окно согласно Рис. 38.

Рис. 38. Диалоговое окно ввода аргументов функции ЧИСТНЗ

5. В ячейке С47 найдем чистую текущую стоимость инвестиции на 01.05.2006. Для этого в ячейке С47 вызвать Мастер функций, выбрать функцию ЧИСТНЗ и заполнить диалоговое окно согласно Рис. 39.

Рис. 39. Диалоговое окно ввода аргументов функции ЧИСТНЗ

6. Сохранить изменения.

1 По данным журнала Компьютер бизнес маркет, №47 / 14 ноября – 20 ноября 2005.

2 C математической точки зрения «купленных» означает, что параметр «количество» для этих товаров больше нуля.

3 Стоимость на 12.03.2005.

4 Сбербанк РФ предоставляет ипотечные кредиты для физических лиц на следующих условиях: процентные ставки по кредиту в рублях – 18 %; в долларах США и евро – 11 %. Кредиты предоставляются на срок не более 15-ти лет. (Информация с сайта http://www.sbrf.ru)

* Аргументы этой формулы пояснены в Задании 7.

* Дисконтирование – приведение стоимостной величины, относящейся к будущему,. На некоторый, обычно более ранний момент времени (операция, обратная наращению).