Компьютерные технологии моделирования финансово-экономических процессов (стр. 3 )

- В окне «Зависимая переменная» первая переменная Y будет выделяться автоматически. После этого необходимо щелкнуть кнопку «Выбрать».

- После этого в окно «Переменные» будет осуществлен перенос всех остальных переменных, т. е. факторных признаков (X). Щелкнуть «Выбрать все».

- Количество точек прогноза установить, равное 2.

Сделанные установки подтвердить кнопкой ОК.

После этой последовательности действий внизу экрана появятся следующие вкладки:

- Оценки коэффициентов,

- Таблица остатков,

- Характеристики остатков,

- Характеристики ряда остатков,

- Интервальный ряд остатков,

- Таблица прогнозов.

В первой вкладке «Оценка коэффициентов» представлен расчет параметров множественной регрессии (см. рисунок 42).

Рисунок 42. Оценка коэффициентов регрессии

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

Y(t)=-0..000481*X1+0.001243*X2-0.001526*X3-0.000131*X4+0.000039*X5+0.018864*X6

3. Анализ качества линейной модели множественной регрессии может быть осуществлен используя информацию вкладки «Характеристики остатков» (см. рисунок 43).

Рисунок 43. Оценка качества множественной регрессии

4-5. Построение прогноза ВВП и представление результатов моделирования
графически.

Точечный и интервальный прогноз результативного признака, а также графическое представление результатов в программе VSTAT осуществляется автоматически при задании периода прогноза в окне установки параметров регрессионного анализа. Результаты прогнозирования представлены во вкладке «Таблица прогнозов» (см. рисунок 44).

Рисунок 44. Прогнозирование по регрессионной модели

Можно сделать следующие выводы:

- Коэффициент детерминации равен 0,96, следовательно, 96 % вариации ВВП объясняется вариацией факторных признаков, включенных в анализ (расходы на конечное потребление в текущих ценах, расходы домашних хозяйств, валовое накопление, суточная калорийность питания населения, ожидаемая продолжительность жизни).

Значение коэффициента детерминации достаточно близко к единице, следовательно, общее качество уравнения множественной регрессии можно признать высоким. Этот вывод подтверждает также значение интегрированного критерия качества, равный 95,15 (по 100 бальной шкале).

- Построенное уравнение регрессии признается статистически значимым с вероятностью расчетов 0,95 ( F-критерий Фишера равен 73,47, что превосходит табличное значение критерия).

- Точность модели можно охарактеризовать средней относительной ошибкой аппроксимации, равной 1,92 %, что свидетельствует о ее высокой точности и достоверности полученных результатов.

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

2.2. Моделирование финансово-экономических процессов с помощью ПП EXCEL

Задача 1. Осуществить анализ зависимости прибыли предприятия (Y) от величины оборотного капитала (X1) и стоимости основных средств (X2). Данные приведены в условных единицах.

Y	148	152	144	132	30	124	132	124	116	118
X1	63	71	67	75	81	91	93	85	95	97
X2	35	45	49	33	55	61	55	61	65	67

Решение задачи рекомендуется осуществлять по следующей схеме:

1.  Вычислить статистические характеристики для каждого показателя.

2.  Построить матрицу коэффициентов парной корреляции и осуществить её анализ.

3.  Построить однофакторную линейную регрессию, рассчитать её параметры.

4.  Вычислить точечный прогноз факторного признака, построив по нему линейный тренд.

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

6.  Результаты расчетов отразить на графике.

Решение

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

Введите исходные данные в MS Excel. В главном меню выберите Сервис->Анализ данных->Описательная статистика(если в закладке Сервис пункт Анализ данных отсутствует, то выберите Сервис-Надстройки и поставьте галочку напротив Пакет анализа, рисунок 11)

Рис.11 Выбор надстройки “Пакет анализа”

Заполните диалоговое окно ввода параметров:

·  Входной интервал – диапазон, содержащий анализируемые данные.(3 столбца в нашем случае).

·  Группирование – по столбцам.

·  Метки - флажок, который указывает, содержит ли первая строка названия столбцов или нет(в нашем случае - содержит).

·  Выходной интервал – достаточно указать левую верхнюю ячейку будущего диапазона.

·  Итоговая статистика – поставить галочку.

Рис.12 Параметры инструмента «Описательная статистика»

Результаты на рисунке 13

Рис.13 Описательная статистика

  II.  Матрицу парных коэффициентов корреляции переменных можно рассчитать, используя инструмент анализа данных Корреляция. Для этого:

1.  В главном меню выбрать Сервис->Анализ данных->Корреляция

2.  Заполнить диалоговое окно ввода параметров Рисунок 14

Рис.14 Параметры инструмента «Корреляция»

3.  Матрица коэффициентов парной корреляции представлена на рисунке 15

Рис.15 Матрица коэффициентов парной корреляции

Из матрицы видно, что между факторами X1 и X2 существует эффект мультиколлинеарности (коэффициент корреляции между ними 0,8422>0,8), следовательно, из рассмотрения при построении модели необходимо исключить X1, так как коэффициент парной корреляции между ним и Y меньше по модулю, чем между Y и X2.

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

1.  В главном меню выберите Сервис->Анализ данных->Регрессия

2.  Заполните диалоговое окно ввода данных и параметров вывода (Рисунок 16)

·  Входной интервал Y – диапазон, содержащий данные результативного признака.

·  Входной интервал X – диапазон, содержащий данные факторов независимого признака.(Так как модель однофакторная, то построим её на основе фактора X2)

·  Метки – флажок, который указывает, содержит ли первая строка названия столбцов или не.

·  Константа-ноль – флажок, указывающий на наличие или отсутствие свободного члена в уравнении(метку напротив параметра не ставить).

·  Выходной интервал – достаточно указать левую верхнюю ячейку будущего диапазона

Рис.16 Параметры инструмента «Регрессия»

Результаты регрессионного анализа представлены на рисунке 17

Рис.17 Результаты регрессии

IV.  Для определения точечного прогноза факторного признака(X2), необходимо воспользоваться мастером диаграмм, в качестве параметра взяв диапазон для X2 – C2:С11(Рисунок 18).

Рис.18 Выбор типа диаграммы

На полученном графике необходимо добавить линию тренда(см. задача 1) Рисунок 19

Рис.19 Прогноз X2

Исходя из полученного уравнения тренда, можно рассчитать прогнозное значение фактора X2 (для следующего, 11-го, измерения):

X2=3,3939*11+33,933=71,2659

  V.  Для отображения прогнозного значения результативного признака нужно воспользоваться мастером диаграмм (Рисунок 20)

Рис.20 Выбор типа диаграммы

В качестве входных диапазонов необходимо взять данные X2 (C2:C11) и Y (A2:A11), на полученном графике отразить линию тренда (Рисунок 21)

Рис.21 Прогноз Y

Используя полученное уравнение, определим прогнозное значение Y при прогнозном значении X2, полученном выше:

Y=-0,9825*71,2659+173,68=103,661

Полученную точку отразить на графике. Для этого

Выделить полученный график Щелкнуть на нем правой кнопкой мыши и выбрать Исходные данные Добавить новый ряд, и ввести значения X и Y, полученные выше (рисунок 22)

Рис.22 Добавление прогнозной точки на график

Результат показан на рисунке 21.

Задача 3. Построение прогнозов по многофакторной регрессионной модели

По десяти кредитным учреждениям получены данные, характеризующие зависимость объема прибыли (Y) от среднегодовой ставки по кредитам (X1), ставки по депозитам (X2) и размера внутрибанковских расходов (X3).

Y	32	40	44	28	50	56	50	56	60	62
X1	22	30	20	32	44	34	52	56	66	68
X2	56	48	52	58	66	62	48	66	70	68
X3	64	68	82	76	84	96	100	104	108	102

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

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

2.  Рассчитать параметры регрессионной модели.

3.  Для характеристики модели определить:

·  парные коэффициенты корреляции;

·  линейные коэффициенты частной корреляции;

·  линейный коэффициент множественной корреляции;

·  коэффициент детерминации;

·  средние коэффициенты эластичности;

·  бета-, дельта-коэффициенты

4.  Оценить с помощью t-критерия Стьюдента статистическую значимость коэффициентов уравнения множественной регрессии.

5.  Определить точечный и интервальный прогноз результирующего показателя

6.  Построить график модели множественной регрессии.

Решение.

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

·  выбрать Сервис->Анализ данных->Корреляция

·  заполнить необходимые поля диалогового меню, рисунок 23

Рис.23 Ввод параметров инструмента «Корреляция»

·  результаты представлены на рисунке 24

Рис.24 Таблица парной корреляции

Так как между факторными признаками X1 и X3 существует эффект мультиколлинеарности (коэффициент корреляции между ними 0,843608>0,8), то для построения модели выбираем признаки X2 и X3 (X1 исключаем, так как коэффициент корреляции между X1 и Y меньше, чем между X3 и Y 0,783838<0,883761).

Расчет параметров регрессионной модели можно осуществить с помощью инструмента анализа данных Регрессия (см. задача 2), отличие заключается в том, что в качестве диапазона значений фактора X необходимо указать диапазон значений факторов X2 и X3 (Рисунок 25)

Рис.25 Ввод параметров регрессии

Результаты представлены на рисунке 26

Рис.26 вывод итогов регрессии

На основе полученных данных можно записать уравнение множественной регрессии: Y=0,197247*X2+0,592429*X3-16,2872

Оценим качество построенной множественной модели с помощью коэффициентов эластичности, b - и D - коэффициентов.

Коэффициент эластичности определяется

, где

- среднее значение соответствующего факторного признака

- среднее значение результативного признака

bi – коэффициенты факторных признаков из уравнения множественной регрессии

ß-коэффициент определяется

, где

- среднеквадратическое отклонение (СКО) соответствующего факторного признака (рассчитывается как корень квадратный из дисперсии признака)

- СКО результативного признака

∆-коэффициент определяется

, где

- коэффициент парной корреляции результативного признака и соответствующего фактора X (1-й столбец таблицы парной корреляции)

- коэффициент детерминации (1 таблица (регрессионная статистика), 2-я строка сверху из вывода итогов для Регрессии см. рисунок 26)

На рисунке 27 представлены формулы расчетов описанных выше коэффициентов

Рис.27 Формулы расчетов коэффициентов

Результаты вычислений представлены в таблице 1.

Табл.1 Результаты расчета коэффициентов

	Y	X2	X3
	32	56	64
	40	48	68
	44	52	82
	28	58	76
	50	66	84
	56	62	96
	50	48	100
	56	66	104
	60	70	108
	62	68	102
ср. знач	47,8	59,4	88,4
эласт.		0,245114	0,881663
дисп	134,6222	67,6	247,8222
ско	11,60268	8,221922	15,74237
bi		0,197247	0,592429
Bi		0,139774	0,803801
ryxi		0,599604	0,883761
deltai		0,105529	0,894471

Выводы по таблице: Частный коэффициент эластичности показывает, на сколько % изменится среднее значение результативного признака, если среднее значение конкретного факторного признака изменится на 1%, т. е при увеличении на 1% размера внутрибанковских расходов (X3) объём прибыли увеличится на 0,88%.