Лабораторная работа №2. Лабораторная работа 1 Прогнозирование в среде excel метод корреляционнорегрессионного анализа
 Скачать 1.22 Mb.
|
|
Лабораторная работа №1 Прогнозирование в среде EXCEL. метод корреляционно-регрессионного анализа. Задание 1. Прогнозирование с помощью функций регрессии Excel Простое скользящее среднее является быстрым, но довольно неточным способом выявления общих тенденций временного ряда. Передвинуть границу оценки будущего по временной оси можно с помощью одной из функций регрессии Excel. Каждый из методов регрессии оценивает взаимосвязь фактических данных наблюдений и других параметров, которые зачастую являются показателями того, когда были сделаны эти наблюдения. Это могут быть как числовые значения каждого результата наблюдения во временном ряду, так и дата наблюдения. Составление линейных прогнозов: функция ТЕНДЕНЦИЯ Использование функции рабочего листа ТЕНДЕНЦИЯ — это самый простой способ вычисления регрессионного анализа. Предположим, результаты ваших наблюдений внесены в ячейки А1:А10, а дни месяца расположены в ячейках В1:В10. Выделите ячейки С1:С10 и введите следующую формулу, используя формулу массива: ТЕНДЕНЦИЯ (А1 :А10;В1 :В10) Замечание Для ввода формулы массива нажмите комбинацию клавиш <Ctrl+Shift+Enter>. Упоминалось, что регрессивный анализ позволяет производить перспективную оценку более удаленного будущего. Применяя данные рабочего листа. введите в ячейку В11 число 11, а в ячейку С11 — следующее: =ТЕНДЕНЦИЯ(А1:А10;В1:В10;В11) Первый аргумент — А1:А10— определяет данные наблюдений базовой линии (известные-значения-у); второй аргумент — В1:В10 — определяет временные моменты, в которые эти данные были получены (известные-значения-х). Значение 11 в ячейке В11 является новым значением- х и определяет время, которое связывается с перспективной оценкой. Введя в ячейку В11 большее значение, вы сможете прогнозировать данные более позднего временного момента, чем непосредственно следующего за текущим. Число 24, введенное в ячейку В11, будет определять двадцать четвертый месяц, т.е. декабрь 2009 года. Выполняя вычисления с помощью функции тенденция, получаем результат 23,8, который и будет отражать прогнозируемый объем продаж в декабре 2009 года, полученный на основе фактических результатов наблюдений за период с января по октябрь 2008 года. Кроме того, существует возможность одновременного прогнозирования данных для нескольких новых временных моментов. Например, введите числа 11-24 в ячейки В11-В24 а затем выделите ячейки С11:С24 и введите с помощью формулы массива следующее: {=ТЕНДЕНЦИЯ(А1:А10;В1:В10;В11:В24)}.  Рис. 1. Функция ТЕНДЕНЦИЯ прогнозирует базовую линию результатов наблюдений на основе некоторых переменных. Для ввода формулы массива нажмите комбинацию клавиш Excel вернет в ячейки С11:С24 прогноз на временные моменты с 11 по 24. Данный прогноз будет базироваться на связи между данными наблюдений базовой линии диапазона А1:А10 и временными моментами базовой линии с 1 по 10, указанными в ячейках В1:В10. Составление нелинейного прогноза: функция РОСТ Функция ТЕНДЕНЦИЯ вычисляет прогнозы, основанные на линейной связи между результатом наблюдения и временем, когда это наблюдение было зафиксировано. Однако, если линия резко изгибается в одном из направлений, то это означает, что взаимосвязь показателей носит нелинейный характер. Существует большое количество типов данных, которые изменяются во времени нелинейным cпoсобом. Некоторыми примерами таких данных являются объем продаж новой продукции, прирост населения, выплаты по основному кредиту и коэффициент удельной прибыли. В случае нелинейной взаимосвязи функция Excel РОСТ поможет вам получить более точную картину направления развития вашего бизнеса, чем функция ТЕНДЕНЦИЯ. Задание 2. Анализ ситуации: продажи новой компьютерной программы Представим, что менеджер по закупкам отдела "Soft-почтой" недавно разослал клиентам каталог, рекламирующий новую программу, получившую очень высокую оценку экспертов. Менеджер считает, что следует заранее заказать дополнительное количество экземпляров, чтобы не оказаться в ситуации, когда CD c программой закончатся раньше, чем перестанут приходить заявки на нее, менеджер начал отслеживать ежедневные заказы на программный продукт и регистрировать объемы продаж.  Рис. 2. Функция РОСТ очень удобна при прогнозировании нелинейных базовых линий Поскольку линия имеющихся наличии товаров резко изгибается вверх, менеджер принимает решение составить прогноз с использованием функции РОСТ. Как и при использовании функции ТЕНДЕНЦИЯ, пользователь в данном случае может генерировать прогнозы, просто подставляя новые значения-х. Чтобы прогнозировать результаты 11—13 недель, следует ввести эти числа в ячейки В12:В14, а затем с помощью формулы массива в диапазон ячеек С2:С14 ввести следующее: {=РОСТ(B2:B11;A2:A11;A2:A14)}. В случаях, когда вы имеете дело с очень резким ростом, удобнее оперировать не самими данными наблюдений, а логарифмами этих показателей. Например, экспоненциальный рост можно представить в виде прямой, используя логарифмическую шкалу для вертикальной оси графика. Постройте линейный график с логарифмической зависимостью экспоненциального роста заказов по отделу "Soft-почтой". Сравним использование функции РОСТ и ТЕНДЕНЦИЯ применив их к анализу следующих значений. В ячейках С2:С14 используется следующая формула: ЕХР(ТЕНДЕНЦИЯ(LN(B2:B11);А2:All;А2:А14))  Рис. 3. Логарифмическая зависимость экспоненциального роста заказов в книжной торговле зачастую более удобна для интерпретации, чем стандартный линейный график  Рис. 4. Линия тренда, построенная с помощью функции РОСТ, дает на основе нелинейной базовой линии намного более точный прогноз, чем линия тренда, построенная с помощью функции ТЕНДЕНЦИЯ Задание 3. Регрессивный анализ с помощью диаграмм Во многих случаях диаграммы Excel бывают очень полезны при создании прогнозов. Иногда возникает необходимость провести регрессивный анализ непосредственно на графике, без введения в рабочий лист значений для прогноза. Это можно сделать с помощью графической линии тренда методом, во многом сходным с методом получения прогноза с применением скользящего среднего на основе графика. Постройте диаграмму на основе данных объема продаж: 593,581,395,625,711,536,565,418,231,243,338,433,714,516,563,656,744,468,594,505,520,685,569,701 Кликнув мышью на диаграмме, вы получите возможность ее редактировать. Кликните правой кнопкой мыши на ряде нужных данных для его выбора. После этого выполните следующие действия. 1. Выберите из контекстного меню команду Добавить линию тренда. 2. Выберите тип линии тренда Линейная. 3. Щелкните на вкладке Параметры. 4. В поле В перед на «ведите количество желаемых периодов, на протяжении которых линия тренда будет прокладываться вперед. 5. При желании можете установить флажок показывать уравнение на диаграмме. В результате уравнение для прогноза разместится на графике в виде текста Excel может расположить уравнение таким образом, что оно перекроет некоторые данные графика или линии тренда (либо (частично) само уравнение), этом случае выделите уравнение, щелкнув на нем мышью, а затем перетащит его в другое, более удобное место. 6. Кликните на кнопке ОК.  Рис. 5 С помощью линий тренда можно создавать прогнозы, основанные на регрессии, непосредственно на диаграмме Задание 4. Прогнозирование с использованием функции экспоненциального сглаживания Сглаживание — это способ, обеспечивающий быстрое реагирование вашего прогноза на все события, происходящие в течение периода протяженности базовой для и. Методы, основанные на регрессии, такие как функции ТЕНДЕНЦИЯ и РОСТ, применяют ко всем точкам прогноза одну и ту же формулу. По этой причине достижение быстрой реакции на сдвиги в уровне базовой линии значительно затрудняется сглаживание представляет собой простой способ обойти данную проблему. Разработка перспективных оценок с применением метода сглаживания Основная идея применения метода сглаживания состоит в том, что каждый новый прогноз, получается, посредством перемещения предыдущего прогноза в направлении, которое дало бы лучшие результаты по сравнению со старым прогнозом. Базовое уравнение имеет следующий вид:  t— временной период (например, 1-й месяц, 2-й месяц и т.д.); F[t] — это прогноз, сделанный в момент времени t; F[t+l] отражает прогноз во временной период, следующий непосредственно за моментом времени t; а — константа сглаживания; e[t] — погрешность, т.е. различие между прогнозом, сделанным в момент времени t, ифактическими результатами наблюдений в момент времени t. Таким образом, константа сглаживания является самокорректирующейся величиной. Сделайте сглаживающий прогноз на основании данных (10,4,5,5,7,8,6,20,19,20) используя формулу =B4+0,8*(A4-A8). Дайте графическое представление полученным результатам (график).  Рис. 6. При прогнозе с использованием линейной линии тренда пропускается скачок функции базовой линии, тогда как при прогнозе с применением сглаживания он отслеживается  Рис. 7. В диалоговом окне Экспоненциальное сглаживание необходимо ввести фактор затухания, а не константу сглаживания Задание 5. Индивидуальное задание. 1.Выберите любой социально-экон6омический процесс (например, объем продаж товара, количество клиентов социальной службы, план производства изделий и т.д.). 2.Для выбранного вами процесса подберите совокупность исходных данных (не менее 50 значений). 3.Выполните расчет прогноза с применением метода скользящего среднего (см. задания 1-4). 4.Оформите отчет по лабораторной работе. Отчет по лабораторной работе должен включать: Исходные данные и задание. Расчётные формулы для проведения необходимых расчетов исходя из задания лабораторной работы. Графическое представление полученных результатов расчета (при необходимости). Результаты расчёта показателей, оформленные в виде таблиц. Основные выводы. Выводы, сделанные в ходе проведенных расчетов должны быть четко и полно аргументированы. 5.Ответьте на вопросы к защите лабораторной работы: Какой процесс вы прогнозировали? При помощи какого метода вы прогнозировали процесс? Его особенности. Какие выводы можно сделать выводы? |