ФОРМА ОТЧЕТА ДЛЯ КОНТРОЛЯ РАСХОДОВ В EXCEL
Создание бюджета не будет казаться сложным процессом, если использовать в работе шаблон. Главное — выбрать из огромного количества шаблонов тот, который подходит для ваших целей: создать бюджет для инвестиционного проекта, отслеживать текущие расходы предприятия, планировать предстоящий финансовый год и др. В данной статье представим наиболее информативные и доступные форматы отчетов для контроля расходов в Excel. Сделаем это на примере компании ООО «Василек» (название условное).
Ежедневно руководителю компании необходимо принимать решение, как распределить денежные средства, расставлять приоритеты по оплатам. Помочь ему в этом может Бюджет движения денежных средств (БДДС) — документ, в котором собраны все поступившие заявки на оплату и сведения об имеющихся в компании свободных денежных средствах.
Этот документ формируется в Excel. В качестве информационного материала берут данные бухгалтерского учета об остатках денежных средств на счетах на начало периода, для которого планируют составить БДДС, остатки по кассе (если имеется кассовый учет), все незакрытые обязательства как по данным бухучета (кредиторская задолженность на начало периода), так и в соответствии с заключенными договорами на оплату.
При подготовке БДДС нужно помнить, что все выплаты должны соответствовать утвержденному плановому бюджету доходов и расходов компании. Как только появляется внеплановый платеж, должно появиться сообщение, что это планом не предусмотрено.
Если в результате планирования на конец периода прогнозируются отрицательные денежные остатки, бюджет подвергается корректировке за счет сокращения плана оплат. Поэтому для понимания ситуации лучше сразу добавить в БДДС информацию о текущей задолженности перед поставщиками, плановых затратах на предстоящий месяц и прогнозной задолженности на конец месяца с учетом сумм оплат, заложенных в бюджет.
В таблице 1 представлен Бюджет движения денежных средств ООО «Василек»» на ноябрь 2019 г.
Как видно из табл. 1, чистый поток по месяцу прогнозируется отрицательным (–2270 руб.), однако за счет начальных остатков в 6500 руб. компания в состоянии выполнить заявленный бюджет на данный месяц. При этом она наращивает дебиторскую задолженность от своих покупателей с 18 500 до 29 000 руб. и уменьшает кредиторскую задолженность перед поставщиками товара с 45 000 до 30 000 руб. В целом по месяцу картина оптимистичная.
Конечно, есть обязательные платежи, которые никак нельзя отложить на вторую половину месяца (оплата аренды, коммунальные платежи, выплата заработной платы). Следовательно, нужен понедельный или подневной план оплат, которого компания должна четко придерживаться. Рассмотрим подневное планирование оплат ООО «Василек» на ноябрь 2019 г. (табл. 2).
Обратим внимание, что представленный план подневных оплат сформирован как сводная таблица Excel. Конечно, можно использовать разработанный отчет, перенесенный в Excel, однако опыт показывает, что лучше работать с информационным массивом или базой данных. В настоящее время в Excel с помощью готовых макетов можно не только составить очень удобный отчет, но и установить многоуровневую систему анализа.
В отчете для расчета величины расходов можно сразу увидеть доходы и расходы, причем возможна группировка по дню (табл. 3).
Из таблицы 2 видно, сколько нужно потратить в периоде по определенной статье затрат, из табл. 3 — какую сумму необходимо иметь на счетах на конкретную дату.
Форма для прогноза и учета расходов по дням не обязательно должна быть консервативной. В ней могут быть небольшие отступления, допустим, более развернутое наименование затрат (иногда недостаточно стандартной группировки, нужно иметь более детальное представление о расходе).
Для учета расходов можно пользоваться формой, представленной в табл. 4.
Чтобы сформировать данную таблицу, нужно переработать огромную массу данных.
АЛГОРИТМ ПОСТРОЕНИЯ ОТЧЕТА
Представим алгоритм формирования отчета для учета и контроля расходов (см. табл. 4).
Прежде всего нужно подготовить справочники по кодам. С их помощью все расходы кодируются, а затем группируются по необходимым статьям. Расходы объединяются в единую базу данных и кодируются по следующей системе (табл. 5).
Шифры учета дают возможность собрать все данные с помощью формул в более крупные статьи. А мелкое дробление — это табл. 6, на основании которой рассчитывают консолидированный отчет по расходам, используя шифры и формулы. Данную таблицу получают в результате кропотливого труда сотрудников по шифровке ежедневных расходов.
На основании утвержденного БДР вносят плановые значения, а по результатам отчетного периода включают данные по фактическому исполнению.
1. Все данные по затратам распределяют по утвержденным ЦФО. Для этого отработка осуществляется ежедневно, причем справочник кодировки затрат (см. табл. 5) — это уже более высокий уровень учета. Первоначально все расходы учитывают с подробной детализацией, как в табл. 6.
Продолжение табл. 6
На основании таких заполненных таблиц в рамках месяца контролируют исполнение бюджета расходов. Затем все детальные расходы при помощи формул консолидируют на уровень табл. 5. Причем для кодировки можно использовать не только ручной режим, но и функционал формул:
ЕСЛИ(ЕСЛИ(B57=»усл.банка»;»23р»;ЕСЛИ(B57=»ПН»;»22р»;ЕСЛИ(B57=»ЕСН»;»22р»;
ЕСЛИ(B57=»налог на дивиденды»;»23р»;ЕСЛИ(B57=»ЗП 10***»;»11р»;ЕСЛИ(B57=»ЗП 25***»;»11р»;ЕСЛИ(B57=»Субподряд»;»21р»;0)))))))).
Отметим, что лучше всего пользоваться формулами. Если использовать формулы с необходимыми и достаточными условиями, формулы по работе с массивами данных, формулы типа: =СЦЕПИТЬ(«Расшифровка расходов подразделений «;D1;» за «;Сценарий!B2), можно настроить учет и контроль так, чтобы при выполнении условия, например, расходы больше 10 000 руб. в день, срабатывала контрольная фраза «Остановить расходы».
Важный момент: с помощью настроенных формул сегодня можно заполнить все отчеты.
2. Консолидация месячных отчетов по расходам в один общий сводный отчет за год.
3. Отчет по контролю расходов — это целая книга, созданная на базе Excel. Для учета расходов первоначально формируется БДДС. Используются данные по заключенным договорам (обязательные платежи), далее — база бухгалтерского учета с кодировкой затрат. В конечном итоге получаем сводную таблицу по расходам и остаткам компании (см. табл. 4).
Конечно, правильно вести учет именно так. Все отчеты и вводная информация на отдельных листах. Это хорошо для тех, кто только начинает пользоваться указанными шаблонами. На наш взгляд, намного удобнее, когда всю информацию видишь на одном листе, не перескакивая из одного листа в другой.
Статья опубликована в журнале «Планово-экономический отдел» № 11, 2019.
Любое успешное предприятие приходит к какому-то строительству. Можно строить завод, торговый центр, объект городской инфраструктуры и т.д.
С началом подобного проекта сразу появляется огромная куча вопросов и проблем. Кому и сколько было оплачено. Какой поставщик уже поставил материалы, а какой еще только должен поставить. Сколько денежных средств выдали каждому из подрядчиков и за какие суммы эти подрядчики уже отчитались.
Не менее важным вопросом является, при строительстве крупного объекта, который финансируется из разных источников, — сколько средств поступило на финансирование объекта всего в разрезе по инвесторам.
С помощью Excel, все вышеперечисленные проблемы решить довольно просто. Файл состоит из двух листов.
Лист «Движение средств»
Для хронологического ведения движения средств необходимо вносить данные вот в такую таблицу:
В таблице предусмотрено ведение учета расходов по строительству в разрезе четырех расчетных счетов или трех расчетных счетов и кассы. А можно один расчетный счет и три кассы — это вы решаете сами. Просто вписываете название источника средств во вторую строку сверху по каждому приходно-расходному блоку. Эти ячейки выделены оранжевым цветом.
Теперь, как обычно, идем по столбцам:
Столбец В — Дата. Дата вносится вручную, хотя в ячейках всегда будет ссылка на предыдущую ячейку. Смело вносите вместо ссылки.
Столбцы C,D,E — Первый приходно-расходный блок. Приход (поступление средств), расход и остаток. Столбец Е «Остаток» выделен светло-серым цветом. В этот столбец данные не вносятся, в нем автоматически формируются данные об остатке средств в кассе или на расчетном счете.
Столбцы с F по N — это еще три приходно-расходных блока, совершенно аналогичных вышеописанному.
Столбец О «Накладная/Акт» — указывается сумма полученных материалов или принятых работ (т.е. сумма по приходной накладной или по акту выполненных работ).
Колонка P — Наименование контрагента (поставщика или подрядчика). При написании названия контрагента следует избегать ошибок. Это очень важно!
Колонка Q — Вид операции. В этой колонке необходимо указать: материал, услуга или финансирование. Выбор значений производится из выпадающего списка. Прошу обратить внимание, что в первой ячейке этого столбца указывается валюта в которой ведет учет. Выбор валюты производится из выпадающего списка.
Колонка R — Вид материала или работы. Пишется за что именно произведена оплата.
Колонка S — примечание. Дополнительная информация, которую вы посчитаете нужным написать.
Лист «Свод»
Итоговая таблица, где вы будете видеть финансирование и расходы по строительству в разрезе контрагентов.
Это вид развернутого свода. Здесь наглядно видно с кем идут расчеты и в каком состоянии эти расчеты находятся.
А это эта же таблица, но со свернутыми «итогами». Это если нужно просто посмотреть итоговые цифры по расходам и финансированию:
Синим цветом помечены итоги, которые можно сворачивать и разворачивать.
Как всегда «пройдемся по столбцам».
Колонка А — вид затрат. По умолчанию заполнена. В каждом разделе просто написано «материалы», «услуги» и в нижней таблице «финансирование». Дописывать или удалять здесь ничего не нужно.
Колонка В — наименование контрагента. Будет два разных файла. В одном варианте эта колонка будет формироваться автоматически, а в другом — эту колонку нужно будет заполнять в ручную. У каждого варианта есть свои преимущества и об этом будет сказано ниже.
Колонка С — отражается сумма поставленных на объект материалов или выполненных работ, которые приняты Заказчиком (то есть вами). Заполняется автоматически на основании колонки Е с предыдущего листа.
Колонка D — отражается сумма оплат (безналичная и наличная). Формируется как сумма скрытых колонок E,F,G,H в которые автоматически попадают данные по расходам с листа «Движение средств»
Колонка I — формируется как разница между колонками С и D. Обратите внимание, что если поставленные товары (или выполненные работы) превышают сумму оплаты, то вся строка окрашивается зеленым цветом. То есть зеленый цвет строки — это должны вы. Если оплаты превышают сумму поставок, то вся строка окрашивается розовым цветом. То есть розовый цвет — это должен контрагент.
Колонка J — вид материала ли работы. Формируется автоматически. Прошу обратить внимание, что если какой-то контрагент поставил вам кирпич, а потом доски, то в данном столбце отобразится только кирпич. То есть, только первая запись по данному контрагенту.
Колонка K — количество материала или объем работ. Эту колонку Вы заполняете вручную. Если вы ее не заполните — ничего не изменится. Только Вы, скорее всего, со временем забудете, так сколько же было привезено кирпича этим поставщиком или сколько квадратов плитки положил этот подрядчик.
Колонка L — примечание. Вручную заносите какую-то информацию, если считаете необходимым.
Другой вариант листа «Свод»
Этот лист выглядит точно так же, как и вышеописанный и разница только в столбце В.
В вышеописанном листе в столбце В находится формула.
Поэтому названия контрагентов попадают в отчет автоматически.
В другом варианте листа «Свод» в этом столбце нет формул.
Поэтому названия контрагентов заносятся вручную.
Конечно, автоматическая запись контрагентов выглядит предпочтительнее, но тут есть один минус. Если вы на листе «Движение средств» неправильно напишете название контрагента, то файл решит, что это новый контрагент и внесет его на лист «Свод»:
В своде с ручным вводом контрагентов эта же ситуация будет выглядеть по-другому:
Столбец горит красным, это говорит о том, что есть ошибка на листе «Движение средств». И эту ошибку надо искать. Таким образом, случайную ошибку в названии фирмы вы не пропустите.
Как выглядят ошибки на листе «Движение средств»
Если вы допустили ошибку при внесении данных, например не написали контрагента в операции, то лист «Движение средств» будет выглядеть вот так:
То есть на каком расчетном счете или кассе случилась ошибка, та и будет «краснить». Если не будет контрагента напротив акта выполненных работ или приходной накладной, то красным будет столбец O.
Соответственно «краснить» будет и таблица «Свод».
Как искать ошибки
Если у вас «закраснило». Необходимо проверить на наличие пустых ячеек столбцы P и Q.
Очень просто это сделать через автофильтр, который находится в строке 3. Нужно требуемые ячейки отфильтровать так, как показано на картинке:
Ну а если у вас неправильно написано название контрагента, то нужно фильтровать по каждому контрагенту и сверять полученную сумму с суммой в «Своде».
Совет по ведению файла
Заполняйте все ячейки по каждой операции. Пропусков быть не должно. Во-первых, это позволит сохранять больше информации по каждой операции, а во-вторых, даст вам больше возможностей для фильтрации данных.
Где можно приобрести файл?
Приобрести можно в нашем магазине. Обращаю внимание, что файл с автоматическим сводом и файл с ручным вводом контрагентов в свод — это разные файлы
После оплаты вам будет выслано две ссылки на скачивание. По одной ссылке будет пример использования файла, по второй шаблон готовый к использованию.
Эти файлы больше подходят для коммерческого строительства. Если же вам необходим учет расходов на личном строительстве, то вам необходимо ознакомиться с файлом, который описан в статье «Как вести учет расходов на собственном строительстве при помощи Excel».
Меня зовут Антон, и я продолжаю жить в экселе.
В прошлой статье я рассказал о своем опыте учета расходов и поделился ссылкой на гугл-таблицу, которую можно адаптировать под свой учет. Сейчас я переосмыслил эту таблицу, сделал ее более простой и удобной.
Расскажу, как пользоваться новой версией таблицы и настроить ее под себя.
Почему таблицу пришлось переделать
Чтобы пользоваться предыдущей версией таблицы и адаптировать ее под себя, требовалось хорошее знание экселя. А еще я переносил таблицу в гугл из обычной эксельки, поэтому были и банальные косяки форматирования. В итоге у многих читателей не получалось разобраться с таблицей: непонятно было, для чего нужны некоторые колонки.
Я проанализировал обратную связь читателей, за которую вам большое спасибо, и оптимизировал таблицы под людей с минимальным знанием экселя. Итак, разберемся, как пользоваться таблицей и настроить ее под себя.
Шаг 1
Копируем таблицу
Перейдите по ссылке ниже, и на вашем гугл-диске автоматически создастся копия таблицы для учета расходов. Чтобы воспользоваться таблицей, понадобится почта на gmail.com.
В этой копии удалены все демо-данные, а также спрятаны все технические колонки. Таблица полностью готова к использованию.
Если вы хотите посмотреть, как будет выглядеть таблица после нескольких месяцев учета, то по ссылке ниже доступна версия с демо-данными и всеми техническими колонками. Эта версия подходит пользователям с хорошим знанием экселя, которые для начала хотели бы разобраться, по какому принципу работает таблица.
Шаг 2
Вносим расходы
Самое важное и в то же время самое сложное в учете расходов — это начать вносить данные в таблицу и делать это регулярно.
Для внесения расходов мы будем использовать следующие листы в таблице:
- Повседневные. Это обычные повседневные регулярные расходы: на еду, супермаркеты, кафе, такси.
- Крупные. Сюда будем заносить расходы на нерегулярные крупные покупки. Например, на абонемент в спортзал, авиабилеты, дорогую одежду и т. д.
- Квартира. Учитываем расходы, связанные с квартирой: на ЖКХ, ипотечные платежи, ремонт.
Зачем разбивать учет расходов на несколько листов
Для анализа и оптимизации важно учитывать именно повседневные расходы. Они часто скрывают в себе мелкие траты, которые незаметны в течение дня, но в итоге из них складывается существенная статья расходов за месяц или более крупный период.
Крупные разовые траты могут сильно повлиять на всю картину, поэтому их мы ведем отдельно. Расходы на квартиру, например на ремонт, покупку мебели, досрочные платежи по ипотеке, также обычно имеют нерегулярный характер.
Повседневные расходы вносятся так:
- В колонке «Дата» указываем дату расхода. Рекомендую вносить записи последовательно, не перемешивая траты за разные дни. Чтобы быстро ввести текущую дату, нужно выделить ячейку и нажать Ctrl и «;».
- В колонке «Категория» выбираем подходящую категорию.
- В колонке «Стоимость» вводим сумму покупки.
- Если нужно, пишем комментарий для себя, чтобы помнить, на что потратились.
Аналогично можно вносить расходы на вкладках «Крупные» и «Квартира».
Что делать, если нет нужных категорий
В копии вашей таблицы уже есть преднастроенные категории, но их можно менять. Для этого нужно перейти на лист «Справочники». Там есть списки категорий для повседневных расходов, крупных расходов и расходов на квартиру.
Во-первых, можно заменить мои категории своими. Например, если вы не пьете алкоголь, такая категория вам не нужна. Вместо нее можно указать свою.
А еще можно добавлять новые категории в пустые строчки — просто напечатайте их названия внутри очерченной области справочника. Для повседневных расходов это колонка B, для расходов на квартиру — E, для крупных — G.
Лучше настроить все категории сразу, потому что если в дальнейшем вы захотите переименовать существующую категорию, то расходы, внесенные в колонку со старым названием, будут учитываться некорректно. Например, вы записывали расходы в категорию «Авто», а потом решили переименовать ее в «Автомобиль». Расходы из категории «Авто» в переименованную категорию не подтянутся.
Я советую создавать не больше 10 категорий повседневных расходов. Для групп расходов «Крупные» и «Квартира» — не больше 5—6 категорий. Чем больше категорий, тем сложнее разносить платежи, а наша цель — сделать учет расходов простым, чтобы он вошел в привычку.
Если у вас нет расходов, связанных с квартирой, можно использовать лист «Квартира» для учета другой группы расходов, например на автомобиль. Для удобства можно переименовать лист и заголовок справочника на вкладке «Справочники». Для справочника расходов на квартиру это ячейка E1.
По моему опыту для формирования более-менее устойчивой картины трат нужно регулярно вносить расходы хотя бы два-три месяца, а в идеале полгода. После этого можно приступать к анализу трат: для этого есть вкладки «Дашборд» и «Динамика».
АНАЛИТИКА
Что показывает вкладка «Дашборд»
Вкладка «Дашборд» — это графики, сводные таблицы и индикаторы, которые визуализируют ваши расходы и помогают их оптимизировать. Вкладка разбита на логические блоки, у каждого блока свои функции.
Первый блок — шапка. Вот что там происходит:
- Выводится последняя дата, когда вы вносили расходы, — это своего рода напоминание, чтобы не забывать делать это регулярно.
- Выводится средний расход на повседневные траты за текущий месяц. Этот индикатор рассчитывается автоматически после каждого ввода новых расходов.
- Устанавливается лимит повседневных расходов в день. Его нужно устанавливать самостоятельно в ячейке F6, а таблица проверяет, получается ли у вас его придерживаться.
- Если средний расход в день в этом месяце превышает установленный вами лимит, в заголовке шапки появится сообщение, что пора начать экономить. Если все в норме, выводится соответствующее сообщение.
- Выводится информация о расходах вообще за все время учета — по группам «Повседневные», «Крупные» и «Квартира».
Второй блок — это сводная таблица расходов в разбивке по месяцам. Она собирает информацию по расходам в каждом из месяцев. В колонке «В день» считается средний расход на повседневные траты за день.
По этой сводной таблице строится общий график расходов в месяц с разделением на повседневные, крупные и на квартиру. Если в каком-то месяце расходы сильно выбиваются на фоне остальных, сначала я смотрю, в какой из групп расходов произошло сильное отклонение, а потом уже перехожу на соответствующую вкладку и разбираюсь, почему так.
Третий блок — распределение повседневных расходов по дням недели. Таблица и диаграмма тут показывают, в какие дни недели сколько вы тратите. Еще в таблице рассчитывается доля повседневных расходов в будние дни и в выходные. Если за два выходных вы тратите столько же, сколько за пять будних дней, это тревожный звонок. Стоит посмотреть, на что именно уходит так много денег в выходные.
Для себя я вывел золотое правило: расходы в выходные не должны превышать 30% от всех расходов.
Четвертый блок — диаграмма повседневных расходов по категориям. Этот блок показывает, на какие повседневные расходы и сколько вы потратили за все время.
Тут все достаточно наглядно. Смотрите на график и анализируете, сколько денег сэкономили бы за все время, если бы вы:
- не покупали алкоголь;
- уменьшили расходы на транспорт на 30% (например, отказавшись от такси);
- отказались от походов в ресторан.
АНАЛИТИКА
Что происходит на вкладке «Динамика»
На вкладку «Динамика» есть смысл заходить, если накопилось достаточно данных для анализа. Например, если вы заносите расходы уже полгода-год. Графики на этой вкладке показывают, как менялись ваши расходы в динамике.
Первый график отражает динамику среднего расхода. Тут соль в том, что рассчитывается она за последние полгода: сумма всех ваших расходов за последние полгода, поделенная на 6.
Такой показатель более правилен с точки зрения анализа. Поясню. Например, обычно вы тратите 70 тысяч рублей в месяц, но хотите снизить расходы до 50 тысяч. В одном из месяцев вам удается потратить только 50 тысяч, и кажется, что цель достигнута. Но вполне вероятно, что повседневные расходы снизились разово: например, большую часть месяца вы провели в деревне, где не на что было тратить. А когда вернетесь в привычные условия, расходы снова будут 70 тысяч.
В этом случае полезно убедиться, что вы закрепили результат — продержались на заданном уровне расходов полгода. Например, если 5 месяцев вы тратили по 70 тысяч, а в последнем — 50, средний расход за полгода составит:
(70 000 × 5 + 50 000) / 6 = 66 666 рублей
Чтобы средний расход стал 50 тысяч рублей, вам необходимо удерживать текущий результат еще 5 месяцев подряд. Окно в шестом месяце я выбрал исходя из личного опыта, эта величина зашита в формулах таблицы.
Еще на графике есть светло-голубая линия тренда. Она показывает, в каком направлении движутся ваши траты, какова тенденция. Если из месяца в месяц траты увеличиваются, то линия тренда будет восходящей. Это сигнал, что пора бы начать оптимизацию расходов.
Следующая таблица — это сводная таблица повседневных расходов в разрезе по месяцам и категориям. Где тратите много — красненькое, где мало — зелененькое. Все просто и наглядно. Таблица сама увеличивается вправо по мере накопления информации.
Эта таблица удобна тем, что позволяет делать выборки в разрезе «месяц — категория». Например, вы видите: в апреле 2018 года были большие расходы на подарки. Надо разобраться, на что было потрачено столько денег. Выделите ячейку, находящуюся на пересечении нужного месяца «04.18» и категории «Подарки». Дважды кликните левой кнопкой мыши на ячейку — и на новом листе гугл-таблицы сформируется нужная выборка. Потом можно удалить эту страницу.
В итоге
- Определитесь с категориями расходов, в разрезе которых вы будете вести учет. Лучше настроить все категории до его начала.
- Установите лимит повседневных расходов в день на вкладке «Дашборд».
- Фиксируйте расходы на вкладках «Повседневные», «Крупные» и «Квартира».
- Изучайте получившуюся аналитику на вкладках «Дашборд» и «Динамика».
- Чтобы получить картину своих расходов, необходимо вести учет несколько месяцев — хотя бы два-три. Чтобы начать анализировать расходы в динамике, продержитесь полгода-год.
- Если вы столкнулись со сложностями или ошибками в гугл-таблице, опишите вашу проблему в комментарии к статье — я обязательно отвечу.
Здравствуйте, друзья!
Вот какие оправдания слышатся часто от людей в ответ на вопрос, почему они не ведут учет личных финансов: “Это пустая трата времени, потому что сколько получили, столько и израсходовали”, “Это скучно и отнимает много времени” и тому подобное. А самое главное, большинство считает, что эта информация им ничего не даст.
Но кто уже попробовал систематизировать свой бюджет, думает по-другому.
Простая таблица доходов и расходов, которую мы сегодня рассмотрим, не только поможет увидеть реальную картину ваших финансов, но также найти скрытые резервы и открыть новые возможности.
В статье мы постараемся развеять миф о том, что планирование и учет – это бесполезный и долгий процесс. Ежедневно такая работа занимает 1 – 2 минуты. Чуть больше в конце месяца, когда надо проанализировать итоги и наметить план на следующий период.
Многолетний опыт показывает, что пользы от таблицы несоизмеримо больше, чем затраченного на ее составление времени.
Что это за таблица, и для чего ее надо вести
Таблицу доходов и расходов мы рассматриваем как обязательный элемент ведения семейного бюджета. Это документ, в котором отражена вся финансовая жизнь семьи. Когда у вас есть четкая картина перед глазами, что вы заработали и куда потратили, то не остается вопросов про утекающие сквозь пальцы деньги, дыру в кармане и невозможность отвести детей на море.
Как бы вы ни относились к деньгам, они есть в вашей жизни и, надеемся, всегда будут. Если вы ими не управляете, то они управляют вами. Устраивает такая ситуация?
Благодаря финансовому контролю многие семьи могут позволить себе больше, чем семьи с таким же достатком, но хаосом в кошельке и голове.
У них нет эмоциональных и спонтанных покупок, нет долгов. Зато есть капитал на “черный день”, долгосрочные инвестиции, деньги на развлечения, отпуск и образование детей.
Естественно, если сидеть на попе ровно и просто каждый день заполнять табличку, то ничего с неба не свалится. Цифры помогают увидеть, есть ли “черные” дыры в вашем кошельке, куда исчезают все деньги, варианты экономии. А самое главное, на наш взгляд, они меняют мышление, когда появляется желание изменить текущее положение дел: повысить доходы и оптимизировать расходы.
В семьях по-разному относятся к деньгам и их учету. В каких-то все контролирует один человек (как правило, тот, кто больше зарабатывает) и выдает нужные суммы на расходы. В этом случае и таблицу удобнее составлять ему, потому что часто вторая половинка не знает реального финансового положения.
В других – бюджет совместный, когда все доходы поступают в общую “казну”, а ежемесячные расходы обсуждаются на семейном совете. Ни в коем случае речь не идет о текущих обязательных тратах.
Например, оплата коммунальных услуг или проезда в общественном транспорте, покупка продуктов или бензина для семейной машины. Мы говорим здесь о крупных затратах, которые могут существенно повлиять на финансовое положение семьи в этом месяце или году.
Например, в некоторых семьях всегда обсуждается покупка бытовой техники, мобильных гаджетов, предстоящий отпуск или расходы на образование детей и развлечения, ремонт в доме и пр.
При таком раскладе учет финансов может вести любой член семьи. Но к сожалению, без взаимного доверия и согласия процесс будет затруднен. Знаю случаи, когда муж категорически отказывался сообщать жене свои траты, потому что считал, что это ущемляет его независимость и, вообще, ниже его достоинства. Понятно, что объективного семейного бюджета получить при таком раскладе вряд ли удастся.
И знаtv ситуацию, когда жене приходилось правдой и неправдой все-таки получать нужную информацию от мужа. В конце месяца она показала ему результаты. Он был шокирован, что около 50 тыс. рублей ушли на покупки, которые были совсем не обязательны семье. И на следующий месяц он вместе с женой определил основные статьи расходов и необходимые на них суммы, самостоятельно заносил все траты в таблицу, стараясь не выходить за план. Только это позволило сэкономить те же 50 тыс. рублей и отложить их на предстоящий ремонт кухни.
Принципы ведения таблицы
Есть несколько способов ведения таблицы по учету доходов и расходов:
- в блокноте или тетради с помощью ручки и калькулятора;
- в электронных таблицах Excel или Google Таблицах;
- в компьютерных программах и мобильных приложениях для ведения домашнего бюджета.
Выбирайте любой, но соблюдайте главные принципы.
Тогда вы получите реальный домашний бюджет, который позволит правильно планировать и правильно распределять деньги.
А это уже путь к составлению полноценного личного финансового плана, в котором есть место для реализации краткосрочных и долгосрочных целей.
Принципы составления и ведения таблицы:
- Регулярность. Лучше всего ежедневно вносить все, что пришло и ушло из вашего семейного кошелька. Ни в коем случае не запоминайте. Забудете. Если расплачиваетесь банковскими картами, то в конце дня внесите суммы из истории операций. Если используете наличные, сохраняйте чеки или где-то в телефоне (например, в заметках) напишите расходы.
- Терпение. Первый месяц вам может быть тяжело и захочется все бросить. У многих так бываеи, и они, к сожалению, перестают вести таблицу. Делают это вручную, и просто не хватает терпения записывать в блокнот цифры, а потом их считать. Но можно подготовить для себя электронную таблицу и автоматизировала процесс. И ей пользоваться несколько лет. По времени занимает 1 – 2 минуты вечером. Чуть ниже мы дадим вам этот вариант для примера.
- Охват всех членов семьи. Это обязательное условие. Муж (жена), дети, бабушка (дедушка) и прочие домочадцы должны каждый день сообщать вам свои доходы и расходы. Лучше не позволять каждому вносить самостоятельно цифры в таблицу. Большая вероятность, что кто-то из них ошибется, и картина исказится. Наверное, для некоторых семей выполнение этого принципа окажется самым сложным. Придется поработать с каждым и выработать эту полезную привычку.
- Совместное обсуждение итогов и планов на следующий месяц. Это может быть очень увлекательно и реально объединять семью.
Этапы составления таблицы
Из принципов вытекают и основные этапы составления таблицы. Мы будем показывать на примере своей электронной версии домашней бухгалтерии, которая ведется в Google Таблицах. Но то же самое можно сделать вручную на бумаге, перечертив в тетрадь наш образец, или в Excel, создав аналогичную таблицу.
Простая схема ведения семейного бюджета выглядит так:
Этап 1. Подготовка.
Если вы впервые занялись бюджетированием, то первые 1 – 2 месяца (многим хватает и одного) доходы и расходы лучше разбить на каждый день. Можно уже на этом этапе сразу сформировать категории или сделать это на следующий месяц. Они у каждой семьи будут разные.
Например, в чьём-то варианте расходы делятся на:
- обязательные (коммунальные платежи, сотовая связь + интернет, образование, продукты питания, промтовары, транспорт, здоровье + красота);
- необязательные (развлечения, одежда/обувь, крупные покупки, дом, сад и огород);
- непредвиденные затраты – 10 % от всех расходов.
Обязательно добавьте графу “На начало месяца”. Это то, что осталось в кошельке или на банковских картах. Эти деньги будут тратиться в первых числах месяца до получения очередных доходов.
Не забудьте про строки “Итого доходов” и “Итого расходов”. В самом конце считаете эти пункты. У кого-то получится “Экономия”, у кого-то “Перерасход”.
Посмотрите фрагмент таблицы на каждый день месяца. Полный вариант можно скачать по ссылке. Чтобы она у вас не пропала, скачайте таблицу себе на Google Диск. Для этого в меню выберите “Файл” – “Создать копию”.
Меняйте статьи, убирайте ненужные и добавляйте свои категории. Обратите внимание на 0 в строках. Там заведены формулы.
Вносите цифры в ячейки доходов – в строке “Итого доходы” автоматически подсчитываются суммы.
То же самое и по расходам. Внизу дана отчетная таблица за месяц, где выводится итоговое сальдо.
Этап 2. Анализ после 1 – 2 месяцев ведения бюджета.
На этом этапе таблица меняется. Вы уже знаете свои основные статьи доходов и расходов, примерные суммы по каждой из них. Пришло время проанализировать результаты. Если в конце месяца получили экономию, с бюджетом все в порядке. Если идет перерасход, надо срочно искать причину и разрабатывать план по устранению дыр. Каждый сам решает, от каких трат можно отказаться совсем, что делать реже, где и как покупать дешевле и пр. Ваша задача при распределении денег не просто выйти в 0, когда Доходы = Расходы, но и получить заветную Экономию.
Этап 3. Корректировка.
Таблицу на этом этапе мы сделали по-другому. Появились графы “План”, “Факт” и “Отклонение”.
Порядок заполнения такой:
- В начале месяца ввожу цифры в графу “Остаток на начало месяца”. Она должна быть равна сумме из ячейки “Экономия/Перерасход” по факту из предыдущего месяца или вашим наличным в кошельке, на банковской карте. Сумма идет одинаковая и по факту, и по плану.
- Потом заполняете колонку “План” на основе анализа данных за предыдущие периоды и ваших планов на этот месяц. Например, в ноябре вам надо было заплатить налог на имущество, поэтому вы заранее планируете эту сумму.
- В течение всего месяца идет заполнение колонки “Факт”. Каждый день в ячейку соответствующей статьи вы просто вводите нужные цифры. Чтобы они суммировались автоматически, надо представить их в виде формулы.
Например, по статье “Основная зар. плата” сначала мы получили аванс 6 000 руб., а потом основную сумму 18 000 руб.
Тогда запись в ячейке D6 будет выглядеть так: = (6 000 + 18 000). Но в самой ячейке у вас сразу отобразится сумма 24 000.
Если вы получаете зарплату не 2 раза в месяц, а чаще, вы просто наводите мышкой на ячейку и в появившейся формуле в скобках продолжаете добавлять цифры. Сумма считается автоматически.
- Итоги по графам рассчитываются автоматически. Вы видите опять 0 в соответствующих ячейках. Если наведете на 0 мышкой, то появится формула.
Можно продолжать вести таблицу, расчерченную на каждый день, добавив колонки “План”, “Факт” и “Отклонение”. Мы дадим ссылки на оба варианта. Первый удобен тем, что к каждой цифре можно писать комментарий, нажав на соответствующую кнопку в меню.
- Образец таблицы учета на каждый день скачайте по этой ссылке.
- Более простой вариант здесь.
Этап 4. Продолжение ведения семейного бюджета.
На каждый месяц можно добавлять новый лист в таблицу.
Нажмите на “+” в левом нижнем углу. В конце года можно подвести итоги и заполнить отчетную годовую таблицу.
Продвинутые пользователи составляют диаграммы, рассчитывают в процентах долю каждой статьи в семейном бюджете.
Мы отдельно хотим остановиться на статье “Накопления”. Считаем, что каждая семья обязана ее иметь. Деньги на нее можно перечислять не в конце месяца, когда уже все истрачено, а с самого первого дохода в текущем периоде.
Вы сами должны определить, сколько вы будете переводить в накопления. Финансовые консультанты рекомендуют не менее 10 % от ежемесячных доходов. Главное, что это надо делать регулярно и до текущих трат.
Многие часто уверяют, что у них просто нет суммы, чтобы откладывать ее в накопления. А мы уверены, что есть. Представьте ситуацию, что в следующем месяце вам повысили плату за коммунальные услуги на 10 %. Вы не станете ее вносить? Станете и найдете где сэкономить, чтобы заплатить за квартиру. Так почему государству вы находите 10 %, а себе нет?
Если вам не понравились наши таблицы, то можете скачать готовые шаблоны из Excel или Google Документов. Мы воспользовались Google.
Выбрали вкладку “Файл” – “Создать” – “Создать документ по шаблону”. Нашли, например, “Месячный бюджет” и “Годовой семейный бюджет”.
Для домашнего пользования они хорошо подходят.
Можно под себя изменить названия статей, добавить или удалить категории. Итоги тоже подсчитываются автоматически.
Заключение
В заключение хотим попросить вас только об одном – начните учитывать свои доходы и расходы прямо сейчас. Пусть это будет 1 000 руб. в месяц. Уверяем, что это только начало. Потом вы поймете, что сможете откладывать и больше. А дальше в голове станут появляться идеи, как повысить свои доходы.
Если не сидеть на диване перед телевизором, то и у вас появится мышление инвестора и обеспеченного человека.
Всем искренне желаем удачи и финансовой независимости.
Сводные таблицы – один из самых эффективных инструментов в MS Excel. С их помощью можно в считанные секунды преобразовать миллион строк данных в краткий отчет. Помимо быстрого подведения итогов, сводные таблицы позволяют буквально «на лету» изменять способ анализа путем перетаскивания полей из одной области отчета в другую.
Cводная таблица в Эксель – это также один из самых недооцененных инструментов. Большинство пользователей не подозревает, какие возможности находятся в их руках. Представим, что сводные таблицы еще не придумали. Вы работаете в компании, которая продает свою продукцию различным клиентам. Для простоты в ассортименте только 4 позиции. Продукцию регулярно покупает пара десятков клиентов, которые находятся в разных регионах. Каждая сделка заносится в базу данных и представляет отдельную строку.
Ваш директор дает указание сделать краткий отчет о продажах всех товаров по регионам (областям). Решить задачу можно следующим образом.
Вначале создадим макет таблицы, то есть шапку, состоящую из уникальных значений товаров и регионов. Сделаем копию столбца с товарами и удалим дубликаты. Затем с помощью специальной вставки транспонируем столбец в строку. Аналогично поступаем с областями, только без транспонирования. Получим шапку отчета.
Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.
Вы справились с заданием и показываете отчет директору. Посмотрев на таблицу, он генерирует сразу несколько замечательных идей.
— Можно ли отчет сделать не по выручке, а по прибыли?
— Можно ли товары показать по строкам, а регионы по столбцам?
— Можно ли такие таблицы делать для каждого менеджера в отдельности?
Даже если вы опытный пользователь Excel, на создание новых отчетов потребуется немало времени. Это уже не говоря о возможных ошибках. Однако если вы знаете, как сделать сводную таблицу в Эксель, то ответите: да, мне нужно 5 минут, возможно, меньше.
Рассмотрим, как создать сводную таблицу в Excel.
Открываем исходные данные. Сводную таблицу можно строить по обычному диапазону, но правильнее будет преобразовать его в таблицу Excel. Это сразу решит вопрос с автоматическим захватом новых данных. Выделяем любую ячейку и переходим во вкладку Вставить. Слева на ленте находятся две кнопки: Сводная таблица и Рекомендуемые сводные таблицы.
Если Вы не знаете, каким образом организовать имеющиеся данные, то можно воспользоваться командой Рекомендуемые сводные таблицы. Эксель на основании ваших данных покажет миниатюры возможных макетов.
Кликаете на подходящий вариант и сводная таблица готова. Остается ее только довести до ума, так как вряд ли стандартная заготовка полностью совпадет с вашими желаниями. Если же нужно построить сводную таблицу с нуля, или у вас старая версия программы, то нажимаете кнопку Сводная таблица. Появится окно, где нужно указать исходный диапазон (если активировать любую ячейку Таблицы Excel, то он определится сам) и место расположения будущей сводной таблицы (по умолчанию будет выбран новый лист).
Обычно ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.
Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.
В верхней части панели находится перечень всех доступных полей, то есть столбцов в исходных данных. Если в макет нужно добавить новое поле, то можно поставить галку напротив – эксель сам определит, где должно быть размещено это поле. Однако угадывает далеко не всегда, поэтому лучше перетащить мышью в нужное место макета. Удаляют поля также: снимают флажок или перетаскивают назад.
Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.
Область значений – это центральная часть сводной таблицы со значениями, которые получаются путем агрегирования выбранным способом исходных данных.
В большинстве случае агрегация происходит путем Суммирования. Если все данные в выбранном поле имеют числовой формат, то Excel назначит суммирование по умолчанию. Если в исходных данных есть хотя бы одна текстовая или пустая ячейка, то вместо суммы будет подсчитываться Количество ячеек. В нашем примере каждая ячейка – это сумма всех соответствующих товаров в соответствующем регионе.
В ячейках сводной таблицы можно использовать и другие способы вычисления. Их около 20 видов (среднее, минимальное значение, доля и т.д.). Изменить способ расчета можно несколькими способами. Самый простой, это нажать правой кнопкой мыши по любой ячейке нужного поля в самой сводной таблице и выбрать другой способ агрегирования.
Область строк – названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.
Область столбцов – аналогично строкам показывает уникальные значения выбранного поля, только по столбцам. Названия столбцов – это также обычно качественный признак. Например, годы и месяцы, группы товаров.
Область фильтра – используется, как ясно из названия, для фильтрации. Например, в самом отчете показаны продукты по регионам. Нужно ограничить сводную таблицу какой-то отраслью, определенным периодом или менеджером. Тогда в область фильтров помещают поле фильтрации и там уже в раскрывающемся списке выбирают нужное значение.
С помощью добавления и удаления полей в указанные области вы за считанные секунды сможете настроить любой срез ваших данных, какой пожелаете.
Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».
В результате мы получаем настоящую сводную таблицу.
На ее построение потребовалось буквально 5-10 секунд.
Работа со сводными таблицами в Excel
Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.
Заменим выручку на прибыль.
Товары и области меняются местами также перетягиванием мыши.
Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.
На все про все ушло несколько секунд. Вот, как работать со сводными таблицами. Конечно, не все задачи столь тривиальные. Бывают и такие, что необходимо использовать более замысловатый способ агрегации, добавлять вычисляемые поля, условное форматирование и т.д. Но об этом в другой раз.
Источник данных сводной таблицы Excel
Для успешной работы со сводными таблицами исходные данные должны отвечать ряду требований. Обязательным условием является наличие названий над каждым полем (столбцом), по которым эти поля будут идентифицироваться. Теперь полезные советы.
1. Лучший формат для данных – это Таблица Excel. Она хороша тем, что у каждого поля есть наименование и при добавлении новых строк они автоматически включаются в сводную таблицу.
2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты по месяцам в отдельных столбцах.
3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.
4. Применяйте правильное форматирование к полям. Числа должны быть в числовом формате, даты должны быть датой. Иначе возникнут проблемы при группировке и математической обработке. Но здесь эксель вам поможет, т.к. сам неплохо определяет формат данных.
В целом требований немного, но их следует знать.
Обновление данных в сводной таблице Excel
Если внести изменения в источник (например, добавить новые строки), сводная таблица не изменится, пока вы ее не обновите через правую кнопку мыши
или
через команду во вкладке Данные – Обновить все.
Так сделано специально из-за того, что сводная таблица занимает много места в оперативной памяти. Чтобы расходовать ресурсы компьютера более экономно, работа идет не напрямую с источником, а с кэшем, где находится моментальный снимок исходных данных.
Зная, как делать сводные таблицы в Excel даже на таком базовом уровне, вы сможете в разы увеличить скорость и качество обработки больших массивов данных.
Ниже находится видеоурок о том, как в Excel создать простую сводную таблицу.
Скачать файл с примером.
Поделиться в социальных сетях: