Прикладные системы обработки данных

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

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

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

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

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

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

Рассмотрим пример. Предположим, существует отчет, в котором отражено ежедневное количество звонков с жалобами на конкретный программный продукт, поступающих в течение двух недель. Эти данные отражены в рабочем листе Ехсеl, как показано на рисунке 6 (столбец A).

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

Одним из способов создания скользящего среднего в Ехсеl является прямое введение формулы. Таким образом, чтобы получить трехдневное скользящее среднее количества телефонных звонков, введем формулу в ячейку В4:

= СРЗНАЧ(А1:А3)

Затем скопируем и вставим эту формулу в ячейки В5:В10. В данном случае показатель скользящего среднего действительно имеет тенденцию к увеличению (рисунок 6).

 

 

Рисунок 6 − Показатель скользящего среднего

 

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

В поле «Входной интервал» вводятся данные о базовой линии. В поле «Интервал» − количество месяцев, которые следует включить в подсчет скользящего среднего. В поле «Выходной интервал» вводится адрес ячейки, с которой необходимо начать выход (рисунок 7).

 

Рисунок 7 − Инструмент анализа «Скользящее среднее»

 

Ехсеl выполняет работу по вычислению скользящего среднего. Значения скользящего среднего начинаются со значений #Н/Д, которые равны значению указанного интервала минус один. Это делается из-за недостаточного количества данных для вычисления среднего значения нескольких первых результатов наблюдений.

На рисунке 8 показан результат вычисления скользящего среднего для данных, приведенных на рисунке 6, с интервалом в 3 периода.

 

 

Рисунок 8 − Вычисление скользящего среднего

 

Как правило, прогноз с применением скользящего среднего рассматривается как прогноз на период, непосредственно следующий за периодом наблюдения. Однако надстройка для вычисления скользящего среднего связывает прогноз с конечными результатами наблюдений в конкретном среднем значении. Каждый показатель скользящего среднего на рисунке 8 сдвинут на одну позицию по сравнению с рисунком 6. Это значит, что в данном примере прогнозом для третьего периода является значение 10,33, т. е. первое вычисленное скользящее среднее.

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

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

Использование функции рабочего листа ТЕНДЕНЦИЯ() – самый простой способ вычисления регрессионного анализа.

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

= ТЕНДЕНЦИЯ(А1:А10;В1:В10)

Результат вычислений представлен на рисунке 9.

 

 

Рисунок 9 − Регрессивный прогноз

 

В данном примере функция ТЕНДЕНЦИЯ() вычисляет прогноз, основанный на связи между фактическими результатами наблюдений и числами 1 – 10, которые могут отражать либо первых десять дней месяца, либо первых десять месяцев года.

Ехсеl выражает первый аргумент как аргумент известные значения-у функции ТЕНДЕНЦИЯ(), а второй – как аргумент функции известные-значения-х.

Регрессивный прогноз, пример которого приведен на рисунке 9, распространяется за пределы данных самого последнего фактического наблюдения. Но на практике желательно составить прогноз хотя бы на первый, следующий за этим, период временного ряда (т. е. на тот, для которого еще нет результатов наблюдения). Дальше описывается, как это можно сделать с помощью функции ТЕНДЕНЦИЯ().

Применяя данные рабочего листа, представленные на рисунке 9, введем в ячейку В11 число 11, а в ячейку С11 – формулу:

= ТЕНДЕНЦИЯ(А1:А10;В1:В10;В11)

На рисунке 10 продемонстрированы описанные выше действия.

 

 

Рисунок 10 − Регрессивный прогноз

 

Функция ТЕНДЕНЦИЯ() вычисляет прогнозы, основанные на линейной связи между результатом наблюдения и временем, в которое это наблюдение было зафиксировано.

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

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

В случае нелинейной взаимосвязи функция Ехсеl РОСТ() поможет получить более точную картину направления развития бизнеса, чем при использовании функции ТЕНДЕНЦИЯ().

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

Как и при использовании функции ТЕНДЕНЦИЯ(), можно генерировать прогнозы, просто подставляя новые-значения-х. Чтобы спрогнозировать результаты 11 – 13 недель, следует ввести эти числа в ячейки В12:В14, а затем с помощью формулы массива в диапазон ячеек С2:С14 ввести следующее:

= РОСТ(А2:А11;В2:В11;В2:В14)

В ячейках С12:С14 приведены значения предварительной оценки количества заказов, которое может ожидать менеджер в последующие три недели при условии, что текущая тенденция роста останется неизменной.

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

Результаты выполнения этих действий представлены на рисунке 11.

 

 

Рисунок 11 − Предварительные оценки количества заказов

 

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

Методы, основанные на регрессии, такие как функции ТЕНДЕНЦИЯ() и РОСТ(), применяют ко всем точкам прогноза одну ту же формулу. По этой причине достижение быстрой реакции на сдвиги в уровне базовой линии значительно затрудняется. Сглаживание представляет собой простой способ обойти данную проблему.

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

Ехсеl непосредственно один из таких методов с помощью средства «Экспоненциальное сглаживание» в надстройке «Пакет анализа».

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

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

Для этого введем данные за первые десять дней в ячейки A1:A10, а затем активизируем средство Excel «Экспоненциальное сглаживание».

Используем данные из диапазона ячеек A2:A11 в качестве параметра «Входной интервал». Ячейку B2 применяем в качестве параметра «Выходной интервал», а значение 0,7 − в качестве параметра «Фактор затухания» (рисунок 12).

 

 

Рисунок 12 − Инструмент анализа «Экспоненциальное сглаживание»

 

Следует избегать использования параметра «Фактор затухания», который меньше значения 0,7. Если создается впечатление, что при большем значении константы сглаживания средство «Экспоненциальное сглаживание» действует значительно лучше, то, вероятнее всего, это происходит благодаря высокому уровню автокорреляции во временном ряду. Этот параметр наблюдается в тех случаях, когда существует зависимость между данными наблюдений, полученными в определенное время, и данными наблюдений, полученными на несколько временных периодов раньше.

Результат вычислений представлен на рисунке 13.

 

 

Рисунок 13 − Экспоненциальное сглаживание

 

Согласно данному сглаженному прогнозу, для удовлетворения потребностей клиентов на одиннадцатый день необходимо иметь 16 или 17 автомобилей с багажниками для лыж.

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

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

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

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

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

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

Для решения задачи линейного программирования необходимо построить экономико-математическую модель исследуемого экономического процесса.

Математическая модель задачи линейного программирования включает в себя:

• максимум или минимум целевой функции (критерий оптимальности);
• систему ограничений в форме линейных уравнений или неравенств;
• требования неотрицательности переменных.

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

Виды задач линейного программирования:

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