Технология выполнения компьютерных расчетов

Расчеты показателей анализа динамики продажи товара выполнены с применением пакета прикладных программ обработки электронных таблиц MS Excel в среде Windows.

Расположение на рабочем листе Excel исходных данных (рис. 3) и расчетных формул (в формате Excel) представлено на рис. 2.

Рис. 2. Расположение исходных данных на рабочем листе.

Рис. 3. Результаты расчетов.

представлено графическое изображение динамики реализации товара предприятия розничной торговли за 12 месяцев.

Рис. 4. Диаграмма динамики реализации товара предприятиями розничной торговли за 12 месяцев.

Анализ результатов статистических компьютерных расчетов

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

Реализация товара предприятиями розничной торговли за 12 месяцев упало на -68,48%, что в абсолютном выражении составляет -63 млн. руб.

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

§ 2 Корреляционно-регрессионный анализ данных

Задание:

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

Таблица 4: данные по заданию.

№ компании	Годовая прибыль	Собственные средства	Страховые резервы	Страховые премии	Страховые выплаты
1	92	3444	9563	11456	1659
2	42	2658	6354	5249	2625
3	186	9723	10245	12968	4489
4	48	4526	6398	7589	6896
5	38	5369	5692	7256	5698
6	74	2248	6359	4963	4321
7	48	5671	6892	7259	6692
8	82	4312	7256	6935	756
9	45	2226	8256	2693	5532
10	46	3654	5982	6324	3235
11	65	2635	6359	7853	5325
12	29	2463	7532	8253	6862

Осуществить анализ данных в соответствии с типовым заданием.

Решение задания:

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

1. Присвоение переменных:

Годовая прибыль =>Y

Собственные средства =>X1

Страховые резервы =>X2

Страховые премии =>X3

Страховые выплаты =>X4

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

3. Заполнить диалоговое окно ввода параметров (рис. -).

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

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

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

2. Построим линейную регрессию от ведущего фактора (парную регрессию) с помощью инструмента Анализа данных Регрессия.

Технология выполнения расчетов следующая:

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

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

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

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

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

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

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

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

Рис. 8. Результаты регрессионного анализа.

Оценим качество построенной однофакторной регрессии, используя коэффициент детерминации R-квадрат. По данным осуществленных расчетов он равен 0,54862 значение не достаточно близкое к 1, следовательно, общее качество построенного уравнения регрессии не является достаточно

высоким. Следовательно, нужно провести множественную регрессию.

3) Построение многофакторной регрессионной модели

В данной работе по матрице коэффициентов парной корреляции

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

Найдем множественную регрессию с использованием всех данных.

Рис.9. Диалоговое окно Регрессия.

Рис 10. Результаты регрессионного анализа.

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

Y= 0,01000*X1 + 0,01228*X2 + 0,00118*X3 + (-0,00506*X4) - 49,38447

4) Оценим качество уравнения множественной регрессии с помощью коэффициента детерминации R-квадрат. Исходя из полученных результатов, R-квадрат для множественной регрессии равен 0,80559. При построении уравнения однофакторной регрессии (от ведущего фактора) R-квадрат равен 0,62588, следовательно, можно утверждать, что уравнение множественной регрессии обладает более высоким качеством.

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

С помощью функции СТЬЮДРАСПОБР(A1;A2) определим табличное значение t_таб, A1 = 0,05, A2 = n - m -1, где n - количество наблюдений, m - количество функций. СТЬЮДРАСПОБР(0,05;12-4-1) = 2,364624. Сравним расчетные значения (t-статистика) с табличным по модулю (расчетные значения берутся из столбца t-статистика табл. -, получаемой при регрессионном анализе):

Рис 11. Результаты регрессионного анализа с выделением t-статистика.

tx1 = 2,21329 ≈ 2,36, следовательно, фактор Х₁ статистически значим и информативен, и, следовательно, его можно оставить в уравнении регрессии.

tx2 = 1,92832 < 2,36 следовательно, фактор Х₂ статистически не значим.

tx3= 0,29798 < 2,36 следовательно, фактор Х₃ статистически не значим.

tx4= -1,41379 < 2,36 следовательно, фактор Х₄ статистически не значим.

Пусть X2 статически не значим, тем не менее является вторым статическим фактором по важности.

Построим уравнение регрессии для информативных факторов Х1 и X2.

Рис. 12. уравнение регрессии для информативных факторов Х1 и X2.

Оценим качество данного уравнения регрессии, используя коэффициент детерминации: R-квадрат равен 0,76859, следовательно, качество построенного уравнения регрессии можно признать высоким.

Сравнивая по качеству (коэффициенту детерминации построенные уравнения, можно сделать вывод о том, что лучшим качеством обладает уравнение множественной регрессии, имеющее наибольшее значение R-квадрат, равный 0,80841.

6) Определим точечный и интервальный прогноз объема прибыли организации, используя уравнение множественной регрессии:

Y=0,197247*X2+0,592429*X3-16,2872

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

Выбор типа диаграммы и технология построения приведены на рис.14 и рис.15-16.

7) Определим точечный и интервальный прогноз объема прибыли организации, используя статически значимые информативные факторы X1, X2 и уравнение множественной регрессии для этих факторов.

Y= 0,00973*X1+ 0,01538*X2 -84,88002

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

Выбор типа диаграммы и технология построения приведены на рис. и рис.

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

Рис.14. Выбор источника данных.

На полученной диаграмме добавить линию тренда (Диаграмма->Добавить линию тренда). В настройках тренда указать Параметры->Показать уравнение на диаграмме, Параметры ->Прогноз вперед на 1 единицу (рис. 16).

Рис.15. Параметры линии тренда.

Результат данных X1 представлен на рис.16. и рис.17.

Линию тренда на графике X1 построили линейную и полиноминальную.

Рис.16. Линейная линия тренда для фактор X1.

Рис.17. Полиноминальная линия тренда для фактор X1.

В полиноминальной модели величина аппроксимации R² 0.212, а в линейной модели 0.145.

0.212 > 0.145. Эффективность полиноминальной модели выше, и она более подходит для прогнозирования.

Из полученного уравнения тренда спрогнозируем значение X1 в 13 и 14 расчетный период:

X1_{Прогн.13} = -50,47*13² + 429,8*13 + 4017 = 1074.97

X1_{Прогн.14} = -50,47*14² + 429,8*14 + 4017 = 142.08

Результат данных X2 представлен на рис.18.

Линию тренда на графике X2 построили полиноминальную как наиболее эффективную.

Рис.18. Полиноминальная линия тренда для фактор X2.

Из полученного уравнения тренда спрогнозируем значение X2 в 13 и 14 расчетный период:

X2_{Прогн.13} = 46,42*13² - 735,0*13 + 9503 = 7792,98

X2_{Прогн.14} = 46,42*14² - 735,0*14 + 9503 = 8311,32

Графики прогноза X1 и X2 ниже. Рис.19. и Рис.20.

Рис.19. Графики прогноза для фактора X1.

Рис.20. Графики прогноза для фактора X2.

Рассчитаем точечный прогноз прибыли (результативного признака) Y, подставив в уравнение множественной регрессии точечные прогнозы факторных признаков X1 и X2 за 13 и 14 период:

Y_прог.13=0,00973*X1_прог.13+0,01538*X2_прог.13-84,88002= 0,00973*1074,97+0,01538*7792,98-84,88002= 45,43547

Y_прог.14=0,00973*X1_прог.14+0,01538*X2_прог.14-84,88002= 0,00973*142,08+0,01538*8311,32-84,88002= 44,33052

Вывод: Полученный коэффициент детерминации R²=0,74502, следовательно, вариация результативного признака Y на 74,5% учтена в модели и обусловлена влиянием включенных в модель факторов. Коэффициент множественной корреляции R= 0,80559 показывает, что зависимая переменная Y тесно связана с включенными в модель факторами X1 и X2.

Прибыль организации на 13 и 14 последующий период составит 45,44 и 44,33 тыс. руб.