Анализ рядов динамики в электронных таблицах | страница 32

Далее мы построим модель тренда за последний год и сделаем прогнозы по доходности.

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

Format Cells — Number — Category — General

Даты превратились в целые положительные числа (рис. 10.30).

Рис. 10.30. Общий формат ячеек

Задание. Установите общий формат вывода для колонки дат.

Подготовим исходные данные для регрессионного анализа. Найдём среднее взвешенное значения по четырём ценам (рис. 10.31). В этой формуле вы даём увеличенный вес ценам открытия и закрытия. В знаменателе — сумма весовых коэффициентов.

Рис. 10.31. Средневзвешенная цена

Добавляем новую колонку в таблицу. Подсчитываем средневзвешенные цены (рис. 10.32).

Рис. 10.32. Средневзвешенные цены

Задание. Добавьте новый столбец и подсчитайте средневзвешенные цены.

Построим график и нанесём на него линию тренда (рис. 10.33). Для начала используем встроенные средства «точечной» диаграммы.

Рис. 10.33. Линия тренда

Задание. Постройте график средневзвешенных цен и включите отображение линейного тренда. Выведите уравнение тренда на свободное место графика.

Для дальнейшей работы нам понадобятся коэффициенты уравнения тренда. Вызываем надстройку «Анализ данных» и строим линейное уравнение регрессии (рис. 10.34). Подробности работы с надстройкой мы уже рассмотрели чуть раньше в данной работе.

Рис. 10.34. Построение линейной регрессии

Получаем оценки коэффициентов. Нам нужны всего два коэффициента. Так что всё лишнее безжалостно удаляем.

Оформляем уравнение регрессии (рис. 10.35). Используем ссылки на ячейки с коэффициентами. Вся процедура была рассмотрена и описана в предыдущих разделах.

Рис. 10.35. Линейная регрессия

Задание. Постройте уравнение тренда с помощью надстройки «Анализ данных». Оформите уравнение регрессии с использованием ссылок на оценки коэффициентов.

У нас всё готово для анализа и прогнозирования доходности.

Нам понадобятся два стандартных показателя динамики (рис. 10.36):

— коэффициент роста К_>р;

— темп прироста Т_>пр.

Коэффициент роста говорит, «во сколько раз» увеличилось значение уровня ряда, а темп прироста сообщает, «на сколько процентов».

В этих формулах y_>0 — это начальное значение, а y_>t — конечное значение цены актива за какой-нибудь период времени. Например, за год.

Для расчёта средней доходности нам нужно оценить средний коэффициент роста, а затем по нему найти средний темп прироста. Здесь попутно нужно будет найти корень n-й степени.