Расчет бюджета организации в excel

Формирование планового бюджета в 1С:ERP (1С:КА)

Расчет бюджета организации в excel

Как правило, решение об автоматизации процесса Бюджетирования в информационной системе принимается руководством предприятия тогда, когда процесс бюджетирования доходов и расходов организован и отлажен с помощью таблиц Excel.

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

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

Несмотря на большой потенциал и гибкость типовых возможностей подсистемы Бюджетирования программных продуктов 1C: ERP и 1C: КА это не всегда бывает целесообразно.

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

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

При планировании Бюджета доходов и расходов нам необходимо было получить в информационной системе Бюджет по Фонду оплаты труда по плановым данным. Самый простой вариант решения данной задачи — занести плановые данные вручную в документ планирования. Но он был и самым трудоемким для пользователей и не отвечал ожиданиям Заказчика. Поэтому мы стали искать другие пути решения данной задачи.

При этом необходимо было учесть следующие особенности планирования ФОТ на предприятии до начала процесса автоматизации:

  • планирование ФОТ производилось в таблицах Excel;
  • размер планового ФОТ зависел от различных факторов: объема выпускаемой продукции, загрузки оборудования, сменности работы производственного персонала и т. п. Все они были заложены в формулы, по которым рассчитывался плановый ФОТ. При этом до момента утверждения планового ФОТ он мог пересчитываться несколько раз;
  • расчет планового ФОТ производился с очень подробной детализаций по производственным и не производственным подразделениям с учетом квалификации работников (по должностям, по категориям и т.п.).

Учитывая все это совместно с Заказчиком было принято решение о нецелесообразности переноса расчета планового ФОТ в информационную систему и использовании для формирования плановых данных по ФОТ типовой механизм загрузки данных их Excel.

Для реализации данного решения первое, что было сделано совместно с Заказчиком —это определен список статей бюджетов, по которым в бюджете БДР будут отражаться плановые и фактические расходы на оплату труда:

Рис 1. Список статей бюджета для отражения плановых и фактических данных по расходам на оплату труда

Аналитика данных статей бюджета — Подразделения предприятия, так как при планировании и анализе исполнения Бюджета по Фонду оплаты труда Заказчику необходима была информация не только по общей сумме расходов в целом по статье бюджета, но и информация о ФОТ в разрезе подразделений.

Затем также совместно с Заказчиком было выработано решение о соответствии между утвержденными статьями бюджетов и категориями персонала, в разрезе которых производиться расчет планового ФОТ в таблицах Excel. Например, расчет ФОТ в таблице Excel производится по каждому цеху в разрезе основных и вспомогательных рабочих:

Рис. 2. Пример расчета планового ФОТ в таблице Excel

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

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

Следующий шаг — это настройка в информационной системе вида бюджета Бюджет по Фонду оплаты труда для заведения плановых данных в утвержденной Заказчиком структуре:

Рис. 3. Бюджет по Фонду оплаты труда, настроенный в информационной системе

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

— структура бюджета должна быть максимально простая, количество аналитик по статье бюджета не должно превышать одного значения;

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

Затем настроенный вид бюджета был выгружен в файл формата Excel.

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

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

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

Рис. 4. Заполнение закладки Основное документа Экземпляр бюджета

Рис. 5. Выгрузка структуры бюджета в файл Excel

При этом открывается окно создания файла, в котором по кнопке «Сохранить как» мы можем выбрать место хранения данного файла и сохранить его:

Рис. 6. Окно создания файла бюджета

Выгруженный из информационной системы файл Бюджет по Фонду оплаты труда имеет следующий вид:

Рис. 7. Выгруженный из информационной системы в Excel файл бюджета

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

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

Рис. 8. Файл Бюджета по ФОТ с плановыми данными

В результате специалист ПЭО, ответственный за формирование в информационной системе плановых данных по ФОТ, мог формировать их автоматически. Для этого в информационной системе необходимо открыть сохраненный ранее документ Экземпляр бюджета (без заполненных данных) и заполнить его по команде Excel / Загрузить из Excel

Рис. 9. Заполнение Бюджета по ФОТ плановыми данными из Excel

В открывшемся по данной команде окне необходимо выбрать файл для загрузки с помощью команды «Выбрать»:

Рис. 10. Выбор файла для загрузки данных

Проверить данные в открывшемся окне «Сопоставление загружаемых данных из Excel» и по команде «Загрузить в документ и закрыть» заполнить документ экземпляр бюджета:

Рис. 11. Экземпляр бюджета, заполненный плановыми данными из Excel

При этом если в исходном файле расчета планового ФОТ данные изменялись в связи с тем, что в процессе формирования и утверждения БДР было принято решение об изменении планового ФОТ либо параметров, которые влияют на его размер, специалисту ПЭО было достаточно еще раз воспользоваться командой «Загрузить» из Excel и обновить в ИС плановые данные по ФОТ, а не вносить изменения в документ формирования плановых данных по Бюджету ФОТ вручную.

Какой вывод можно сделать? Нет ничего страшного в том, что в процессе автоматизации, особенно на первом ее этапе, некоторые плановые данные для формирования БДР или БДДС будут рассчитываться с помощью таблиц Excel. Типовые возможности программных продуктов 1С: ERP и 1С: КА позволяют загрузить данные из Excel в информационную систему и избавить пользователей от необходимости переносить их вручную.

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

______________________________________

заместитель директора по продуктам финансово-экономического учета Сальникова Ольга Владимировна. Дата обновления статьи 04.09.2019 г.

Источник: https://sitec-it.ru/blog/stati-po-bukhgalterii/formirovanie-planovogo-byudzheta-v-1s-erp-1s-ka/

Образец бюджета предприятия

Расчет бюджета организации в excel

Руководитель небольшого бизнеса вполне может вести бюджет самостоятельно. ПРОВЕРЕНО! Если заниматься ведением бюджета регулярно, не реже одного или двух раз в неделю, то начинаешь «чувствовать» свое предприятие и, как следствие, умело балансировать между осмотрительностью и деловым азартом.

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

Итак, бюджет. Бюджет обычно требуется в трех случаях: 1. Вам нужен классический БДДС. 2. Берете кредит в банке, нужен план доходов и расходов и прогноз движения денежных средств.

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

Это разные бюджеты. Чтобы не томить ожиданием студентов и заемщиков, даем ссылку первых двух бюджетов. Скачивайте. А сами пойдем дальше. Реальный бизнес гораздо интереснее.

Бюджет движения денежных средств для реального бизнеса

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

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

В отличии от Бюджета доходов и расходов этот инструмент делает акцент не на планируемую прибыль, а на возможность практически контролировать денежный поток.

 

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

Бюджет выполнен в электронных таблицах Excel. Он прост в эксплуатации, не требует специальных знаний по информационным технологиям. Все расчеты сделаны на основе формулы СУММЕСЛИМН и функции «Проверка данных». В состав файла входят два основных листа: Лист «Бюджет движения ДС»и Лист «Реестр платежей».

Лист «Бюджет движения ДС» включает в себя ячейки с формулами (окрашены) с данными о фактических поступлениях и затратах и пустые ячейки (белого цвета) для планируемых данных.

Начинать работу с Бюджетом движения денежных средств следует с Листа «Бюджет движения ДС». В колонку «Проект» необходимо занести данные о заказах или проектах. Можно корректировать статьи расходов разделов «Постоянные затраты», «Финансовая деятельность», «Инвестиционная деятельность», добавлять копированием строки раздела «Проект».

Затем необходимо заполнить колонки с планируемыми данными, при этом оплаты за ТМЦ, услуги и т.д. по проектам надо проставить со знаком «минус».

Теперь в строке «Начало Банк» хорошо видны кассовые разрывы (отрицательные значения).

Переходим к Листу «Реестр платежей». В реестр платежей можно вносить планируемые к оплате документы в две очереди.

Для того, чтобы фактические данные по поступлениям корректно отражались на Листе «Бюджет движения ДС», необходимо не только выбрать «Проект»,

но и в колонке «Вид расходов» выбрать «Оплата от заказчика». Сумму прихода надо проставить со знаком «минус».

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

Наименование статей общехозяйственных затрат можно изменять, но для этого следует перейти на Лист «Бюджет движения ДС».

Работать можно с несколькими расчетными счетами, кассой, подотчетными суммами, при этом наименование банков можно изменить в строках 5-15, а в колонке «Сальдо» проставить первоначальные входящие остатки.

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

Итак, Бюджет готов. Через неделю работы, процесс внесения фактических данных и корректировки планируемых будет занимать совсем немного времени. А Вы сможете полностью сосредоточиться на развитие своего дела, привлечении новых клиентов, повышении объема продаж и иными важными и полезными делами.

Такой Бюджет хорошо сочетается с удобным Табелем учета рабочего времени в разбивке по проектам и категориям сотрудников и его легко приспособить для целей Раздельного учета ГОЗ.

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

Теория. Готовые таблицы. Формы документов. Схемы. Шаблоны. Пояснения.Здесь

Источник: https://apbhelp.ru/budjet.html

Составление бюджета предприятия в Excel с учетом скидок

Расчет бюджета организации в excel

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

Для наглядного примера рассмотрим дистрибьюторскую фирму и составим для нее простой бюджет предприятия с примером в Excel (пример бюджета можно скачать по ссылке под статьей). В бюджете можно планировать расходы на бонусные скидки для клиентов. Он позволяет моделировать различные программы лояльности и при этом контролировать расходы.

Наша фирма обслуживает около 80-ти клиентов. Ассортимент товаров составляет около 120-ти позиций в прайсе. Она делает наценку на товары 15% от их себестоимости и таким образом устанавливает цену продажи. Такая низкая наценка экономически обоснована плотной конкуренцией и оправдывается большим товарооборотом (как и на многих других дистрибьюторских предприятий).

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

Условия и размер процентной ставки бонусной системы определяется двумя параметрами:

  1. Количественная граница. Количество приобретенного конкретного товара, которое дает клиенту возможность получить определенную скидку.
  2. Процентная скидка. Размер скидки – это процент, что вычисляется от суммы, на которую приобрел клиент при преодолении количественной границы (планки). Размер скидки зависит от размера количественной границы. Чем больше товара приобретено, тем больше скидка.

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

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

Нужно чтобы маржа удерживалась в определенных границах (например, не меньше 7% или 8%, вед это же прибыль фирмы). А клиенты смогут выбирать себе несколько вариантов бонусных скидок.

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

Проект бюджета в Excel состоит из двух листов:

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

Движение денежных средств по клиентам

Структура таблицы «Продажи за 2015 год по клиенту:» на листе «продажи»:

Модель бюджета предприятия

На втором листе устанавливаем границы для достижения бонусов соответствующие им проценты скидок.

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

Структура таблицы «Условия бонусной системы» на листе «результаты»:

  1. Граница бонусной планки 1. Место для установки уровня граничной планки по количеству.
  2. Бонус % 1. Место для установки скидки при преодолении первой границы. Как рассчитывается скидка для первой границы? Хорошо видно на листе «продажи». С помощью функции =ЕСЛИ(Количество > граница 1 бонусной планки[количество]; Объем продаж * процент 1 бонусной скидки; 0).
  3. Граница бонусной планки 2. Более высокая граница по сравнению с предыдущей границей, которая дает возможность получить большую скидку.
  4. Бонус % 2 –скидка для второй границы. Рассчитывается с помощью функции =ЕСЛИ(Количество > граница 2 бонусной планки[количество]; Объем продаж * процент 2 бонусной скидки; 0).

Структура таблицы «Общий отчет по обороту фирмы» на листе «результаты»:

  1. Суммарный объем продаж. Общая сумма проданного товара.
  2. Суммарная закупка. Общая сумма, на которую приобретено товара у поставщиков.
  3. Суммарный бонус. Общая сумма скидок.
  4. Прибыль БРУТТО: Суммарный объем продаж — Суммарная закупка – Суммарный бонус.
  5. Маржа 1: Прибыль БРУТТО / Суммарный объем продаж (в процентном выражении грязной прибыли).
  6. Расходы по реализации – сумма расходов на дистрибуцию товара (логистика, доставка, реклама и т.п.).
  7. Расходы на управление – суммарные расходы на зарплату сотрудникам, налоги и т.п.
  8. Прибыль НЕТТО (чистая прибыль) – Прибыль БРУТТО — Расходы по реализации — Расходы на управление.
  9. Маржа 2 – Прибыль НЕТТО / Суммарный объем продаж (в процентном выражении).

Готовый шаблон бюджета предприятия в Excel

И так у нас есть готовая модель бюджета предприятия в Excel, которая является динамической. Если граничная планка бонусов находится на уровне 200, а бонусная скидка составляет 3%.

Это значит, что в прошлом году клиент приобрел товара в количестве 200шт. А в конце года получит за это бонус скидку 3% от стоимости.

А если клиент приобрел 400шт определенного товара, значит, он преодолел вторую граничную планку бонусов и получает скидку уже 6%.

При таких условиях изменится показатель «Маржа 2», то есть чистая прибыль дистрибьютора!

Задача руководителя дистрибьюторской фирмы выбрать самые оптимальные уровни граничных планок для предоставления клиентам скидки. Выбирать нужно так чтобы показатель «Маржа 2» находился хотя бы в приделах 7%-8%.

Скачать бюджет предприятия-бонус (образец в Excel).

Чтобы не искать лучшее решение методом тыка, и не делать ошибок рекомендуем прочитать следующею статью. Там описано как сделать в Excel простой и эффективный инструмент: Таблица данных в Excel и матрица чисел. С помощью «таблицы данных» можно в автоматическом режиме визуализировать самые оптимальные условия для клиента и дистрибьютора.

Источник: https://exceltable.com/otchety/byudzheta-predpriyatiya-primer-excel

Закон 24/7
Добавить комментарий