Формулы в excel для бухгалтерии. В помощь бухгалтеру – полезные функции Excel
![Формулы в excel для бухгалтерии. В помощь бухгалтеру – полезные функции Excel](https://i2.wp.com/habrastorage.org/getpro/habr/post_images/622/0c9/5e9/6220c95e9acbfefc3b49fa67e6a04d7d.jpg)
Я работаю обычным аналитиком и, так получилось, что летом 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. Собственно сам
Однако при заполнении приходных или расходных документов некоторые значения всё же придется забивать руками.
К ним относятся:
- дата документа;
- количество товара;
- номер документа;
- прочие часто меняющиеся параметры.
При помощи встроенных в Excel инструментов можно добиться автоматического заполнения нескольких столбцов на основании данных справочника. Однако настроить этот функционал самостоятельно без глубоких знаний программы вряд ли удастся.
Самому же можно добавить элементарные формулы, например, указать столбец с суммарной стоимостью товара, умножив количество на цену.
Указанным выше образом формируются листы также «Приход» и «Расход». Их отдельное ведение удобно для последующего быстрого поиска нужного документа.
Формирование оборотной ведомости
Сделать оборотную ведомость самостоятельно можно, но придется разобраться в некоторых функциях табличного редактора. Навыков программирования здесь не требуется.
На отдельном листе можно оформить информацию о товарных остатках, чтобы понимать необходимость закупки той или иной позиции ассортимента.
Отслеживание критических остатков
Ведение учета товара в магазине в Excel можно настроить таким образом, чтобы на листе с остатками продукции выводилась информация и о необходимости закупки той или иной позиции ассортимента. Далее будет рассмотрен простейший пример, как оформить это в табличном редакторе.
В предложенном примере имеется три места хранения товара с указанием остатков в каждом из них. С помощью функции ЕСЛИ(ИЛИ…) можно настроить автоматическую проверку соблюдения нормы запаса по каждому складу. Итоговая формула будет выглядеть следующим образом:
ЕСЛИ(ИЛИ(C3<3;D3<3;E3<3);«Необходимо пополнение склада»;«Товара достаточно»).
Таким образом, при падении остатков в каком-либо месте хранения меньше трех единиц, пользователь увидит сообщение о необходимости закупки или внутреннего перемещения товара. Этим же образом можно мониторить обратный процесс - затаривание определенной номенклатурной позицией. Используемая формула очень проста и может быть всегда добавлена в готовую таблицу.
Указанный способ мониторинга значений применим не только к складским остаткам. Аналогично можно анализировать следующие показатели:
- объемы выполненных работ или продаж в разрезе сотрудников;
- прирост валового оборота или прибыли;
- выявление спадов торговли по определенным группам товаров и другие показатели.
Эксель может представить предпринимателям множество аналитических инструментов при учете в нем розничных продаж. Одна из его сложностей заключается в малом количестве профессионального обучающего материала и необходимости самостоятельно составлять все формулы.
Плюсы учета товаров в магазине в эксель
Испытайте все возможности платформы ЕКАМ бесплатно
Соглашение о конфиденциальности
и обработке персональных данных
1.Общие положения
1.1.Настоящее соглашение о конфиденциальности и обработке персональных данных (далее - Соглашение) принято свободно и своей волей, действует в отношении всей информации, которую ООО «Инсейлс Рус» и/или его аффилированные лица, включая все лица, входящие в одну группу с ООО «Инсейлс Рус» (в том числе ООО «ЕКАМ сервис»), могут получить о Пользователе во время использования им любого из сайтов, сервисов, служб, программ для ЭВМ, продуктов или услуг ООО «Инсейлс Рус» (далее - Сервисы) и в ходе исполнения ООО «Инсейлс Рус» любых соглашений и договоров с Пользователем. Согласие Пользователя с Соглашением, выраженное им в рамках отношений с одним из перечисленных лиц, распространяется на все остальные перечисленные лица.
1.2.Использование Сервисов означает согласие Пользователя с настоящим Соглашением и указанными в нем условиями; в случае несогласия с этими условиями Пользователь должен воздержаться от использования Сервисов.
«Инсейлс» - Общество с ограниченной ответственностью «Инсейлс Рус», ОГРН 1117746506514, ИНН 7714843760, КПП 771401001, зарегистрированное по адресу: 125319, г.Москва, ул.Академика Ильюшина, д.4, корп.1, офис 11 (далее - «Инсейлс»), с одной стороны, и
«Пользователь» -
либо физическое лицо, обладающее дееспособностью и признаваемое участником гражданских правоотношений в соответствии с законодательством Российской Федерации;
либо юридическое лицо, зарегистрированное в соответствии с законодательством государства, резидентом которого является такое лицо;
либо индивидуальный предприниматель, зарегистрированный в соответствии с законодательством государства, резидентом которого является такое лицо;
которое приняло условия настоящего Соглашения.
1.4.Для целей настоящего Соглашения Стороны определили, что конфиденциальная информация - это сведения любого характера (производственные, технические, экономические, организационные и другие), в том числе о результатах интеллектуальной деятельности, а также сведения о способах осуществления профессиональной деятельности (включая, но не ограничиваясь: информацию о продукции, работах и услугах; сведения о технологиях и научно-исследовательских работах; данные о технических системах и оборудовании, включая элементы программного обеспечения; деловые прогнозы и сведения о предполагаемых покупках; требования и спецификации конкретных партнеров и потенциальных партнеров; информацию, относящуюся к интеллектуальной собственности, а также планы и технологии, относящиеся ко всему перечисленному выше), сообщаемые одной стороной другой стороне в письменной и/или электронной форме, явно обозначенные Стороной как ее конфиденциальная информация.
1.5.Целью настоящего Соглашения является защита конфиденциальной информации, которой Стороны будут обмениваться в ходе переговоров, заключения договоров и исполнения обязательств, а равно любого иного взаимодействия (включая, но не ограничиваясь, консультирование, запрос и предоставление информации, и выполнение иных поручений).
2.Обязанности Сторон
2.1.Стороны соглашаются сохранять в тайне всю конфиденциальную информацию, полученную одной Стороной от другой Стороны при взаимодействии Сторон, не раскрывать, не разглашать, не обнародовать или иным способом не предоставлять такую информацию какой-либо третьей стороне без предварительного письменного разрешения другой Стороны, за исключением случаев, указанных в действующем законодательстве, когда предоставление такой информации является обязанностью Сторон.
2.2.Каждая из Сторон предпримет все необходимые меры для защиты конфиденциальной информации как минимум с применением тех же мер, которые Сторона применяет для защиты собственной конфиденциальной информации. Доступ к конфиденциальной информации предоставляется только тем сотрудникам каждой из Сторон, которым он обоснованно необходим для выполнения служебных обязанностей по исполнению настоящего Соглашения.
2.3.Обязательство по сохранению в тайне конфиденциальной информации действительно в пределах срока действия настоящего Соглашения, лицензионного договора на программы для ЭВМ от 01.12.2016г., договора присоединения к лицензионному договору на программы для ЭВМ, агентских и иных договоров и в течение пяти лет после прекращения их действия, если Сторонами отдельно не будет оговорено иное.
(а)если предоставленная информация стала общедоступной без нарушения обязательств одной из Сторон;
(б)если предоставленная информация стала известна Стороне в результате ее собственных исследований, систематических наблюдений или иной деятельности, осуществленной без использования конфиденциальной информации, полученной от другой Стороны;
(в)если предоставленная информация правомерно получена от третьей стороны без обязательства о сохранении ее в тайне до ее предоставления одной из Сторон;
(г)если информация предоставлена по письменному запросу органа государственной власти, иного государственного органа, или органа местного самоуправления в целях выполнения их функций и ее раскрытие этим органам обязательно для Стороны. При этом Сторона должна незамедлительно известить другую Сторону о поступившем запросе;
(д)если информация предоставлена третьему лицу с согласия той Стороны, информация о которой передается.
2.5.Инсейлс не проверяет достоверность информации, предоставляемой Пользователем, и не имеет возможности оценивать его дееспособность.
2.6.Информация, которую Пользователь предоставляет Инсейлс при регистрации в Сервисах, не является персональными данными, как они определены в Федеральном законе РФ №152-ФЗ от 27.07.2006г. «О персональных данных».
2.7.Инсейлс имеет право вносить изменения в настоящее Соглашение. При внесении изменений в актуальной редакции указывается дата последнего обновления. Новая редакция Соглашения вступает в силу с момента ее размещения, если иное не предусмотрено новой редакцией Соглашения.
2.8.Принимая данное Соглашение Пользователь осознает и соглашается с тем, что Инсейлс может отправлять Пользователю персонализированные сообщения и информацию (включая, но не ограничиваясь) для повышения качества Сервисов, для разработки новых продуктов, для создания и отправки Пользователю персональных предложений, для информирования Пользователя об изменениях в Тарифных планах и обновлениях, для направления Пользователю маркетинговых материалов по тематике Сервисов, для защиты Сервисов и Пользователей и в других целях.
Пользователь имеет право отказаться от получения вышеуказанной информации, сообщив об этом письменно на адрес электронной почты Инсейлс - .
2.9.Принимая данное Соглашение, Пользователь осознает и соглашается с тем, что Сервисами Инсейлс для обеспечения работоспособности Сервисов в целом или их отдельных функций в частности могут использоваться файлы cookie, счетчики, иные технологии и Пользователь не имеет претензий к Инсейлс в связи с этим.
2.10.Пользователь осознает, что оборудование и программное обеспечение, используемые им для посещения сайтов в сети интернет могут обладать функцией запрещения операций с файлами cookie (для любых сайтов или для определенных сайтов), а также удаления ранее полученных файлов cookie.
Инсейлс вправе установить, что предоставление определенного Сервиса возможно лишь при условии, что прием и получение файлов cookie разрешены Пользователем.
2.11.Пользователь самостоятельно несет ответственность за безопасность выбранных им средств для доступа к учетной записи, а также самостоятельно обеспечивает их конфиденциальность. Пользователь самостоятельно несет ответственность за все действия (а также их последствия) в рамках или с использованием Сервисов под учетной записью Пользователя, включая случаи добровольной передачи Пользователем данных для доступа к учетной записи Пользователя третьим лицам на любых условиях (в том числе по договорам или соглашениям). При этом все действия в рамках или с использованием Сервисов под учетной записью Пользователя считаются произведенными самим Пользователем, за исключением случаев, когда Пользователь уведомил Инсейлс о несанкционированном доступе к Сервисам с использованием учетной записи Пользователя и/или о любом нарушении (подозрениях о нарушении) конфиденциальности своих средств доступа к учетной записи.
2.12.Пользователь обязан немедленно уведомить Инсейлс о любом случае несанкционированного (не разрешенного Пользователем) доступа к Сервисам с использованием учетной записи Пользователя и/или о любом нарушении (подозрениях о нарушении) конфиденциальности своих средств доступа к учетной записи. В целях безопасности, Пользователь обязан самостоятельно осуществлять безопасное завершение работы под своей учетной записью по окончании каждой сессии работы с Сервисами. Инсейлс не отвечает за возможную потерю или порчу данных, а также другие последствия любого характера, которые могут произойти из-за нарушения Пользователем положений этой части Соглашения.
3.Ответственность Сторон
3.1.Сторона, нарушившая предусмотренные Соглашением обязательства в отношении охраны конфиденциальной информации, переданной по Соглашению, обязана возместить по требованию пострадавшей Стороны реальный ущерб, причиненный таким нарушением условий Соглашения в соответствии с действующим законодательством Российской Федерации.
3.2.Возмещение ущерба не прекращают обязанности нарушившей Стороны по надлежащему исполнению обязательств по Соглашению.
4.Иные положения
4.1.Все уведомления, запросы, требования и иная корреспонденция в рамках настоящего Соглашения, в том числе включающие конфиденциальную информацию, должны оформляться в письменной форме и вручаться лично или через курьера, или направляться по электронной почте адресам, указанным в лицензионном договоре на программы для ЭВМ от 01.12.2016г., договоре присоединения к лицензионному договору на программы для ЭВМ и в настоящем Соглашении или другим адресам, которые могут быть в дальнейшем письменно указаны Стороной.
4.2.Если одно или несколько положений (условий) настоящего Соглашения являются либо становятся недействительными, то это не может служить причиной для прекращения действия других положений (условий).
4.3.К настоящему Соглашению и отношениям между Пользователем и Инсейлс, возникающим в связи с применением Соглашения, подлежит применению право Российской Федерации.
4.3.Все предложения или вопросы по поводу настоящего Соглашения Пользователь вправе направлять в Службу поддержки пользователей Инсейлс либо по почтовому адресу: 107078, г. Москва, ул. Новорязанская, 18, стр.11-12 БЦ «Stendhal» ООО «Инсейлс Рус».
Дата публикации: 01.12.2016г.
Полное наименование на русском языке:
Общество с ограниченной ответственностью «Инсейлс Рус»
Сокращенное наименование на русском языке:
ООО «Инсейлс Рус»
Наименование на английском языке:
InSales Rus Limited Liability Company (InSales Rus LLC)
Юридический адрес:
125319, г. Москва, ул. Академика Ильюшина, д. 4, корп.1, офис 11
Почтовый адрес:
107078, г. Москва, ул. Новорязанская, 18, стр.11-12, БЦ «Stendhal»
ИНН: 7714843760 КПП: 771401001
Банковские реквизиты:
В течение четырех дней опытные редакторы «Главбуха» рассказывали на занятиях, как создавать качественную журнальную статью. Одна из учениц школы – Полина Алексеева – решила закрепить полученные знания на практике и написала специально для сайта статью о полезных функциях известной всем программы Microsoft Excel.
Microsoft Excel - программа, обладающая широкими возможностями. Многие знакомы лишь с основными навыками работы с программой, простыми функциями. Бухгалтеру часто не хватает времени, чтобы узнать, как автоматизировать расчет какого-либо показателя, а зря. Мы предлагаем Вам уделить 10 минут в день и освоить особенности Excel, полезные в Вашей работе.
Перед Вами обзор возможностей Excel, полезных бухгалтеру. Откройте программу и попробуйте выполнить наши примеры. Это действительно просто!
1. Как поменять цвет ячейки в зависимости от ее значения
Все очень просто - меняем цвет (заливку, шрифт, рамки), если значение соответствует определенным условиям. Отрицательный баланс заливаем красным, а положительный - зеленым. Просроченные заказы выделяем красным, а доставленные вовремя - черным.
Чтобы сделать подобное - выделяем ячейки, которые должны автоматически менять свой цвет и выбираем в меню Формат - Условное форматирование.
В открывшемся окне задаем условия и, нажав затем кнопку Формат, параметры форматирования ячейки, если условие выполняется. В этом примере крупных клиентов делаем полужирным синим шрифтом, а мелких - черным курсивом:
В условии мы проверяем либо значение ячейки, либо заданную формулу.
Примеры условий:
формула = $А1 =, выделяет цветом всю строку
формула = ячейка = МАКС/МИН ($A$1:$A$10) выделяет максимальные, минимальные значения
формула = ячейка, = СРЗНАЧ ($A$1:$A$10) выделяет значения больше (меньше) среднего
2. Как выбрать из списка нужные записи
Первый способ – автофильтр
Скрываем все строки, за исключением тех, которые удовлетворяют заданным условиям отбора. Для этого выделяем любую ячейку списка и выбираем в меню Данные - Фильтр – Автофильтр.
В первой строке, содержащей заголовки столбцов, появятся кнопки со стрелками – кнопки автофильтра. Для выборки записей щелкаем по кнопке автофильтра в заголовке нужного столбца и выбираем из выпадающего списка условие для фильтрации:
Автофильтр выполняет следующие функции:
Сортирует список по выбранному столбцу (выбираем «Сортировка»)
- находит заданное число (или процент) наибольших или наименьших элементов в списке (выбираем «Первые 10»)
- выполняет фильтрацию по заданному условию (выбираем «Условие»)
В данном случае, мы выбираем из списка номера документов, проведенные в указанный период.
Второй способ - расширенный фильтр
Чтобы использовать расширенный фильтр, прежде всего, зададим диапазон условий:
Скопируем заголовки столбцов, по которым будут вводиться условия
- введем условия для фильтрации ниже скопированных заголовков: ячейки одной строки Excel связываются логическим "И", а ячейки разных строк - логическим "ИЛИ".
Например, чтобы отобрать из списка документы, проведенные в период 15 -16 января, в которых фигурирует счет 903003, нужно задать условие следующим образом:
Чтобы отфильтровать данные по критериям из нашей таблицы, выделяем исходный список и выбираем в меню Данные - Фильтр - Расширенный фильтр. Откроется окно Расширенного фильтра, в которое введем адрес исходного диапазона и адрес диапазона условий.
3. Функция СУММ суммирует весь заданный диапазон, как просуммировать только значения, соответствующие определенному условию?
В этом случае используем функцию СУММЕСЛИ. Рассмотрим конкретный пример. Допустим, необходимо рассчитать выручку, облагаемую НДС, по данной таблице:
Наши действия в этом случае таковы: используем функцию СУММЕСЛИ («ставка, %»,">0", «всего покупок»)
Синтаксис функции - СУММЕСЛИ (Диапазон; Критерий; Диапазон суммирования). Здесь «Диапазон» - ячейки, которые проверяются на соответствие критерию; «Критерий» - критерий в форме числа, выражения или текста; «Диапазон суммирования» - ячейки для суммирования.
Синтаксис функции – СЧЕТЕСЛИ (Диапазон; Критерий)
Excel обладает широким спектром функций, выполняющих финансовые расчеты. Например, функция ПЛТ, позволяющая определять ежемесячные платежи по ссуде. В качестве исходных данных для этой функции нужно ввести процентную ставку, число периодов и сумму займа.
В примере мы рассчитываем сумму месячных выплат и полную сумму выплат.
Синтаксис функции ПЛТ (ежемесячная процентная ставка; число периодов; величина ссуды)
Другие финансовые функции, которые можно использовать для расчета кредитов и инвестиций:
Функция КПЕР (Ставка; Платеж; Приведенная стоимость; Будущая стоимость; Тип) – вычисляет срок, за который можно расплатиться с кредитом, если есть данные о сумме кредита, ежемесячных выплат и проценте банка.
Функция СТАВКА (Периоды; Платеж; Приведенная стоимость; Будущая стоимость; Тип) - вычисляет, каков должен быть процент банка для кредита с заданными суммой, сроком и ежемесячной выплатой.
Функция ПС (Ставка; Периоды; Платеж; Будущая стоимость; Тип)- вычисляет, какую сумму можем взять в кредит в данном банке, если готовы выплачивать ежемесячно определенную сумму.
5. Как соотнести записи из одной таблицы с записями из другой?
Рассмотрим, данный вопрос на примере. Наш план счетов представлен в Excel в виде – счет, название. Перед обновлением программного обеспечения было решено удалить счета, по которым не было оборота в течение последних 3 лет. Теперь перед нами список счетов и к ним требуется скопировать наименования из первоначального плана счетов. Функция ВПР позволяет автоматизировать этот процесс.
Вырезка из первоначального плана счетов (1):
Счета, по которым был оборот в течение последних 3 лет (2) – 1000003 и 1100001.
Определим название данных счетов с помощью первой таблицы. Если перед нами список из тысячи счетов – задача уже не выглядит такой простой.
Решение – функция ВПР , которая ищет заданное значение (в нашем примере - это номер счета) в крайнем левом столбце указанной таблицы, двигаясь сверху вниз и, найдя его, выдает содержимое соседней ячейки.
Для функции необходимо ввести такие аргументы, как: искомое значение (указывается ячейка содержащее значение), массив, в котором происходит поиск, и номер столбца:
6. Как из огромной таблицы сформировать компактный и наглядный отчет?
Для этого мы используем сводные таблицы. Например, с помощью данного механизма очень удобно анализировать выгруженные из программы 1С проводки. Рассмотрим эту ситуацию подробнее.
Исходная таблица имеет вид:
Чтобы построить сводную таблицу, ставим курсор в любую ячейку списка данных и выбираем пункт меню Данные – Сводная таблица. Далее следуем указаниям мастера создания сводных таблиц. В результате этой операции получаем шаблон отчета на отдельном листе:
Работать с шаблоном несложно: перетаскиваем мышью названия столбцов (полей) из окна списка полей сводной таблицы в области строк, столбцов, страниц и данных макета:
1) в область строк таблицы данных перетаскиваем поле «Дебет»;
2) в область столбцов помещаем поле «Месяц»;
3) в область данных - поле «Сумма» и т.д.
Итак, отчет готов. Заметьте: в исходной таблице могут быть тысячи записей, а мы потратили на подготовку отчета всего несколько минут.
Отчет можно детализировать, перетащив в область строк любое дополнительное поле, например, счет кредита, субконто и т.д.
Когда количество показателей в таблице не больше 3-4, удобней вместо сводных таблиц использовать механизм Промежуточных итогов. Действуем по следующей схеме:
Первый шаг – Сортировка
Сначала отсортируем таблицу, чтобы сформировать группы строк, по которым в дальнейшем будем подсчитывать итоги – Данные – Сортировка
Второй шаг – Подведение итогов
В этом диалоговом окне мы указываем поле, по которому отсортирована наша таблица, выбираем математическую функцию (сумма, среднее, минимум-максимум, количество и т.д.), отмечаем галочками те столбцы, по которым необходимо подвести итоги.
Нажимаем на клавишу ОК и получаем таблицу, в которую автоматически включены итоги.
При подведении итогов Excel сам производит группировку. Отсортированные группы быстро и удобно сворачивать-разворачивать с помощью знаков "плюс" и "минус" слева от таблицы. В свернутом состоянии отчет выглядит более компактно и информативно.
Управленческий учет предназначен для представления фактического состояния дел на предприятии и, соответственно, принятия на основе данных управленческих решений. Это система таблиц и отчетов с удобной ежедневной аналитикой о движении денежных средств, прибылях и убытках, расчетах с поставщиками и покупателями, себестоимости продукции и т.п.
Каждая фирма сама выбирает способ ведения управленческого учета и нужные для аналитики данные. Чаще всего таблицы составляются в программе Excel.
Примеры управленческого учета в Excel
Основные финансовые документы предприятия – отчет о движении денежных средств и баланс. Первый показывает уровень продаж, затраты на производство и реализацию товаров за определенный промежуток времени. Второй – активы и пассивы фирмы, собственный капитал. Сопоставляя эти отчеты, руководитель замечает положительные и отрицательные тенденции и принимает управленческие решения.
Справочники
Опишем учет работы в кафе. Предприятие реализует продукцию собственного производства и покупные товары. Имеют место внереализационные доходы и расходы.
Для автоматизации введения данных применяется таблица управленческого учета Excel. Рекомендуется так же составить справочники и журналы с исходными значениями.
![](https://i2.wp.com/exceltable.com/otchety/images/otchety23-2.png)
![](https://i1.wp.com/exceltable.com/otchety/images/otchety23-3.png)
Если экономист (бухгалтер, аналитик) планирует расписывать по статьям и доходы, то такой же справочник можно создать для них.
Удобные и понятные отчеты
Не нужно все цифры по работе кафе вмещать в один отчет. Пусть это будут отдельные таблицы. Причем каждая занимает одну страницу. Рекомендуется широко использовать такие инструменты, как «Выпадающие списки», «Группировка». Рассмотрим пример таблиц управленческого учета ресторана-кафе в Excel.
Учет доходов
![](https://i2.wp.com/exceltable.com/otchety/images/otchety23-4.png)
Присмотримся поближе. Результирующие показатели найдены с помощью формул (применены обычные математические операторы). Заполнение таблицы автоматизировано с помощью выпадающих списков.
При создании списка (Данные – Проверка данных) ссылаемся на созданный для доходов Справочник.
![](https://i0.wp.com/exceltable.com/otchety/images/otchety23-5.png)
![](https://i1.wp.com/exceltable.com/otchety/images/otchety23-6.png)
Учет расходов
![](https://i2.wp.com/exceltable.com/otchety/images/otchety23-7.png)
Для заполнения отчета применили те же приемы.
Отчет о прибылях и убытках
![](https://i2.wp.com/exceltable.com/otchety/images/otchety23-8.png)
Чаще всего в целях управленческого учета используется отчет о прибылях и убытках, а не отдельные отчеты по доходам и расходам. Данное положение не нормируется. Поэтому каждое предприятие выбирает самостоятельно.
В созданном отчете для подсчета результатов используются формулы, автозаполнение статей с помощью выпадающих списков (ссылки на Справочники) и группировка данных.
Анализ структуры имущества кафе
![](https://i1.wp.com/exceltable.com/otchety/images/otchety23-9.png)
Источник информации для анализа – актив Баланса (1 и 2 разделы).
Для лучшего восприятия информации составим диаграмму:
![](https://i2.wp.com/exceltable.com/otchety/images/otchety23-10.png)
Как показывает таблица и рисунок, основную долю в структуре имущества анализируемого кафе занимают внеоборотные активы.
По такому же принципу анализируется пассив Баланса. Это источники ресурсов, за счет которых кафе осуществляет свою деятельность.
Руководство компаний, которое приняло решение о начале внедрения системы управленческого учета и непосредственно самого бюджетирования, на первых шагах вынуждено столкнуться с несколькими проблемами , а именно:
- нехватка финансового капитала на внедрение специально разработанной платформы;
- отсутствие четкого понимание того, что необходимо делать.
В таких ситуациях на помощь приходит MS Excel , которая кроме того, что является бесплатной, еще и представляет собой универсальный вариант программного обеспечения для полноценного ведения управленческого учета.
Бухгалтерский учет в обязательном порядке должна вести любая организация, вне зависимости от того, в какой сфере она работает, каковы масштабы и так далее. Во многом это связано с тем, что бухучет должен устанавливать все факты жизнедеятельности компании и отображать их на соответствующих счетах для достижения максимальной картинки финансового положения в целом.
Сведения бухгалтерского учета нередко являются базой для определения налоговых обязательств организации. К примеру, налоговая база на имущество определяется по сведениям бухучета.
Главным предназначением бухучета и параллельно конечной бухгалтерской отчетности является предоставление интересующимся пользователям (внешним) обобщенной информации относительно итогов трудовой деятельности за определенный отчетный период.
В качестве заинтересованных пользователей могут выступать:
- банковские учреждения;
- прочие кредиторы;
- инвесторы и так далее.
Иными словами, в учет берутся все без исключения лица, перед которыми у компании есть долговые обязательства.
В свою очередь, управленческий учет используется исключительно руководством предприятий, а не внешними пользователями. Часто он применяется не только менеджерами компании для оценки текущего положения, но и позволяет предоставлять достоверные сведения для принятия верных управленческих решений, включая возможность мгновенно реагировать на различные факторы.
Важно обращать внимание на то, что пользователи, на которых в первую очередь ориентирована управленческая отчетность, могут быть самыми разными: начиная от непосредственно самих структурных подразделений и заканчивая президентами организаций либо управляющими партнерами.
В свою очередь, направленность управленческого учета на качественные и мгновенные принятия каких-либо решений руководством объясняется разницей в форматах непосредственного представления значений рассматриваемого учета по сравнению с бухгалтерским.
Помимо этого, управленческие отчеты могут различаться высокой степенью детализации или, проще говоря, используемыми способами учета (к примеру, действующим НК РФ, а не РСБУ либо же МСФО), применяемыми специальными счетами/субсчетами, которые не могут быть задействованы в бухучете, а также высокой регулярностью формирования и так далее.
Как составить
Многие отечественные компании сегодня практикуют использование управленческого учета в таблицах, сформированных в MS Excel.
Во многом это связано с тем фактом, что Excel, по сути, относится к универсальному инструменту , который включает в себя огромный набор математических формул, алгоритмов, специальных механизмов учета и так далее, но при этом не требует каких-либо существенных затрат (к примеру, как это требует иное программное обеспечение учета).
Исходя из этого, благодаря использованию MS Excel, квалифицированный персонал имеет право сформировать все необходимые для принятия каких-либо решений отчеты .
Стандартный управленческий учет в обязательном порядке должен включать в себя несколько базовых компонентов :
- отчет относительно прибыли и финансовых затрат;
- отчет относительно движения финансового капитала;
- баланс организации.
В процессе формирования необходимых таблиц, изначально необходимо обращать внимание на обособленность таблиц.
Каждая отдельно взятая таблица (включающая сведения относительно той или иной стороны трудовой деятельность организации) в обязательном порядке предоставляется отдельный лист. Нет необходимости стараться вместить все на одном листке (имеется в виду все числовые показатели). Более того, объем книги может существенно различаться в зависимости от того, какой уровень детализации затребован руководством.
Для решения данной задачи на практике нередко используются встроенные функции Excel, среди которых выпадающие списки, группировки и так далее.
Все сформированные таблицы с исходными сведениями в обязательном порядке следует размещать на отдельных листах
Excel
(допустим, лист учета прибыли от реализации строительных материалов и так далее), а в самом начале данной книги следует создать сводную таблицу, в которой с помощью выпадающих списков отображаются конечные показатели относительно иных листов книги.
По причине того, что группировка «Предметов бизнеса» остается одинаковой (к примеру, крупы-напитки и так далее), а непосредственно сами таблицы учета в Excel требуют к себе формирования огромного числа таблиц (учета прибыли, прямых финансовых затрат, косвенных денежных потерь, сопровождающих денежные затраты и так далее), компаниям необходимо помнить об одном нюансе.
Он заключается в том, что наиболее оптимальным инструментом в Excel по праву считаются справочники . В процессе их формирования организации смогут в полной мере по завершению таблиц применять их в качестве автозаполнения всех имеющихся полей.
Помимо всех имеющихся преимуществ наибольшее значение имеет наличие огромного числа всех необходимых формул и различных иных математических механизмов (к примеру, возможность автокорреляции, дисперсия, математическое ожидание и так далее), что оказывает существенную помощь в процессе проведения анализа конечных значений, а также планирования будущего развития.
Это дает полное право в конечном итоге принимать эффективные управленческие решения .
Алгоритм формирования
Алгоритм составления заключается в следующем :
- Проведение анализа денежной структуры организации.
- Обязательное формирование классификаторов управленческого учета.
- Создание плана счетов.
- Формирование концепции управленческого учета.
- Создание соответствующего перечня форматов управленческого учета.
- Внедрение системы и ее автоматизация.
На самом деле на практике многие отечественные организации уже показали, что разработка учета в Excel не составит большого труда. Достаточно только следить за достоверностью указанных сведений и четко придерживаться имеющегося алгоритма.
Для лучшего понимания стоит обратиться к картинке-инструкции :
Исходя из данного изображения, можно видеть, как предположительно должна выглядеть будущая модель.
Внедрение системы в организацию
В процессе внедрения управленческого учета в MS Excel изначально следует уделить внимание автоматизации учета финансовых потоков . Это своего рода информационная база управленческого учета в целом.
Финансовое движение, по сути, является своего рода отображением всех иных операций организации.
На следующем этапе идет внедрение учета прибыли и финансовых затрат. И только в последнюю очередь следует автоматизация активов и пассивов исключительно той части, которая не связана с финансовыми потоками и прибылью.
Существующие правила
На сегодня есть несколько ключевых правил , которые в обязательном порядке следует придерживаться в процессе внедрения автоматизированного управленческого учета в MS Excel.
- Разделение функций относительно указания сведений и тех, которые припадают на формирование различной отчетности. На первый взгляд это может казаться очевидным, поскольку нередко учреждения рисуют, к примеру, формулу отчета ДДС и в дальнейшем все имеющиеся операции по ДДС заносит в данную формулу.
- Указание сведений относительно различных хозяйственных операций в форме реестра однотипных записей.
- Указание сведений по всем имеющимся юридическим лицам и счетам, которые напрямую относятся к организации, в единый реестр.
Допускается возможность различными способами указывать сведения в Excel, поскольку ограничения отсутствуют.
Главное, чтобы это являлся единственный реестр по хозяйственным операциям, где в дальнейшем будет отображаться вся без исключения информация относительно организации, вне зависимости от того, к какому юридическому лицу она относится и какой есть расчетный счет.
Примеры
Пример финансовой структуры организации в управленческом учете на основании разработанных таблиц в Excel будет иметь примерно такой вид:
Что касается классификатора, то он имеет такой вид:
Хотелось бы уточнить тот факт, что классификаторы рассматриваемого учета могут описывать разнообразные объекты с целью их достоверной трактовки всеми без исключения участниками данной системы.
На сегодня существуют следующие наиболее популярные классификаторы :
- разновидность прибыли;
- разновидность изготавливаемой продукции либо предоставление каких-либо услуг (к примеру, строительных);
- различные типы клиентов;
- места формирования прибыли и финансовых затрат;
- разновидности финансовых потерь;
- разновидности обязательств;
- разновидность собственного финансового капитала;
- категория наемного штата сотрудников и так далее.
Исходя из всего вышесказанного, можно сделать вывод, что, по сути, в таком варианте учета, который был рассмотрен, нет ничего сложного.
Как вести управленческий учет в 1С — на видео.