Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1 2 След.
RSS
Формула расчетного остатка на начало месяца
 
Добрый день! Есть  таблица  с расчетом  товара  в разрезе  каждой позиции ,  есть  формула РАСЧЕТНОГО ОСТАТКА НА НА НАЧ МЕСЯЦА ( СТОЛБЕЦ -Х И  СТОЛБЕЦ  АТ). Формула считает некорректно расчетный остаток на нач.  Февраля, и на начало марта месяца.Как  можно изменить или переписать формулу так,чтоб рассчетный  остаток  считался правильно,а  именно   с учетом разницы между планом продаж и фактическими продажами .Факт  прихода за месяц можна не учитывать. Например  в  ячейке Х3  -рассчетный  остток  равен о, но это не правильно. Так как  остаток  на конец месяца был 17 шт..Исходя из этого расчетный остаток  на нач  февраля  должен быть около  17 шт., но и не больше 17.Как  можно переписать формулу так чтоб  правильно считался  расч.остаток .Может прописать  условие   с учетом  разницы между  планом  продаж и  фактическими продажами, например: если план продаж равен факту продаж, то  одно условие  и    если  нет фактических продаж ,то  равно план продаж.Подскажите  пож.! Спасибо большое!
Изменено: bhelen_1988 - 26 Мар 2015 17:39:27
 
А словами логику рассчёта можно попросить? Обычно используют такое понятие как норматив товарного запаса ...
 
Переходящий остаток на начало нового месяца
Данные расчеты вводились другим аналитиком до меня
 
Правда не понимаю- а как учитывать разницу между планом и фактом продаж? Именно алгоритма не понимаю формулу то наваять 3 секунды! (Но уже завтра!)
 
Я  тоже не понимаю.!)  мне поставили задание, что  был корректный переходящий остаток , а  как   - не знаю.Потому  и  обратилась за помощью на форум!  
 
Это чисто случайное совпадение как я понял. Просто так совпало что план продаж минус факт продаж вышел на факт. остаток (21-4=17). По сути формула верная.
Ошибка аналитика в следующем: Если остаток по факту равен 17 а планируем продать 21 то должны запланировать также и приход (недостающих 4 штуки) а план прихода равен нулю (также как и факт), если же смотреть на расчетный остаток 23 штуки, то запланировать должны не менее 6 штук! (поставьте в J3 =6, и у вас расчетный остаток станет 6 [ФО (17) + разница между плановым и факт. приходом (6-0) - разница между плановым и факт. расходом (21-4) отсюда 17+(6-0)-(21-4)]). Поставьте ради интереса план продаж меньше и у вас расчетный остаток возрастет. Дальше файл не рассматривал, потому как считаю что формула все же верная.
PS: А пришли вы все-таки на форум по экселю а не по планированию, поэтому понятно что желающих помочь не так много, если бы вы указали в каком месте вам нужно что сложить что вычесть и что получить, вам бы сразу помогли. А так, Вы говорите что формула некорректно считает, но при этом не знаете как она должна считать, откуда тогда уверенность в ее некорректном счете?
Изменено: GeorgeDark - 27 Мар 2015 07:41:51
 
Добрый день!Спасибо  большое!Но как  формула может считать правильно, как может быть расчетный остаток быть на начало  февраля месяца ноль ( ячейка Х3), если фактический  остаток  на кон января 17 шт. Остаток на начало нового месяца должен быть не меньше  17. Ведь на конец января  на остатках  оставалось 17 шт ,а не о!Спасибо!
 
Я спрашиваю как изменить формулу  расч .остатка на начало февраля ( ячейка Х3), чтобы  считало корректно !Переходящий остаток на начало февраля не может быть ноль, если на конец января фактический остаток был 17.  Также в формуле должны  учитываться  ожидаемые приходы
 
Цитата
bhelen_1988 написал:
остаток  на кон января 17 шт
Это правда? Потому что по логике формулы это остаток на начало января.
 
Да.Остаток на дату, столбец  Е -это остаток на кон. января и аналогично столбец АА-остаток на кон   февраля, на конец  марта нет данных -так как март пока не закончился, таблица обновляется  в конце каждой недели.Может  подскажите другой форум  ,где  можно спросить, если тут  не могут  подсказать!Спасибо!
Изменено: bhelen_1988 - 27 Мар 2015 12:42:46
 
Хотелось бы понять, что такое Расчетный остаток. Остаток, который должен был получиться, при условии... каком?
Или это всего лишь плановый остаток на начало/конец месяца?

Сейчас логика формулы следующая:
Если Факт.Остаток на конец предыдущего месяца = 0
Расчетный остаток = Расчетный.остаток на пред.месяц +ожидаемый приход пред. месяца - план продаж пред. месяца
то есть мы берем некое число "сколько должно было быть на начало месяца", добавляем приходы, убираем плановые продажи, и смотрим, сколько же получится на начало следующего месяца. Некоторая плановая величина.
Если Факт.Остаток на конец предыдущего месяца <> 0
Расчетный остаток = Факт.Остаток на конец + (плановый приход - фактический приход) - (план продаж - факт продаж)
Если какие-то остатки на начало месяца все же есть, то формула вдруг резко меняется: к имеющемуся остатку прибавляем почему-то только разницу между плановым и фактическим приходом, и вычитаем превышение плана продаж над фактом.

В чем логика резкого изменения формулы?
F1 творит чудеса
 
Да.Плановый  остаток на нач месяца
 
Ну тогда понятно почему расчетный остаток выдает ноль, смотрите если на конец осталось 17, не было прихода (и не ожидалось) был расход 4 то остаток на начало января был 17+4=21 шт, а в плане продаж как раз и стоит 21, т.е. на начало января планировали продать все под ноль и при этом не ожидалось поставок. Есть еще такие столбцы как "необх. запас" и "заказать" там странные цифры стоят (с половинками), можно только их докрутить в формулу по логике если планируем продать все под ноль, и обеспечить необх. запас, то нужно заказать кол-во этого необх. запаса (хотя цифры там стоят нелогичные), тогда и расчетный остаток будет высчитан на сумму необх. заказа.
 
Мое мнение, всё это от лукавого, какие-то невероятные и непонятные измышлизмы.
Если это плановый остаток, то определитесь, он плановый потому что должен был быть таким исходя из планов предыдущих месяцев (учитываем тогда не фактические данные, а только плановые), или исходя из планов будущего месяца (можно учитывать фактический остаток)
Плановый остаток на нач. месяца, отталкиваясь от плана предыдущего месяца:
Плановый остаток на начало предыдущего + плановый приход предыдущего месяца - план продаж предыдущего месяца.
Плановый остаток на нач. месяца, отталкиваясь от плана будущего (этого же) месяца и фактического остатка:
Фактический остаток на конец предыдущего + плановый приход будущего месяца - план продаж будущего месяца.
вся формула в X3 сводится либо к B3-C3+J3 для расчета планового остатка исходя из планов по остаткам, приходам и продажам предыдущего периода, либо к E3-Y3+AF3 для второго случая: факт. остаток +/- потребность на будущий месяц
F1 творит чудеса
 
Можно ли прописать, изменить  формулу условия так чтоб :
1) если факт равен пустому значению, пустой ячейке, то учитываем план  продаж
2) если факт продаж равен какому-то значению , от  0  и больше,то ссылается на факт продаж
Изменено: bhelen_1988 - 27 Мар 2015 13:26:48
 
Ну это легко:
Код
=ЕСЛИ(ИЛИ(E3="";E3<=0);ЕСЛИ(B3<0;0;B3)+J3-C3;E3+T3-F3)
Если по факту остатки меньше или равны нулю или пустые то расчет ведем по плановым показателям, в противном случае по фактическим.
PS: посмотрел дальше формулу на февраль, там все тоже рассчитано верно, отсаток на начало по факту 17, планируем продать 7 шт, планируемый остаток на конец = 10, именно такое значение и выдает формула в AT.
Изменено: GeorgeDark - 27 Мар 2015 13:26:47
 
странное пожелание, честно говоря, ну да ладно,
чуть изменил формулу GeorgeDark
Код
=ЕСЛИ(E3>0;E3-Y3+AF3;МАКС(B3;0)+J3-C3)


GeorgeDark, во второй части считаем остаток на конец-факт продаж+факт приход??? смысл?

Ой, запутали вы нас, bhelen_1988
Изменено: Максим Зеленский - 27 Мар 2015 13:34:37
F1 творит чудеса
 
Цитата
Максим Зеленский написал: конец-факт продаж+факт приход??? смысл?
Вообще я честно говоря не понял bhelen_1988, "если факт равен пустому значению то считаем план продаж" факт чего продаж или остатка? если продаж то фигня получится 17-21=-4. Если факт остатка, то еще куда ни шло 23-21=2. Поэтому просто проставил формулу: если по факту остатков нет то план остатков на начало + приход план - расход план. В противном случае факт остатка на конец + факт прихода - факт расхода (я не знаю для чего, подумал просто, что автор сам подставит в формулу нужные ему ссылки))).  
 
Цитата
GeorgeDark написал: В противном случае факт остатка на конец + факт прихода - факт расхода
и получим вообще непонятно что :)
F1 творит чудеса
 
((((Я  уже и сама  замучилась!
Еще добавила примеры  строка ,генератор 7.  Там  на начало месяца  было 0 генераторов, пришло 160 шт  из них мы продали по факту 13  шт- знач  переходящий остаток на начало февраля должен быть равен 147., а не  -20  как  считает по формуле.  Подставляю вашу формулу, тоже считает некорректно. Или на примере шлиф машины: на  кон января было 98  шт.  и переходящий остаток на февраль правильный 98 шт. ( 98  шт.   на кон. января  и  60  шт. всего продано за январь).в  феврале продано о штук,  приходов не было ,знач перходящий  расчетный остаток на нач марта  должно быть тоже 98 шт., а формула считает 8 .шт.- это не корректно
Как можно переписать формулу так чтоб считало корректно, но приэтом  не отнимало факт продаж   за месяц от  остатка на  конец месяца, как  в  ваших формулах.  
Спасибо!

Можно ли прописать, изменить  формулу условия так чтоб :
1) если факт равен пустому значению, пустой ячейке, то ВЫЧИТАЕМ   план  продаж  как  фактические продажи
2) если факт продаж равен какому-то значению , от  0  и больше,то ссылается на факт продаж
 
Цитата
bhelen_1988 написал: 1) если факт равен пустому значению, пустой ячейке
факт чего? продаж?
 
Да.Факт продаж.
 
Ну не знаю правильно ли это будет, но вот формула, как говориться просите помочь, помогаю )
остатки на начало в обоих случаях беру плановые, т.к. факт. остатка на начало нет (его конечно можно высчитать ост.на конец+расх-приход (все по факту)).
Код
=ЕСЛИ(ИЛИ(F3="";F3<=0);ЕСЛИ(B3<0;0;B3)+J3-C3;ЕСЛИ(B3<0;0;B3)+T3-F3)
Но хотелось бы еще раз напомнить, что формулы там считают правильно, просто неверно задается план продаж, посмотрите на ситуацию с генератором 3 по февралю месяцу остаток на начало (факт) 147 ожидаем поставок 0 планируем продать 150 неудивительно что при таких планах он расчетный остаток на конец выдает -3. Еще раз повторюсь что при таких раскладах нужно прикрутить графы необх.запас и заказ, или в заказ привязать формулу которая будет формировать его отталкиваясь от необх. запаса и расч. остатка, т.е. чтобы обеспечить запас в 324 при выполнении плановых показателей, то необходимо от 324 отнять кол-во по расч. остатку 324-(-3) = 327 штук нужно заказать.
 
Всё, кажется, понял... Сейчас накидаю
F1 творит чудеса
 
Цитата
bhelen_1988 написал: таблица обновляется  в конце каждой недели
В этом вся фишка.

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

Итого, имеем, что если март уже закончился, то на 1 апреля расчетный остаток должен быть =
Формула 1 Расчетный остаток на начало Марта + фактические приходы за МАРТ - фактические расходы за МАРТ

Если март еще не закончился, то чтобы посчитать расчетный остаток на 1 апреля, надо:
Формула 2 Расчетный остаток на начало МАРТА + факт.приход за МАРТ - факт расход за МАРТ

2-я формула почти идентичная, но она не учитывает, что март еще не закончился, впереди еще продажи. Какие они будут? План продаж - Факт продаж = сколько еще должны продать.
И также нужно учесть, что товар, может быть, еще довезут. План приходов - Факт приходов = сколько осталось привезти.

Итого, расчетный остаток на 1 апреля:
Расчетный остаток на начало МАРТА + факт.приход за МАРТ - факт расход за МАРТ + (План приходов на МАРТ - факт.приход за МАРТ) - (План продаж на МАРТ - факт расход за МАРТ)
Из нее получается Формула 3 = Расчетный остаток на начало МАРТА + План приходов на МАРТ - План продаж на МАРТ.

Коряво очень, конечно, потому что дата снятия отчета не известна, и рассчитывать, что недовыполненный на 50% план может быть реализован полностью в последние дни - наивно. Поэтому будут всевозможные глюки.
Поэтому итоговая формула должна быть такая:
ЕСЛИ(месяц еще идет?;Формула 3;Формула 1)
Где-то в таблице должна быть дата формирования отчета. Если эта дата раньше конца анализируемого месяца, то расчет по плановым показателям, если месяц уже закончился - по фактическим.
Где дата?
F1 творит чудеса
 
Отчет  по текущему месяцу марту формируется каждую пятницу и  закрывается  месяц  в последний день месяца , как  пройдут все отгрузки.
 
но она не учитывает, что март еще не закончился, впереди еще продажи.
Какие они будут? План продаж - Факт продаж = сколько еще должны продать.

И также нужно учесть, что товар, может быть, еще довезут. План приходов - Факт приходов = сколько осталось привезти.
  Да так и есть!
 
Выгрузка товара может быть  и в последний день  месяца
 
Цитата
Максим Зеленский написал:
Где дата?
Где в файле расположена дата выгрузки отчета? Хотя бы - указание текущего месяца? Если этих данных нет, то посчитать как написал выше - не выйдет.
Цитата
bhelen_1988 написал:
закрывается  месяц  в последний день месяца , как  пройдут все отгрузки.
Почему на начало января было 23 шт., привезли 0, продали 4, остаток на дату по отчету = 17? Должен быть 23-4=19. Пока не объясните, в файле будет каша, и сделать что-то адекватное трудно.

Еще вопрос. Например, если на 27 марта мы считаем расчетный остаток на начало апреля еще с учетом плановых данных марта, то на 3 апреля нужно будет пересчитать по фактическим данным за март. Так ли это?

ЗЫ Не цитируйте так много. Ваших слов в итоге не видно и понять вообще трудно
F1 творит чудеса
 
Ну это примерно то, о чем говорил Максим, в ячейке А1 стоит дата отчета, в А10 я прописал формулу которая на данный момент ссылается на ячейку Е1 (в которой написан месяц). Если в Е1 написать Апрель, то в А10 напишется "Формула3".
PS: создал в диспетчере имен именной диапазон "Месяцы".
Изменено: GeorgeDark - 27 Мар 2015 17:22:02
Страницы: 1 2 След.
Читают тему (гостей: 1)