Задача Приведены поквартальные данные об объеме продаж сырой нефти в условных единицах за 4 года (всего 16 кварталов). Задание




Скачать 200.06 Kb.
Дата10.05.2016
Размер200.06 Kb.
Лабораторная работа по финансовой математике
Задача 1.

Приведены поквартальные данные об объеме продаж сырой нефти в условных единицах за 4 года (всего 16 кварталов).


Задание:

  1. Построить адаптивную мультипликативную модель Хольта-Уинтерса с учетом сезонного фактора, приняв параметры сглаживания α1=0,3; α2=0,6; α3=0,3.

  2. Оценить адекватность построенной модели на основе исследования:

    • Случайности остаточной компоненты

    • Независимости уровней ряда остатков по d-критерию (критические значения d1 = 1,10 и d2 = 1,37) и по первому коэффициенту автокорреляции при критическом значении r1=0,32.

    • Нормальности распределения ряда остатков по критерию RS c критическими значениями от 3 до 4,21

  3. Оценить точность построенной модели

  4. Построить точечный прогноз на 4 шага вперед, т.е. на 1 год

  5. Построить график, на котором должны быть отражены фактические, расчетные и прогнозные значения.


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


Квартал

Вариант 1

Вариант 2

Вариант 3

Вариант 4

Вариант 5

Вариант 6

1

35

34

38

36

38

64

2

38

40

42

39

43

62

3

45

47

49

48

49

69

4

30

32

33

31

36

56

5

33

36

36

34

38

59

6

42

44

46

43

46

66

7

51

53

56

51

55

75

8

32

33

35

30

37

58

9

36

38

39

36

41

62

10

46

48

50

47

52

71

11

54

57

59

54

61

80

12

35

36

37

35

38

58

13

41

43

44

41

46

66

14

50

52

54

50

56

75

15

60

62

64

59

65

84

16

38

39

41

47

53

72

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


Для решения первой задачи используйте шаблон. Выделите блок ячеек В5:Р30. Правой кнопкой мыши вызовите контекстное меню и нажмите «Формат ячеек».



На вкладке «Число» выберите формат данных «Числовой» и установите число десятичных знаков равным 2 (рисунок ниже).

Выделите блок ячеек В35:В53. Установите для него число десятичных знаков равным 4.


  1. Скопируйте исходные данные своего варианта в столбец 2, ячейки B9:B24.




  1. В первую очередь необходимо определить значения коэффициентов а(0) и b(0). Получить значения а(0) и b(0) можно двумя способами.

Способ 1.

Чтобы определить а(0) и b(0) нужно построить линейную модель регрессии. Для построения линейной модели используем ТОЛЬКО ПЕРВЫЕ 8 ЗНАЧЕНИЙ временного ряда.

Установите курсор в ячейку B37. Введите формулу для расчета tср.

В ячейку B38 введите формулу для расчета Yср.

В столбцах 3-6 проведем промежуточные расчеты. Установите курсор в ячейку C9. Введите в нее формулу «= B9-B$38». Скопируйте формулу вниз до ячейки C16 (включительно). Знак $ означает, что значение ячейки B38 (Yср) будет зафиксировано и при копировании все формулы в диапазоне ячеек С9:С16 будут ссылаться на ячейку В38. Этот прием позволяет сэкономить время и копировать формулы на целый блок ячеек, а не вводить формулу в каждую ячейку вручную.
Аналогично рассчитайте значения в столбце 4. Не забудьте про знак $.
В столбце 5 нужно рассчитать значения (Y(t)-Yср)*(t-tср), то есть перемножить значения столбца 4 и 5. В ячейке E30 найдите сумму значений в столбце 5. Для этого используйте функцию СУММ(). Установите курсор в ячейке E30 и нажмите пиктограмму ∑ на панели инструментов.

В столбце 6 нужно найти значения (t-tср)2, то есть возвести в квадрат значения столбца 4. Для того, чтобы возвести число в квадрат используйте встроенную функцию СТЕПЕНЬ(). Установите курсор в ячейке F9. Нажмите «Вставка-Функция». На экране появится окно «Мастера функций».


В выпадающем списке «Категория» выберите «Математические», затем в списке функций выберите функцию СТЕПЕНЬ. Нажмите OK. На экране появится диалоговое окно функции СТЕПЕНЬ.

В поле «Число» введите адрес ячейки, в поле «Степень» введите цифру 2. Нажмите OK. Скопируйте формулу в ячейке F9 вниз до ячейки F16 включительно. В ячейке F30 найдите сумму значений в столбце 6.
Установите курсор в ячейку I8. Введите в нее формулу для b(0).
Установите курсор в ячейку H8. Введите в нее формулу для a(0).
Способ 2.
Нажмите пункт меню «Сервис»-«Анализ данных». Появится диалоговое окно. В списке выберите «Регрессия».

Нажмите OK. Появится диалоговое окно инструмента «Регрессия».

В поле «Входной интервал Y» укажите диапазон ячеек B9:B16.

В поле «Входной интервал X» укажите диапазон ячеек А9:А16. (Для определения а(0) и b(0) берем только 8 первых значений временного ряда). Никакие дополнительные флажки и метки не устанавливайте (см. рисунок). После нажатие кнопки OK, в Вашей рабочей книге появится новый лист «Лист1», на котором будет таблица



Значения, обведенные красной линией – это коэффициенты линейной модели а(0) и b(0).

Y-пересечение =300,07 – это коэффициент а(0). Переменная X1=8,59 – это коэффициент b(0). Естественно, значения коэффициентов для Ваших вариантов будут другие. Введите значения найденных коэффициентов а(0) и b(0) в ячейки H8 и I8 Вашей рабочей таблицы.




  1. Далее нужно заполнить столбец 7. В ячейку G9 введите формулу для расчета Yрег(t). Скопируйте формулу вниз до ячейки G16 включительно. Не забудьте про знак $, так как при расчете Y(t)рег используются одни и те же значения а(0) и b(0).




  1. Далее нужно рассчитать значения сезонного коэффициента F(-3), F(-2), F(-1) и F(0). Установите курсор в ячейку J5. Введите в нее формулу для расчета коэффициента F(-3). Скопируйте формулу вниз до ячейки J8 включительно.




  1. Установите курсор в ячейку K9. (момент времени t=1) Введите в нее формулу для нахождения коэффициента Yp(1).




  1. Установите курсор в ячейку H9. (момент времени t=1) Введите в нее формулу для нахождения коэффициента a(1).




  1. Установите курсор в ячейку I9. (момент времени t=1) Введите в нее формулу для нахождения коэффициента b(1).




  1. Установите курсор в ячейку J9. (момент времени t=1) Введите в нее формулу для нахождения коэффициента F(1).


Будьте особенно внимательны при вводе формул в ячейки H9, I9 и J9 и K9 !!!
Выделите диапазон ячеек H9:K9 и скопируйте их вниз до строки 24 включительно (момент времени t=16).


  1. В столбце 11 получили расчетные значения Yр(t). Перед тем, как рассчитывать прогнозные значения Yр(17), Yр(18), Yр(19) и Yр(20), необходимо провести проверку качества модели.




  1. Заполните столбец 12. Формула для расчета: E(t)=Y(t)-Yp(t).




  1. В столбце 13 находим поворотные точки. Если значение E(t) больше или меньше значений E(t-1) и E(t+1), то в соответствующей строке столбца 13 ставится «1». Если это условие не выполняется, то ставится «0». Установите курсор в ячейку М10 (первую точку поворота ищем для момента времени t=2). Введите в нее формулу:

=ЕСЛИ((ИЛИ((И((L10>L9);(L10>L11)));(И((L10
Если указанное выше условие выполняется, то в ячейке появится цифра «1», если нет – то «0». Скопируйте формулу вниз до ячейки M23 включительно. В ячейках М9 и М24 проставьте символы «*****».


  1. В ячейку М30 введите формулу =СЧЁТЕСЛИ(M10:M23;1). Эта функция подсчитывает количество ячеек со значением «1» в заданном диапазоне, т.е. находит число поворотных точек P.




  1. Заполните столбец 14, начиная с ячейки N9. Используйте функцию СТЕПЕНЬ().




  1. В ячейке N30 подсчитайте сумму значений в столбце 14.




  1. Заполните столбец 15, начиная с ячейки O10. Используйте функцию СТЕПЕНЬ(). В ячейке O30 подсчитайте сумму в столбце 15.




  1. Заполните столбец 16, начиная с ячейки P10. Подсчитайте сумму.




  1. Установите курсор в ячейку B48. Введите формулу для расчета величины q. При вводе формулы оператор int опустите.




  1. В ячейке B50 рассчитайте величину d (критерий Дарбина-Уотсона). Обратите внимание, что все необходимые суммы уже рассчитаны ранее. Если значение d>2 это означает, что имеет место отрицательная автокорреляция. В этом случае величину d уточняют, вычитая полученное значение из 4. Введите в ячейку В50 следующую формулу:

=ЕСЛИ((значение d)>2;(4-значение d);(значение d))


Вместо «значение d» должна быть формула для расчета d.


  1. В ячейке B51 найдите значение первого коэффициента автокорреляции.




  1. В ячейке B52 найдите значение среднеквадратического отклонения. Используйте функцию КОРЕНЬ() или СТЕПЕНЬ().




  1. В ячейке B53 найдите значение критерия RS. Для нахождения Emax и Emin используйте функции МАКС() и МИН().


Проверка качества модели.

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




  1. Установите курсор в ячейку H38. Введите в нее формулу:

=ЕСЛИ((ABS(100%*L30/B30)<=0,05);"OK";"НЕТ")

Если условие точности выполнено, то в ячейке появится «OK», в противном случае

«НЕТ».


  1. Установите курсор в ячейку H39. Введите в нее формулу:

=ЕСЛИ((B49>ЦЕЛОЕ(B48));"OK";"НЕТ")

Если условие случайности ряда остатков выполнено, то в ячейке появится «OK», в

противном случае «НЕТ».
26. Установите курсор в ячейку H41. Введите в нее формулу:

=ЕСЛИ(И((B50<2);(B50>B44));"OK";"Нет ответа")

Если условие независимости уровней ряда остатков по критерию Дарбина-Уотсона

выполнено, то в ячейке появится «OK». Если появится «Нет ответа», то необходимо провести проверку по первому коэффициенту автокорреляции, т.к. критерий Дарбина-Уотсона не дает ответа на вопрос о независимости ряда остатков.


27. Установите курсор в ячейку H42. Введите в нее формулу:

=ЕСЛИ((ABS(B51)

Если первый коэффициент автокорреляции меньше критического значения, то в

ячейке появится «OK», в противном случае «НЕТ».

28. Установите курсор в ячейку H43. Введите в нее формулу:

=ЕСЛИ(И((B53>B46);(B53

Если ряд E(t) распределен по нормальному закону, то в ячейке появится «OK», в

противном случае «НЕТ».


29. Если все проверки прошли успешно, то можно приступать к построению прогноза. Прогноз сделаем на 4 квартала вперед. Установите курсор в ячейку K25. Введите в нее формулу для расчета Yp(17). Обратите внимание, что при расчете значений Yp(1)- Yp(16) коэффициент k всегда был равен 1.

При расчете значений Yp(17)- Yp(20) коэффициент k увеличивается на каждом шаге. Для расчета Yp(17)- Yp(20) используем последние значения коэффициентов а(16) и b(16). В ячейки К26, К27 и К28 введите формулы для расчета Yp(18), Yp(19) и Yp(20).


30. Установите курсор в ячейку Q24. Введите в нее: =К24. Скопируйте эту формулу вниз до ячейки Q28 включительно. Это нужно для правильного построения графика.

31. Построение графика. На панели задач нажмите «Вставка-Диаграмма». Появится окно .



Выберите:

Тип: «График»;

Вид: график с маркерами (см. рисунок). Нажмите «Далее».
30. В следующем окне введите диапазон ячеек B9:B24, по которым будет строиться график.


Щелкните мышкой на вкладке «Ряд».

Пока выбран только один ряд чисел. В поле «Имя» введите «Фактические значения».

Нажмите кнопку «Добавить».


Появится новый ряд. Дайте ему имя «Расчетные значения» и в поле «Значения» введите диапазон ячеек K9:K24.


Добавьте еще один ряд с названием «Прогнозные значения» и диапазоном ячеек Q9:Q28


Нажмите «Далее».

Введите название диаграммы, и названия осей. На вкладке «Легенда» укажите, что легенда диаграммы должна располагаться снизу. Нажмите «Готово».
График должен иметь примерно такой вид:

Задача 2.


Даны цены (закрытия, минимальная, максимальная) за 20 дней. Интервал сглаживания равен 7 дням.
1). Рассчитать:

  • Скользящую среднюю (МА)

  • Взвешенную скользящую среднюю (WMA)

  • Экспоненциальную скользящую среднюю (ЕМА)

  • Момент (MOM)

  • Скорость изменения цен (ROC)

  • Индекс относительной силы (RSI)

  • Стохастические линии %K, %R, %D, медленную %D

2) Построить 3 диаграммы:

На первой диаграмме должны быть ценовой график, график скользящей средней MA и график момента MOM.

На второй диаграмме должен быть график индекса роста цен ROC

На третьем графике должны быть графики %K, %R, %D и медленная %D


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

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



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





Вариант 1.

Вариант 2.

Вариант 3.

Дни

Цены

Цены

Цены




Макс.

Мин.

Закр.

Макс.

Мин.

Закр.

Макс.

Мин.

Закр.

1

745

711

725

728

670

685

660

628

655

2

760

725

748

695

611

656

690

640

642

3

755

725

730

639

580

585

667

637

667

4

735

717

722

595

511

580

697

660

664

5

748

712

733

608

525

533

700

670

699

6

757

726

754

545

511

516

749

695

735

7

845

765

845

565

510

563

735

705

725

8

885

822

837

590

550

580

790

733

790

9

863

831

848

590

555

574

868

824

855

10

830

770

777

613

560

613

882

850

881

11

754

715

719

610

560

565

868

795

814

12

753

685

748

570

540

540

859

791

859

13

760

710

745

546

511

534

880

811

816

14

769

717

761

555

531

549

815

765

770

15

780

750

765

593

550

579

795

752

773

16

786

671

775

597

572

591

805

765

805

17

766

725

730

592

571

572

822

791

810

18

755

695

749

583

566

583

864

801

863

19

768

735

750

620

589

602

885

829

830

20

740

683

688

655

595

655

830

755

766

Для решения задачи 2 воспользуйтесь шаблоном. Вам понадобятся следующие формулы:





Подсказка! Первое значение EMAt принять равным первому значению MAt. Далее рассчитывать по формуле.


База данных защищена авторским правом ©ekonoom.ru 2016
обратиться к администрации

    Главная страница