Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

Министерство образования и науки Российской Федерации

Государственное образовательное учреждение высшего профессионального образования

«Южно-Уральский государственный университет»

Торгово-Экономический факультет

Кафедра «Маркетинг и менеджмент»

КУРСОВАЯ РАБОТА

По дисциплине: Бизнес-аналитика

Автор работы: Сорокин Г.В.

студент группы ЭТТ-255з

Челябинск 2015



ВВЕДЕНИЕ

В современном мире точность при принятии решений - вопрос выживания бизнеса. Анализ трендов и прогнозирование просто необходимо для выбора правильного пути развития.

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

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

Объектом работы данной работы послужили данные о продажах топлива на автоматической АЗС Салават города Златоуст за Февраль 2014 года, а так же данные о продажах интернет сайта за Февраль-Март 2015 года.



ПРОГНОЗИРОВАНИЕ ДЛЯ ОДНОЙ ПЕРЕМЕННОЙ

принятие решение прогнозирование трендовый

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

Исходные данные для задания 1 представлены в таблице 1.

Таблица 1. Исходные данные

День	Объем продаж
1	52 730,00
2	51 454,00
3	51 722,00
4	61 810,00
5	58 338,00
6	42 676,00
7	57 712,00
8	74 161,00
9	62 492,00
10	46 054,00
11	55 342,00
12	57 805,00
13	55 810,00
14	58 323,00
15	69 670,00
16	62 920,00
17	37 085,00
18	66 739,00
19	46 803,00
20	51 712,00
21	51 681,00
22	78 430,00
23	61 756,00
24	37 765,00
25	56 061,00
26	52 202,00

Задача 1. Построить описывающие линии для y(t) трендовым прогнозированием (тип - полиномиальный степени 1;2;3;4) и авторегрессионным: метод - скользящее среднее (для 3 интервалов на ваш выбор; начинаем с n=3) и метод - экспонециальное сглаживание (для 3 значений фактора затухания; начинаем с 0,7);

Для построения описывающих линий трендовым прогнозированием необходимо построить диаграмму для наших данных (Рисунок 1).

Рисунок 1. Диаграмма исходных данных

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

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

Для построения линии тренда воспользуемся командой «Добавить линию тренда…». Параметры тренда представлены на рисунке 2.

Рисунок 2. Параметры линии тренда

Для прогнозирования используем линейную функцию (Полиномиальная 1-ой степени) и полиномиальные 2-ой, 3-ий и 4-ой степени. Так же не забываем настроить параметр прогнозирования на несколько периодов. Для получения уравнения тренда так же необходимо установить флаг. Графики линий трендов представлены на рисунках 3-6.

Рисунок 3. Линия тренда (линейная)

Рисунок 4. Линия тренда (Полином 2-ой степени)

Рисунок 5. Линия тренда (Полином 3-ий степени)



Рисунок 6. Линия тренда (Полином 4-ой степени)

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

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

Мы воспользуемся двумя авторегрессионными методами: Скользящего среднего и экспоненциальное сглаживание.

Использование в качестве приближения скользящего среднего позволяет сгладить колебания данных и таким образом более наглядно показать характер зависимости. Такая линия тренда строится по определенному числу точек (оно задается параметром Шаг). Элементы данных усредняются, и полученный результат используется в качестве среднего значения для приближения. Так, если Шаг равен 2, первая точка сглаживающей кривой определяется как среднее значение первых двух элементов данных, вторая точка -- как среднее следующих двух элементов и так далее.

Для использования данного метода необходимо установить пакет анализа данных для используемой версии Microsoft Office. Далее выбираем на вкладке ДАННЫЕ «Анализ данных» и в списке переходим на инструмент «Скользящее среднее» (Рисунок 7).

Рисунок 7. Анализ данных

В форме «Скользящее среднее» (Рисунок 8) устанавливаем входной интервал, интервал - количество шагов, и ячейку выхода. Так же не забываем про вывод графика. Так как нас интересует прогнозирование, то во входном интервале были установлены дополнительные пустые ячейки.

Рисунок 8. Скользящее среднее

Результаты для интервалов 3, 5 и 6 можно увидеть ниже (Рисунки 9, 10, 11)



Рисунок 9. Скользящее среднее. Интервал - 3

Рисунок 10. Скользящее среднее. Интервал - 5

Рисунок 11. Скользящее среднее. Интервал - 6



Экспоненциальное приближение следует использовать в том случае, если скорость изменения данных непрерывно возрастает. Однако для данных, которые содержат нулевые или отрицательные значения, этот вид приближения неприменим.

Экспоненциальное приближение настраивается аналогично скользящему среднему (Рисунок 12) представленному ранее.

Рисунок 12. Форма «Экспоненциальное сглаживание»

В качестве фактора затухания выбираем параметры 0.55, 0.7, 0.85. Результаты представлены ниже (Рисунки 13, 14, 15)

Рисунок 13. Экспоненциальное сглаживание. Фактор затухания - 0.55



Рисунок 14. Экспоненциальное сглаживание. Фактор затухания - 0.7

Рисунок 15. Экспоненциальное сглаживание. Фактор затухания - 0.8

Задача 2. определить лучший прогноз на следующий временной период tо+1 по критерию наименьшей близости тренда к базовой линии.

Для определения лучшего прогноза используем критерий суммы квадратов ошибок (SSE) и R-квадрат.

Критерий SSE (Sum of squares due to error) - сумма квадратов ошибок вычисляется по формуле:



где wk - веса (в нашем случае приравнивается к 1), yk - данные в xk, а k - значения параметрической модели в xk. Близость SSE к нулю говорит о хорошем качестве приближения данных параметрической моделью.

Критерий R-квадрат (R-square) - квадрат смешанной корреляции. Определяется как отношение суммы квадратов относительно регрессии SSR к полной сумме квадратов (SST), т.е.

Критерий R-квадрат может принимать значения только от нуля до единицы и, как правило, чем ближе он к единице, тем лучше параметрическая модель приближает исходные данные. Итоговые результаты представлены в таблице 2.

В итоге по выбранным нами критериям самым точным методом является метод экспоненциального сглаживания при выбранном факторе затухания 0,55. А значит самое вероятное значение на следующую дату 52982 руб.

Таблица 2. Сравнительный анализ

Объем продаж, Y	ST	Линейный тренд	Полином 2-й степени	Полином 3-й степени
		nY	SE	nY	SE	nY	SE
52 730,00	11 526 808,47	56 383,85	13 350 649,05	52 853,66	15 291,05	52 237,66	242 399,07
51 454,00	21 819 318,94	56 401,71	24 479 814,45	53 674,43	4 930 300,50	53 354,41	3 611 568,81
51 722,00	19 387 425,09	56 419,56	22 067 088,74	54 425,31	7 307 901,18	54 339,62	6 851 940,75
61 810,00	32 317 913,09	56 437,42	28 864 658,84	55 106,31	44 939 432,80	55 198,65	43 709 996,42
58 338,00	4 896 858,32	56 455,27	3 544 672,25	55 717,43	6 867 413,33	55 936,85	5 765 521,32
42 676,00	180 878 704,63	56 473,12	190 360 630,67	56 258,65	184 488 435,35	56 559,59	192 754 171,25
57 712,00	2 518 202,78	56 490,98	1 490 894,72	56 729,99	964 337,75	57 072,24	409 294,39
74 161,00	325 293 133,86	56 508,83	311 599 035,10	57 131,45	290 005 641,32	57 480,15	278 250 850,14
62 492,00	40 537 219,71	56 526,69	35 584 971,12	57 463,02	25 290 670,01	57 788,68	22 121 215,26
46 054,00	101 427 365,09	56 544,54	110 051 429,49	57 724,70	136 205 238,49	58 003,20	142 783 380,64
55 342,00	613 269,71	56 562,39	1 489 361,52	57 916,50	6 628 034,80	58 129,07	7 767 745,81
57 805,00	2 822 012,32	56 580,25	1 500 017,46	58 038,41	54 479,29	58 171,64	134 428,41
55 810,00	99 297,71	56 598,10	621 104,76	58 090,43	5 200 374,67	58 136,29	5 411 640,05
58 323,00	4 830 696,78	56 615,96	2 913 999,22	58 072,57	62 714,18	58 028,37	86 804,24
69 670,00	183 463 899,24	56 633,81	169 942 249,72	57 984,83	136 543 314,78	57 853,25	139 635 580,56
62 920,00	46 170 456,94	56 651,66	39 292 036,21	57 827,19	25 936 693,32	57 616,28	28 129 428,87
37 085,00	362 525 993,86	56 669,52	383 553 345,29	57 599,67	420 851 808,30	57 322,83	409 569 795,49
66 739,00	112 654 546,63	56 687,37	101 035 225,45	57 302,27	89 051 910,84	56 978,26	95 272 060,96
46 803,00	86 901 835,24	56 705,23	98 054 079,76	56 934,98	102 656 957,93	56 587,93	95 744 823,79
51 712,00	19 475 587,40	56 723,08	25 110 922,77	56 497,80	22 903 881,64	56 157,20	19 759 803,04
51 681,00	19 750 161,55	56 740,93	25 602 932,08	55 990,74	18 573 833,01	55 691,44	16 083 593,70
78 430,00	497 507 877,71	56 758,79	469 641 429,55	55 413,79	529 746 014,83	55 196,00	539 818 904,70
61 756,00	31 706 861,55	56 776,64	24 794 006,09	54 766,95	48 846 777,97	54 676,25	50 122 928,03
37 765,00	337 093 836,94	56 794,50	362 121 718,01	54 050,23	265 208 781,29	54 137,54	268 060 144,64
56 061,00	4 110,78	56 812,35	564 526,82	53 263,63	7 825 306,89	53 585,25	6 129 338,06
52 202,00	15 390 834,32	56 830,20	21 420 272,27	52 407,13	42 079,14	53 024,73	676 883,99
56 125,12		SSE =	2 469 051 071,42	SSE =	2 381 147 624,68	SSE =	2 378 904 242,41
SST =	2 461 614 228,65	R =	- 0,00302	R =	0,0327	R =	0,0336
Полином 4-й степени	Скользящее среднее. Шаг - 3	Скользящее среднее. Шаг - 5	Скользящее среднее. Шаг - 6
nY	SE	nY	SE	nY	SE	nY	SE
50 789,78	3 764 456,36
53 064,36	2 593 274,79
54 773,22	9 309 951,42	51 968,67	60 844,44
56 009,80	33 642 264,36	54 995,33	46 439 681,78
56 859,56	2 185 777,44	57 290,00	1 098 304,00	55 210,80	9 779 379,84
57 399,93	216 794 197,10	54 274,67	134 529 068,44	53 200,00	110 754 576,00	53 121,67	109 111 952,11
57 700,35	135,83	52 908,67	23 072 011,11	54 451,60	10 630 208,16	53 952,00	14 137 600,00
57 822,22	266 955 705,75	58 183,00	255 296 484,00	58 939,40	231 697 106,56	57 736,50	269 764 200,25
57 818,97	21 837 197,23	64 788,33	5 273 146,78	59 075,80	11 670 422,44	59 531,50	8 764 560,25
57 736,00	136 469 124,00	60 902,33	220 473 002,78	56 619,00	111 619 225,00	56 905,50	117 755 052,25
57 610,70	5 147 005,59	54 629,33	507 893,78	59 152,20	14 517 624,04	56 406,17	1 132 450,69
57 472,46	110 582,32	53 067,00	22 448 644,00	59 170,80	1 865 409,64	58 927,67	1 260 380,44
57 342,66	2 349 032,27	56 319,00	259 081,00	55 500,60	95 728,36	58 610,67	7 843 733,78
57 234,65	1 184 499,63	57 312,67	1 020 773,44	54 666,80	13 367 798,44	55 971,00	5 531 904,00
57 153,81	156 654 949,54	61 267,67	70 599 205,44	59 390,00	105 678 400,00	57 167,33	156 316 673,78
57 097,48	33 901 683,25	63 637,67	515 045,44	60 905,60	4 057 807,36	59 978,33	8 653 402,78
57 055,01	398 801 351,32	56 558,33	379 210 711,11	56 761,60	387 168 587,56	56 935,50	394 042 350,25
57 007,72	94 697 717,02	55 581,33	124 493 525,44	58 947,40	60 709 030,56	58 424,50	69 130 910,25
56 928,95	102 534 849,23	50 209,00	11 600 836,00	56 643,40	96 833 472,16	56 923,33	102 421 146,78
56 784,00	25 725 184,00	55 084,67	11 374 880,44	53 051,80	1 795 064,04	55 821,50	16 887 990,25
56 530,18	23 514 578,68	50 065,33	2 610 378,78	50 804,00	769 129,00	52 823,33	1 304 925,44
56 116,80	497 879 037,04	60 607,67	317 635 565,44	59 073,00	374 693 449,00	55 408,33	529 997 136,11
55 485,13	39 323 819,34	63 955,67	4 838 533,44	58 076,40	13 539 456,16	59 520,17	4 998 950,69
54 568,46	282 356 294,86	59 317,00	464 488 704,00	56 268,80	342 390 614,44	54 691,17	286 495 118,03
53 292,06	7 667 014,88	51 860,67	17 642 800,11	57 138,60	1 161 221,76	56 234,17	29 986,69
51 573,20	395 393,46	48 676,00	12 432 676,00	57 242,80	25 409 664,64	56 315,83	16 923 624,69
SSE =	2 365 795 076,70	SSE =	2 127 921 797,22	SSE =	1 930 203 375,16	SSE =	2 122 504 049,53
R =	0,0389	R =	0,1356	R =	0,2159	R =	0,1378

Таблица 3

Эксп.сглаживание. 0,55	Эксп.сглаживание. 0,7	Эксп.сглаживание. 0,8
nY	SE	nY	SE	nY	SE
52 730,00	1 628 176,00	52 730,00	1 628 176,00	52 730,00	1 628 176,00
52 155,80	188 182,44	52 347,20	390 875,04	52 538,60	666 835,56
51 960,59	97 010 877,35	52 159,64	93 129 448,13	52 416,11	88 245 169,33
56 392,82	3 783 707,73	55 054,75	10 779 743,70	53 825,19	20 365 422,51
57 268,15	212 930 943,04	56 039,72	178 589 108,46	54 502,11	139 856 983,58
50 701,68	49 144 524,65	52 030,61	32 278 231,87	52 728,20	24 838 289,32
53 856,33	412 279 768,94	53 735,02	417 220 472,51	53 475,77	427 878 834,97
62 993,43	251 431,58	59 862,82	6 912 602,22	56 578,55	34 968 860,72
62 767,79	279 350 650,61	60 651,57	213 089 109,36	57 465,57	130 223 922,31
55 246,58	9 104,51	56 272,30	865 458,88	55 753,83	169 607,42
55 289,52	6 327 637,93	55 993,21	3 282 581,92	55 692,06	4 464 519,31
56 421,49	373 915,36	56 536,75	528 161,51	56 009,00	39 601,09
56 146,32	4 737 947,13	56 318,72	4 017 126,11	55 979,15	5 493 631,93
57 125,82	157 356 337,18	56 920,01	162 562 343,63	56 330,73	177 936 186,53
62 770,70	22 289,44	60 745,00	4 730 606,33	58 331,62	21 053 244,68
62 837,89	663 211 185,38	61 397,50	591 097 802,36	59 019,88	481 138 773,52
51 249,09	239 937 379,56	54 103,75	159 649 489,41	55 729,64	121 205 911,28
58 219,55	130 337 575,02	57 894,33	123 017 522,92	57 381,05	111 895 093,54
53 082,10	1 877 178,29	54 566,93	8 150 616,92	55 794,34	16 665 504,45
52 465,56	615 527,90	53 710,45	4 118 667,19	55 181,99	12 256 927,32
52 112,51	692 610 505,02	53 101,61	641 527 087,72	54 656,84	565 163 086,21
63 955,38	4 837 264,24	60 700,13	1 114 860,43	58 222,81	12 483 396,97
62 965,66	635 073 162,68	61 016,89	540 650 450,90	58 752,79	440 487 440,84
51 625,36	19 674 885,45	54 041,32	4 079 091,39	55 604,62	208 279,27
53 621,40	2 014 693,63	54 647,23	5 979 133,89	55 673,08	12 048 397,75
SSE =	3 615 584 851,05	SSE =	3 209 388 768,79	SSE =	2 851 382 096,40
R =	- 0,4688	R =	- 0,3038	R =	- 0,1583

АНАЛИЗ ВЛИЯНИЯ ПЕРЕМЕННЫХ Х1 И Х2 НА ЗАВИСИМУЮ ПЕРЕМЕННУЮ Y

Анализ влияния переменных Х1 и Х2 на зависимую переменную Y.

- таблица Excel: проанализировать три зависимости yi = f(xi) по следующему алгоритму - определить коэффициенты корреляции и их квадраты; построить точечную диаграмму и провести ее аппроксимацию линейным трендом; оценить величину достоверности аппроксимации и произвести прогнозирование для Y на три следующих периода по частному уравнению регрессии. Провести множественный регрессионный анализ с помощью надстройки «Регрессия». Сделать выводы.

Для данного задания были выбраны данные по продажам интернет сайта за Февраль-Март 2015 года.

Таблица 4. Исходные данные для задания 2

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

Y (x1,x2,…, xk) =a0+a1*x1+a2*x2+…+a k*xk

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

· предварительная оценка силы связи;

· количественная характеристика силы связи;

· построение модели связи;

· применение модели связи.

Построим на одном графике зависимости uids_count, exposures, updated

Рисунок 16. Зависимости переменных uids_count, exposures и updated

По графику видно, что между данными uids_count и exposures имеется обратная зависимость, между данными uids_count и updated ярко выраженной зависимости не видно.

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

Таблица 5. Коэффициенты корреляций

Таблица 6. Квадраты коэффициентов корреляций

Из полученных данных видим, что между значениями uid_count и exposures установлена средняя (близкая к сильной) обратная связь.

А между значениями uid_count и updated - связь прямая средняя.

Значение квадрата корреляции 0,43 показывает нам, что 43% изменчивости exposures объясняются изменчивостью uids_count.

Так же мы видим, что между собой значения exposures и updated связаны не так сильно.

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

Рисунок 17. Зависимость exposures от uid_count



Рисунок 18. Зависимость updated от uids_count

Как мы видим из графиков коэффициент достоверности линейной функции очень низкий. Это значит, что значение, прогнозируемое по данной функции, имеет низкую достоверность.

Проведем множественный регрессионный анализ с помощью надстройки «Регрессия».

Рисунок 19. Надстройка «Регрессия»

Рисунок 20. Множественный регрессионный анализ

ЗАКЛЮЧЕНИЕ

В проделанной работе мы выполнили анализ исходных данных для дальнейшего прогнозирования переменной, а так же проанализировали степень влияния двух переменных X1 и X2 на зависимую Y.

В первом задании были использованы методы: трендовое прогнозирование, скользящее среднее и экспоненциальное сглаживание.

При трендовом прогнозировании следует ограничиться лишь линейной функцией, так как остальные имеют заранее «оптимистичный» или «пессимистичный эффект»

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

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

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



БИБЛИОГРАФИЧЕСКИЙ СПИСОК

1. В.Г. Соломенчук, А. Романович Практическая бухгалтерия на Excel для малого бизнеса// - 2004 - С. 110 - 130

2. Уокенбах Д. Подробное руководство по созданию формул в Excel 2003// - 2004 - С. 300 - 342

3. Вильямс. Бизнес-анализ с помощью MS Excel// - 2006 - С. 220- 267

4. Конрад Карлберг. Прогнозирование продаж в Excel для «Чайников»// ООО «И.Д.Вильямс». - 2006 - С. 223 - 280

Размещено на Allbest.ur