Расчет линейной регрессии и корреляции

Куссый М.Ю.

СБОРНИК ЗАДАЧ ПО ДИСЦИПЛИНЕ

«ТЕХНОЛОГИИ ФИНАНСОВЫХ РАСЧЕТОВ»

для студентов 4 курса дневной формы обучения и

5 курса заочной формы обучения

№ варианта	1	2	3	4	5
1 буква фамилии	А, Е, Й, О, У, Ш, Э	Б, Ё, К, П, Ф, Щ, Ю	В, Ж, Л, Р, Х, Ь, Я	Г, З, М, С, Ц, Ы	Д, И, Н, Т, Ч, Ъ

Симферополь, 2011

Рекомендовано к печати заседанием кафедры

от «26» января 2011 г.,

протокол № 5

Рекомендовано к печати учебно-методическим

советом ТНУ от 16.03.2011

протокол № 3

Желтым в оглавлении выделены задания на практику

Синим в оглавлении выделено задание на сам/ работу (М.Ю.)

СОДЕРЖАНИЕ

Предисловие 1. МЕТОДИЧЕСКИЕ УКАЗАНИЯ ПО РЕШЕНИЮ ЗАДАЧ ПО ДИСЦИПЛИНЕ «ТЕХНОЛОГИИ ФИНАНСОВЫХ РАСЧЕТОВ 1.1. Расчет процентов по депозитному вкладу 1.2. Расчет линейной регрессии и корреляции 1.3. Прогнозирование динамики временных рядов 1.4. Расчеты по купонным облигациям 1.5. Расчет чистой текущей стоимости проекта (NPV) 1.6. Работа со штатным расписанием фирмы 1.7. Расчет себестоимости изделия 1.8. Решение задачи оптимизации 2. ЗАДАНИЯ ПО ПРЕДМЕТУ «СОВРЕМЕННЫЕ ТЕХНОЛОГИИ В ФИНАНСОВЫХ РАСЧЕТАХ» 2.1. Расчет процентов по депозитному вкладу 2.2. Расчет линейной регрессии и корреляции 2.3. Прогнозирование динамики временных рядов 2.4. Расчеты по купонным облигациям 2.5. Чистая текущая стоимость (NPV) 2.6. Работа со штатным расписанием фирмы 2.7. Расчет себестоимости изделия 2.8. Решение задачи оптимизации Максимальная оценка за решенную задачу в баллах 3. ФУНКЦИИ EXCEL, ИСПОЛЬЗУЕМЫЕ В РАСЧЕТАХ СБОРНИКА СПИСОК РЕКОМЕНДУЕМЫХ ИСТОЧНИКОВ

2 3 3 5 10 13 16 25 28 32 39 39 39 40 40 41 42 44 45 46 47 50

ПРЕДИСЛОВИЕ

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

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

Место в учебном процессе: дисциплина «Технологии финансовых расчетов» базируется на знаниях и навыках, которые приобретаются студентами в процессе изучения следующих дисциплин: «Финансы и кредит», «Инвестирование», «Экономико-математическое моделирование», «Финансовая математика», «Финансы предприятий».

В сборнике рассмотрен и решен ряд практических финансовых задач с использованием встроенных средств Microsoft Excel.

МЕТОДИЧЕСКИЕ УКАЗАНИЯ ПО РЕШЕНИЮ ЗАДАЧ ПО ДИСЦИПЛИНЕ «ТЕХНОЛОГИИ ФИНАНСОВЫХ РАСЧЕТОВ

РАСЧЕТ ПРОЦЕНТОВ ПО ДЕПОЗИТНОМУ ВКЛАДУ

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

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

Исходные данные:

· срок вклада – 1 год или 12 месяцев;

· сумма вклада на депозит – 2000 руб.;

· проценты на остаток на счете (годовые) – 20%;

· выплата процентов по вкладу осуществляется ежемесячно.

Для расчетов понадобятся следующие функции Excel:

· ОКРУГЛ() – для округления результатов вычислений до копеек (2 знака после запятой);

· СУММ() – для суммирования по столбцу (строке);

· СТЕПЕНЬ() – для вычисления степени по формуле 2.

ПРИМЕЧАНИЕ: Порядок использования функций – см. в мастере функций, который находится на листе Excel в закладке Вставка в подзакладке Функция (f_x).

Алгоритм расчетов на листе Excel с указанием используемых формул и полученными результатами представлен на рисунке 1.

Алгоритм расчетов

Расчет простых процентов по депозитному вкладу осуществляем по формуле:

(1)

где Р – сумма процентов доход по депозиту за период;

D – размер депозита;

r – процент, выплачиваемый за месяц (в нашем случае r=20/12/100*2000=1,67 руб.);

n – количество месяцев в рассматриваемом периоде.

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

(2)

Расчет простых процентов по депозитному вкладу за первый месяц депозитного хранения согласно формуле (1) осуществляем в Excel по формуле:

=ОКРУГЛ(B$2*B$3/100/12*E2;2),

где B$2 – ячейка Excel, в которой хранится значение суммы депозита (для удобства дальнейших расчетов в Excel адрес этой ячейки фиксируется с помощью значка «$»; нужно фиксировать только строку, так как применяется способ расчета по столбцу);

B$3 – ячейка Excel, в которой хранится значение годовых процентов по депозитному вкладу (для удобства дальнейших расчетов в Excel адрес этой ячейки фиксируется с помощью значка «$»);

E2 – ячейка Excel, в которой хранится значение номера отчетного месяца (в данном случае – первый месяц), за который выплачиваются проценты.

Рис. 1. Алгоритм расчетов по 1 заданию

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

Расчет суммы общего дохода (включая сумму первоначального депозита) по депозитному вкладу за расчетный период осуществляем в Excel по формуле:

=F28+B$2,

где B$2 – ячейка Excel, в которой хранится значение суммы депозита;

F28 – ячейка Excel, в которой хранится значение суммы полученных простых процентов за весь расчетный период.

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

=ОКРУГЛ(B$2*СТЕПЕНЬ((1+B$3/12/100);E17)-B$2;2),

где B$2 – ячейка Excel, в которой хранится значение суммы депозита;

B$3 – ячейка Excel, в которой хранится значение годовых процентов по депозитному вкладу;

Е17 – ячейка Excel, в которой хранится значение номера отчетного месяца (в данном случае – первый месяц), за который выплачиваются проценты. Эта величина в формуле представляет собой показатель степени, в которую возводится число.

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

=F28+B$2,

где B$2 – ячейка Excel, в которой хранится значение суммы депозита;

F28 – ячейка Excel, в которой хранится значение суммы полученных сложных процентов за весь расчетный период.

Результаты расчетов дали следующие значения дохода:

· доход по простым процентам (с учетом округления) составил 400 руб., что вместе с суммой первоначального вклада составило 2400 руб.;

· доход по сложным процентам (с учетом округления) составил 438,78 руб., что вместе с суммой первоначального вклада составило 2438,78 руб.

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

РАСЧЕТ ЛИНЕЙНОЙ РЕГРЕССИИ И КОРРЕЛЯЦИИ

Цель задачи: ознакомиться с технологиями построения уравнение линейной регрессии, реализации метода наименьших квадратов и расчета линейной корреляции, реализованными в Excel.

Предлагается, пользуясь встроенными функциями Excel:

1) построить уравнение линейной регрессии: ;

2) определить при помощи метода наименьших квадратов величину корреляции между исчисленным расчетным значением y (по построенному уравнению регрессии) и значением y из таблицы 1.1 для 2001-2009 годов.

3) провести анализ полученных результатов.

Для расчетов понадобятся следующие функции Excel:

· КОРЕНЬ() – для расчета квадратного корня числа;

· СУММ() – для суммирования по столбцу (строке);

· КОРРЕЛ() – для вычисления парной корреляции.

Исходные данные для задания 2 представлены в таблице 1.1:

Таблица 1.1

Год	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009
Объем выпуска продукции, млн. руб. (y_i)	2910	2890	2933	2941	2945	2952	2957	2964	2968	2970
Объем капиталовложений, млн. руб. (x_i)	1356	1390	1410	1422	1431	1446	1452	1458	1461	1464

Алгоритм расчетов

Линейная регрессия сводится к определению параметров а₀ и а₁ уравнения:

(3)

Система уравнений для определения параметров а₀ и а₁ выглядит так:

(4)

где y_i и x_i – данные таблицы 1;

n – количество членов временного ряда.

Решение системы (4) находим с помощью метода исключения переменных (вычитая из 1 уравнения системы (4) второе уравнение системы) по следующим формулам:

(5)

(6)

Расчет подготовительных вычислений для системы (4) осуществляем в Excel по формулам:

=B5*B5,

где В5 – ячейка Excel, в которой хранится значение объема капиталовложений за текущий год (в данном случае – первый расчетный год).

=B5*B4,

где В5 – ячейка Excel, в которой хранится значение объема капиталовложений за текущий год (в данном случае – первый расчетный год);

В4 – ячейка Excel, в которой хранится значение объема выпуска продукции за текущий год (в данном случае – первый расчетный год).

За все остальные года расчеты (1-2) осуществляются по тому же алгоритму (см. рис. 2).

=СУММ(B4:K4),

где В4:К4 – диапазон суммирования ячеек Excel, в которых хранятся значения объема выпуска продукции за весь расчетный период.

=СУММ(B5:K5),

где В5:К5 – диапазон суммирования ячеек Excel, в которых хранятся значения объема капиталовложений за весь расчетный период.

=СУММ(B9:K9),

где В9:К9 – диапазон суммирования ячеек Excel, в которых хранятся значения объема капиталовложений, возведенного в квадрат, за весь расчетный период.

=СУММ(B11:K11),

где В11:К11 – диапазон суммирования ячеек Excel, в которых хранятся значения объема капиталовложений, умноженного на объем выпуска продукции, за весь расчетный период.

Расчет искомых коэффициентов уравнения линейной регрессии а₁ (5) и а₀ (6) осуществляем в Excel по формулам:

1) а₁:

=(L4-(L4-L11)/(B6-L5)*B6)/(L5-(L5-L9)/(B6-L5)*B6),

где L4 – ячейка Excel, в которой хранится значение за весь расчетный период;

L5 – ячейка Excel, в которой хранится значение за весь расчетный период;

L9 – ячейка Excel, в которой хранится значение за весь расчетный период;

L11 – ячейка Excel, в которой хранится значение за весь расчетный период;

В6 – ячейка Excel, в которой хранится значение количества лет расчета (10);

2) а₀:

=(L4-L11-B14*(L5-L9))/(B6-L5),

где L4 – ячейка Excel, в которой хранится значение за весь расчетный период;

L5 – ячейка Excel, в которой хранится значение за весь расчетный период;

L9 – ячейка Excel, в которой хранится значение за весь расчетный период;

L11 – ячейка Excel, в которой хранится значение за весь расчетный период;

В6 – ячейка Excel, в которой хранится значение количества лет расчета (10);

В14 – ячейка Excel, в которой хранится значение а₁.

Расчетные значения Y_i по уравнению регрессии получаем в Excel по формулам (представлена формула для первого года расчетного периода):

=$B$15+ $B$14*B5,

где В5 – ячейка Excel, в которой хранится значение объема капиталовложений за 1 год расчета;

$В$14 – ячейка Excel, в которой хранится значение а₁(для удобства дальнейших расчетов в Excel адрес этой ячейки фиксируется с помощью значка «$»);

$В$15 – ячейка Excel, в которой хранится значение а₀ (для удобства дальнейших расчетов в Excel адрес этой ячейки фиксируется с помощью значка «$»).

По этому алгоритму осуществляются расчеты за остальные годы расчетного периода (см. рис. 2).

Для применения метода наименьших квадратов используем формулу:

(7)

где В – суммарная оценка погрешности расчетов по методу наименьших квадратов;

Y_i – расчетные значения по уравнению регрессии по годам;

у_i – исходные значения объема выпуска продукции по годам.

Погрешность расчета за первый год вычислим в Excel по формуле:

=(B18-B4)*(B18-B4),

где В18 – ячейка Excel, в которой хранится расчетное значение объема выпуска продукции за 1 год расчета;

В4 – ячейка Excel, в которой хранится исходное значение объема выпуска продукции за 1 год расчета.

По этому алгоритму осуществляются расчеты за остальные годы расчетного периода (см. рис. 2).

Суммарную погрешность расчета В за весь период вычислим в Excel по формуле:

=КОРЕНЬ(СУММ(B20:K20)),

где В20:К20 – диапазон суммирования ячеек Excel, в которых хранятся значения погрешности расчетов за весь расчетный период.

Рис. 2. Алгоритм расчетов по 2 заданию

Суммарную относительную погрешность расчета Y_i за период вычислим в Excel по формуле:

=B22*B6/L4*100,

где В22 – погрешность расчета за весь расчетный период по методу наименьших квадратов;

В6 – ячейка Excel, в которой хранится значение количества лет расчета (10);

где L4 – суммарный объем выпуска продукции за весь расчетный период.

Парную корреляция по y за период вычислим в Excel по формуле:

= КОРРЕЛ(B18:K18;B4:K4),

где В18:К18 – диапазон ячеек Excel, в которых хранятся расчетные значения Y_i за весь расчетный период;

В4:К4 – диапазон ячеек Excel, в которых хранятся значения объема выпуска продукции за весь расчетный период.

Парную корреляцию по х и y за период вычислим в Excel по формуле:

=КОРРЕЛ(B4:K4;B5:K5),

где В5:К5 – диапазон ячеек Excel, в которых хранятся значения объема капиталовложений за весь расчетный период;

В4:К4 – диапазон ячеек Excel, в которых хранятся значения объема выпуска продукции за весь расчетный период.

Алгоритм расчетов на листе Excel с указанием используемых формул и полученными результатами представлен на рисунке 2.

Результаты расчетов позволяют сделать следующие выводы:

· Высокое значение показателя парной корреляции (>0,91) и малая величина относительной погрешности прогнозирования (<1,09%) позволяют утверждать, что составленное уравнение регрессии (у=0,672484783387301 х+1982,01924453955) для рассматриваемого временного ряда имеет высокую степень достоверности прогноза;

· Такие результаты объясняются высоким значением показателя парной корреляции для исходных временных рядов. y_iи x_i (>0,91).

Понравилась статья? Добавь ее в закладку (CTRL+D) и не забудь поделиться с друзьями: