Использование готовых шаблонов

1. Выполнить команду Файл – Создать.

2. На экране появится стандартное окно создания файлов.

3. Выбрать в этом окне вкладку с требуемым шаблоном и загрузить его. В рабочей книге появится таблица с бланком выбранного шаблона.

4. Сохранить эту таблицу как обычный файл Excel в своей папке (то есть как файл с расширением .xls).

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

Защита ячеек

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

Выполнить данные действия можно следующим образом:

1. Выделить все ячейки, защиту с которых нужно снять;

2. Выполнить команду Формат – Ячейки – Защита.

3. На экране появится окно с вкладкой " Защита".

4. Щелкнуть мышью в поле флажка " Защищаемая ячейка ", чтобы выключить для данных ячеек защиту, и закрыть окно.

5. Установить защиту рабочего листа с помощью команды Сервис –Защита – Защитить лист.

Для снятия защиты с листа достаточно выполнить команду Сервис – Защита – Снять защиту листа.

Замечание. Для перемещения по рабочему листу от одной незащищенной области ячеек к другой можно воспользоваться клавишей Tab.

9. Финансовые расчеты и составление отчетных
ведомостей

 

Финансовые расчеты в Microsoft Excel

 

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

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

Для исчисления характеристик финансовых операций с элементарными потоками платежей удобно использовать функции Б3 (БС), КПЕР, HOPMA (СТАВКА), П3 (ПС) (табл. 9.1).

Таблица 9.1

Функции для анализа потоков платежей

 

Наименование функции	Формат функции
Англоязычная версия	Русская версия
FV	БЗ	Б3 (БС)(ставка; кпер; платеж; нс; [тип])
NPER	КПЕР	КПЕР(ставка; платеж; нз; бс; [тип])
RATE	НОРМА	НОРМА (СТАВКА)(кпер; платеж; нз; бс; [тип])
PV	ПЗ	П3 (ПС)(ставка; кпер; платеж; бс; [тип])
РМТ	ППЛАТ	ППЛАТ (Плт)(ставка; кпер; нз; [бс]; [тип])

 

Как следует из табл. 9.1, большинство функций имеют одинаковый набор базовых аргументов:

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

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

• выплата – величина периодического платежа;

• нз – начальное значение;

• бс – будущее значение;

• [тип] – тип начисления процентов (1 – начало периода, 0 – конец периода), необязательный аргумент.

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

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

Напомним, что аргументы функций в Microsoft Excel разделяются символом “;”, а признаком функции служит символ “=”.

 

Функция БЗ (ставка; кпер; выплата; нз; [тип])(БС)

Эта функция позволяет определить будущее значение потокаплатежей.

Пример 1.

Определить будущую величину вклада в 10000, помещенного в банк на 5 лет под 5% годовых, если начисление процентов осуществляется:

а) раз в году; б) раз в месяц.

Следует ввести в любую ячейку таблицы:

=БЗ(0,05; 5; 0; -10000) (Результат: 12762,82) (или БС)

=БЗ(0,05/12; 5*12; 0; -10000) (Результат: 12833,59) (или БС).

 

Следует обратить особое внимание на способы задания аргументов.

Значение процентной ставки (аргумент ставка) обычно задается в виде десятичной дроби:

5% - 0,05;

10% - 0,1;

100% -1

и т. д.

Если начисление процентов осуществляется m раз в году, аргументы необходимо откорректировать соответствующим образом:

i = i/m

и n = n×m.

Аргумент «начальное значение – нз» здесь задан в виде отрицательной величины (-10000), так как с точки зрения вкладчика эта операция влечет за собой отток его денежных средств в текущем периоде с целью получения положительной величины (12762,82) через 5 лет.

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

=БЗ(0,05; 5; 0; 10000) (Результат: -12762,82).

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

Аргумент «выплата» не используется при анализе элементарных потоков, поэтому здесь и в дальнейшем он имеет нулевое значение. Его также можно задать в виде пустого параметра – «;», например:

=БЗ(0,05; 5;; 10000) (Результат: -12762,82).

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

 

Функция КПЕР (ставка; выплата; нз; бс; [тип])

Функция КПЕР вычисляет количество периодов начисления процентов, исходя из трех других известных величин.

Пример 2.

По вкладу в 10000, помещенному в банк под 5% годовых, начисляемых ежегодно, была выплачена сумма 12762,82.

Определить срок проведения операции (количество периодов начисления).

=КПЕР(0,05; 0; -10000; 12762,82) (Результат: 5 лет).

Соответственно при начислении процентов раз в месяц, число необходимых периодов будет равно:

=КПЕР(0,05/12; 0;-10000;12762,82) (Результат: 60 месяцев).

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

Функция НОРМА(кпер; выплата; нз; бс; [тип]) (СТАВКА)

Функция НОРМА (СТАВКА) вычисляет процентную ставку, которая в зависимости от условий операции может выступать либо в качестве цены, либо в качестве нормы ее рентабельности.

Определим процентную ставку для примера 2.

=НОРМА(5; 0; -10000; 12762,82) (Результат: 0,05, или 5%) (или СТАВКА)

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

Необходимо помнить, что для получения корректного результата при работе функций КПЕР и HOPMA (СТАВКА), аргументы «нз» и «бс» должны иметь противоположные знаки. Данное требование вытекает из экономического смысла подобных операций.

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

Шаблон состоит из двух частей. Первая часть занимает блок ячеек А2:В10 и предназначена для ввода исходных данных (известных параметров финансовой операции). Текстовая информация в ячейках А2:А10 содержит наименование исходных параметров финансовой операции, ввод которых осуществляется в ячейки В6:В10. Ячейка В7 содержит принятое по умолчание число начислений процентов, равное 1 (т.е. раз в году). Для получения искомого результата необходимо ввести еще три величины.

Рис. 9.1. Шаблон для анализа элементарных потоков

 

Рис. 9.2. Шаблон для анализа элементарных потоков (формулы)

 

Вторая часть таблицы занимает блок ячеек А14:B18 и предназначена для вывода результатов вычислений, т. е. искомой величины. При отсутствии исходных данных эта часть таблицы содержит нулевые значения в ячейках В14и В18, а также сообщения об ошибках. Блок ячеек В14:В18 содержит формулы, необходимые для исчисления соответствующих параметров финансовой операции (рис. 9.2).

Величины i(процентная ставка) и n (срок операции) в формулах скорректированы на число начислений процентов в году путем деления и умножения на значение ячейки В7 соответственно. Поскольку по умолчанию значение ячейки В7 равно 1, для операций с начислением процентов раз в год, корректировка параметров i и nне будет оказывать никакого эффекта. При этом здесь и в дальнейшем подразумевается задание параметра iв виде годовой процентной ставки, а срока проведения операции n - вколичестве лет.

Руководствуясь рис. 9.1 и 9.2, можно подготовить таблицу для элементарных потоков платежей и сохранить ее на магнитном диске в виде шаблона под именем SINGL_AN.XLT.

Осуществим проверку работоспособности шаблона на примерах решении практических задач.

Пример 3.

Фирма X предполагает взять кредит в100000 на 5 лет под 12% годовых. Проценты начисляются ежеквартально и подлежат выплате вместе с основной суммой долга по истечению срока кредита. Определите сумму выплаты на момент погашения кредита.

Прежде всего, осуществим загрузку таблицы-шаблона.

Теперь необходимо ввести в соответствующие ячейки столбца В исходные данные – величины PV, n, m, i.

Введите 0,12 в ячейку В6, 4 в ячейку В7, 5 в ячейку В8 и 100000 в ячейку В9. Полученная таблица должна иметь следующий вид (рис. 9.3).

Рис. 9.3. Решение примера 3

 

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

Допустим, что фирма X имеет альтернативную возможность получения кредита в 100000 на 5 лет под 11% годовых, выплачиваемых ежемесячно. Какой вариант получения кредита выгодней?

Для решения задачи можно просто скопировать блок ячеек В14:В18 в блок ячеек С14:С18; ввести исходные данные альтернативного варианта в ячейки С6:С9. Полученная таблица должна иметь следующий вид (рис. 9.4).

 

Рис. 9.4. Анализ двух альтернативных вариантов

 

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

В группу функций Microsoft Excel, предназначенных для автоматизации расчетов характеристик аннуитетов, входит функция ППЛАТ (Плт).

 

Функция ППЛАТ (ставка; кпер; нз; [бс]; [тип]) (или Плт)

Данная функция применяется в том случае, если необходимо определить величину периодического платежа – PMT.

Пример 4.

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

=ППЛАТ(0,1; 4; 0; 46410) (Результат: -10000,00) (или Плт).

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

ППЛАТ(0,1; 4; 0; -46410) (Результат: 10000,00) (или Плт).

Следует обратить особое внимание на значение параметра «нз» (PV). Условиями данной операции наличие первоначальной суммы на депозите в момент времени t = 0 не предусмотрено, поэтому значение параметра «нз» равно нулю.

 

Пример 5.

Финансовая компания создает фонд для погашения обязательств путем помещения в банк суммы в 50000, с последующим ежегодным пополнением суммами по 10000. Ставка по депозиту равна 10% годовых. Какова будет величина фонда к концу 4-го года?

=БЗ(0,1; 4; -10000; -50000) (Результат: 119615,00) (или БС).

Соответственно изменится и формат функции для определения величины ежегодного платежа:

=ППЛАТ(0,1; 4; -50000; 119615) (Результат: -10000,00) (или Плт).

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

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

=БЗ(0,1; 4; -10000; -50000; 1) (Результат: 124256,00) (или БС).

=ППЛАТ(0Д; 4; -50000; 124256; 1) (Результат: -10000,00) (или Плт).

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

При начислении процентов m-раз в году, величины i и nкорректируются так же, как и в предыдущих примерах.

На рис. 9.5 приведен один из простейших вариантов подобного шаблона, который может быть взят за основу. Формулы шаблона приведены в табл. 9.2.

 

Рис. 9.5. Шаблон для анализа аннуитетов

 

Таблица 9.2 – Формулы шаблона (аннуитеты )

 

Ячейка	Формула
В15	=БЗ (БС)(В5/В6;В7*В6;В10;В8;В11)
В16	=НОРМА (СТАВКА)(В7*В6;В10;В8;В9;В11)
В17	=В16*В6
В18	=КПЕР(В5/В6;В10;В8;В9;В11)
В19	=ПЗ (ПС)(В5/В6;В7*В6;В10;В9;В11)
В20	=ППЛАТ (Плт)(В5/В6;В7*В6;В8;В9;В11)

 

Можно сохранить разработанный шаблон на диске под именем ANNUI_AN.XLT.

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

Пример 6.

Корпорация планирует ежегодно в течение 10 лет делать отчисления по 5000 для создания фонда выкупа своих облигаций. Средства помещаются в банк под 12% годовых. Какая сумма будет накоплена к концу срока операции?

Введем в ячейки столбца В необходимые исходные данные. Полученная в итоге таблица будет иметь следующий вид (рис. 9.6).

 

Рис. 9.6. Решение примера 6

 

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

Рассмотрим на примере вычисление точного и приближенного числа дней. Пусть необходимо найти точное и приближенное число дней между двумя датами, например, 5 марта и 28 сентября 2005 г. Решение представлено на рис. 9.7.

 

Рис. 9.7. Нахождение числа дней между двумя датами

 

Для нахождения точного и приближенного числа дней необходимо поместить начальную и конечную дату в ячейки, для которых задается формат Дата и представление дд.мм.гг. В нашем случае в ячейке В2 – начальная дата 05.03.05, а в ячейке ВЗ – конечная дата 28.09.05.

Напомним, что Microsoft Excel хранит даты как целые числа и может выполнять над ними различные операции. По умолчанию порядковый номер 1 января 1900 г. – 1, а 1 января 2008 г. – 39448.

Результатом вычислений является число в ячейке В4, определяемое по формуле =ВЗ-В2. В нашем случае это 207 дней. Полученное значение будет являться точным числом дней между указанными датами. Если мы будем вычитать конечную дату из начальной, то результат получим отрицательный (см. ячейку В5).

Поскольку в финансовых расчетах в основном принято считать, что год состоит из 360 дней, в Excel предусмотрена функция ДНЕЙ360, определяющая количество дней между двумя датами. Она относится к категории функций Дата и время.

В ячейке В7 записано обращение к функции =ДНЕЙ360(В2;ВЗ), в результате которого будет вычислено приближенное число дней в связи с использованием допущения о 360 днях в году. Приближенное число дней между двумя датами составит 203 дня. Следует обратить внимание на то, что в этой функции порядок аргументов следующий: нач_дата, а затем кон_дата, т. е. первым аргументом является дата начала интервала времени. Если при обращении к функции задать иной порядок аргументов, т. е. первым аргументом будет дата окончания, а вторым – дата начала, то результат будет со знаком «–», что и представлено в ячейке В8.

 

Пример простейшей отчетной ведомости

 

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

Рис. 9.8. Отчет продаж сети магазинов

 

В этой отчетной ведомости надо определить:

- суммарную и среднюю выручку каждого из магазинов за отчетный период;

- суммарную выручку всех магазинов за каждый месяц отчетного периода;

- место, которое занимает каждый из магазинов в суммарном объеме выручки;

- долю каждого из магазинов в суммарном объеме выручки;

- количество магазинов, имеющих суммарную выручку до 100 млн. руб., от 1000 млн. руб. до 1500 млн. руб., от 1500 млн. руб. до 2000 млн. руб. и свыше 2000 млн. руб.

При составлении данного отчета необходимо выполнить следующее действия:

1. Для нахождения суммарной выручки первого магазина введите в ячейку Е3 формулу:

=СУММ(B3:D3)

2. Выберите ячейку Е3, расположите указатель мыши на маркере заполнения и переместите его вниз на диапазон Е4:Е8. Это позволит найти суммарную выручку каждого из магазинов.

3. Для нахождения суммарного объема выручки всех магазинов за июнь введите в ячейку В9 формулу:

=СУММ(B3:В8)

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

5. Для нахождения средней выручки первого магазина введите в ячейку G3 формулу

=СРЗНАЧ(В3:D3)

6. Выберите ячейку G3, расположите указатель мыши на маркере заполнения и переместите его вниз на диапазон G4:G8. Это позволит найти среднюю выручку каждого из магазинов.

7. Для определения доли объема выручки первого магазина по отношению к суммарной выручке всей сети магазинов введите в ячейку Н3 формулу:

=Е3/$E$9

8. Выберите ячейку Н3, расположите указатель мыши на маркере заполнения и переместите его вниз на диапазон Н4:Н8. Это позволит найти долю объема выручки каждого из магазинов по отношению к суммарной выручке всей сети магазинов.

9. Выберите диапазон Н3:Н8 и нажмите кнопку Процентный формат панели инструментов Форматирование. Это позволит установить процентный формат в формат в ячейках выбранного диапазона.

10.Для определения места первого магазина в суммарной выручке всей сети магазинов введите в ячейку F3 формулу:

=РАНГ(Е3;$E$3:$E$8)

11.Выберите ячейку F3, расположите указатель мыши на маркере заполнения и переместите его вниз на диапазон F4:F8. Это позволит найти место каждого магазина в суммарной выручке всей сети магазинов.

12.Для определения количество магазинов, имеющих суммарную выручку до 1000 млн. руб., от 1000 млн. руб., от 1500 млн. руб. до 2000 млн. руб. и свыше 2000 млн. руб., сначала заполните ячейки I3:I5 согласно рис. 4.11, а затем выберите диапазон J3:J6 и введите в него формулу массива (не забудьте завершить ее ввод нажатием комбинации клавиш <Ctrl>+<Shift>+<Enter>):

{=ЧАСТОТА(Е3:Е8;I3:I5)}

 

При составлении отчетной ведомости использовались функции СРЗНАЧ, РАНГ и ЧАСТОТА.

Функция СРЗНАЧ находит среднее арифметическое значение из указанного диапазона ячеек.

Синтаксис:

СРЗНАЧ(число1; число2; …)

Функция РАНГ возвращает ранг числа в списке чисел. Ранг числа – это его величина относительно других значений в списке. (Если список отсортировать, то ранг числа будет его позицией.).

Синтаксис: