Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Обсуждение вопросов по использованию Excel, Access и других программ оптимизирующих работу закупщика
Аватар пользователя
market-1
Гуру
Гуру
Сообщений: 777
Зарегистрирован: 02 май 2010 09:37
Имя: Илья
Фамилия: Константинов
Должность: Директор по маркетингу
Откуда: Москва

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение market-1 » 31 янв 2014 08:54

ABaturin писал(а):Выкладываю данные, реальные продажи по товарным группам. Данные обрезал на 53 периоде. Есть данные до 60 периода.

А чему равен период?

Реклама
Аватар пользователя
Роман Бодряков
Авторитет
Авторитет
Сообщений: 5253
Зарегистрирован: 19 апр 2004 03:00
Имя: Роман
Фамилия: Бодряков
Должность: Ген.Директор в кубе - наноолигарх
Откуда: Россия

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение Роман Бодряков » 31 янв 2014 13:21

Илья, я наверно открою тебе тайну...

Ты в своем расчете не используешь линейный тренд как таковой.
1. Ты считаешь долю месяца от года
2. Потом считаешь среднюю долю по всем месяцам. Называешь это сезонным коэффициентом.
3. Потом считаешь средний прирост по годам.
4. И на основании своих коэффициентов разносишь по месяцам.

Ты ни разу в своих вычислениях не строишь линейного тренда.

Разве что среднее за последние три года, но это ближе к скользящему среднему по трем точкам, а не к линейному тренду описанному функцией y=ax+b

Поэтому и расхождения в цифрах.
Данные для прогноза_Закуп_81 месяц-1.xlsx


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

Посмотри как изменятся приросты если посчитать линейный тренд по трем годам как ты делаешь.
У вас нет необходимых прав для просмотра вложений в этом сообщении.
Есть такие решения, после принятия которых тараканы в голове аплодируют стоя! И просят повторить "НА БИС!!!"
Образование круче не у того, кто больше Знает, а у того, кто хоть что-то умеет.

Аватар пользователя
market-1
Гуру
Гуру
Сообщений: 777
Зарегистрирован: 02 май 2010 09:37
Имя: Илья
Фамилия: Константинов
Должность: Директор по маркетингу
Откуда: Москва

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение market-1 » 31 янв 2014 13:33

ABaturin писал(а):Выкладываю данные, реальные продажи по товарным группам. Данные обрезал на 53 периоде. Есть данные до 60 периода.

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

Небольшие комментарии к нему:
1. Красным выделены новинки, по которым нет достаточной статистики продаж и построение прогнозов методом экстраполяции на длительный период будет методологически, да и "по жизни" некорректно. Их нужно прогнозировать вручную, экспертной оценкой. Поэтому их не учитывал.
2. Суммарный прогнозный рост продаж по всем позициям в последний год получился 6%. Если предположить, что это все тот же злополучный 2013 год, то думаю, что реальные данные даже могут уйти в минус процентов на 5. Но вопрос не в этом, а в том, чтобы сравнить автоматический и простейшие ручные алгоритмы.
3. Там получились в некоторых местах отрицательные продажи. Это издержки модели. Но так как в фактических данных тоже есть отрицательные продажи (видимо, возвраты), то предлагаю не обращать на это внимания.
У вас нет необходимых прав для просмотра вложений в этом сообщении.

Аватар пользователя
market-1
Гуру
Гуру
Сообщений: 777
Зарегистрирован: 02 май 2010 09:37
Имя: Илья
Фамилия: Константинов
Должность: Директор по маркетингу
Откуда: Москва

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение market-1 » 31 янв 2014 13:45

Роман Бодряков писал(а):Ты в своем расчете не используешь линейный тренд как таковой.

Согласен.
Извиняюсь за вольное обращение с терминами. В предыдущем сообщении уже это оговорил.
При малых колебаниях продаж в 10%-20% год к году это практически одно и то же. Вот и привык. Кстати, сравнил сейчас строгий линейный и первоначальный вариант, прогноз даже чуть ближе к факту - не 12% отклонение, а 8%.

Аватар пользователя
Роман Бодряков
Авторитет
Авторитет
Сообщений: 5253
Зарегистрирован: 19 апр 2004 03:00
Имя: Роман
Фамилия: Бодряков
Должность: Ген.Директор в кубе - наноолигарх
Откуда: Россия

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение Роман Бодряков » 31 янв 2014 14:05

Илья, у тебя в данных какая-то засада, которая не дает работать линейному тренду.
Сбрось мне пожалуйста пяток ключевых товаров с привязкой к реальными месяцам и годам и подлиннее статистику продаж (81 месяц достаточно). Названия не нужны.
В почту.
У меня есть идея, хочу немного другим способом поискать зависимости и причины, почему сбоит алгоритм с линейностью.
Потом тебе отпишу свои выводы.
Есть такие решения, после принятия которых тараканы в голове аплодируют стоя! И просят повторить "НА БИС!!!"
Образование круче не у того, кто больше Знает, а у того, кто хоть что-то умеет.

Аватар пользователя
market-1
Гуру
Гуру
Сообщений: 777
Зарегистрирован: 02 май 2010 09:37
Имя: Илья
Фамилия: Константинов
Должность: Директор по маркетингу
Откуда: Москва

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение market-1 » 31 янв 2014 14:35

Роман Бодряков писал(а):Илья, у тебя в данных какая-то засада, которая не дает работать линейному тренду.

Сбросил.
Уж какие есть...
И у меня нормально работает. Там на отдельной страничке линейный тренд, который дал отклонение в целом по году на 8% от фактических данных. Все формулы есть.
Спасибо тебе, что заставил меня копаться в собственных данных! В итоге мне удалось даже немного уточнить прогноз. Хотя если учесть динамику рынка, то один метод дал +2% отклонений, а другой метод -2% (если смотреть в целом по группе товаров). Хорошая точность, черт возьми!

Аватар пользователя
ABaturin
Пользователь
Пользователь
Сообщений: 29
Зарегистрирован: 26 ноя 2013 13:58
Имя: Alexey
Фамилия: Baturin
Должность: Предприниматель

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение ABaturin » 01 фев 2014 23:20

market-1 писал(а):
ABaturin писал(а):Выкладываю данные, реальные продажи по товарным группам. Данные обрезал на 53 периоде. Есть данные до 60 периода.

А чему равен период?

Извиняюсь за задержки с ответами, периодичность месячная.

Аватар пользователя
ABaturin
Пользователь
Пользователь
Сообщений: 29
Зарегистрирован: 26 ноя 2013 13:58
Имя: Alexey
Фамилия: Baturin
Должность: Предприниматель

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение ABaturin » 02 фев 2014 00:58

Сравнил прогнозы с фактическими данными. Результат прогноза по модели Ильи очень хорошая.

В среднем точность полу с среднем получилась 88%, против 92% автоматического расчета.

По итого даже выше чем у F4AC PRO
у Ильи 99,38%
у F4AC PRO 99.13%

Из 54 товарных групп F4AC PRO смог спрогнозировать 47 позиций, Илья 37.

Расчеты во вложении.

Илья, спасибо!
У вас нет необходимых прав для просмотра вложений в этом сообщении.

SAV
Профессионал
Профессионал
Сообщений: 372
Зарегистрирован: 14 июн 2011 18:56
Имя: Андрей
Фамилия: МММ
Должность: Менеджер

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение SAV » 03 фев 2014 08:55

Алексей, а кнопки "удалить созданные листы" нет?
Анализирую ассортимент по 1,5 тысяч позиций, строю графики. Боюсь Эксель взорваться может, а руками лень удалять.

Аватар пользователя
ABaturin
Пользователь
Пользователь
Сообщений: 29
Зарегистрирован: 26 ноя 2013 13:58
Имя: Alexey
Фамилия: Baturin
Должность: Предприниматель

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение ABaturin » 03 фев 2014 09:38

SAV писал(а):Алексей, а кнопки "удалить созданные листы" нет?
Анализирую ассортимент по 1,5 тысяч позиций, строю графики. Боюсь Эксель взорваться может, а руками лень удалять.

Андрей, в настройках есть галочка "Удалить листы с расчетами"
http://www.4analytics.ru/dop.-vozmojnos ... etami.html

Аватар пользователя
market-1
Гуру
Гуру
Сообщений: 777
Зарегистрирован: 02 май 2010 09:37
Имя: Илья
Фамилия: Константинов
Должность: Директор по маркетингу
Откуда: Москва

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение market-1 » 03 фев 2014 10:01

ABaturin писал(а):Сравнил прогнозы с фактическими данными. Результат прогноза по модели Ильи очень хорошая.

В среднем точность полу с среднем получилась 88%, против 92% автоматического расчета.

Не совсем понял, как считалась точность, поэтому проведу свой анализ.

1. Если просуммировать все товарные группы (в моей модели за вычетом тех семи, которые я посчитал некорректным прогнозировать из-за очень короткой истории продаж), то отклонение моего прогноза от факта получилось +6%.
4АС PRO дал в зависимости от выбранной модели отклонение от +10% (модель 90-10) до +21% (модель 95-5). Но по нему суммировались все товарные группы без исключения.

2. Если и по моему автоматическому прогнозу взять все группы, то отклонение вырастет до 13%. И это естественно, потому что модель предусматривает расчет тренда по трем годам продаж, и для товаров, которые продаются всего два года, погрешность будет запредельной. Но с учетом этих позиций можно было вручную применить более короткий горизонт прогнозирования (2 года вместо 3-х). И тогда в моей модели отклонение по всем 54 группам падало с 13% до 9%, что примерно соответствует уровню 4АС PRO.

3. Дальше я сравниваю только мою первую модель и давшую минимальное отклонение от факта модель 90-10 от 4АС PRO (надеюсь, что автоматический алгоритм выбрал бы именно ее).

4. Просуммируем продажи за 7 месяцев по каждой группе и сравним с фактическими. Так вот, обе модели ошиблись в прогнозировании отдельных групп, где отклонение от факта составило более 100%. Но в моей модели эти отклонения больше - до 450%, то есть почти в пять раз от факта. В модели 4АС PRO максимальное отклонение составило 327%. Основная причина этого - попадание на границу жизненного цикла , когда прогнозировать динамику продаж нужно не по годам, а уже по месяцам. (пример такого графика прикрепил).

5. Если же убрать такие выбросы, то точность поартикульного прогноза становится сопоставимой: в моей программе она ошибается в среднем на 30% от фактических продаж в ту и другую стороны, а 4АС PRO примерно на 25%. После такой точности суммарного прогноза на 7 месяцев говорить о точности помесячного прогноза не стоит, потому что она явно не устроит никакой отдел закупок.

Что имеем в итоге? Соглашусь, что точность прогноза сопоставима и в моих моделях, и в 4АС PRO. Но есть один очень важный момент: если по суммарным продажам точность от 6% до 10% - это приемлемо, то поартикульный (или по отдельным группам товаров, как они названы в данном случае) прогноз дает ошибку выше 25% с выплесками до 300% и более. Работать с таким прогнозом уже очень рискованно. И на мой взгляд, нужно переходить на другие модели.

То есть поартикульный прогноз для ассортимента с сотнями наименований (ради чего и задумываются все автоматические алгоритмы) на данных фактических продаж дает не столько большую, сколько непредсказуемую погрешность. Основной вклад в нее вносят любые отклонения от стандартной сезонности:
1. границы жизненного цикла товара, связанные с выходом товаров-заменителей (спрос переключается на них, нарушая естественный ход вещей).
2. случайные факторы, связанные с внутренними процессами перераспределения спроса между товарами. Например, ниже привожу график, на котором первые 30 недель товар продавался в количестве 27 тыс. шт. в месяц, а потом взлетел до 500 тыс. шт. (в 19 раз!). Но это полбеды. Через год продажи резко упали втрое. Что это? Дефицит, мода, товар-заменитель? Хотя именно с этим случаем 4AC PRO справился и дал отклонение от факта всего в 30% (против моего четырехкратного). Но прогнозирование 10-12 месяцев назад неминуемо бы дало огромную ошибку в разы от фактических продаж. Хотя и 30% - это та еще ошибка. И это погрешность целиком на весь прогнозный период в 7 месяцев!
Если мы начнем смотреть помесячный поартикульный прогноз, то станет понятно, что он лишен и физического, и коммерческого смысла. Во всяком случае, за погрешность в сотни процентов, которая и в моем прогнозе, и у 4AC PRO является нормой, я бы собственное увольнение воспринял как слишком легкое наказание.

Поэтому получается, что достаточно точный прогноз по группе - это не достоинство наших алгоритмов, а просто нивелирование случайных факторов. В итоге "плюсы" и "минусы" прогноза компенсируются. Но ведь в реальных-то продажах не так! Если "минусы" в прогнозе мы еще можем компенсировать в случае возможности оперативного дозаказа товара, то вот "плюсы" неминуемо лягут избыточным запасом на склад.
Короче, что делать с такими нашими прогнозами на практике мне не очень понятно. Поэтому мы и ушли от поартикульного помесячного прогнозирования на период более 1 месяца.
У вас нет необходимых прав для просмотра вложений в этом сообщении.

SAV
Профессионал
Профессионал
Сообщений: 372
Зарегистрирован: 14 июн 2011 18:56
Имя: Андрей
Фамилия: МММ
Должность: Менеджер

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение SAV » 03 фев 2014 10:35

SAV писал(а):Алексей, а кнопки "удалить созданные листы" нет?
Анализирую ассортимент по 1,5 тысяч позиций, строю графики. Боюсь Эксель взорваться может, а руками лень удалять.

Так стоит галочка.
А листы размножаются.

Кстати, только у меня наблюдается картинка, когда я ставлю в настройках "тренд за последний период", то макрос начинает воспринимать каждый год, как новый тренд со сдвигом вниз или вверх? Т.е., как я понимаю, очистка от сезонности в данном случае работает несколько иначе, чем положено? Каждый год - новая волна?

Добавил 2 графика по одному товару.
У вас нет необходимых прав для просмотра вложений в этом сообщении.

Аватар пользователя
Роман Бодряков
Авторитет
Авторитет
Сообщений: 5253
Зарегистрирован: 19 апр 2004 03:00
Имя: Роман
Фамилия: Бодряков
Должность: Ген.Директор в кубе - наноолигарх
Откуда: Россия

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение Роман Бодряков » 03 фев 2014 13:38

Если удерживать Ctrl и выделить листы, то потом их можно удалить скопом
Есть такие решения, после принятия которых тараканы в голове аплодируют стоя! И просят повторить "НА БИС!!!"
Образование круче не у того, кто больше Знает, а у того, кто хоть что-то умеет.

SAV
Профессионал
Профессионал
Сообщений: 372
Зарегистрирован: 14 июн 2011 18:56
Имя: Андрей
Фамилия: МММ
Должность: Менеджер

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение SAV » 03 фев 2014 14:09

Роман Бодряков писал(а):Если удерживать Ctrl и выделить листы, то потом их можно удалить скопом

Получилось, спасибо.

Но по трендам вопрос остался.

Аватар пользователя
Роман Бодряков
Авторитет
Авторитет
Сообщений: 5253
Зарегистрирован: 19 апр 2004 03:00
Имя: Роман
Фамилия: Бодряков
Должность: Ген.Директор в кубе - наноолигарх
Откуда: Россия

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение Роман Бодряков » 03 фев 2014 20:52

Алгоритм с годовыми трендами это моя разработка.

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

Станлей в свое время меня сильно критиковал, но работает не плохо.

Что за алгоритм у Алексея я не знаю. надо статью с описанием на его сайте смотреть.
Есть такие решения, после принятия которых тараканы в голове аплодируют стоя! И просят повторить "НА БИС!!!"
Образование круче не у того, кто больше Знает, а у того, кто хоть что-то умеет.

Аватар пользователя
ABaturin
Пользователь
Пользователь
Сообщений: 29
Зарегистрирован: 26 ноя 2013 13:58
Имя: Alexey
Фамилия: Baturin
Должность: Предприниматель

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение ABaturin » 03 фев 2014 21:08

SAV писал(а):Кстати, только у меня наблюдается картинка, когда я ставлю в настройках "тренд за последний период", то макрос начинает воспринимать каждый год, как новый тренд со сдвигом вниз или вверх? Т.е., как я понимаю, очистка от сезонности в данном случае работает несколько иначе, чем положено? Каждый год - новая волна?

Добавил 2 графика по одному товару.

Если в настройках выберите "тренд за последний период", то для каждого года строится отдельный тренд, но для прогноза берется тренд за последний год. Сезонность берется за весь период, относительно тренда для каждого года, т.е. сохранятся логика работы с трендом последнего года.
Возможно для некоторых рядов подойдет лучше логика тренд за последний период, а сезонность за весь период.
Добавлю её в новую версию. Спасибо!

Кстати, если вы строите графики, то листы не удаляются, т.к. данные на график выводятся из листов.

Аватар пользователя
ABaturin
Пользователь
Пользователь
Сообщений: 29
Зарегистрирован: 26 ноя 2013 13:58
Имя: Alexey
Фамилия: Baturin
Должность: Предприниматель

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение ABaturin » 03 фев 2014 21:36

market-1 писал(а):Поэтому получается, что достаточно точный прогноз по группе - это не достоинство наших алгоритмов, а просто нивелирование случайных факторов. В итоге "плюсы" и "минусы" прогноза компенсируются. Но ведь в реальных-то продажах не так! Если "минусы" в прогнозе мы еще можем компенсировать в случае возможности оперативного дозаказа товара, то вот "плюсы" неминуемо лягут избыточным запасом на склад.
Короче, что делать с такими нашими прогнозами на практике мне не очень понятно. Поэтому мы и ушли от поартикульного помесячного прогнозирования на период более 1 месяца.


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

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

В план доработок поставил модель, которая будет группировать продажи по группам, строить прогноз, а потом разносить его по позициям.

Илья, спасибо за участие. Очень ценная информация для размышления и дальнейшего развития!
Последний раз редактировалось ABaturin 03 фев 2014 21:40, всего редактировалось 1 раз.

Аватар пользователя
ABaturin
Пользователь
Пользователь
Сообщений: 29
Зарегистрирован: 26 ноя 2013 13:58
Имя: Alexey
Фамилия: Baturin
Должность: Предприниматель

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение ABaturin » 03 фев 2014 21:38

Роман Бодряков писал(а):Алгоритм с годовыми трендами это моя разработка.

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

Станлей в свое время меня сильно критиковал, но работает не плохо.

Что за алгоритм у Алексея я не знаю. надо статью с описанием на его сайте смотреть.


Да, модель и мне пару лет назад подсказал, Роман! Не уверен, что он имел ввиду, то что получилось.:-)

Спасибо, Роман! :ya_hoo_oo:

Аватар пользователя
Роман Бодряков
Авторитет
Авторитет
Сообщений: 5253
Зарегистрирован: 19 апр 2004 03:00
Имя: Роман
Фамилия: Бодряков
Должность: Ген.Директор в кубе - наноолигарх
Откуда: Россия

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение Роман Бодряков » 04 фев 2014 01:10

Не за что.

Алгоритм Ильи... Алгоритм Валерия Разгуляева. Описан, опубликован и имя присвоено в явочном порядке.

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

Это все экспертные заморочки. У меня на моих данных работает, значит это панацея.
1. Не панацея
2. Простота, чем определяется, уровнем владения екселем или ...
3. Если освоить нормальный прогнозный пакет и математику не найдется ли чего-то придуманного докторами наук и дающего при нажатии одной кнопки более обоснованный результат.

В прогнозировании важно не терять связь с физикой процесса.
Как только Вы перестали понимать, что за результат Вам дает математика... Либо разбирайтесь с математикой, либо возвращайтесь к модели, которую понимаете.
Иначе Вы не управляете процессом прогнозирования, а процесс управляет Вами.
Это не правильно.
100 плюс-минус 20 лучше, чем 120 АПАТАМУЧТО.

И никогда не говорите, "мой алгоритм самый точный..."

Если бы существовал единственный точный алгоритм, то его давно бы включили в Excel в виде стандартной функции.

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

Хотите на весь следующий год? Вам потребуется модель с механизмом выделения сезонного тренда и тенденции. Тренд-Сезонные модели
Вариантов тьма. Начиная с того, что любой тренд можно "нарисовать от руки", Можно усреднить, можно сгладить скользящим средним, описать полиномом, косинусоидой и т.п.
Есть более формализованный и проверенный практикой алгоритм выделения трендов - CENSUSII. Его американское бюро статистики использует для прогнозирования макроэкономических показателей. Они выделяют три тенденции в данных.
Эти алгоритмы исходят из того что в данных есть определенное количество трендов. Они не всегда простые, но их легко осознать умом и понять их физический смысл.

Есть алгоритмы, которые сами ищут зависимости в данных, а потом используют самые значимые при построении прогноза
ARIMA, Муравей, Нейронные сети и т.п.
Для этих алгоритмов требуется достаточно длинный ряд данных для обучения (достоверного выявления зависимостей)
Осознать физический смысл результатов прогнозирования по этим алгоритмам очень непросто...
При этом механизм их работы простой и очень логичный. Объем простых вычислений только большой. Зато результаты по точности самые высокие.
Есть такие решения, после принятия которых тараканы в голове аплодируют стоя! И просят повторить "НА БИС!!!"
Образование круче не у того, кто больше Знает, а у того, кто хоть что-то умеет.

Аватар пользователя
Роман Бодряков
Авторитет
Авторитет
Сообщений: 5253
Зарегистрирован: 19 апр 2004 03:00
Имя: Роман
Фамилия: Бодряков
Должность: Ген.Директор в кубе - наноолигарх
Откуда: Россия

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение Роман Бодряков » 04 фев 2014 10:31

Алексей, идея для расширения функциональности
Для игрушек с тренд-сезонными моделями
1. Выделение трендов. Линейных, криволинейных по годам, по каким-то из годов
2. Выделение сезонности. Усреднение, взвешенное усреднение, с отбрасыванием отдельных значений, медиана и т.п.
Есть такие решения, после принятия которых тараканы в голове аплодируют стоя! И просят повторить "НА БИС!!!"
Образование круче не у того, кто больше Знает, а у того, кто хоть что-то умеет.

SAV
Профессионал
Профессионал
Сообщений: 372
Зарегистрирован: 14 июн 2011 18:56
Имя: Андрей
Фамилия: МММ
Должность: Менеджер

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение SAV » 04 фев 2014 13:27

Роман Бодряков писал(а):Алгоритм с годовыми трендами это моя разработка.

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

Станлей в свое время меня сильно критиковал, но работает не плохо.

Что за алгоритм у Алексея я не знаю. надо статью с описанием на его сайте смотреть.


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

Разве нет?

SAV
Профессионал
Профессионал
Сообщений: 372
Зарегистрирован: 14 июн 2011 18:56
Имя: Андрей
Фамилия: МММ
Должность: Менеджер

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение SAV » 04 фев 2014 13:33

ABaturin писал(а):
SAV писал(а):Кстати, только у меня наблюдается картинка, когда я ставлю в настройках "тренд за последний период", то макрос начинает воспринимать каждый год, как новый тренд со сдвигом вниз или вверх? Т.е., как я понимаю, очистка от сезонности в данном случае работает несколько иначе, чем положено? Каждый год - новая волна?

Добавил 2 графика по одному товару.

Если в настройках выберите "тренд за последний период", то для каждого года строится отдельный тренд, но для прогноза берется тренд за последний год. Сезонность берется за весь период, относительно тренда для каждого года, т.е. сохранятся логика работы с трендом последнего года.
Возможно для некоторых рядов подойдет лучше логика тренд за последний период, а сезонность за весь период.
Добавлю её в новую версию. Спасибо!

Кстати, если вы строите графики, то листы не удаляются, т.к. данные на график выводятся из листов.


Мне нравится такой вариант.
Но нельзя строить прогноз на последнем тренде.
Необходимо формировать новый.
Причем здесь я бы подключил пользователя. Необходимо получить от него хотя бы намек на то, ожидает ли он, что следующий год будет похож на предыдущий, лучше или хуже?
И не с конца прошлогоднего рассчитывать данные (с вершины), а тренд также должен опуститься вниз и уже оттуда....
Хотя по сути это смесь тренда с сезонностью.
Нужно определение придумать.
Трезонность?
Сезонтра?
:ya_hoo_oo:

Аватар пользователя
market-1
Гуру
Гуру
Сообщений: 777
Зарегистрирован: 02 май 2010 09:37
Имя: Илья
Фамилия: Константинов
Должность: Директор по маркетингу
Откуда: Москва

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение market-1 » 04 фев 2014 13:46

SAV писал(а):Но почему прогноз строится на тренде последнего года???
По идее должне быть построен новый тренд, со своим смещением, со соим углом и на нем уже должен быть построен прогноз.

Разве нет?

В распоряжении программы есть только данные предыдущих продаж. Все, больше у нее ничего нет.
Тренд на будущее она может строить только исходя из прошлых данных. А это метод экстраполяции, то есть ПРОДОЛЖЕНИЯ СУЩЕСТВУЮЩЕГО тренда на некий незначительный промежуток времени вперед. А уж как вы будете определять существующий тренд - за последний год, за 2,3 - 5 лет, линейно, полиномиально или как-то еще - это вопрос многогранный.

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

Аватар пользователя
Роман Бодряков
Авторитет
Авторитет
Сообщений: 5253
Зарегистрирован: 19 апр 2004 03:00
Имя: Роман
Фамилия: Бодряков
Должность: Ген.Директор в кубе - наноолигарх
Откуда: Россия

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение Роман Бодряков » 04 фев 2014 14:36

SAV писал(а):Но почему прогноз строится на тренде последнего года???
По идее должне быть построен новый тренд, со своим смещением, со соим углом и на нем уже должен быть построен прогноз.

Разве нет?


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

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

То есть можно взять прогноз Ильи и добавить эту фишку на последнем году. В следующем месяце, сам прогноз не пересчитывать, а только тренд за 12 месяцев скорректировать.
И так до тех пор, пока не пересчитается весь прогноз. В его случае, это раз в три месяца.
Есть такие решения, после принятия которых тараканы в голове аплодируют стоя! И просят повторить "НА БИС!!!"
Образование круче не у того, кто больше Знает, а у того, кто хоть что-то умеет.

Аватар пользователя
market-1
Гуру
Гуру
Сообщений: 777
Зарегистрирован: 02 май 2010 09:37
Имя: Илья
Фамилия: Константинов
Должность: Директор по маркетингу
Откуда: Москва

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение market-1 » 04 фев 2014 14:48

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

Вот в этом-то и вопрос: может ли? Потому что формально все алгоритмы аппроксимации на прошлых периодах дают вполне приличную точность, на совокупностях товаров - тоже. А как только заглянуть вперед по конкретному товару, то отклонения прогноза от реальных продаж при таком подробном поартикульном помесячном прогнозе в вашем самом точном варианте составляет в среднем 80% (от -26% до +54%). То есть при прогнозе продаж 100 штук мы можем ожидать реальные продажи от 64 до 154 штук с вероятностью процентов 80. А в оставшихся 20% случаев отклонения будут еще больше и достигают порядка (10 раз!). В моем прогнозе эти отклонения еще больше.

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

Вопрос: реально ли на основании такого прогноза принимать хоть какие-то управленческие решения по каждому отдельному артикулу продукции в перспективе нескольких месяцев? На мой взгляд, нет. А это значит, что мы явно выходим за пределы применимости модели. И не с алгоритмами нужно играться, а с методами, которые исключают случайные факторы:
1.Укрупнение групп товаров;
2. Внедрение в автоматические алгоритмы необходимости введения поправочных экспертных коэффициентов, учитывающих специфику рынка и т.п. (при этом одновременно с укрупнением групп).
3. Есть наверняка еще масса методов.

Потому что сейчас обсуждение подобных алгоритмов напоминает ломание копий из-за того, сколько времени понадобится конкретному человеку на то, чтобы пешком обойти землю по экватору 100 раз - 250 или 300 лет. Какая разница? Он все равно раньше или умрет от старости или утонет в океане.

Аватар пользователя
Роман Бодряков
Авторитет
Авторитет
Сообщений: 5253
Зарегистрирован: 19 апр 2004 03:00
Имя: Роман
Фамилия: Бодряков
Должность: Ген.Директор в кубе - наноолигарх
Откуда: Россия

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение Роман Бодряков » 04 фев 2014 19:42

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

Теперь переходим к следующему моему предложению?
Обсудить организацию бизнес-процесса "прогнозирование"

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

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

Да и маркетологи с закупщиками многие интуичить умеют лучше, чем считать. Такова жизнь.
ТЫРК и прогноз готов. Умножил на два и маркетинговый план готов, умножил на два и бюджет на маркетинговые мероприятия получился.
Есть такие решения, после принятия которых тараканы в голове аплодируют стоя! И просят повторить "НА БИС!!!"
Образование круче не у того, кто больше Знает, а у того, кто хоть что-то умеет.

SAV
Профессионал
Профессионал
Сообщений: 372
Зарегистрирован: 14 июн 2011 18:56
Имя: Андрей
Фамилия: МММ
Должность: Менеджер

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение SAV » 05 фев 2014 10:11

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

Первый вариант дает явно завышенные прогнозы.
У вас нет необходимых прав для просмотра вложений в этом сообщении.

Аватар пользователя
Роман Бодряков
Авторитет
Авторитет
Сообщений: 5253
Зарегистрирован: 19 апр 2004 03:00
Имя: Роман
Фамилия: Бодряков
Должность: Ген.Директор в кубе - наноолигарх
Откуда: Россия

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение Роман Бодряков » 05 фев 2014 10:25

Спасибо Андрей.
Это и есть сбой в логике моей модели. Ты его очень показательно представил.
1. Когда выделяем линейный тренд из факта, то убираем всю линейность и ту что имеет отношение к годовым факторам и ту, что имеет отношение к внутригодовым или сезонным факторам.
2. Продолжая тренд по остатку последнего года без сезонности... мы транслируем в будущее не только тенденцию прошлого года, но и внутри годовую тенденцию которая является частью сезонности.

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

Это легко лечится.
Есть такие решения, после принятия которых тараканы в голове аплодируют стоя! И просят повторить "НА БИС!!!"
Образование круче не у того, кто больше Знает, а у того, кто хоть что-то умеет.

Аватар пользователя
Роман Бодряков
Авторитет
Авторитет
Сообщений: 5253
Зарегистрирован: 19 апр 2004 03:00
Имя: Роман
Фамилия: Бодряков
Должность: Ген.Директор в кубе - наноолигарх
Откуда: Россия

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение Роман Бодряков » 05 фев 2014 12:29

Вот и я.

Чуть поправил алгоритм и сделал файлик на котором все три прогноза видно.

Там слева вверху на листе бегунок. Его тыркайте и товар будет меняться.

Продажи по месяцам Данный Ильи с корректировкой.xlsx
У вас нет необходимых прав для просмотра вложений в этом сообщении.
Есть такие решения, после принятия которых тараканы в голове аплодируют стоя! И просят повторить "НА БИС!!!"
Образование круче не у того, кто больше Знает, а у того, кто хоть что-то умеет.

Аватар пользователя
Роман Бодряков
Авторитет
Авторитет
Сообщений: 5253
Зарегистрирован: 19 апр 2004 03:00
Имя: Роман
Фамилия: Бодряков
Должность: Ген.Директор в кубе - наноолигарх
Откуда: Россия

Re: Прогнозирование в MS Excel. Forecast4AC PRO. Рекомендую!

Сообщение Роман Бодряков » 06 фев 2014 12:40

Обратите внимание как построен сам файл.

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

Такой подход дает возможность на листе или листах...
- построить любое количество моделей
- вносить в них корректировки
- смотреть как изменения отразятся на разных товарах
- смотреть поведение не только результата, но и промежуточных рядов
- сравнивать прогнозы по разным моделям
= все что Ваша фантазия подскажет и без каких либо серьезных ограничений со стороны Екселя.

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

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

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

А если исходные данные получать не в виде этих "дебильных" широких таблиц...
То появится возможность использовать сводную таблицу для выборки данных. Это еще удобнее, нагляднее, быстрее и проще.

Знай и люби MS Excel!!!
Тот редкий случай, когда любовь окупается...


Вот пример работы облегченного CENSUS на данных Ильи.
CENSUSДля тестирования.xlsx

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

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

Можно воспользоваться Forecast4AC PRO и просто нажимать кнопку...
У вас нет необходимых прав для просмотра вложений в этом сообщении.
Есть такие решения, после принятия которых тараканы в голове аплодируют стоя! И просят повторить "НА БИС!!!"
Образование круче не у того, кто больше Знает, а у того, кто хоть что-то умеет.


Вернуться в «Программы для закупщиков»

Кто сейчас на форуме

Количество пользователей, которые сейчас просматривают этот форум: CommonCrawl [Bot] и 1 гость