Как сделать экстраполяцию в excel

4. Построение прогнозов в Excel

Прогнозирование– это метод научного исследования, ставящей своей целью предусмотреть возможные варианты тех процессов и явлений, которые выбраны в качестве предмета анализа. Одним из методов прогнозирования является метод экстраполяции. Метод экстраполяции заключается в нахождении значений, лежащих за пределами данного статистического ряда: по известным значениям статистического ряда находятся другие значения, лежащие за пределами этого ряда. При экстраполяции переносится выводы, сделанные при изучении тенденций развития явления в прошлом и настоящем, на будущее, т.е. в основе экстраполяции лежит предположение об определенной стабильности факторных признаков, влияющих на развитие данного явления. Чем более устойчивый характер носит прогнозируемые процессы и тенденции, тем дальше может быть отодвинут горизонт прогнозирования. Как показывает практика, интервал наблюдения должен быть в три и более раз длиннее интервала упреждения. Как правило, этот период – довольно короткий. Метод экстраполирования не работает при скачкообразных процессах. Метод экстраполяции легко реализуется на персональном компьютере. Использование современных табличных процессоров, таких как MSExcelпозволяет оперативно проводить прогнозирование различных процессов с использованием экстраполяционного метода. Для повышения точности прогноза, необходимо учитывать зависимость прогнозируемой величины Y, от внешних факторов Х.Совокупность изучаемых величин подвержена, как правило, воздействию случайных факторов. В связи с этим зависимость прогнозируемой величины Y, от внешних факторов Х чаще всего статистическая, или – корреляционная. Статистическойназывается зависимость случайных величин, при которой каждому значению одной их них соответствует закон распределения другой, то есть изменение одной из величин влечет изменение распределения другой. Корреляционнойназывается статистическая зависимость случайных величин, при которой изменение одной из величин влечет изменение среднего значения другой. Мерой корреляционной зависимости двух случайных величин Х и Y служит коэффициент корреляции r, который является безразмерной величиной, и поэтому он не зависит от выбора единиц измерения изучаемых величин. Свойства коэффициента корреляции: 1) Если две случайные величины Х и Y независимы, то их коэффициент корреляции равен нулю, т.е. r=0. 2) Модуль коэффициента корреляции не превышает единицы, т.е. |r|1, что эквивалентно двойному неравенству:1r1. 3) Равенство коэффициента -1 или +1 показывает наличие функциональной (прямой) связи. Знак «+» указывает на связь прямую (увеличение или уменьшение одного признака сопровождается аналогичным изменением другого признака), знак «-» — на связь обратную (увеличение или уменьшение одного признака сопровождается противоположным по направлению изменением другого признака). После определения наиболее существенных факторных признаков влияющих прогнозируемую величину, не менее важно установить их математическое описание (уравнение), дающее возможность численно оценивать результативный показатель через факторные признаки. Уравнение, выражающее изменение средней величины результативного показателя в зависимости от значений факторных признаков, называется уравнение регрессии. Линии на координатной плоскости, соответствующие уравнениям регрессии называются линиями регрессии. Корреляционные зависимости могут выражаться уравнениями регрессии различных видов: линейной, параболической, гиперболической, показательной и т.д. Линейная регрессияУравнением линейной регрессии(выборочным)YнаХназывается зависимость от наблюдаемых значений величины Х, выраженная линейной функцией: , где величина называется коэффициентом линейной регрессииYнаХ,b— константа. Линейная аппроксимация хорошо описывает изменение величин, происходящее с постоянной скоростью. Если коэффициент корреляции двух величин ХиYравенr=1, то эти величины связаны линейной зависимостью. Коэффициент корреляции служит мерой силы (тесноты) линейной зависимости измеряемых величин. На практике, если коэффициент корреляции двух величинХиY|r|>0.5, то считают, что есть основания предполагать наличие линейной зависимости между этими величинами. Однако ориентироваться при выборе типа линии регрессии (линейной или нелинейной) лучше по виду эмпирической зависимости величинХиY. Параболическая и полиномиальная регрессии.Параболическойзависимостью величиныYот величиныХназывается зависимость, выраженная квадратичной функцией (параболой 2-ого порядка):_. Это уравнение называетсяуравнением параболической регрессииYнаХ. Параметрыа,b,сназываютсякоэффициентами параболической регрессии. Уравнение параболической регрессии является частным случаем более общей регрессии, называемой полиномиальной. Полиномиальнойзависимостью величиныYот величиныХназывается зависимость, выраженная полиномомn-ого порядка: ,где числаа_i(i=0,1,…,n) называютсякоэффициентами полиномиальной регрессии. Полиномиальная аппроксимация используется для описания величин, попеременно возрастающих и убывающих. Степенная регрессия.Степеннойзависимостью величиныYот величиныХназывается зависимость вида: . Это уравнение называетсяуравнением степенной регрессииYнаХ. Параметрыаиbназываютсякоэффициентами степенной регрессии. Степенная аппроксимация полезна для описания монотонно возрастающей либо монотонно убывающей величины, например расстояния, пройденного разгоняющимся автомобилем. Использование степенной аппроксимации невозможно, если данные содержат нулевые или отрицательные значения. Показательная регрессия.Показательной(илиэкспоненциальной) зависимостью величиныYот величиныХназывается зависимость вида: (или ) Это уравнение называется уравнениемпоказательной(илиэкспоненциальной)регрессииYнаХ. Параметрыа(илиk) иbназываютсякоэффициентами показательной(илиэкспоненциальной)регрессии. Экспоненциальная аппроксимация полезна в том случае, если скорость изменения данных непрерывно возрастает. Однако для данных, которые содержат нулевые или отрицательные значения, этот вид приближения неприменим. Логарифмическая регрессия.Логарифмическойзависимостью величиныYот величиныХназывается зависимость вида:_.Это уравнение называетсяуравнением логарифмической регрессииYнаХ. Параметрыаиbназываютсякоэффициентами логарифмической регрессии. Логарифмическая аппроксимация полезна для описания величины, которая вначале быстро растет или убывает, а затем постепенно стабилизируется. Логарифмическая аппроксимация использует как отрицательные, так и положительные величины. Гиперболическая регрессия.Гиперболическойзависимостью величиныYот величиныХназывается зависимость вида: . Это уравнение называетсяуравнением гиперболической регрессииYнаХ. Параметрыаиbназываютсякоэффициентами гиперболической регрессии. Проведение регрессионного анализа можно разделить на три этапа: выбор формы зависимости (вида уравнения) на основе статистических данных, вычисление коэффициентов выбранного уравнения, оценка достоверности выбранного уравнения. Использование табличного процессора позволяет легко выполнить все этапы регрессионного анализа. Использование функции ТЕНДЕНЦИЯ Для прогнозирования процессов, изменение которых носит линейный характер можно использовать функцию ТЕНДЕНЦИЯ. Эта функция позволяет находить значения в соответствии с линейным трендом. Она аппроксимирует прямой линией (по методу наименьших квадратов) массивы известных значений Yи известных значения Х. Находит новые значенияY, в соответствии с этой прямой для новых значений Х. Например, если у нас есть данные изменения цены на энергоносители за несколько последних лет, то с помощью функции ТЕНДЕНЦИЯ мы можем получить прогноз на цену на энергоносители на будущий год. При этом если зависимость цены от времени близка к линейной, то результат будет удовлетворительным. Предварительно, перед использованием функции ТЕНДЕНЦИЯ необходимо ввести (в столбец или в строку) массивы XиY, взаимосвязанных величин, а также значенияXдля которых будет спрогнозирована величинаY. Затем, вызываем статистическую функцию ТЕНДЕНЦИЯ. Для этого необходимо вызвать Мастер функций, щелкнув на кнопке строки формул или отдав команду Вставка/Функция.В окне Мастера функций выберите категориюСтатистическиеи в полеВыберите функцию выберите из списка функцию ТЕНДЕНЦИЯ. В появившемся диалоговом окнеАргументы функции (рис.5.8) в соответствующие поля внесите ссылки на диапазоны ячеек в которых хранятся известные значения величинYиX, новые значения Х, для которых ТЕНДЕНЦИЯ возвращает соответствующие значенияY. С помощью поляКонст можно указать вычислять ли константу b или принять равной 0. Если это поле имеет значение ИСТИНА или опущено, то b вычисляется обычным образом, если ЛОЖЬ – то b полагается равным 0, и значенияподбираются таким образом, чтобы выполнялось соотношениеy =x. Рис. 8. Фрагмент диалогового окна Аргументы функции. После ввода всех необходимых аргументов функции необходимо нажать на кнопке ОК. Быстрое построение линий регрессии вExcel: линия тренда. В Excel имеется быстрый и удобный способ построить график линейной регрессии, а также основных видов нелинейных регрессий. Это можно сделать следующим образом: Выделить столбцы с данными X и Y (они должны располагаться именно в таком порядке!). Вызвать Мастер диаграмм (используя инструмент или команду Вставка/Диаграмма) и в открывшемся окне Мастер диаграмм выбрать в группе Тип – Точечная и сразу нажать Готово. Не сбрасывая выделения с диаграммы, выбрать появившейся пункт основного меню Диаграмма, в котором следует выбрать пункт Добавить линию тренда. В открывшемся диалоговом окне Линия тренда во вкладке Тип выбрать тип. Можно выбрать одну из шести зависимостей: линейная, степенная, логарифмическая, экспоненциальная, полиномиальная и линейная фильтрация. Для полиномиальной аппроксимации можно указать степень. При линейной фильтрации (скользящее среднее) элементы данных усредняются, и полученный результат используется в качестве среднего значения для приближения. Так, если шаг линейной фильтрации равен 2, первая точка сглаживающей кривой определяется как среднее значение первых двух элементов данных, вторая точка – как среднее следующих двух элементов и так далее. Рис.5.9. Вкладка Линия тренда..

Расчет скользящей средней в Excel и прогнозирование

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

Использование скользящих средних в Excel

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

Временной ряд – это множество значений X и Y, связанных между собой. Х – интервалы времени, постоянная переменная. Y – характеристика исследуемого явления (цена, например, действующая в определенный период времени), зависимая переменная. С помощью скользящего среднего можно выявить характер изменений значения Y во времени и спрогнозировать данный параметр в будущем. Метод действует тогда, когда для значений четко прослеживается тенденция в динамике.

Например, нужно спрогнозировать продажи на ноябрь. Исследователь выбирает количество предыдущих месяцев для анализа (оптимальное число m членов скользящего среднего). Прогнозом на ноябрь будет среднее значение параметров за m предыдущих месяца.

Задача. Проанализировать выручку предприятия за 11 месяцев и составить прогноз на 12 месяц.

Сформируем сглаженные временные ряды методом скользящего среднего посредством функции СРЗНАЧ. Найдем средние отклонения сглаженных временных рядов от заданного временного ряда.

1. По значениям исходного временного ряда строим сглаженный временный ряд методом скользящего среднего по данным за 2 предыдущих месяца. Формула скользящей средней в Excel. Используя маркер автозаполнения, копируем формулу на диапазон ячеек С6:С14.
2. Аналогично строим ряд значений трехмесячного скользящего среднего. Формула:
3. По такому же принципу формируем ряд значений четырехмесячного скользящего среднего.
4. Построим график заданного временного ряда и рассчитанные относительно его значений прогнозы по данному методу. На рисунке видно, что линии тренда скользящего среднего сдвинуты относительно линии исходного временного ряда. Это объясняется тем, что рассчитанные значения сглаженных временных рядов запаздывают по сравнению с соответствующими значениями заданного ряда. Ведь расчеты базировались на данных предыдущих наблюдений.
5. Рассчитаем абсолютные, относительные и средние квадратичные отклонения по сглаженным временным рядам. Абсолютные отклонения:

Средние квадратичные отклонения:

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

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

Прогнозное значение выручки на 12 месяц – 9 430 у.е.

Применение надстройки «Пакет анализа»

Для примера возьмем ту же задачу.

На вкладке «Данные» находим команду «Анализ данных». В открывшемся диалоговом окне выбираем «Скользящее среднее»:

Заполняем. Входной интервал – исходные значения временного ряда. Интервал – число месяцев, включаемое в подсчет скользящего среднего. Так как сначала будем строить сглаженный временной ряд по данным двух предыдущих месяцев, в поле вводим цифру 2. Выходной интервал – диапазон ячеек для выведения полученных результатов.

Установив флажок в поле «Стандартные погрешности», мы автоматически добавляем в таблицу столбец со статистической оценкой погрешности.

Точно так же находим скользящее среднее по трем месяцам. Меняется только интервал (3) и выходной диапазон.

Сравнив стандартные погрешности, убеждаемся в том, что модель двухмесячного скользящего среднего больше подходит для сглаживания и прогнозирования. Она имеет меньшие стандартные погрешности. Прогнозное значение выручки на 12 месяц – 9 430 у.е.

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Как сделать экстраполяцию в excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Как сделать экстраполяцию в excel

1. Гусарова О.М. Методы и модели прогнозирования деятельности корпоративных систем // Теоретические и прикладные вопросы образования и науки. Тамбов: Юком, 2014. С. 42-43.

2. Орлова И.В. Опыт использования компьютерных технологий при преподавании математического моделирования //Успехи современного естествознания. 2014. № 12-4. С. 433-435.

3. Орлова И.В. Экономико-математическое моделирование: Практическое пособие по решению задач. 2-е изд., испр. и доп. М.: Вузовский учебник: ИНФРА-М, 2012. 140 с.

4. Орлова И.В., Махвытов М.А. Прогнозирование выдачи ипотечных кредитов с помощью модели Брауна//Современные наукоемкие технологии. 2014. № 7-3. С. 22-24.

5. Орлова И.В., Половников В.А. Экономико-математические методы и модели: компьютерное моделирование: учебное пособие для студентов высших учебных заведений, обучающихся по специальности «Статистика» и другим экономическим специальностям / Москва, 2011. Сер. Вузовский учебник (3-е издание, переработанное и дополненное)

6. Орлова И.В., Турундаевский В.Б. Некоторые особенности, возникающие при изучении нелинейной регрессии с использованием Еxcel и других программ // Экономика, статистика и информатика. Вестник УМО. 2014. № 1. С. 158-161.

7. Эконометрика Орлова И.В., Половников В.А., Филонова Е.С., Гусарова О.М., Малашенко В.М., Дайитбегов Д.М. Учебно-методическое пособие / Москва, 2010.

8. Эконометрика Орлова И.В., Филонова Е.С., Агеев А.В. Компьютерный практикум для студентов третьего курса, обучающихся по специальностям 080105.65 «Финансы и кредит», 080109.65 «Бухгалтерский учет, анализ и аудит» / Москва, 2011.

9. http://eclib.net/14/index.html
10. http://www.rae.ru/monographs/10-168

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

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

Следует иметь в виду, что экстраполяция в рядах динамики носит приближенный характер. Точность прогноза зависит от сроков прогнозирования: чем они короче, тем надежнее результат экстраполяции, так как за короткий период времени не успевают значительно измениться условия развития явления и характер его динамики [1].

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

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

В зависимости от того, какие принципы и исходные данные положены в основу прогноза, выделяют следующие методы экстраполяции (прогнозирования):

– на основе среднего абсолютного прироста ,

– на основе среднего коэффициента роста ,

– на основе аналитического выравнивания ряда.

Метод прогнозирования на основе среднего абсолютного прироста t1 применяется в том случае, если уровни изменяются равномерно (линейно).

Прогнозируемое значение уровня определяется по формуле:

где – экстраполируемый уровень; yn – конечный уровень ряда динамики; l – период упреждения прогноза (срок экстраполяции).

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

Прогнозирование на основе аналитического выравнивания является наиболее распространенным методом прогнозирования. Для получения прогноза используется аналитическое выражение тренда. Чтобы получить прогноз, достаточно в модели продолжи ть значение условного показателя времени от t1 до tn+1.

Интервальные прогнозы строятся на основе точечных прогнозов. Доверительным интервалом называется такой интервал, относительно которого можно с заранее выбранной вероятностью утверждать, что он содержит значение прогнозируемого показателя. Ширина интервала зависит от качества модели, т.е. степени ее близости к фактическим данным, числа наблюдений, горизонта прогнозирования и выбранного пользователем уровня вероятности и других факторов [5].

Интервальные прогнозы имеют значительные преимущества перед точечными – они учитывают вероятность свершения прогноза. Величина доверительного интервала определяется в общем виде так[5]:

,

где коэффициент tα является табличным значением t-статистики Стьюдента при заданном уровне значимости и числе наблюдений; σ – средняя квадратическая ошибка тренда, рассчитываемая по формуле:

,

где n – число уровней исходного ряда; m – число параметров трендового уравнения.

Для ряда динамики прогнозное значение Y принадлежит интервалу: .

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

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

Применение метода экстраполяции для составления прогноза некоторых показателей компании ОАО «Ростелеком».

ОАО «Ростелеком» – одна из крупнейших в России и Европе телекоммуникационных компаний национального масштаба, присутствующая во всех сегментах рынка услуг связи и охватывающая более 34 млн домохозяйств в России.

Компания занимает лидирующее положение на российском рынке услуг широкополосного интернет-доступа (ШПД) и платного телевидения: количество абонентов услуг ШПД превышает 11,0 млн. а платного ТВ «Ростелекома» – более 7,8 млн пользователей, из которых свыше 2,5 миллиона смотрит уникальный федеральный продукт «Интерактивное ТВ».

Консолидированная выручка Группы компаний за 3 кв. 2014 г. составила 75,5 млрд. руб., чистая прибыль – 24,5 млрд. руб.

«Ростелеком» является безусловным лидером рынка телекоммуникационных услуг для российских органов государственной власти и корпоративных пользователей всех уровней.

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

В табл. 1 представлена динамика изменения ключевых показателей деятельности компании с 2008 по 2013 год.