Построение прогнозов с использованием возможностей Microsoft Excel

Тип работы:
Лабораторная работа
Предмет:
Программирование


Узнать стоимость

Детальная информация о работе

Выдержка из работы

Лабораторная работа «Построение прогнозов с использованием возможностей Microsoft Excel»

Цель работы

Приобретение навыков разработки прогнозов развития экономических явлений с использованием возможностей Microsoft Excel.

Имеются статистические данные за несколько лет о работе фирмы в одном из направлений ее деятельности. Необходимо, используя статистические методы, рассчитать прогнозное значение интересующего показателя на следующий год, т. е. на будущий период. Кроме того, необходимо проанализировать имеющиеся данные и найти закономерность их изменения во времени. Таким образом, задание сводится к следующим этапам:

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

2) Найти наиболее точную форму зависимости между статистическими данными и временем, а также определить вид этой зависимости и ее точность, используя коэффициент детерминации R2. Используя уравнение регрессии, найти значение исследуемого показателя в будущем периоде.

Указания к построению прогноза

Для примера возьмем следующие исходные данные из табл. 1

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

Период

Данные

Период

Данные

1

2

3

4

5

543

555

583

628

659

6

7

8

9

10

732

843

984

1109

1000

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

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

Рисунок 1. Расчет значения среднего скользящего

Далее производится расчет по следующим периодам. Для быстрого расчета необходимо растянуть данную ячейку на следующие периоды, включая прогнозный. Далее необходимо сделать прогноз с использованием функции ТЕНДЕНЦИЯ. Данная функция находит линейную зависимость между заданными значениями функции y и значениями ее аргументов x. Данная зависимость представляется линейной функцией, а значения ее аргументов находятся в Excel по методу наименьших квадратов. В данном случае значения функции y — это данные, а значения аргументов x — номера периодов для соответствующих данных. Более подробную информацию о данной функции можно узнать с помощью помощника Excel, вызвав ее из меню или нажав клавишу «F1». Для расчета прогноза за необходимый период сначала введем в необходимой клетке таблицы «=ТЕНДЕНЦИЯ (», а далее укажем массив, в котором находятся известные значения функции y. Затем укажем массив, в котором находятся значения аргументов функции x. Последним указывается значение того периода, за который необходимо рассчитать прогноз, т. е. и необходимое значение x, для которого необходимо найти значение функции y.

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

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

Далее сделаем прогноз с использованием функции РОСТ. Данная функция Excel рассчитывает прогнозируемый экспоненциальный рост на основании имеющихся данных. Функция РОСТ возвращает значения y для последовательности новых значений x, задаваемых с помощью существующих x- и y-значений. Т. е. данная функция строит зависимость между функцией и ее аргументами в виде. О ней более подробно также можно узнать из помощника, вызвав ее из меню или нажав клавишу «F1». Для расчета прогноза за необходимый период применяется функция «РОСТ» аналогично функции «ТЕНДЕНЦИЯ» из предыдущего прогноза. Сначала необходимо указать массив, в котором находятся известные значения функции y. Затем массив, в котором находятся значения аргументов функции x, и необходимое значение x, для которого необходимо найти значение функции y.

excel экспоненциальный детерминация полиномиальный

Рисунок 3. Расчет прогноза с использованием функции роста

В итоге получаем прогноз по трем методам (рис. 4).

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

Таким образом, можно сделать вывод, что в зависимости от используемого метода прогнозирования прогнозное значение исследуемого показателя в будущем периоде будет равняться: 1031 для средней скользящей, 1120,73 — при использовании функции тенденции, 1177,31 — при использовании функции роста. Далее, применяя «Мастер диаграмм» строим графики для данных прогнозов. Данный мастер можно вызвать из меню «Вставка» пункт меню «Диаграмма» или нажав на кнопку на панели под главным меню программы. Далее, в появившемся окне необходимо выбрать тип диаграммы. Нам необходимо выбрать тип диаграммы «График» т.к. он наиболее подходит в данном случае для отображения развития изучаемого параметра в динамике (во времени).

Рисунок 5. Построение графика прогноза по методу скользящей средней

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

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

Рисунок 6. График прогноза скользящей средней

Указания к решению второго задания

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

Рисунок 7. График развития исследуемого показателя во времени

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

Рисунок 8. Выбор типа зависимости

Также в закладке «Параметры» необходимо отметить пункты «Показывать уравнение на диаграмме» и «Поместить на диаграмму величину достоверности аппроксимации (R2)». Первый пункт необходимо отметить для того, чтобы на диаграмме отображалось уравнение регрессии, а второй — для отображения значения коэффициента детерминации R2 (рис. 9).

Рисунок 9. Изменение параметров для линии тренда

После нажатия на кнопку «ОК» добавится линия, отображающая зависимость между имеющимися данными, а также уравнение регрессии и значение R2.

Рисунок 10. Линейная зависимость между исследуемыми показателями

В данном случае был выбран линейный тип зависимости. Как видно из результатов (рис. 10), уравнение регрессии линейного вида довольно точно отображает зависимость между изучаемым параметром и временем. Значение данного коэффициента может лежать в интервале от нуля от единицы. Чем ближе значение коэффициента к R2 единице, тем точнее данное уравнение регрессии определяет зависимость между имеющимися данными, а чем ближе его значение к нулю — тем хуже данный тип зависимости подходит в данном случае. В данном случае R2 = 0,9031, т. е. линейная зависимость достаточно точно отображает зависимость изучаемого параметра от периода времени.

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

В данном случае наиболее точно развитие исследуемого процесса в динамике отображает полиномиальная зависимость 3й степени (рис. 11). Коэффициент детерминации наиболее близок к единице и равен 0,9555, а уравнение регрессии имеет следующий вид:

.

Подставив в данное уравнение значение будущего периода (одиннадцатого), получим прогнозное значение:

.

Рисунок 11. Полиномиальная зависимость между исследуемыми показателями

Варианты заданий (по исходным данным построить прогноз с использованием функций Скользящая Средняя, Тенденция, Рост)

Периоды

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

Варианты

1

4,2

4,5

4,2

4,6

3,5

3,3

3,9

4,0

4,3

4,1

3,5

3,4

2

16,3

15,5

20,0

22,1

29,9

39,9

36,4

33,5

25,1

18,0

13,3

12,7

12,9

12,4

3

9,5

9,4

8,9

8,3

8,1

7,1

5,3

5,4

4,9

5,6

6,7

8,7

10,4

9,5

9,5

8,6

4

117

116

127

108

133

129

147

166

148

227

170

131

5

4272

4478

4964

4514

4430

3786

3722

3039

3029

2969

2440

3592

3517

3727

4245

6

33,9

30,3

23,8

26,1

26,2

65,9

43,4

47,4

43,3

50,3

35,5

53,7

7

7,3

7,4

7,4

4,2

3,2

3,3

2,5

2,5

5,5

2,3

14,7

12,8

13,6

8

302

257

256

281

296

267

320

324

293

278

289

300

318

268

288

312

282

308

281

9

130,0

129,3

130,8

131,4

138,1

142,1

149,4

145,7

145,2

147,4

143,8

155,5

10

31,0

31,9

48,6

37,8

34,4

44,2

50,8

42,4

42,7

37,7

36,3

42,8

34,3

36,6

44,6

42,7

42,1

11

17,8

16,7

17,7

17,7

18,5

16,4

21,4

21,0

19,5

18,8

18,5

18,7

19,6

22,0

12

14,6

11,8

10,2

6,0

2,4

1,4

0,7

1,4

1,8

3,5

6,8

13,4

12,5

11,2

11,3

13

32,2

31,5

34,3

33,7

34,1

32,5

33,4

34,3

33,5

34,2

34,6

35,0

14

16,3

18,4

23,9

19,5

21,4

17,4

17,1

18,9

17,7

17,6

16,0

18,0

19,9

19,5

20,4

19,8

15

82,3

49,0

48,8

45,5

50,6

39,5

46,2

42,9

32,2

32,9

50,7

30,6

63,6

52,1

57,2

59,0

16

0,5

0,6

0,8

1,0

1,8

1,9

2,0

1,7

2,0

1,6

1,0

0,7

0,7

0,7

0,9

1,2

1,5

1,6

1,8

17

4,3

4,5

4,7

4,9

4,8

4,7

4,7

2,6

4,1

4,3

3,6

4,8

4,4

5,0

3,7

5,5

18

26,7

23,6

24,9

22,9

24,0

30,9

33,8

36,7

39,1

36,5

47,3

59,8

64,8

77,4

19

136,3

124,4

137,7

133,4

137,0

130,0

135,3

136,4

134,4

139,3

134,5

141,0

20

95,7

99,4

96,8

93,8

93,2

91,5

91,2

92,3

91,5

95,8

99,3

96,8

100,4

101,6

21

26,0

24,0

28,0

17,0

22,0

15,0

26,0

27,0

27,5

28,5

30,0

27,0

27,0

28,0

30,0

31,1

35,0

34,8

22

8,3

7,4

14,6

10,9

8,5

9,8

13,0

12,6

11,4

13,8

10,6

8,0

7,2

6,4

12,5

14,3

13,4

12,8

15,0

23

62,1

151,9

165,9

134,2

82,7

110,7

74,1

51,1

118,3

89,1

46,2

93,7

80,7

51,7

118,1

24

8,3

7,4

14,6

10,9

8,5

9,8

13,0

12,6

11,4

13,8

10,6

8,0

7,2

6,4

25

221

126

373

284

287

263

226

280

223

250

183

220

231

321

309

299

236

218

26

35,1

33,3

35,4

33,0

32,7

40,5

41,7

51,6

56,7

106,9

107,9

27

253

287

453

728

599

557

759

718

573

587

472

427

493

601

547

809

28

829

523

775

615

448

477

535

487

513

334

296

937

486

543

543

467

456

459

29

701

709

780

861

786

764

886

844

318

806

673

788

583

560

674

660

602

30

1297

1207

1548

1523

1321

1114

1306

1170

1532

1338

1282

1641

1565

1771

1940

31

12 328

11 624

14 196

10 974

8973

10 841

7528

17 651

20 618

32

1530

1535

1435

1284

1125

1258

1364

2080

2053

2581

2574

3203

ПоказатьСвернуть
Заполнить форму текущей работой