Информатика как наука

Средства анализа данных и решения задач оптимизации Excel 2007 аналогичны средствам Excel 2003. Работа со средствами Excel 2003 для анализа данных и решения задач оптимизации: подбор параметров, диспетчер сценариев и надстройка Solver подробно изложены на страничке: Решение задач оптимизации в Excel.

Общий алгоритм выполнения прогнозирования:

В целом, процесс прогнозирования можно выполнить, соблюдая последовательность расчёта следующих пунктов:

1. Выделение трендовой составляющей как функции регрессии от нескольких факторов - обязательный этап прогнозирования;

2. Выделение сезонной составляющей в виде сезонных коэффициентов;

3. Трендовая и сезонная составляющая комбинируются (с помощью операции сложения или умножения) в единую функцию прогнозирования - виртуальный (не вычислительный) этап;

4. Вычисляются прогнозные значения как значения функции «прогнозирования» при будущих значениях факторов.

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

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

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

2. Выделение трендовой составляющей - подбор функций, аппроксимирующих трендовую компоненту, и вычисление параметров этих функций, отбор значимых факторов;

3. Выделение сезонной составляющей - вычисление сезонных коэффициентов по разностям между фактическими значениями переменной У и вычисленным значениям тренда;

4. Анализ остатков и отбор моделей. После построения функции прогнозирования (модели данных) проводится статистический анализ остатков - разностей между значениями переменной У и вычисленным значением функции прогнозирования. На основе анализа остатков отбираются одна или несколько моделей данных, наиболее адекватно представляющих исходные данные;

5. Вычисление прогнозного значения. На основе отобранных функций прогнозирования вычисляются прогнозные значения.

Суть всех реализованных методов сводится к построению функции регрессии («тренда») с дальнейшей ее экстраполяцией на основе данных за последние 6 месяцев.

Прогноз производится в несколько этапов:

1 Этап. Расчёт исходных данных

2 Этап. Построение функций регрессии различными методами

3 Этап. Выбор функции регрессии по условию минимальной ошибки аппроксимации

4 Этап. Расчёт доверительных интервалов и окончательный прогноз.

Этап №1: Входные данные для прогнозирования в Excel

Надо сказать, что в виду большого ассортимента товаров, находящихся на складах компании, наиболее оптимальным в плане ведения бизнеса для компании было внедрение системы краткосрочного прогнозирования (период упреждения был равен 1 месяцу).

Большинство методов и алгоритмов прогнозирования, реализованных в Excel, связано с построением тренда. В рамках поставленной задачи: построение прогноза на 1 месяц вперёд, для реализованной модели в Excel было необходимо и достаточно построение модели по шести последним месяцам продаж.

Этап №2: Построение функций регрессии

В рамках данного этапа, были реализованы следующие методы:

а) построение линейной функции регрессии:

- метод наименьших квадратов (реализуется посредством встроенной функции Excel ТЕНДЕНЦИЯ( )

Выполняется в два этапа:

1) Для определения погрешности прогноза:

Рис 2. Построение прогноза при помощи функции ТЕНДЕНЦИЯ

а) По 5-ти известным периодам, строится прогноз на 6-ой период;

б) Вычисляется погрешность (сравнение прогноза с исходным значением за 6-ой период).

Рис 3. Определение погрешности прогноза

2) Определение прогнозного значения на следующий (седьмой) период:

Рис 4. Определение прогноза с учётом погрешности

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

Прогноз по скользящим средним реализуется при помощи встроенной формулы Excel:

Y'3=СРЗНАЧ(Y1ч Y3)

Рис 5. Сглаживание исходных данных методом скользящих средних (по 3-м месяцам)

Обратите внимание:

Если сглаживание производиться по трём месяцам, то для первых двух месяцев не хватает данных!

Рис 6. Построение прогноза по уже сглаженным исходным данным

Расчёт погрешности метода прогнозирования по скользящим средним:

После сглаживания исходных данных, воспользуемся отношением:

е = 1/N * У |yi - y'i| / yi

Поскольку первые два периода сглаженных значений не определяются, расчёт величины погрешности определяется за 4 периода (с 3-го по 6-ой)!

б) построение нелинейной функции регрессии:

- метод экспоненциального сглаживания

Алгоритм построения функций регрессии:

прогнозирования методом экспоненциального сглаживания

Экспоненциальное сглаживание применяется к временным ряда, т.е. тогда, когда фактором, от которого зависит переменная прогнозирования У, является время t. В этом методе сглаживания учитывается «старение» данных - в процессе сглаживания больший вес имеют последние данные.

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

Экспоненциальное сглаживание для построения функции регрессии

Вопрос 20

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

Изучим инструмент Подбор параметра на примерах из области финансов.

Калькуляция

Пусть дана структура цены договора: Собственные расходы, Прибыль, НДС.

Известно, что Собственные расходы составляют 150 000 руб., НДС 18%, а Целевая стоимость договора 200 000 руб. (ячейка С13). Единственный параметр, который можно менять, это Прибыль. Подберем такое значение Прибыли (С8), при котором Стоимость договора равна Целевой, т.е. значение ячейки Расхождение (С14) равно 0.

В структуре цены в ячейке С9 (Цена продукции) введена формула

Собственные расходы + Прибыль (=С7+С8).

Стоимость договора (ячейка С11) вычисляется как

Цена продукции + НДС (=СУММ(С9:C10)).

Конечно, можно подобрать значение вручную. В данном случае необходимо уменьшить значение прибыли на величину расхождения без НДС. Однако, часто зависимости в формулах достаточно сложны. В этом случае поможет инструмент Подбор параметра (на вкладке Данные в группе Работа с данными выберите команду Анализ «что-если?», а затем выберите в списке пункт Подбор параметра…).

Выделите ячейку С14, вызовите Подбор параметра. В качестве целевого значения для ячейки С14 укажите 0, изменять будем ячейку С8(Прибыль).

Нажмите ОК.

Теперь, о том когда этот инструмент работает.

1. Изменяемая ячейка не должна содержать формулу, только значение.

2. Необходимо найти только 1 значение, изменяя 1 ячейку. Если требуется найти 1 конкретное значение (или оптимальное значение), изменяя значения в НЕСКОЛЬКИХ ячейках, то используйте Поиск решения.

3. Уравнение должно иметь решение, в нашем случае уравнением является зависимость стоимости от прибыли. Если целевая стоимость была бы равна 1000, то положительной прибыли бы у нас найти не удалось, т.к. расходы больше 150 тыс. Или например, если решать уравнение x2+4=0, то очевидно, что не удастся подобрать такое х, чтобы x2+4=0

Примечание: В файле примера приведен алгоритм решения Квадратного уравнения с использованием Подбора параметра.

Подбор суммы кредита

Теперь другой пример. Предположим, что нам необходимо определить максимальную сумму кредита, которую мы можем себе позволить взять в банке. Пусть нам известна сумма ежемесячного платежа в рублях (1800 руб./мес.), а также процентная ставка по кредиту (7,02%) и срок на который мы хотим взять кредит (180 мес).

В EXCEL существует функция ПЛТ() для расчета ежемесячного платежа в зависимости от суммы кредита, срока и процентной ставки. Но эта функция нам не подходит, т.к. сумму ежемесячного платежа мы итак знаем, а вот сумму кредита (параметр функции ПЛТ()) мы как раз и хотим найти. Но, тем не менее, мы будем использовать эту функцию для решения нашей задачи. Без применения инструмента Подбор параметра сумму займа пришлось бы подбирать в ручную с помощью функции ПЛТ() или искать соответствующую формулу (в общем случае ее может и не существовать).

Введем в ячейку B6 ориентировочную сумму займа, например 100 000 руб., срок на который мы хотим взять кредит введем в ячейку B7, % ставку по кредиту введем в ячейку B8, а формулу =ПЛТ(B8/12;B7;B6) для расчета суммы ежемесячного платежа в ячейку B9 (см. файл примера).

Чтобы найти сумму займа соответствующую заданным выплатам 1800 руб./мес., делаем следующее:

на вкладке Данные в группе Работа с данными выберите команду Анализ «что-если?», а затем выберите в списке пункт Подбор параметра…;

в поле Установить введите ссылку на ячейку, содержащую формулу. В данном примере - это ячейка B9;

введите искомый результат в поле Значение. В данном примере он равен -1800;

В поле Изменяя значение ячейки введите ссылку на ячейку, значение которой нужно подобрать. В данном примере - это ячейка B6

Нажмите ОК

Что же сделал Подбор параметра? Инструмент Подбор параметра изменял по своему внутреннему алгоритму сумму в ячейке B6 до тех пор, пока размер платежа в ячейке B9 не стал равен 1800,00 руб. Был получен результат - 200 011,83 руб. В принципе, этого результата можно было добиться, меняя сумму займа самостоятельно в ручную.

Подбор параметра подбирает значения только для 1 параметра. Если Вам нужно найти решение от нескольких параметров, то используйте инструмент Поиск решения. Точность подбора параметра можно задать через меню Кнопка офис/ Параметры Excel/ Формулы/ Параметры вычислений. Вопросом об единственности найденного решения Подбор параметра не занимается, вероятно выводится первое подходящее решение.

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

Размещено на Allbest.ru