Учебное пособие Нижний Новгород 2010
 Скачать 1.29 Mb.
|
|
0,7365 R Рис. 8. Определение прогнозных значений объемов продаж 47 Первый прогноз объемов продаж на июль-декабрь 2007 г . Т а б л и ц а 4 Месяц Прогноз объема продаж, тыс.руб. Месяц Прогноз объема продаж, тыс.руб. Июль 2982,35 Октябрь 3068,06 Август 3011,54 Ноябрь 3095,45 Сентябрь 3040,09 Декабрь 3122,31 Спрогнозируем подобным способом производственные затраты и расходы на рекламу. Рабочие листы с данными и результатами прогноза показаны на рис. 9 и 10. В обоих случаях использовались линейные функции тренда, которые дают очень высокие значения коэффициентов детерминации. (Для производственных затрат экспоненциальная линия тренда дает еще более высокое значение коэффициента детерминации, однако остановимся на линейной функции тренда, поскольку такая функция больше соответствует действительности.) В данном случае аппроксимация исходных данных и прогнозные значения получились значительно лучше, чем при прогнозировании объемов продаж. Это связано с тем, что производственные затраты и расходы на рекламу не имеют сезонных составляющих. Как видно из вышеприведенных примеров, графические средства Excel плохо приспособлены для прогнозирования. Можно выделить следующие недостатки графических средств Excel для прогнозирования: • возможность прогнозирования только для случая зависимости прогнозируемой переменной от одного фактора; • ограниченный набор типов линий тренда; • невозможность автоматического вычисления прогнозных значений. Есть и достоинство – это быстрое построение линии тренда. 48 Рис. 9. Прогнозирование производственных затрат Рис. 10. Прогнозирование затрат на рекламу 49 В заключение остановимся на вопросе, какой тип линии тренда выбирать. Выбирается тот тип линии тренда, который обеспечивает максимальное значение коэффициента детерминации 2 R . Однако к этому показателю качества аппроксимации надо подходить осторожно, поскольку хорошее качество аппроксимации еще не гарантирует хорошее качество прогнозирования. Например, если к нашим данным применить полиномиальную линию тренда с показателем степени 6, то, как показано на рис. 11, аппроксимация исходных данных будет хорошей, значение коэффициента детерминации достаточно высоким, но прогнозируемые значения явно не соответствуют возможному поведению переменной Y в будущем. Рис. 11. Полиномиальная линия тренда для объемов продаж 50 4.2. Прогнозирование с помощью встроенных функций Excel Прогнозирование с помощью функций предоставляет большие возможности, чем графические средства. Некоторые из этих функций дают возможность построить доверительные интервалы для вычисленных прогнозных значений. Эти функции используются для проведения регрессионного анализа. В регрессионном анализе исследуются зависимости между случайными результирующими показателями Y от неслучайных входных переменных X. Математической моделью такого рода зависимостей является уравнение вида: ε (X) Y(X) + = f , где – случайная переменная. Это уравнение называется уравнением ε регрессии; функция ) X ( f – функцией регрессии. (Случайная величина имеет ε нормальное распределение с нулевым математическим ожиданием.) Выбор наилучшей в некотором смысле функции ) X ( f составляет задачу регрессионного анализа. Регрессионный анализ решает ту же задачу построения модели данных, что и задача прогнозирования. Методы регрессионного анализа применяются для построения модели данных, но в задаче прогнозирования используются и другие методы, которые не входят в арсенал методов регрессионного анализа. Приведем список и описание функций, используемых в процессе прогнозирования (табл. 5). Каждая из этих функций имеет не менее двух аргументов, один из которых задает массив значений независимой переменной X, а второй – массив значений зависимой переменной Y. В некоторых функциях можно задавать не только одномерный массив переменной X, но и двумерный, т.е. существует возможность исследовать зависимость между набором факторов X и переменной Y и строить множественную регрессию. Функции ЛГРФПРИБЛ и РОСТ работают с экспоненциальной регрессией, остальные – с линейной. 51 Список функций, применяемых в регрессионном анализе Т а б л и ц а 5 Функция Назначение ЛГРФПРИБЛ Возвращает параметры кривой, полученной в результате экспоненциальной аппроксимации исходных данных методом наименьших квадратов ЛИНЕЙН Возвращает массив коэффициентов функции регрессии, полученный в результате аппроксимации исходных данных методом наименьших квадратов ПРЕДСКАЗ Возвращает предсказанное значение функции на основе линейной регрессии для массивов известных значений X и Y или интервалов данных РОСТ Рассчитывает прогнозируемый экспоненциальный рост на основании имеющихся данных СТОШYX Возвращает стандартную ошибку аппроксимации для линейной регрессии ТЕНДЕНЦИЯ Возвращает значение прогноза в соответствии с линейной функцией регрессии 4.3. Быстрое вычисление прогнозных значений Для быстрого вычисления прогнозных значений переменной Y без явного построения функции прогнозирования используют статистические функции ПРЕДСКАЗ , РОСТ и ТЕНДЕНЦИЯ Эти функции применяются в следующих ситуациях. • Функция ПРЕДСКАЗ применяется, если функция прогнозирования (функция регрессии) зависит от одного фактора X и предполагается, что между прогнозируемой переменной Y и фактором X существует линейная зависимость вида: b m + = X Y . • Функция ТЕНДЕНЦИЯ применяется, если функция прогнозирования линейно зависит от нескольких факторов, а также в случае полиномиальной зависимости прогнозируемой переменной Y от одного фактора. Зависимости между прогнозируемой переменной Y и факторами k X ,..., X , X 2 1 имеют вид: k k m m m b X X X Y 2 2 1 1 0 + + + = + – линейная множественная регрессия, либо 52 k k m m m b X X X Y 2 2 1 0 + + + + = – полиномиальная регрессия от одного фактора, либо в виде комбинации множественной и полиномиальной регрессий. • Функция РОСТ применяется, если функция прогнозирования экспоненциально зависит от нескольких факторов, т.е. предполагается, что между прогнозируемой переменной Y и факторами k X ,..., X , X 2 1 существует зависимость вида: k k m m m b X X 2 X 1 0 Y 2 1 ⋅ ⋅ ⋅ ⋅ = Функция ПРЕДСКАЗ имеет синтаксис: =ПРЕДСКАЗ(х; Известные значения_Y; Известные значения_Х), где аргумент х – значение фактора, для которого вычисляется прогноз; аргумент Известные значения _Y – одномерный массив значений переменной Y (или ссылка на диапазон ячеек, содержащий этот массив); аргумент Известные значения _Х – массив значений фактора X (или ссылка на диапазон ячеек, содержащий этот массив). Функции ТЕНДЕНЦИЯ и РОСТ имеют одинаковый синтаксис: =ТЕНДЕНЦИЯ(Известные значения_Y; Известные значения_Х; Новые_значения_х; Константа) =РОСТ(Известные значения_Y; Известные значения_Х; Новые_значения_х; Константа) , где аргумент Известные значения_Y – одномерный массив значений переменной Y (или ссылка на диапазон ячеек, содержащий этот массив); аргумент Известные значения_Х – массив значений факторов k X ,..., X , X 2 1 (или ссылка на диапазон ячеек, содержащий этот массив); 53 аргумент Новые_значения_х – значения факторов, для которых вычисляется прогнозное значение; аргумент Константа принимает логическое значение: если он имеет значение ИСТИНА или 1 либо опущен, то коэффициент уравнения регрессии 0 b вычисляется как обычно; если же он имеет значение ЛОЖЬ или 0, то коэффициент полагается равным 0, и значения коэффициентов уравнения 0 b регрессии вычисляются с учетом этого условия. Если в функциях ТЕНДЕНЦИЯ и РОСТ аргумент Известные значения_Х опущен, то предполагается, что это массив натуральных чисел {1; 2; 3;...} такого же размера, как и массив аргумента Значения_Y. Если опущен аргумент Новые_значения_х, то по умолчанию предполагается, что он совпадает с аргументом Известные значения_Х. Эти функции используют для одновременного вычисления массива прогнозных значений по заданному массиву {х}значений факторов. Для этого в качестве аргумента х надо указать массив {х}, а саму функцию применяют как формулу массива: выделяют диапазон ячеек, в котором будет записан выходной массив прогнозных значений, затем вводят функцию и завершают процедуру нажатием комбинации клавиш На примере покажем применение этих функций для вычисления прогнозных значений производственных затрат, затрат на рекламу и объемов продаж на 31-36 периоды (июль-декабрь 2007 г.). Формулы, вычисляющие прогнозные значения, показаны на рис. 12 (формула для диапазона I4:I9 приведена в строке формул). Обратите внимание на то, что все эти формулы являются формулами массивов – это позволяет с помощью одной формулы получить массив прогнозных значений. При этом аргумент х у всех использованных функций является ссылкой на диапазон ячеек. 54 Рис. 12. Вычисление прогнозных значений с помощью статистических функций Получили еще два прогноза объемов продаж на второе полугодие 2007г.: линейный многофакторный (факторы – Период, Производственные затраты и Затраты на рекламу) и экспоненциальный многофакторный. Запишем эти прогнозы в таблицы (табл. 6 и 7 ). Прогнозы отличаются друг от друга и от первого прогноза (см. табл. 4). Линейный многофакторный прогноз объемов продаж на июль-декабрь 2007 г. Т а б л и ц а 6 Месяц Прогноз объема продаж, тыс.руб. Месяц Прогноз объема продаж, тыс.руб. Июль 3260,640 Октябрь 3440,867 Август 3320,715 Ноябрь 3500,943 Сентябрь 3380,791 Декабрь 3561,018 55 Экспоненциальный многофакторный прогноз объемов продаж на июль-декабрь 2007 г. Т а б л и ц а 7 Месяц Прогноз объема продаж, тыс.руб. Месяц Прогноз объема продаж, тыс.руб. Июль 3455,589 Октябрь 3760,659 Август 3554,425 Ноябрь 3868,220 Сентябрь 3656,088 Декабрь 3978,858 С помощью функции ТЕНДЕНЦИЯ вычислим прогнозные значения для полиномиальной функции регрессии. Допустим, что в нашем примере объемы продаж полиномиально зависят от времени (ограничимся многочленом третьей степени): 3 3 2 2 1 0 Y t m t m t m b + + + = В этом случае необходимо, чтобы в качестве аргумента Известные значения_Х были заданы значения 2 t и 3 t . Эти значения на рабочем листе, показанном на рис. 13, вычисляются в столбцах В иС. Аналогично для значений периодов, для которых вычисляется прогноз, необходимо подсчитать квадраты и кубы этих периодов (значения в столбцах I и J на рис. 13). Формула, по которой вычисляются прогнозные значения в столбце К, показана в строке формул. Получили еще один прогноз. Полиномиальный прогноз объемов продаж на июль-декабрь 2007 г. Т а б л и ц а 8 Месяц Прогноз объема продаж, тыс.руб. Месяц Прогноз объема продаж, тыс.руб. Июль 2719,8142 Октябрь 2447,6482 Август 2643,7094 Ноябрь 2326,4983 Сентябрь 2553,1863 Декабрь 2189,1400 56 Рис. 13. Вычисление прогнозных значений для полиномиальной регрессии 4.4. Быстрое вычисление коэффициента детерминации и доверительных интервалов для прогнозных значений В результате вычислений с помощью статистических функций получено четыре набора прогнозных значений (см. табл. 4, 6, 7 и 8) – какой же из них выбрать? Естественно отдать предпочтение тому, который более точен. Но как определить точность прогноза? На этот вопрос есть два ответа: во-первых, можно использовать в качестве «измерителя» точности прогноза коэффициент детерминации 2 R , во-вторых, можно построить для вычисленных прогнозных значений доверительные интервалы, которые бы содержали неизвестное, но «точное» значение прогноза с заданной вероятностью. Коэффициент детерминации показывает, насколько точно аппроксимированы исходные данные функцией прогнозирования. Если этот коэффициент имеет значение близкое к единице, то считается, что функция прогнозирования достаточно точно описывает прогнозируемую переменную Y в «прошлом», и на этом основании делается вывод, что вычисленные значения 57 функции прогнозирования также будут точно соответствовать «будущим» значениям переменной Y. Однако, это не всегда соответствует действительности. Коэффициент детерминации не всегда вычисляется автоматически, как при построении линии тренда для первого прогноза (рис. 7). Во время вычисления следующих прогнозов значение коэффициента детерминации автоматически не было получено. Коэффициент детерминации могут вычислить функции ЛИНЕЙН и ЛГРФПРИБЛ. Функция ЛИНЕЙН предназначена для вычисления коэффициентов множественной линейной или полиномиальной регрессий, а функция ЛГРФПРИБЛ – для вычисления коэффициентов экспоненциальной регрессии. Эти функции имеют аргумент Статистика, и если этот аргумент будет равен 1 или ИСТИНА, то функция выводит дополнительный набор статистических характеристик регрессии, среди которых и находится коэффициент детерминации. Функция ЛИНЕЙН вычисляет коэффициенты в уравнении k m m b ,..., , 1 0 линейной множественной регрессии: k k m m m b X X X Y 2 2 1 1 0 + + + + = , либо эти же коэффициенты в уравнении полиномиальной регрессии (от одного фактора): k k m m m b X X X Y 2 2 1 0 + + + + = Функция ЛГРФПРИБЛ вычисляет коэффициенты в k m m b ,..., , 1 0 уравнении экспоненциальной регрессии: k k m m m b X X 2 X 1 0 Y 2 1 ⋅ ⋅ ⋅ ⋅ = Эти функции имеют одинаковый синтаксис: =ЛИНЕЙН(Известные_значения_Y; Известные_значения_Х; Константа; Статистика) , =ЛГРФПРИБЛ(Известные_значения_Y; Известные_значения_Х; Константа; Статистика) , где 58 аргумент Известные_значения_Y – одномерный массив (или ссылка на диапазон ячеек, содержащий этот массив) значений переменной Y; аргумент Известные_значения_Х – массив (или ссылка на диапазон ячеек, содержащий этот массив) значений факторов X. Если данный аргумент опущен, предполагается, что это массив натуральных чисел {1; 2; 3;...} такого же размера, как и массив Значения_Y; аргумент Константа – логическое значение, которое указывает, должен ли коэффициент быть равным 0. Если этот аргумент имеет значение ИСТИНА, 0 b 1 или опущен, то коэффициент вычисляется как обычно. Если аргумент 0 b имеет значение ЛОЖЬ или 0, то полагается равным 0, и значения 0 b коэффициентов подбираются с учетом этого условия; i m аргумент Статистика принимает логическое значение, которое указывает, требуется ли рассчитывать дополнительные статистические характеристики регрессии. Если этот аргумент имеет значение ИСТИНА или 1, то функция рассчитывает и выводит эти дополнительные характеристики (табл. 9). Если аргумент Статистика имеет значение ЛОЖЬ, 0 или опущен, то функция возвращает только значения коэффициентов и i m 0 b . Функции возвращают массивы значений коэффициентов и (не i m 0 b менее двух значений), а также дополнительные статистические характеристики (если аргумент Статистика равен ИСТИНА). Поэтому функции должны обязательно задаваться в виде формулы массива (с использованием для ввода комбинации клавиш k . 59 Статистические характеристики, рассчитываемые функциями ЛИНЕЙН и ЛГРФПРИБЛ Т а б л и ц а 9 Характеристика Описание k s s s ,..., , 2 1 Среднеквадратические отклонения для коэффициентов k m m m ,..., , 2 1 b s Среднеквадратическое отклонение для коэффициента (это значение будет иметь значение ошибки #Н/Д, если аргумент Константа имеет значение ЛОЖЬ) 0 b 2 R Коэффициент детерминации ε s Остаточное среднеквадратическое отклонение (стандартная ошибка регрессии) F Критериальная статистика для проверки значимости уравнения регрессии df Степень свободы 1 SS Сумма квадратов регрессии 2 SS Сумма квадратов остатков В выходном массиве данные располагаются следующим образом. k m 1 − k m … 2 m 1 m 0 b k s 1 − k s … 2 s 1 s b s 2 R ε s F df 1 SS 2 SS Остальные ячейки этого массива заполняются значениями #Н/Д. Пример вычисления функции статистических характеристик с помощью функции |