Эксперимент с прогнозированием среднесписочной численности

Источником для написания данной статьи послужили вот эти публикации:

www.cfin.ru: «Алгоритм прогнозирования объема продаж в MS Excel» С.А. Кошечкина (опубликовано в 2001 г.) и «Доработка алгоритма прогнозирования объема продаж» А.В. Бондаренко (опубликовано в 2004 г.). А также развернутая инструкция, дописанная на их основе http://www.cfin.ru/finanalysis/math/add_to_kosh-bond.shtml и позволяющая, даже не очень подкованному в области математики  человеку строить  вполне приемлемый прогноз, имеющий определенное научное обоснование.

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

Начнем с начала: у нас есть исходные данные по среднесписочной численности цеха, примем порядковый номер за номер месяца, начиная с января:

Период	Факт
1	84
2	84
3	86
4	83
5	77
6	76
7	71
8	67
9	62
10	63
11	62
12	61
13	61
14	61
15	75
16	76
17	77

Для составления прогноза воспользуемся синусоидным трендом из статьи с периодом полтора год:

Y18 = A18+B18 х sin х (2π/18 х (X18 –C18))

A18 — смещение синусоиды относительно нуля,

 B18 — амплитуда синусоиды,

C 18 имеет смысл начальной фазы колебания,

индекс 18 указывает на период планирования.

Теперь заходим в Эксель в меню Файл - Надстройки ставим, галки напротив  «Пакета анализа» и «Поиска решений», нажимаем на «ок», устанавливаем надстройки.

После этого нужно определить коэффициенты A18, B18, C18 - почему 18, да потому что я решил прогноз построить на 1,5 года, или по-другому на 18 месяцев.

Для начала на рабочем листе, обозвав его оригинальным названием «Шаг 1» (не стыжусь плагиата), в ячейках А2:В18 набиваем номера периодов и данные по численности. После этого вводим строго по инструкции начальные значения для наших коэффициентов.

Значение A18 должно быть близко к среднему значению за период, можно взять что-то около 77,76 и вбить в ячейку G2. B18 — это амплитуда колебаний, ее можно посмотрев период изломов по графику введенного факта, определим этот показатель как полгода или 1/3 от периода планирования, добавим данное значение в ячейку Н2. С18  начальная стадия колебаний, можно просто поставить минимальное значение в ячейку I2. Ну и рядом с ней вводим период планирования в 18 меcяцев – ячейка J2.

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

$G$2+$H$2*SIN(2*ПИ()*(A2-$I$2)/$J$2)

И благополучно протягиваем ее вниз на все периоды планирования, в колонке рядом пишем формулу отклонений факт – прогноз (думаю тут сложностей не возникнет), в в следующей колонке квадрат отклонений  =D2^2.

В ячейке Е25 считаем сумму квадратов отклонений, просто вводим = СУММ(E2:E24). Теперь настал момент истины, используем поиск решения, изменяя ячейки $G$2:$I$2 , мы стремимся к минимизации суммы квадратов отклонений в ячейке Е25. Вот как это должно выглядеть:

А теперь чистый результат после поиска решения:

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

A₁₈=73, B₁₈=87,6 и C₁₂=12,38

Далее в ячейках считаем коэффициент детерминации по формуле =КВПИРСОН(B2:B20;C2:C20) в F25, в квадрате H25 - распределения Стьюдента, не помню что это значит в голове всплывают основы теории вероятности, но внятно объяснить значение не могу: =СТЬЮДРАСПОБР(0,05;G25-1). В общем дальше все как в статье, я думаю эти коэффициенты больше нужны для технического обоснования нашей гипотезы, а не для практических целей, поэтому эту часть опущу.

Создаем второй лист, копируем туда все формулы с первого листа, на место фактических данных копируем отклонения с листа Шаг1, повторяем последовательность действий, которые уже проводили, только период колебаний ставим 3 месяца. Получим:

Проделываем аналогичные действия еще на одном листе обзываем лист "Шаг 3", но период колебаний меняем на 4, а отклонения берем с "Шага 2":

Автор предлагает делать несколько таких итераций, но мое терпение здесь начало заканчиваться и я решил сделать предпоследний лист с линейным трендом отклонений. Для этого скопировал все как и до этого, но вместо формулы синусоиды прописал функцию линейного уравнения, чтобы учесть небольшую тенденцию к росту (будет ли он - вопрос). Итак наше уравнение:  Y=A+BX, для него нужно найти коэффициент пересечения с осью Y или постоянный член - А, и коэффициент который показывает влияние на наклон линии  - это B. Для определения первого введем в ячейку  В2 : =ОТРЕЗОК(B2:B19;A2:A19), для определения углового коэффициента В введем в ячейку H2 : =НАКЛОН(B2:B19;A2:A19). 

Получили А=0,10

 В= -0,01007, упс.... - а вот и сюрприз - у нас получилась тенденция к снижению численности, но это не повод останавливаться.

Теперь в колонку прогноз ставим формулу: =$G$2+$H$2*A2 и протягиваем ее вниз. Все - это итоговая часть общей кривой прогноза: 

Самый непонятный для меня шаг это высисчление последней сезонной составляющей, но для чистоты эксперимента я и ее сделал, опять копируем лист обзываем "ШАГ 5" и делим отклонения с предыдущего листа на 2. В остальном все то же самое, вот что получилось:

И вот он, последний момент, складываем колонку В и С со всех предыдущих листов, и получаем наш плановый показатель:

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

19	86
20	87
21	81
22	81
23	82
24	75
25	68
26	68
27	63
28	62
29	61
30	58
31	63
32	66
33	68
34	74
35	82
36	81

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