Сравнить доходную часть городского бюджета в 2012 и 2013 годах в эксель
Обновлено: 06.07.2024
Некоторое время назад в нашем блоге была статья о том, как показать цифры в БДР с помощью сводных таблиц, диаграмм и Power Query. Тогда был подготовлен Dashboard в Excel и оформлен в деловом стиле: минимум цветов, все четко и понятно. На самом деле с оформлением можно было «поиграть» и сделать более интересные решения по цветам и акцентам. А можно «перегрузить» отчет и в погоне за красотой сделать его нечитаемым. Короче, сегодня будем говорить о том, как можно оформить бюджеты.
Файл с бюджетом тоже приложен. Как всегда, его можно скачать, разобраться как он устроен и использовать в работе. В файле несколько листов:
- Лист «данные»: бюджет, отформатированный в виде smart-таблицы Excel.
При добавлении новых данных: а) переместите границы smart-таблицы, б) задайте нумерацию столбцов в верхней строке и в) заполните столбец «показатель» с помощью выпадающего списка. - Вспомогательные листы. «1» — результат преобразования данных бюджета с помощью Power Query, и «2» — лист, где хранятся сводные таблицы.
- Сами отчеты находятся на листах «БДР» и «бдр1»-«бдр5». Они оформлены в разных стилях, но цифры показывают из одного бюджета (на листе с данными).
Отчет сделан универсальным на случай, если в вашем Excel нет надстройки Power Pivot. Поэтому чтобы цифры в графиках обновились при изменении данных, нужно нажать кнопку в меню Данные → Обновить х два раза подряд: первый раз для выполнения запроса Power Query, второй – для формирования сводных таблиц.
Итак, как оформить Dashboard для бюджета.
Вариант 1. БДР в «классическом» стиле
Классический способ оформления отчета – с помощью белого, черного и серого цветов. Именно такое цветовое решение рекомендует компания McKinsey для создания презентаций. А белый фон, по мнению различных экспертов по визуализации, лучше всего подходит для восприятия информации.
В своем бюджете мы немного отступили от «радикально»-классического варианта и добавили один цветовой акцент – на динамику изменения чистой прибыли. Все-таки большинство компаний (хотя и не все) при анализе БДР в первую очередь обращают внимание на прибыль.
А вот что об акцентах с помощью цвета пишет Стивен Фью: «Когда какой-либо цвет появляется как контраст с нормой, наши глаза обращают на это внимание, а мозг пытается придать смысл различиям». Теперь посмотрите на картинку ниже. Ну как, работает прием или нет?
Вариант 2. БДР по мотивам The Economist
Следующий пример цветовых решений для отчета – по мотивам журнала The Economist. Если вы видели этот журнал, то точно узнаете цвета на картинке ниже. Синий фон появился в диаграммах журнала в 2001 году (в последнее время фон также чаще светлый и белый ).
Ссылки для скачивания:
Планирование доходов и расходов. Виды потребностей.
Прежде чем мы перейдем к изучению таблицы учета доходов и расходов в excel стоит затронуть тему планирования как семейного, так и личного бюджета. Ни для кого не секрет, что наши потребности делятся на базовые первостепенные и второстепенные, навязанные нам реалиями современного маркетинга. Таким образом, главным ключом к эффективному планированию доходов и расходов бюджета является удовлетворение основных жизненных потребностей и минимизация расходов на необязательные нужды.
Какие же потребности можно отнести к необходимым базовым?
- Физиологические потребности. Не будем перечислять их все, только те, которые относятся к нашим расходам. А это потребность в пищи, воде, одежде и жилье (сюда отнесем оплату самого жилья и коммунальных услуг). Данные нужды непосредственно влияют на наше физическое и эмоциональное состояние, поэтому их нельзя игнорировать и необходимо удовлетворять в первую очередь.
- Потребность в безопасности. В первую очередь сюда можно отнести защищенность собственного здоровья и здоровья членов наших семей. Человек должен быть уверен, что ему ничего не угрожает и, в случае возникновения непредвиденных обстоятельств (например, болезни), он и его семья смогут получить квалифицированную помощь. Сюда же относится и покупка лекарств, что особенно актуально в наше непростое время.
- Потребность в комфорте. Наше психоэмоциональное состояние напрямую зависит от условий комфорта нашей жизни. Но здесь нужно видеть тонкую грань между необходимыми потребностями, такими как: удобная кровать для сна или обувь для работы, и навязанными: последняя модель телефона, компьютерные игры и т.д..
Инструкция макроса учет доходов и расходов в excel
Макрос создаст 3 таблички: Перечень доходов, Перечень расходов и Сравнение итогов. Итак, Вы успешно создали шаблон на месяц для учета своих денежных потоков!
Ваши дальнейшие шаги по ведению учета доходов и расходов в excel:
Послесловие
Спасибо за прочтение данной статьи! Скачивание программы является абсолютно бесплатным. Буду рад, если данная программа окажется полезной для Вас, с удовольствием отвечу на Ваши вопросы в комментариях.
Ссылки для скачивания:
Здесь можно ознакомиться с другими программами автоматизации, которые представлены на данном сайте:
В Т—Ж вышло много статей про эксель-таблицы для ведения бюджета.
В них, в отличие от приложений, нет рекламы, их можно использовать бесплатно и настраивать функционал на свой вкус.
Мы выбрали шесть популярных таблиц в одной статье и сравнили их способности: выбирайте, где вам удобнее вести бюджет.
План-2035
Главная цель: построить долгосрочный финансовый план для семьи и накопить на пенсию.
Как устроена таблица. Основная идея таблицы — составить план на год уже в первые дни января и отслеживать его выполнение каждые две недели.
В годовой финансовый план вносятся все планируемые доходы: зарплаты, авансы и отпускные, кэшбэк, налоговые вычеты и подарки.
А расходы делятся на четыре блока:
- Обязательные — услуги ЖКХ и еда, накопления, детские секции, ипотека.
- Автомобиль — бензин, ремонт, страхование, налоги и штрафы.
- Дом — налоги и строительство.
- Семья — одежда, подарки, здоровье и отпуск.
Основные функции. В начале каждых двух недель авторы таблицы заполняют планируемые доходы и расходы на этот период, а потом смотрят, по каким категориям превысили план и почему.
Таблица анализируют расходы, помогает держать в памяти обязательные платежи и дает понять, может ли семья позволить себе спонтанные траты вроде ремонта машины или их надо отложить на другой месяц.
В статистике за месяц и за две недели видно разницу между доходами и расходами, а также долю, которую съедает из бюджета каждая категория тратТаблица показывает, какой остаток получается на конец года, в каких месяцах есть риск потратить больше и как поживают накопления на важные категории. Для авторов таблицы важные категории — инвестиции, отпуск, ремонт и одежда. Деньги на них считаются как фиксированный процент от месячного дохода. В сумме набегает 56% — именно эта доля направлена на основные жизненные цели. Оставшиеся 44% уходят на повседневные нужды.
Приятные мелочи. Если в какой-то месяц остается меньше 10 тысяч свободных денег, загорается тревожная кнопка «Мало!».
В чем сложность. Не каждый сможет спланировать основные доходы и расходы на год вперед, а количество цифр и столбцов с первого взгляда немного отпугивает.
Бюджет на 1000 дней
Главная цель: сократить расходы по отдельным категориям без снижения уровня жизни. Для этого при планировании бюджета на месяц основное внимание уделяется не средним значениям, а расходам за этот месяц в прошлые годы.
Как устроена таблица. Для записи расходов есть три вкладки: «Повседневные», «Крупные» и «Квартира».
В первую отправляется большинство расходов: продукты, бары, транспорт, кино, подарки и другие.
Каждой трате присуждается категория. Категорию нужно выбирать из выпадающего спискаДля детального анализа есть подкатегории. Они автоматически подтягиваются при выборе категории, и их также можно выбрать из выпадающего списка. Так, в категории «Транспорт» нужно выбрать такси, метро, автобус, электричку или что-то другое, а в «Ресторанах» — бары, рестораны, бургерные, доставку и кальянные.
Не в каждом городе есть метро и не все курят кальян, поэтому набор категорий легко настроить под себя.
Во вкладку «Крупные траты» создатель таблицы заносит расходы на путешествия, абонемент в спортзал и покупки дороже 15 000 Р . Так, майка за 2000 Р и зимняя куртка за 20 000 Р отправятся в разные вкладки, хотя относятся к одежде: майка — в «Повседневные», а куртка — в «Крупные».
На вкладку «Квартира» уходит все, что связано с недвижимостью — ипотека, аренда, услуги ЖКХ и ремонт.
Чтобы на все хватало и даже оставалось. Дважды в неделю рассказываем в нашей рассылкеОсновные функции. Данные о расходах собираются на первой вкладке в интерактивные диаграммы и помогают понять, куда уходит бюджет.
Здесь можно увидеть распределение расходов по категориям и средние расходы по месяцам и дням недели. Благодаря этому можно заметить, как увеличиваются траты на подарки в декабре, на алкоголь — в январе, и успеть подготовиться к перепадам.
Для каждой категории трат в таблице есть своя вкладкаПриятные мелочи. На главной есть средний бюджет на месяц и интерактивная вкладка, которая обзывает транжирой, если дневной лимит расходов превышен.
На вкладке «Справочники» есть инструкции, которые помогают быстрее разобраться в функциях таблицы.
Некоторые подсказки автор оставил прямо в описании сводных таблиц. Это помогает не запутаться в формулах и не сломать все при настройке под себяВ чем сложность. Нужно терпеливо вести таблицу хотя бы три месяца, чтобы анализ данных имел смысл.
Сделай сам!
Главная цель: сделать таблицу, в которой не придется заполнять бесполезные вкладки и которая на 100% подойдет под ваши расходы и экономические цели.
Как устроена таблица. Это упрощенная версия таблицы «1000 дней», которую легко настроить под себя.
Траты здесь также вносятся на трех вкладках: «Повседневные», «Крупные» и «Квартира».
Для каждой покупки надо указать категорию, стоимость и дату, чтобы потом найти нужный деньНеобязательно пользоваться стандартными категориями. Можно удалить ненужные, заменить их или добавить новые.
Для этого на последней вкладке — «Справочники» — нужно изменить содержимое ячеекОсновные функции. Таблица показывает, как вы придерживаетесь лимита расходов, и предостерегает от лишних трат. Кроме этого, на ней видно, сколько в месяц уходит на каждую категорию, процент расходов по дням недели и подробный анализ повседневных трат.
Автор таблицы делает упор на ежедневные расходы, ведь именно в них прячутся спонтанные покупки, которые съедают бюджет. Да, абонемент в зал влияет на картину, но это не то, с чем нужно бороться.
Суммы повседневных трат можно оценить по дням недели и категориямНа вкладке «Динамика» можно сравнить расходы в этом месяце со средним значением за последние полгода, но для этого нужно вести таблицу не меньше этого времени.
Голубая линия показывает рост или падение расходов. Синяя — среднее значение трат за месяцПриятные мелочи. Есть подробная инструкция. Можно быстро понять, как работают формулы, и оставить только нужные функции.
Первая вкладка не даст забросить хорошее начинание. Она напомнит вовремя внести расходы и скажет «Пока все в норме», если расходы не перешагнули через дневной лимит, или «Начни уже экономить!» — если вы потратили больше, чем заработали.
В чем сложность. В таблице нет раздела с доходами, поэтому, если траты растут синхронно с заработком, этого никто не оценит.
Бюджет с IFTTT
Главная цель: вести таблицу со смартфона.
Как устроена таблица. Главная фишка таблицы — простой ввод данных и быстрый доступ к информации. Для этого придется потратить около 10 минут для интеграции с сервисами IFTTT и «Гугл-формами» — это сервис, который помогает вносить изменения в таблицу в простом окне вместо открытия громоздкой конструкции. IFTTT — это бот для мессенджеров, который берет данные из общей таблицы и рассылает оповещения об остатке и превышении лимита.
Таблица делит расходы на три группы: «Постоянные», «Крупные» и «Повседневные».
«Постоянные» — ипотека, услуги ЖКХ, мобильная связь, подписки и все, что повторяется каждый месяц. «Крупные» — это нерегулярные большие покупки. Например, расходы на ремонт и отпуск, покупка бытовой техники, гаджетов и автомобиля. «Повседневные» — это все остальное: продукты, бытовая химия, развлечения и другие ежедневные траты.
Расходы можно заносить в «Гугл-форму» даже не открывая таблицу: для этого надо вынести ярлык на экран смартфона и открывать как приложение После этого бот IFTTT отправит уведомление об остатке на день в удобном мессенджереОсновные функции. Все данные собираются в сводных таблицах на вкладке «Дашборд», и это единственная вкладка, куда нужно заходить после настройки таблицы. Тут можно выбрать месяц и разбить траты по основным категориям, а также посмотреть динамику повседневных расходов от месяца к месяцу.
Все траты отображаются в виде графиков и диаграмм. Можно выбрать категорию и посмотреть детальный отчетПриятные мелочи. Есть подробная инструкция, как вместо сводных таблиц на вкладке «Дашборд» собирать красивые отчеты в Datastudio.
Теперь по соседству с графиками повседневных трат на «Дашборде» есть диаграммыВ чем сложность. Для удобной работы с таблицей нужно подключить сторонние сервисы: «Гугл-формы», IFTTT, Datastudio.
Личный бюджет на год
Главная цель: спланировать бюджет на год в небольшой и понятной таблице.
Как устроена таблица. Финансовый план на год помещается на одной вкладке. Чтобы понять, на что получится накопить, нужно помесячно вносить планируемые доходы, расходы, распределять остатки по финансовым целям и просчитывать непредвиденные траты.
Доходы учитывают зарплаты, премии, налоговые вычеты и прочие источники. В последний пункт попадают подарки, деньги от сдачи в аренду квартиры, фриланс и другие специфические поступления. Стандартные расходы делятся на повседневные, кредитные, квартирные и автомобильные.
Основные функции. Если в каком-то месяце расходы превышают доходы, он выделяется красным цветом. В этом случае надо сократить крупные траты или перенести остаток с другого месяца — для этого есть строчка «Перенос».
Когда ни в одном месяце нет дефицита, можно распределить остатки на основные цели. По умолчанию это финансовая подушка, отпуск, новая машина и досрочное погашение ипотеки, но можно добавить любой близкий сердцу пункт.
Остатки распределяются в процентных долях, которые вы задаете в самом начале использования таблицы.
Остатки распределяются в процентных долях. При этом сразу видно, сколько нужно откладывать, чтобы собрать определенную сумму к дедлайнуПриятные мелочи. Есть инструкция по планированию бюджета. Автор построчно объясняет что, куда и зачем записывать в таблицу.
Также в ячейки можно добавлять напоминания: продлить ОСАГО, подать документы на вычет или пройти техобслуживание авто.
Напоминания остаются в виде небольших меток в каждой категории и не мешают работать с таблицейВ чем сложность. Нет анализа повседневных трат. Они попадают в бюджет одной суммой, которую нельзя поделить на категории и уменьшить.
Семейный бюджет за 15 минут в неделю
Главная цель: накопить деньги без ежедневного учета расходов.
Как устроена таблица. Финансовый план состоит из одной таблицы, которую нужно каждую неделю копировать и начинать с чистого листа. Новый период начинается с блока «Остатки». В нем отметьте, сколько осталось денег на разных счетах, и все отрицательные активы — займы и долги по кредиткам.
В блок «Ежедневные расходы» занесите основные данные по расходам в течение месяца, а блоке «Фонды» нужен для учета остатков на месяц и накоплений на долгосрочные цели.
Планирование нового периода начинается с подведения итогов прошлого в блоке «Остатки»Блок финансовых операций разделен на три группы: «Регулярные расходы», «Поступления» и «Разное».
«Регулярные расходы» — это все повседневные траты. Тут не обязательно учитывать все до копейки, достаточно указать средний расход за месяц. Чтобы было проще, выходные и будние дни выделены разным цветом, так как траты в праздники обычно выше.
В «Разное» попадают все остальные покупки, а в «Поступления» — зарплаты, дивиденды и другие доходы.
Так как повседневные траты записываются средней суммой, а поступления и другие расходы бывают нечасто, заполнять таблицу можно раз в неделюОсновные функции. Так как главная цель таблицы — накопления, все самое интересное происходит в блоке «Фонды».
В столбцах «Обороты по фондам» нужно заносить каждую трату. Чтобы не запутаться, автор предлагает оставлять комментарии в ячейках: например, по скриншоту понятно, что 2306 Р потратили в «Икее»Светло-желтые строчки — это запасы, которые можно тратить в течение месяца. Если что-то останется — хорошо. Если нет — ничего страшного. Столбец «Баланс» автоматически считает остаток на каждую категорию по мере записи расходов.
Темно-желтые строки — это цели, на которые нужно накопить. Сумму, которая в конце месяца остается в строке «Баланс», можно добавить к любой цели. Если во время планирования в остатке получается отрицательное значение, нужно пересмотреть категории расходов: значит, где-то потратили больше планируемого.
В чем сложность. Так как таблица не анализирует мелкие расходы, а только ограничивает их общие суммы, нужна большая сила воли, чтобы не спустить бюджет на кофе с собой.
Российский бюджет в номинальных ценах с 2003 года вырос в пять раз по доходам и в пять с половиной раз по расходам. И пусть с учетом инфляции увеличение выглядело не таким существенным, все равно очевидно: правительство наращивало и наращивало расходы вплоть до 2014 года, когда кризис и боязнь несбалансированности бюджета заставили чиновников остановиться. «Лента.ру» решила посмотреть, какие именно статьи расходов выросли за последнее десятилетие больше всего.
В принятом за несколько дней до наступления нового, 2003 года бюджете говорилось, что доходы страны составят 2,4 триллиона рублей, а расходы 2,3 триллиона, или чуть менее 18 процентов ВВП. В открытой части бюджета было распределено более 90 процентов расходов, в секретной — около девяти с половиной.
Через 10 лет бюджет на следующий год президент подписал почти за месяц до Нового года — планирование в правительстве явно улучшилось, да и в Госдуме чинить препятствия бюджету стало некому (до декабря 2003 года в России заседала Госдума третьего созыва, где большинства не было ни у одной из партий, а КПРФ располагала четвертью парламентских мест). В бюджете-2014 доходы страны составляют уже 12,8 триллиона рублей, расходы — 13,4 триллиона (больше 20 процентов ВВП), а в открытой части распределяется менее 86 процентов расходов.
Очевидное увеличение расходов (как в абсолютных цифрах, так и по отношению к ВВП) и рост «закрытой» части — далеко не единственные выводы, которые можно сделать из погодового сравнения бюджетов за десятилетие.
Особенность российского бюджета заключается в том, что принятый накануне нового года документ может подвергаться значительным изменениям уже во время отчетного периода. Обычно правок бывает не меньше двух, и часто они весьма существенно меняют конфигурацию бюджета. Классический пример — бюджет 2009 года, сверстанный изначально с профицитом и прогнозом объема ВВП в 51,5 триллиона рублей. Уже в апреле профицит сменился дефицитом почти в три триллиона рублей, а бюджетный прогноз по ВВП сократился до 40,4 триллиона. При этом доходы страны существенно упали, а расходы выросли на несколько сот миллиардов рублей.
Из-за этих изменений считать структуру бюджетных расходов в течение десятилетия довольно сложно. В инфографике «Ленты.ру» схематично отражены только те бюджетные показатели, которые подписывались накануне отчетного периода, а в текстовом пояснении учтены конечные данные, в том числе и для тех бюджетов, которые подверглись изменениям. Для 2014 года использовались данные, зафиксированные в федеральном бюджете на 2013 год и прогнозный период 2014-2015 годов.
Постатейному анализу «Лента.ру» подвергла только самые интересные статьи расходов, традиционно вызывающие широкую общественную дискуссию: «социальная политика», «национальная оборона», «образование», «здравоохранение», «национальная безопасность и правоохранительная деятельность, «культура», «физическая культура и спорт», а также «обслуживание государственного долга».
На протяжении всего десятилетия правительство наращивало расходы практически по всем направлениям. Наиболее разительное исключение составляет обслуживание госдолга, на который в 2003 году пришлось почти 12 процентов от всего количества расходов. Уже к 2005 году этот показатель упал до восьми процентов, а абсолютный минимум на уровне двух процентов был достигнут по итогам 2009 года. Объясняется такой феномен довольно просто: в условиях высоких цен на нефть Россия сумела досрочно оплатить долги советского времени и 1990-х годов, в том числе 21 миллиард долларов Парижскому клубу кредиторов в 2007 году. После начала мирового финансового кризиса выплаты по долгам вновь начали нарастать, в проекте бюджета на 2014 год на эти цели выделяется уже 452 миллиарда рублей, или 3,2 процента от всех расходов.
В статью «национальная оборона» входят расходы на вооруженные силы страны, в том числе закупки вооружения, а в статью «национальная безопасность» — траты на органы прокуратуры, органы внутренних дел, внутренние войска, органы юстиции, систему исполнения наказаний, органы безопасности и другие ведомства, в том числе и МЧС.
Другие резкие колебания связаны с техническими моментами. Так, обращает на себя внимание увеличение трат на «социальную политику» с 3,3 процента от общих расходов в 2010 году до 27,69 процента в 2011 году. Это связано с тем, что в соответствующую статью бюджета стали включать трансферты в Пенсионный фонд, которые составляют более двух триллионов рублей. Соответственно, уменьшение трат по этой статье расходов (по плану на 2014 год — с 3,9 до 3,5 триллиона рублей, или с 29 до 25 процента от бюджета) связано в первую очередь с надеждами на уменьшение трансфертов и повышение самостоятельности пенсионной системы.
По статье «Национальная оборона» расходы (в отношении к бюджету в целом) падали вплоть до 2009 года — если в 2003 году они составляли 9,4 процента от общих расходов, то в 2009-м — менее семи. В 2008 году Россия провела свою первую в новейшей истории войну на территории другого государства (с Грузией) и после этого объявила о масштабной госпрограмме перевооружения (несогласие с ней стоило кресла министру финансов Алексею Кудрину). В итоге доля военных расходов сначала стабилизировалась, а потом стала понемногу расти — вплоть до 7,5 процента от расходов 2013 года. Очевидно при этом, что секретная часть росла быстрее, чем общедоступная, но сказать, насколько именно она увеличилась по конкретной статье из открытых данных, невозможно.
В несколько раз (опять-таки в процентах от общих расходов) за 10 лет выросли инвестиции в здравоохранение. Если в начале 2000-х они колебались на уровне полутора-двух процентов, то в 2012-м составили 4,25 процента, после чего последовал значительный спад (3,7 процента по плану на 2013 год, 3,27 процента на 2014-й). Снижение зафиксировано даже в абсолютных цифрах — 545 миллиардов в 2012-м против 495 в 2013-м и 457 в 2016-м. Не исключено, правда, что часть денег просто перешла в какие-то другие программы и исчезла из этой статьи расходов.
Для инфографики «Лента.ру» выбрала далеко не самые «затратные» статьи расходов. Так, по статье «Общегосударственные вопросы» в 2014 году Россия должна потратить около 800 миллиардов рублей, по статье «Национальная экономика» — 1,7 триллиона. На фоне этих сумм достаточно скромными выглядят затраты на СМИ (65 миллиардов рублей), из которых более 52 миллиардов придутся на телевидение и радиовещание.
Наконец, статья «национальная безопасность и правоохранительная деятельность» с 2012 года забирает по 10-11 процентов от всех бюджетных расходов против семи-восьми процентов на протяжении всего предыдущего десятилетия. Губернатор Кировской области, делая выкладки по бюджету региона-2012, отмечал, что полицию стал полностью финансировать федеральный центр, поэтому у регионального бюджета расходы сократились.
Основной вывод, который можно сделать из анализа бюджетных показателей за последние годы, заключается в том, что Россия проводила планомерную политику по увеличению всех типов расходов. Даже объявление о государственных приоритетах (например, на национальную оборону) приводило лишь к небольшому (в масштабах всего бюджета) перераспределению расходов.
Таким образом, становится понятно, почему в условиях, когда понадобилось срезать расходную часть бюджета 2014 года (из-за экономической стагнации), правительство выбрало меру, настолько напоминающую настоящий секвестр, — попросило все министерства сократить расходы на 5-10 процентов, оставив незначительное место на распределение доходов по кажущимся чиновникам профильным направлениям. Кроме того, инфографика «Ленты.ру» объясняет, почему Минфин в поисках дополнительных доходов обратил внимание прежде всего на пенсии и на госпрограмму вооружений: именно по этим направлениям расходы в последнее время росли существеннее всего.
Читайте также: