Управленческая таблица в Excel

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

Задача управленческой бухгалтерии — установление норм затрат материалов, энергии, рабочего времени, труда, зарплаты и всех других производственных расходов. Оперативно выявленные отклонения на счетах управленческого учета обеспечивает анализ и своевременное решение различных задач.

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

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

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

Первая группа: счета 20 — 29 — для группировки расходов по статьям в разрезе центров затрат, мест возникновения затрат, носителей затрат.

Вторая группа: счета 30 — 36 — для группировки расходов по элементам затрат.

Первая группа счетов определяет ведение управленческого учета, вторая — финансового. Взаимосвязь между ними может осуществляться с помощью специальных счетов-экранов. В частности, это может быть счет 37 «Отражение общих затрат».

Нормативный метод калькулирования не исключает учета фактических затрат, отражение которых будет производиться на вышеназванных счетах. Но при этом возникает потребность в аналитическом учете затрат на производство (по объектам калькулирования или другим признакам локализации) и по нормативной стоимости. Для этого можно использовать специальный счет, например счет 38 «Выпуск продукции (работ, услуг) по нормативной стоимости», для отражения в учете готовой продукции по нормативной (плановой) себестоимости в течение отчетного периода. На этом счете формируется информация о выпущенной из производства продукции в двух оценках: по дебету — фактическая производственная себестоимость, по кредиту — нормативная (плановая) себестоимость.

На отчетную дату в результате сопоставления дебетового и кредитового оборотов по счету 38 «Выпуск продукции (работ, услуг) по нормативной стоимости» выявляется отклонение фактической производственной себестоимости от нормативной (плановой), т.е. экономия или перерасход. Отклонение списывается со счета 38 в дебет счета 90 «Продажи» дополнительной или сторнировочной записью. Счет 38 закрывается, и сальдо на отчетную дату не имеет.

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

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

счет 30 «Материальные затраты»;

счет 31 «Затраты на оплату труда»;

счет 32 «Отчисления на социальные нужды»;

счет 33 «Амортизация»;

счет 34 — 36 «Прочие затраты».

По окончании месяца при необходимости получения аналитических данных о затратах в разрезе статей затрат кредитуются счета учета элементов затрат со списанием сумм в дебет счета 37 «Отражение общих затрат». Информация о производственных затратах по статьям отражается по дебету счетов 20 — 29 в корреспонденции со счетом 37 «Отражение общих затрат». Счета управленческой бухгалтерии для учета производственных затрат следующие:

счет 20 «Основное производство»;

счет 23 «Вспомогательные производства»;

счет 25 «Общепроизводственные расходы»;

счет 26 «Общехозяйственные расходы»;

счет 28 «Брак в производстве»;

счет 29 «Обслуживающие производства и хозяйства».

Необходимость перегруппировки затрат, например, с центров затрат на носители затрат, может быть реализована с использованием счета 37 «Отражение общих затрат». При формировании информации по центрам ответственности также производится ее перегруппировка с использованием счета 37 «Отражение общих затрат».

По окончании месяца затраты отражаются по кредиту счета 43 «Готовая продукция» с последующим списанием в дебет счета 90 «Продажи» по факту отгрузки продукции.

В случае второго альтернативного варианта формирование в учете производственных затрат предусматривает выделение счетов 20 — 29 в самостоятельную локальную систему управленческого учета. Возможность такого выделения может обеспечиваться использованием счета 27 «Распределение общих затрат».

Расходы в финансовом учете в течение отчетного периода формируются на счетах 30 — 36 в разрезе элементов. По окончании периода они закрываются по кредиту в корреспонденции с дебетом счета 90 «Продажи».

В течение месяца для управления и контроля за уровнем затрат формируется информация в разрезе объектов учета (центров затрат, центров ответственности, носителей затрат) по дебету счетов 20 — 29 в корреспонденции со счетом 27 «Распределение общих затрат». Суммы, отражаемые на счетах производственного учета, должны быть равны затратам, учтенным на счетах 30 — 36 финансовой бухгалтерии. По окончании месяца формируется управленческая отчетность о производственных затратах, и отпадает необходимость в дальнейшем использовании и хранении аналитической информации о затратах, что сопровождается бухгалтерской записью по дебету счета 27 в корреспонденции со счетами 20, 23, 25, 26. В этом случае в финансовом учете уже не используется информация о затратах по нормативной стоимости.

Ольга Феклисова Руководитель консалтинговой группы УЧЁТ ЧЁТКО

Бюджет движения денежных средств (сокращенно БДДС) или, как его еще называют, «Cash Flow» (сокращенно CF) показывает, буквально, движение денежных средств. Т.е. сколько денег реально получено на расчетный счет компании и в кассу и сколько денег израсходовано с расчетного счета и из кассы за выбранный период в детализации по выделенным статьям и подстатьям.

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

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

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

Однако есть универсальные рекомендации.

Во-первых, в любом бюджете движения денежных средств должно быть 3 раздела по видам деятельности:

  • операционная,
  • инвестиционная,
  • финансовая.

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

Инвестиционная деятельность — вид деятельности, связанный с приобретением, созданием и продажей внеоборотных активов (основных средств, нематериальных активов), строительством, капитальным ремонтом, реконструкцией и модернизацией.

Финансовая деятельность — вид деятельности, связанный с привлечением и возвратом кредитов и займов, необходимых для финансирования операционной и инвестиционной деятельности, а также с изменением собственного капитала (выпуск акций, облигаций и т.п.).

Во-вторых, в каждом из этих разделов есть доходная часть: «Поступления по….. (вид деятельности)» и расходная часть: «Расходы по…. (вид деятельности)». По каждому виду деятельности должен подводиться итог, рассчитываемый как разница между поступлениями и расходами денежных средств по данному виду деятельности.

Далее поступления и расходы по каждому виду деятельности делятся на статьи, а те, в свою очередь, на подстатьи.

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

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

Следовательно, с одной стороны, перечень статей должен быть максимально полным, с другой — статьи должны выделяться и детализироваться на подстатьи по принципу «существенности».

При разработке статей бюджета полезно иметь аналитику (фактические данные) за предыдущие периоды.

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

Рекомендации по выделению статей.

  1. Принцип «существенности»: статьи и подстатьи нужно выделять, руководствуясь тем, насколько существенные суммы по ним проходят. Понятно, что если, например, на статью «Связь» всего в месяц тратится 100 рублей, детализировать ее на подстатьи смысла не имеет. А вот если на эту же статью в месяц тратится 150 000 рублей, скорее всего нам нужна будет детализация на подстатьи, из которой мы увидим, что:
    • 60 000 руб. израсходовано на МН/МГ переговоры,
    • 50 000 руб. – на мобильную связь,
    • 40 000 руб. – на Интернет.

Принцип «существенности» означает также сопоставление расходов по статье с суммой оборотов по другим статьям и с оборотом компании в целом. Понятно, что для одной компании расходы на связь в размере 150 000 рублей в месяц значительны, а для другой – капля в море. В первом случае выделение статьи и ее детализация на подстатьи нужны, во втором ею можно пренебречь и объединить статью «Связь», например, со статьей «Расходы на ИТ инфраструктуру».

  1. Очень рекомендую при выделении статей избегать статьи/подстатьи «Прочие». Да, по каждому виду деятельности должна быть статья «Прочие расходы по …. (вид деятельности)» на тот случай, если возникнут какие-то существенные расходы по данному виду деятельности, под которые не выделена отдельная статья. Но в качестве подстатьи «Прочие» должна быть выделена только в самом крайнем случае, когда есть подозрение, что перечень подстатей для данной статьи не является полным.
    Исключение: можно допустить наличие подстатьи «Прочие» во всех статьях бюджета, когда формат бюджета разрабатывается впервые, и нет достаточной статистики по операциям за предыдущие периоды.
    Почему надо избегать статьи/подстатьи «Прочие»? Потому что каждая такая статья — это соблазн запланировать лишние расходы и неправильно отнести операции по факту. Точно такой же соблазн вызывают статьи со схожими названиями: «Непредвиденные расходы», «Чрезвычайные расходы», «Резерв на непредвиденные расходы» и т.п. Названия статей могут быть разными, но суть одна – неправомерное увеличение расходной части бюджета. Редко кто из руководителей, поняв, что бюджет – это инструмент контроля расходов, удерживается от соблазна запланировать суммы по таким статьям, чтобы в течение месяца можно было оправдать расходы сверх бюджета.
    Поэтому мой совет: не оставлять таких лазеек. Иначе бюджет нельзя будет рассматривать как инструмент контроля.
  2. Каждая статья или подстатья должна находиться в своем разделе и иметь уникальный код. Желательно, чтобы, помимо уникального кода, статья/подстатья имела и уникальное название (исключение: подстатья «Прочие»). Название статьи должно максимально полно раскрывать суть относимых на нее операций и быть не длинным, но точным. Из названия статьи и ее расположения в конкретном разделе бюджета пользователи должны однозначно понимать, какие доходы/расходы должны быть на нее отнесены.
    Что я имею в виду? Например, если в разделе «Операционная деятельность» в статье «Приобретение сырья, материалов, работ, услуг» есть подстатья «Транспорт» и одновременно в разделе «Коммерческие расходы» тоже есть статья «Транспорт», у пользователей однозначно будут вопросы, какие расходы, на какую статью относить и чем эти статьи отличаются? И придется все время пояснять, что в первом случае на эту статью должны относиться расходы по доставке сырья и материалов (товара) до склада, которые формируют их себестоимость, а во втором – расходы по доставке товара до потребителя. Это если пользователь сознательный.
    Чаще всего пользователи лишними вопросами себя не утруждают и самостоятельно принимают решение, на какую статью что относить. А это приводит к ошибкам в бюджете. Для того чтобы избежать ошибок при планировании и учете операций по факту, называйте статьи так, чтобы их названия не допускали разных мнений и трактовались однозначно. Например, «Транспортные расходы по доставке товаров, сырья и материалов до склада» и «Транспортные расходы по доставке товара до потребителя».

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

Теперь очень важный вопрос.

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

Ответ: все зависит от уровня автоматизации управленческого учета.

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

Если же бюджетирование ведется на программном продукте, далеком от ERP-системы, или в Excel, то подход один: перечень статей и формат бюджета утверждаются на год. В течение года перечень статей и их названия не меняются, иначе это не только повредит первоначальный формат бюджета, но и сделает невозможной консолидацию за год. Потому что в начале года использовали, к примеру, 60 статей, в середине их перечень поменяли, и статей стало 64, еще и названия некоторых откорректировали, к концу года еще раз перечень поменялся. Как корректно консолидировать информацию за год?

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

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

Еще раз про уровень автоматизации: если вы не работаете на ERP-системе, используете для ведения управленческого учета программный продукт, далекий от ERP-системы, или Excel, нужно понимать, что при ежегодном изменении перечня статей нельзя будет сделать консолидацию за несколько лет. А перекладка статей одного года на статьи другого занимает колоссальное количество времени.

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

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

Вернемся к анализу статей. Как проводить анализ статей по году?

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

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

В-третьих, за весь год проанализировать ВСЕ статьи «Прочие»: досконально просмотреть все операции, которые на эти статьи относились, чтобы понять, нужно ли выделять под доходы/расходы по этим статьям отдельную статью или подстатью? Однозначно нужно, если доходы/расходы по подстатье «Прочие» превышали расходы по основной статье.

Цель такого анализа: исключить либо свести к минимуму в перечне статей для следующего года статьи/подстатьи «Прочие».

И в заключение.

Для автоматизации на уровне Excel, либо на другом программном продукте, далеком от ERP-системы, формат бюджета движения денежных средств должен быть единым для всех Центров ответственности. Это нужно с точки зрения консолидации данных на уровне Холдинга или группы компаний. И, поверьте, нет ничего страшного, если, например, сотрудники Центра ответственности «Юридический отдел», будут видеть, что в бюджете есть статья «Дивиденды» и какие-либо другие статьи, не имеющие отношения к юридическому отделу. Ведь они не увидят суммы, которые по этой статье запланированы. И даже не будут знать, планируются ли они в принципе.

Если есть автоматизация на уровне ERP-системы, для каждого Центра ответственности можно настроить доступ только к тем статьям, по которым у него есть право планировать доходы/расходы.

Пример бюджета движения денежных средств.

Версия для печати

Я работаю обычным аналитиком и, так получилось, что летом 2014 года, участвуя в одном e-commerce проекте, на коленке за 3 недели сделал управленческий учет в MS Excel. Давно планировал и наконец-то решил выложить на Хабр. Думаю, будет полезно малым предпринимателям, понимающим важность управления финансовыми потоками, но не желающим тратить значительное количество времени и средств на ведение управленческого учета. Не претендую на истину в последней инстанции и буду рад иным решениям, предложенным участниками сообщества.
Бизнес, к которому я летом имел отношение, был обычным интернет-магазином одежды премиум и выше сегмента с оборотом около 1 млн рублей в месяц. Бизнес работал, не сказать, чтобы очень успешно, но работал и продолжает работать. Собственник понимал необходимость ведения управленческого учета и, с этим пониманием, взял меня в качестве финансового директора (аналитика/менеджера …), так как предыдущий ушел из бизнеса за 3 месяца до моего прихода. Собственно, дыра такой же продолжительности была и в ведении управленческого учета. Забегая вперед скажу, что дыру не устранил (решили не ворошить прошлое), но создал систему, которая успешно работает при минимальных трудозатратах и по сей день.
Мой предшественник вёл управленку в Финграде, который оказался весьма мощным инструментом. Например, он позволял автоматически грузить информацию из 1С и выписок разных банк-клиентов, создавая проводки по заранее сформулированным правилам. Вещь, безусловно полезная, однако, при соблюдении системы двойной записи увеличивала время работы в разы. Чтобы избежать увеличения работы этот инструмент позволял генерировать «зависимые проводки». В создании этих дополнительных проводок и была зарыта собака. И тут выяснилось, что за всей мощью Финграда крылась уникальность, обусловившая полное отсутствие экспертизы в свободном доступе. Обычным пользователям (платившим, кстати, 3000 рублей в месяц за доступ к системе) были доступны лишь «Руководство пользователя» на официальном сайте, да 6 видео-уроков там же. Youtube, дававший доступ к ещё паре десятков видеоуроков, также не сильно помогал. Форумов с информацией «how to…» не было в принципе. Поддержка, на конкретные вопросы о правилах создания «зависимых проводок» и просьбах помочь именно в моем случае — морозилась фразами «у нас с вами не заключен договор на поддержку, поэтому на такие специфические вопросы мы не готовы отвечать». Хотя казалось бы — чего специфического в таких просьбах, да ещё и со скриншотами с моей стороны? Понятно, что все можно бить руками, но спрашивается, а зачем тогда вообще платить за инструмент, который сильно увеличивает время, необходимое на ведение управленки и не дает никаких преимуществ для малого бизнеса?
Убедив собственника в нецелесообразности использования «Финграда» при таких объемах бизнеса и выгрузив всю информацию из системы, я поставил на нем БОЛЬШОЙ и жирный крест. При этом решение уйти именно в MS Excel было не спонтанным. Хорошенько загуглив на тему ведения управленческого учета находил монстров, похожих на «Финград», либо ссылки на веб-приложения для ведения личных финансов, в то время как основными требованиями к системе были:
— возможность ведения БДДС и БДР на основе изменяемого плана счетов;
— простота в дальнейшем ведении управленческого учета (в том числе силами «финансово-неграмотных» пользователей);
— гибкость (возможность на ходу расширять/убирать функционал);
— отсутствие перегруженности инструмента/интерфейса.
Для начала проясним термины: будучи не финансистом, под БДДС понимаю «Баланс Движения Денежных Средств», БДР — «Бюджет Доходов и Расходов». БДДС считаем кассовым методом (днем совершения операции — колонка «Дата операции») и используем для операционного day-to-day планирования, а БДР методом начисления (колонка «Период начисления») для стратегического, в рамках года и более.
Итак, как все устроено и как оно работает (в идеале):
1. Управленческий учет собирается на основе информации вводимой конечными пользователями при помощи формы в Google Docs. Красным помечены названия полей и кодировки вариантов в конечном файле управленческого учета — своего рода мапинг полей.

2. В итоге выглядит оно так (зеленым залито то, что перенесено в итоговый файл управленки).

3. Управленческий учет построен на базе .xls выгрузки из Финграда (отсюда странные для сторонних пользователей названия и, в целом избыточное количество колонок). Убедительная просьба не воспринимать всерьез значения колонок «Приход», «Расход» — многое рандомно изменено.

Механизм заполнения прост: аккуратно переносим во вкладку «Общая книга» из формы Google Docs и банковских выписок. Красным выделены строки, используемые для формирования БДР, зеленым — БДДС., которые представляют собой сводные таблицы и строятся на основе промежуточных вкладок с говорящими названиями. Единственные колонки, информация в которых не связана с иными источниками: «Исходный ID» (уникальные значения строк) и «Дата создания» (=ТДАТА(), а затем копируем и вставляем как значение)
4. Статьи ДДС (движения денежных средств) располагаются на отдельной вкладке «ПС_служебный» и вполне могут регулярно пересматриваться в зависимости от конкретных потребностей (не забываем обновлять формулы на листах «Данные_БДДС», «Данные_БДР»).

5. На картинке образец БДДС, в формате по умолчанию, свернутый до понедельной «актуальности».

6. Образец БДС (помесячный). Обратите внимание на уже упоминавшийся выше тезис об использовании строк из «Общей книги»: Бюджет и Факт для БДР, План и Факт — для БДДС.

7. Работа с БДДС подразумевает поддержание строк «План» в максимально актуальном состоянии. Я достаточно педантичен в работе с первичной информацией и комментарии сделанные мной сохраняли всю историю изменений. Как будет у Вас — вопрос к Вам. Мой подход позволил мне отлавливать примерно 1 существенную ошибку в неделю, грозившую расхождениями на десятки-сотни тысяч рублей. Время, кстати, съедалось немного.

8. Собственно сам файл управленческого учета.
PS: Долго думал над тем, как автоматизировать процесс «перелива» информации из формы Google Docs, пока не пришел к мысли о необходимом ручном контроле вводимой разнородной информации (много людей заполняет формы + наличие минимум одного банк-клиента + 1С). Тем более не знаю VBA… Отдаю на суд хабрасообщества как есть, надеюсь, кому-нибудь поможет или просто будет интересно.
UPD от 30.04.2020: ввиду текущей экономической ситуации решил вспомнить Excel и бесплатно помочь малому бизнесу созданием подобных инструментов до конца 2020 года. Все интеллектуальные права останутся за мной и будут опубликованы в виде статьи (без данных, содержащих коммерческую тайну).
Критерии попадания в выборку кому хочу помочь:
1. Ваше дело имеет очевидную ценность для общества (благотворительные фонды, волонтерские движения и т.д.)
2. Вы платите белые зарплаты и приемлемый для оборота объем налогов (придется доказать выписками перечислений в фонды и на счета ФНС)
3. Вы не МММ, продавцы алкоголя возле школ, микрофинансовые организации и прочие лохотронщики
3 пункт — обязательный, также необходимо попадание в 1 или 2 пункты. Если попадаете в выборку, то пишите в личку.

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

Построим опорный план транспортной задачи с помощью инструмента «Поиск решений». Рядом составим такие же по объему таблицы с пустыми ячейками. Таблица А – аналог стоимостной, Б – «запасов», В – «спроса».

Элементы таблицы Б – сумма соответствующих строк в таблице А. Элементы таблицы В – сумма соответствующих столбцов в таблице А.

Отдельно составим результирующую таблицу Г. В ней отразятся оптимальные транспортные расходы. Каждый элемент таблицы Г – произведение элемента А и соответствующего элемента стоимостной таблицы.

В отдельном месте листа введем формулу функции: =СУММПРОИЗВ(A1:C3;G1:I3)

Первый массив – стоимостная таблица, второй – диапазон А.



Ставим курсор в ячейку со значением функции. Вызываем инструмент «Поиск решения». Заполняем диалоговое окно:

  1. Целевая ячейка – ссылка на ячейку со значением функции.
  2. Она должна быть равна «максимальному значению», как наиболее выгодному для перевозчика.
  3. Команда изменяет значения ячеек в таблице А. Значения – целые числа.
  4. Диапазон таблицы Б = «Запасам».
  5. Диапазон В = «Потребительскому спросу».

В открытом диалоговом окне нажимаем кнопку «Параметры» и устанавливаем следующие настройки:

Жмем ОК – «Выполнить». Получаем опорный план транспортной задачи:

Он залит бледно-зеленым цветом. Ячейки со значениями выше нуля называются «базисными», «занятыми». Ячейки со значением 0 – «свободными».

Далее действуем по плану:

Посчитаем число занятых клеток с помощью функции СЧЕТЕСЛИ.

Так как результат равен 5, опорный план является не вырожденным. Проверим оптимальность опорного плана – найдем потенциалы по занятым клеткам.

Нужно составить систему уравнений. Предполагается, что αj = 0, а αi + βj = сij (стоимость доставки единицы груза). Вызываем команду «Поиск решения». Вносим условия системы уравнений в качестве ограничений.

Заполненное диалоговое окно:

Результат работы инструмента «Поиск решения»:

Посчитаем оценки свободных клеток. Формула: сij – (αi + βj). Берем свободную клетку из таблицы А. Смотрим ее значение в стоимостной таблице. Это будет сij. Далее смотрим, какие потенциалы соответствуют данной клетке. Вставляем их значения в формулу.

В программе Excel найдем оценки с помощью математических операторов и ссылок на соответствующие ячейки.

План считается оптимальным, если оценки больше или равны 0. В нашем случае получились отрицательные значения – план не является оптимальным. Поэтому двигаемся дальше.

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

В исходной клетке (с минимальной оценкой) ставим знак «+». Далее чередуем: «-«, «+» и т.д.

В таблице стоимости находим минимальное значение со знаком «-«.

В нашем примере – это «5», ячейка В1. Эту клетку нужно убрать из базиса. А ячейку с минимальной оценкой сделать базисной.

С учетом изменившихся данных вновь строим опорный план транспортной задачи. Применяем инструмент «Поиск решения». Пересчитанный план перевозок выглядит так:

Обратите внимание: ячейка I1 (где была минимальная оценка) стала базисной, занятой.

Проводим те же расчеты для нового плана (с пункта №1): находим потенциалы, оценки свободных клеток для проверки оптимальности. И так до тех пор, пока оценки свободных клеток не будут больше или равны 0.

Add a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *