W7 (Финансовый контроль и планирование с помощью Excel), страница 4

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

Составление прогнозов с помощью надстроек скользящего среднего

Другим способом применения скользящего среднего является использование надстройки Пакет анализа. Установить эту надстройку можно следующим образом.

1. Выберите команду Сервис-Надстройки.

2 Появится диалоговое окно Надстройки. Установите флажок опции Пакет

анализа и щелкните на кнопке ОК.

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

4. В меню Сервис вы обнаружите новую команду Анализ данных. Выберите команду Сервис-Анализ данных.

5. Появится диалоговое окно Анализ данных, в котором содержатся все доступные функции анализа данных. Из списка выберите инструмент анализа Скользящее среднее и щелкните на кнопке ОК.

6. Появится диалоговое окно Скользящее среднее.

7. В поле Входной интервал наберите данные о вашей базовой линии либо укажите диапазон в рабочем листе, ссылка на него появится в этом поле.

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

9. В поле ввода Выходной интервал введите адрес ячейки, с которой хотите начать выход (либо просто щелкните на этой ячейке в рабочем листе).

10. Щелкните на кнопке ОК.

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

Как выполнить вычисления

с использованием скользящего среднего Ехсеl

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

Однако надстройка для вычисления скользящего среднего (линия тренда) связывает прогноз с конечными результатами наблюдений в конкретном среднем значении. Сравните, например, рис.9, на котором скользящее среднее получено путем введения формул непосредственно в рабочий лист, с рис.11, составленным с помощью надстройки для вычисления скользящего среднего. Заметьте, что каждый показатель скользящего среднего на рис. 11 сдвинут на одну позицию по сравнению с рис.9. Это значит, что в данном примере прогнозом для третьего периода является значение 550, т.е. первое вычисленное скользящее среднее.

Нелогично рассматривать некое число в качестве прогноза на период, на основе которого составлялся этот прогноз. В стандартной интерпретации значение 550 является прогнозом не на третий, а на четвертый месяц.

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

Составление прогнозов скользящего среднего с помощью диаграмм

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

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

1. Выделите данные своей базовой линии.

2. Щелкните на кнопке Мастер диаграмм, расположенной на стандартной панели инструментов, либо выберите команду Вставка-Диаграмма-На новом листе. Если вам больше нравится первый способ, нажмите и, не отпуская правой кнопки мыши, выделите область на рабочем листе, в которой вам бы хотелось увидеть график.

3. На первом шаге работы средства Мастер диаграмм проверьте правильность ссылок на ячейки базовой линии и щелкните на кнопке Далее.

4. На втором шаге выберите тип диаграммы График и щелкните на кнопке Далее.

5. На третьем шаге выберите вид графика, включающий как линии, так и маркеры, например, вид 1, 4 или 5. Щелкните на кнопке Далее.

6. На четвертом шаге установите переключатель В столбцах опции Ряды данных находятся. Установите параметры опций Считать стлб. метками оси х или Считать стр. метками легенды. Щелкните на кнопке Далее.

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

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

9. Выделите ряд данных диаграммы, а затем выберите команду Вставка-Линия тренда.

10. В появившемся диалоговом окне Линия тренда щелкните на корешке вкладки Тип. Выберите линию тренда Скользящее среднее, а затем – необходимые периоды с помощью счетчика Точки. Период – это количество наблюдений, которое включается в любое вычисление скользящего среднего.

11. Щелкните на кнопке ОК.

Закончив выполнение вышеперечисленных действий, вы увидите на графике линию тренда скользящего среднего (вместе с фактическими данными наблюдений), как это показано на рис.12. Первых несколько показателей скользящего среднего отсутствуют по той же причине, по которой средство Скользящее среднее возвращает вместо этих показателей #Н/Д. Дело в том, что скользящее среднее, включающее данные трех предшествующих наблюдений, не может быть вычислено до тех пор, пока не будет закончено наблюдение за третьим периодом.

Прогнозирование о помощью функций регрессии Ехсеl

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

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

Составление линейных прогнозов: функция ТЕНДЕНЦИЯ

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

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

и получим результат, показанный на рис.13.

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

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

  Ячейка С1: = 9,13 + 0,61*1

  Ячейка С2: = 9,13 + 0,61*2

  Ячейка С3: = 9,13 + 0,61*3

• Значение 9,13 представляет собой длину отрезка, отсекаемого на оси ординат линией прогноза, т.е. значение прогноза в начальный момент. Значение 0,61 равно угловому коэффициенту линии прогноза, другими словами, значения прогноза изменяются в результате изменений дат проведения наблюдений.

• Поскольку все значения прогноза составляются на основе одних и тех же показателей отрезка, отсекаемого на оси ординат, и углового коэффициента, прогноз не отражает происходящих изменений во временном ряду. Например, данные ряда резко изменяются между восьмым (10) и девятым результатами наблюдений (16). Это изменение влияет на все значения прогноза, даже значение прогноза временного отрезка (2), хотя и располагается на шесть результатов наблюдений раньше, чем это изменение фактически произошло.

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

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

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

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

На рис.14 продемонстрированы описанные выше действия. Первый аргумент – А1:А10 – определяет данные наблюдений базовой линии (известные-значения-у); второй аргумент – В1:В10 – определяет временные моменты, в которые эти данные были получены (известные-значения-х). Значение 11 в ячейке В11 является новым-значением-х и определяет время, которое связывается с перспективной оценкой.

Формула фактически говорит о следующем: "Если известно, каким образом у-значения в диапазоне А1:А10 соотносятся с х-значениями в диапазоне В1:В10, то какой результат у-значения мы получим, зная новое х-значение временного момента, равное 11?". Полученное значение 15,87 является прогнозом на основе фактических данных на пока еще не наступивший одиннадцатый временной отсчет.

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

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

Ехсеl вернет в ячейки С11:С24 прогноз на временные моменты с 11 по 24. Данный прогноз будет базироваться на связи между данными наблюдений базовой линии диапазона А1:А10 и временными моментами базовой линии с 1 по 10, указанными в ячейках В1:В10.

Составление нелинейного прогноза: функция РОСТ

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

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

Пример.

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