2.4 Финансовые функции ms excel

2.4.1 Обзор возможностей MS EXCEL

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

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

Excel предлагает широкий набор функциональных средств по обработке табличных данных:

• создание и редактирование электронных таблиц с применением богатого набора встроенных функций;

• оформление и печать электронных таблиц;

• построение диаграмм и графиков различной степени наглядности и детализации;

• работа с электронными таблицами как с базами данных: фильтрация, сортировка, создание итоговых и сводных таблиц, консолидация данных из различных таблиц, в том числе из внешних баз данных;

• решение экономических задач типа «что-если» путем подбора параметров;

• решение оптимизационных задач;

• численное решение разнообразных математических задач;

• статистическая обработка данных;

• использование интегрированной среды разработки собственных программ – макросов на основе языка программирования Visual Basic for Applications.

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

• наращение и дисконтирование доходов и затрат (БЗ, ПЗ, КПЕР, НОРМА, ППЛАТ, и др.)

• анализ эффективности капитальных вложений (НПЗ, ВНДОХ и др.);

• расчеты по ценным бумагам (ДОХОД, ЦЕНА и др.);

• расчет амортизационных отчислений (АМР, АМГД и др.).

Всего в Excel встроено более 50 финансовых функций.

2.4.2. Задание

Предприятие состоит из трех крупных подразделений. Руководители финансовых отделов этих подразделений составили финансовые планы (бюджета) на период с 01.01.2000 по 31.12.2000 гг. и направили эти планы руководству предприятия для анализа и выработки согласованной финансовой политики. На первом этапе руководство приняло решение провести анализ финансовых потоков. Средства ( в млн. руб.) на 01.01.2000 г. (таблица 3.2) и ожидаемые ежемесячные поступления и платежи для каждого подразделения представлены в следующей таблице 3.1.

Таблица 3.1

Месяц	Подразделение 1		Подразделение 2		Подразделение 3
	Средства на начальный период: S1		Средства на начальный период: S2		Средства на начальный период: S3
	поступл.	платежи	поступл.	платежи	поступл	платежи
Январь	100	80	50	35	80	65
февраль	75	120	30	40	70	65
Март	50	120	15	50	50	145
Апрель	70	50	30	30	60	20
Май	85	80	45	30	70	35
Июнь	60	40	20	20	50	20
Июль	120	45	50	25	35	20
Август	110	35	50	15	90	25
Сентябрь	90	150	50	110	60	80
Октябрь	150	160	70	90	125	130
Ноябрь	55	35	40	20	35	25
декабрь	45	20	20	10	30	15

Таблица 3.2

вариант	19
S1	15
S2	15
S3	15

На основе приведенных данных требуется:

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

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

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

• Кредит берется только сроком на 3 или на 6 месяцев;

• Возврат суммы кредита производится ежемесячно равными долями, начиная с месяца, следующего за месяцем, в котором кредит был взят;

• Выплата процентов производится ежемесячно с суммы непогашенного долга по состоянию на предыдущий месяц из расчета Q1 процентов годовых (на 3 месяца) либо Q2 процентов годых (на 6 месяцев). Исходные данные по процентам, под которые может быть взят кредит, необходимо выбрать из приведенной ниже таблицы (табл. 3.3)

Таблица 3.3

Вариант	19
Q1	42
Q2	60

Результаты расчетов оформить в виде таблицы и графика.

3. Оценить целесообразность взятия заемных средств исходя из двух критериев:

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

• Устранение дефицитов наличия денежных средств по периодам до и после взятия заемных средств.

Сделать окончательный вывод, содержащий управленческое решение;

Фирма поместила на счет $___. По этому депозиту в первом году будет начислено К%. Во втором – М%, в третьем – N%, а в четвертом и мятом по L% годовых. Сколько будет на счете в конце пятого года. (БЗРАСПИС). Данные по K, M, N, L взять из таблицы 3.4

Таблица 3.4

Вариант	19
K	12
M	13
N	14
L	16

2.4.3 Расчет суммы кредиты и выплат по нему

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

Рисунок 3.1(а) – Движение денежных средств по подразделениям 1 и 2

Рисунок 3.1 (б) - Движение денежных средств по подразделению 3 и в целом

	Таблица 3.5
	Подразделение 1				Подразделение 2					Подразделение 3						Консолидированный финансовый поток
Месяц	Средства				Средства					Средства на нач. период:
	На нач. период:				На нач. период:					S3
	S1				S2
	поступл	платежи	баланс		поступл			платежи	баланс	поступл		платежи			баланс	поступл	платежи		баланс
Январь	100	80	35		50			35	30	80		65			30	230	180		95
Февраль	75	120	-10		30			40	20	70		65			35	175	225		45
Март	50	120	-80		15			50	-15	50		145			-60	115	315		-155
Апрель	70	50	-60		30			30	-15	60		20			-20	160	100		-95
Май	85	80	-55		45			30	0	70		35			15	200	145		-40
Июнь	60	40	-35		20			20	0	50		20			45	130	80		10
Июль	120	45	40		50			25	25	35		20			60	205	90		125
Август	110	35	115		50			15	60	90		20			130	250	70		305
Сентябрь	90	150	55		50			110	0	60		80			110	200	340		165
Октябрь	150	160	45		70			90	-20	125		130			105	345	380		130
Ноябрь	55	35	65		40			20	0	35		25			115	130	80		180
Декабрь	45	20	90		20			10	10	30		15			130	95	45		230

пост по кред.		выплаты по кредиту
			95
			45
155			0
		57,125	2,875
		55,3155	2,5595
		53,5095	-0,95
			114,05
			294,05
			154,05
			119,05
			169,05
			219,05

Средства на начало периода

S1	S2	S3
15	15	15

Проценты по кредиту
Q1	Q2
42	60

Сумма кредита	Выплаты по кредиту
155	1-й месяц	57,125
103,3	2-ой месяц	55,3155
51,7	3-ий месяц	53,5095

Как видно из рис. 3.1 (диаграмма «Консолидированный поток в целом по предприятию») и таблицы 3.5 в марте появляется нехватка денежных средств в размере 155 млн.руб., поскольку платежи превышают поступления.

Нехватка денежных средств ощущается на протяжении трех месяцев. Поэтому предприятие берет кредит на эту сумму сроком на три месяца под 42% годовых.

Как показали расчеты, привдеенные в табл. 3.5 это оказалось выгодным, так как прирост средств на конец года превысил исходный показатель более чем на 10 млн.руб.

2.4.4. Использование финансовой функции БЗРАСПИС

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

Синтаксис.

БЗРАСПИС(основной_капитал; ставки)

Основной_капитал – это текущая стоимость инвестиции.

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

Значения в аргументе ставки могут быть числами или пустыми ячейками; любые другие значения дают в результате значение ошибки #ЗНАЧ! При работе функции БЗРАСПИС. Пустые ячейки трактуются как нули (нет дохода).

Пример.

Фирма положила на счет 6млн.руб., процентные ставки по годам следующие:

12% в 1-ом году

13% во 2-ом году

14% в 3-ем году

16% в 4-ом году

16% в 5-ом году.

Тогда эта функция может быть записана как:

БЗРАСПИС(D4;B5:B10), и в итоге результат будет 11648461руб.

Решение задачи в табличной форме выглядит так, как показано в таблице 3.6

Таблица 3,6
Пусть фирма поместила на счет сумму:			6000000
		Начислено
K	0,12	В 1-ом году
M	0,13	Во 2-ом году
N	0,14	В 3-ем году
L	0,16	В 4-ом году
L	0,16	В 5-ом году
В конце пятого года будет начислено:			1,2E+07

Все расчеты представлены в приложении С.