Предсказ(X;изв_значY;изв_значX),
где X – элемент независимой переменной для которой вычисляется прогнозное значение; изв_значY – известные значения зависимой переменной; изв_значX - известные значения независимой переменной.
Для той же задачи спрогнозируем цену на сахар.
Пример заполнения параметров функции приведен на рис. 18, а пример расчета на рис. 19.
Рис. 18
а) |
б) |
Рис. 19
Использование функции ТЕНДЕНЦИЯ(). Назначение функции ТЕНДЕНЦИЯ() очень близко к функции ПРЕДСКАЗ(), но ее возможно употреблять для прогноза не только линейно зависимых величин (неявная линейная регрессия). Функция ТЕНДЕНЦИЯ() имеет следующий синтаксис:
Тенденция(изв_значY; изв_значX; нов_значX;константа),
где изв_значY – множество известных значений зависимой переменной; изв_значX – множество известных значений независимой переменной; нов_значX – новые значения независимой переменной; константа – логическая константа, если она принимает значения ИСТИНА, то свободный член уравнения регрессии рассчитывается обычным образом, в противоположном случае он равен 0, то есть график функции линейной регрессии будет проходить через начало координат. Пример использования функции в случае линейной зависимости приведен на рис. 20 и 21.
Рис. 20
|
|
|
|
а) |
б) |
Рис. 21
Внимание! Функция ТЕНДЕНЦИЯ() может быть использована не только для расчета одного прогнозного значения, но и для расчета массива значений, как это приведено на рис. 21. Необходимо помнить, что после заполнения одной ячейки (C10 на рис. 21) , надо выделить массив ячеек (C10:C13), перевести в режим редактирования (клавиша F2), потом заполнить его комбинацией клавиш CTRL-SHIFT-ENTER.
Таким образом, прогнозное значение цены составляет 1856,39 грн/т. Следовательно, фирма «Альфа» может принять решение о закупке, так как прогнозное значение цены выше предлагаемой на фьючерсных торгах.
Рассмотрим пример использования функции ТЕНДЕНЦИЯ(), когда одна величина зависит от нескольких независимых переменных. Вернёмся к примеру 3 (рис. 10).
Результаты приведены на рис. 22 - 24.
Рис. 22
Рис. 23
Рис. 24
Выше было подчеркнуто, что использования всех описанных методов и функций возможно лишь тогда, когда проверена адекватность принятых аппроксимаций.
Тексты заданий
Вариант 1.
Компания рассматривает возможность увеличения сметы на рекламу конкретной продукции, с помощью чего рассчитывает добиться увеличения объёма продаж.
Имеются данные сметы на рекламу и объёмы продаж в единицах продукции, проданной компанией (табл.1).
Задания
Охарактеризовать совокупность данных объёма продаж.
Оценить взаимосвязь и взаимное влияние объёма продаж и сметы на рекламу.
Построить график зависимости между объёмом продаж и сметой на рекламу. Используя возможности Excel, подобрать наиболее подходящий тип тренда. Сделать выводы.
Установить функциональную зависимость между объёмом продаж и сметой на рекламу. Проанализировать адекватность полученного уравнения. Сравнить результаты пунктов
3 и 4.
Спрогнозировать объём продаж для бюджета $170000.
Спрогнозировать объём продаж для бюджета: $170000; $183100; $194215; $200000.
Кроме увеличения расходов на рекламу продукции, компания исследует последствия изменения цены на единицу этой продукции. Добавить столбец данных цены за шт. (табл.2).
Выполнить задания пунктов 2; 4; 5; 6, учитывая зависимость объёма продаж от бюджета и цены за шт. (табл.2).
Сравнить спрогнозированные результаты для объёма продаж, полученные с учётом