Валентин Арьков - Бизнес-аналитика. Сводные таблицы. Часть 2

Format Trendline — Trendline Options — Linear — Display Equation on chart.

Заодно и название графика зададим. Чтобы было понятно читателю, что мы тут изобразили.

Рис. 4.15. Вывод уравнения тренда

Получаем уравнение тренда на графике. Можно его немного подвинуть. Перетащим уравнение мышкой на свободное место (рис. 4.16).

Итак, вот наше уравнение:

y = 0,0027 x — 116,25.

Рис. 4.16. Уравнение и линия тренда

Задание. Включите вывод уравнения тренда на график

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

Мы пойдём другим путём. Вызовем надстройку «Анализ данных» (рис. 4.17) и попросим построить нам уравнение регрессии:

Data — Analysis — Data Analysis — Regression.

Что это такое и как это работает — мы разбирали в одной из предыдущих работ [2].

Появляется диалоговое окно

Regression.

Указываем диапазоны ячеек для «игреков» и для «иксов»:

— Input Y Range;

— Input X Range.

Чтобы всё запутать, вначале нас просят ввести «игреки». Но мы не поддаёмся на провокации и делаем всё правильно.

Ещё нужно указать, куда выводить результаты анализа. Указываем на свободное место.

Нажимаем ОК.

Рис. 4.17. Построение регрессии

Задание. Вызовите надстройку «Анализ данных» и выберите построение регрессии.

Рассмотрим результаты регрессионного анализа (рис. 4.18).

Среди большого количества цифр нас будет интересовать раздел с коэффициентами. Напомним, кто есть кто:

Intercept — свободный член уравнения

X Variable 1 — коэффициент регрессии, то есть коэффициент при «иксе». В нашем случае, при переменной t, которая обозначает время, номер дня.

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

y = —116,25 +0,0027435 t .

Последний разряд округляем.

Обратите внимание, как выглядят ЗНАЧАЩИЕ ЦИФРЫ. Перед ними и после них может быть много нулей, которые могут и не содержать полезной информации.

Кстати, это пример ситуации, когда ДАННЫЕ и ИНФОРМАЦИЯ — не одно и то же. Много данных в виде цифр — это не обязательно много полезной информации. Это просто цифры. А информация должна быть ПОЛЕЗНОЙ для дела.

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

Задание. Запишите уравнение тренда с точностью до пяти значащих цифр.

Сезонные колебания — это изменения с периодом в один год. То есть двенадцать месяцев, или примерно 365 дней. Сезон — это времена года и всё, что с ними связано.

Причина сезонных колебаний цен — это изменение количества товаров, которое предлагается на рынке. В экономике это называется ПРЕДЛОЖЕНИЕ. Понятно, что сразу после сбора урожая сельскохозяйственной продукции много, и цены обычно снижаются. А вот когда запасы подходят к концу, цена может вырасти. Эта картина повторяется каждый год.

Мы будем моделировать сезонные колебания цен в диапазоне плюс-минус 10% от среднего значения цены. Пусть все цены достигают минимального значения в октябре каждого года. И пусть они меняются по синусоиде.

Пусть минимум будет 1 октября 2018 года. Находим порядковый номер этого дня, как мы уже проделали в предыдущем разделе (рис. 4.19). Получаем число

t (min) = 43104.

Это не наименьшее время.

Это день, когда цены минимальные.

Рис. 4.19. Дата минимальных цен

Определим начало периода синусоидальных колебаний (рис. 4.20). Это будет номер дня 1 октября 2018 года минус 9 месяцев, то есть минус 9*30 = 270 дней:

Рис. 4.20. Начало периода колебаний

Таким образом, получаем начало периода колебаний (рис. 4.21).

t 0= 43374 — 270 = 43104.

Рис. 4.21. Начало периода колебаний

Мы собираемся моделировать колебания в пределах плюс-минус 10% средней цены. В этом случае придётся использовать мультипликативную модель [3]. Так что в уравнении сезонных колебаний это будут колебания вокруг единицы с амплитудой 0,1 (рис. 4.22).

Рис. 4.22. Мультипликативная модель

Конечно, это очень упрощённая модель. Форма колебаний не похожа на синусоиду. А время сбора урожая различается для моркови и для орехов.

В наших упражнениях самое главное — почувствовать саму идею. А если будет желание, можно сделать более реалистичное описание.

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

Наша модель будет дополнительно учитывать «рациональное» поведение покупателей. Будем считать, что клиенты стремятся покупать большее количество, когда цены падают. И стараются экономить при повышении цен, то есть покупать меньшее количество товара.

В нашей модели количество покупаемого товара, то есть СПРОС, будет переживать сезонные колебания. Колебания будут в противоположной фазе в сравнении с колебаниями цен. То есть 1 октября будет максимум покупок. Соответственно, начало периода колебаний — это 1 октября минус три месяца:

t 0= 43374 — 3*30 = 43374 — 90 = 43284.

Это начало июля. К началу октября покупки растут. К январю цены выросли, а закупки упали. И так повторяется каждый год.

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

Мы заложили в нашу модель взаимосвязь между уровнем цен и спросом, то есть количеством купленного товара. Это зависимость в среднем — на фоне случайного разброса, отклонений. Наличие такой зависимости называется КОРРЕЛЯЦИЯ (рис. 4.23). Слово «корреляция» происходит от латинского correlatio — «соотношение, взаимосвязь», где co - — «со-, взаимо-, вместе» и relatio — «отнесение, связь». Если в среднем значение увеличивается, это прямая корреляция, если уменьшается — то обратная.

Рис. 4.23. Прямая и обратная корреляция

Задание. Запишите в отчёте ответ на следующий вопрос. Какой вид корреляции между спросом и предложением заложен в нашей модели и насколько это соответствует действительности?

Ко всем значениям спроса и предложения мы добавим случайную составляющую. Это будет разброс вокруг цены и количества товара в каждой покупке. Случайный разброс составит плюс-минус указанное количество процентов. В нулевом варианте это плюс-минус 20%.

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

сигма = 20% / 3 = 0,2 / 3 = 0,066667.

Задание. Определите величину сигмы для своего варианта.