Проблема моделирования: недели и месяца

Проблема моделирвоания недели и месяца

Проблема моделирования: недели и месяца

Частая проблема моделирования – преобразование данных понедельного учета в показатели соответствующего отчётного месяца. При кажущейся простоте, очень часто не очевидно каким образом преобразовать данные из недель в месяца. Какой же наиболее корректный способ сделать это?

Условие задачи

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

Каков наилучший способ решить эту задачу?

Немного теории управленческого учета

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

Существует 3 основных подхода как перевести недельные показатели в месячные:

  • Новый финансовый месяц начинается с недели, в которой есть первое число этого месяца. Другими словами, если на любой день анализируемой недели приходится первое число месяца, то продажи всей недели относятся к новому месяцу
  • Финансовый месяц для недели определяется по тому к какому месяцу относится большая часть недели. Например, если первое число приходится на четверг, то неделя относится к новому месяцу (4 дня из 7 будут нового месяца).
  • Неделя распределяется пропорционально количеству дней каждого месяца. Например, если первое число приходится на четверг, то 3/7 будут отнесены к предыдущему месяцу и 4/7 к новому.

Существуют и другие методы (как например в рознице встречается схема 5 / 4 / 4 недель в квартал), эти методы могут требовать отдельных корректировок на актуальное количество недель в году.

Для целей нашей статьи мы будем рассматривать только три метода представленных выше.

В первую очередь нужно создать необходимую базу для расчетов. Под базой я понимаю создание двух строк где будет первый и последний день недели. Можно конечно же сделать дополнительные строки с человекочитаемыми названиями недель, например «25 неделя», «1 нед. Март» и прочее. Под словом создать я понимаю сделать расчеты таким образом, чтобы эти строки рассчитались в Excel формулами. Ни в коем случае не нужно делать это в ручную.

С основными предпосылками расчета разобрались. Поехали!

Метод 1. Новый финансовый месяц начинается с недели, в которой есть первое число этого месяца

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

=МЕСЯЦ( [Конец недели])

С первым способом разобрались! Поехали дальше 😉

Метод 2. Финансовый месяц для недели определяется по тому к какому месяцу относится большая часть недели

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

  1. Сравниваем месяц начала и окончания недели. Для этого с использованием функции МЕСЯЦ() получаем номер месяца одной и второй даты. Сравниваем их через оператор неравно «<>». При этом лучше не использовать оператор больше или меньше, потому что он не сработает в начале года.
  2. Если месяца равны, то выводим месяц начала или конца недели. В данном случае не важно.
  3. Если месяца не равны, то вытаскиваем номер дня функцией ДЕНЬ(). Получаем число от 1 до 6. Делим полученное число на 7 дней в неделе и получаем 1/7, 2/7 ... 6/7. Если получается брось меньше 0,5, то выводим месяц начала недели. В противном случае выводим месяц окончания недели.

Результаты расчетов по первому и второму способам можно увидеть ниже:

Проблема моделирования недели и месяца 1

Проблема моделирования недели и месяца 1

Как видите очень часто эти способы могут совпадать, но в какой-то момент они все равно разойдутся и будет существенная разница.

Метод 3. Неделя распределяется пропорционально количеству дней каждого месяца

Механника решения задачи второго месяца может оказаться более сложной в плане реализации. Возможно даже применение "шахматки". Опытные модельеры используют её для расчета амортизации и капитальных затрат. Выглядеть это решение может примерно следующим образом:

Проблема моделирования недели и месяца рисунок 2

Проблема моделирования недели и месяца рисунок 2

Для третьего способа был расчитан коэфициент какая доля недели приходится на месяц, которым заканчивается текущаяя неделя. Для этого в случае если месяц дты начала и окончания недели не равны, число последнего дня недели делится на 7. Для февраля это 4/7 соответственно. После чего данные выручки перемножаются на полученый коэфициент в соответствии с месяцем, которому данный коэфициент принадлежит. В случае с последней неделей на которую попадает месяц это единица минус полученный коэфициент.

Результаты применения трех методов

Результаты применения всех трёх методов могут сильно отличаться. Для того, чтобы проиллюстрировать на сколько могут разниться результаты применения всех трёх методов я экстраполировал полученные алгоритмы на весь год и сгенерировал генератором случайных чисел недельные значения в диапазоне от 90 до 120. Как видим девиации очень существенные.

Ну что же, подведём итоги:

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

Спасибо за прочтение статьи. Оставляйте свои комментарии ниже. Очень важно ваше мнеине.

No Comments

Post a Reply

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.

s2Member®