Pull to refresh

Семейный бюджет в Google Docs v3

Reading time 6 min
Views 42K
C Новым Годом всех, кто не может в праздники без мозговой деятельности и без хабра.
Продолжаю полюбившуюся серию постов (раз, два) про теорию и практику ведения семейного бюджета в Google Docs.

Я честно вел свой бюджет, написанный в Google docs год назад, и родил новую версию и новую философию (для себя) ведения бюджета. Чем и спешу поделиться.

Вступление

Я знаю, что есть много специализированных систем для семейного бюджета вроде 4 Конверта или ИзиФинанс, но я люблю все делать сам, ибо это позволяет сделать то, что сам хочешь и своим детищем пользоваться приятнее.

Основные преимущества ведения бюджета на Google Docs:

1. Можно заполнять откуда угодно (ноутбуки, телефоны, айпады) — с помощью Google forms
2. Можно самому разрабатывать логику и визуализацию
3. Надежность и почти 100% отказоустойчивость обеспечена корпорацией зла :)

Немного философии.

Зачем мы начинаем вести бюджет? У каждого есть свои причины: понять, почему за 10 дней до ЗП кончаются деньги; понять, почему долги по кредиткам только растут; понять какие расходы лучше сократить или как накопить денег на новую машину.
Многие финансовые системы предлагают нам завести целую уйму статей бюджета и записываться свои расходы в них, не задумываясь, а зачем нам все это. Я в самом начале пошел тем же путем и столкнулся с тем, что расходы почти невозможно анализировать, т.к. часть из них были явно не постоянными (покупка ноутбука или костюма — явно расходы «по необходимости/желанию») и планировать и анализировать их невозможно.
Сферический бюджет в вакууме — это две статьи: «приход» и «расход». Каждый день вписываете расходы и доходы и смотрите, что получается. Потом у вас появляются мысли, а что мы хотим анализировать? Кто-то хочет посмотреть, а сколько в итоге уходит денег на авто (бензин, страховка, налоги, ремонт, мойка), кому-то интересно понять, сколько он тратит на развлечения (походы в рестораны, кино, бары).
Я советую делить статьи расходов не более чем на 10-15 позиций исходя из того, что вы хотите анализировать в этом году/квартале.

Мои статьи расходов выглядят примерно так:
image

А самое интересное — как это сделать в Гуглодоксах с подробными инструкциями и формулами и примеров — под катом.


Давайте начнем с самого начала.

Создаем spreadsheet в Google Docs.
И делаем на нем ФОРМУ для заполнения —
image

В итоге данные с этой формы будут попадать на лист, который я назвали «F-0» — типа сырые данные.
image

Данные тут простые:
— дата (проставляется автоматом, что очень приятно, хотя можно ручками изменить)
— расход (сумма)
— доход (сумма)
— комментарий
тут мы ставим тэг с ключевым символом двоеточие. Тэги задаются на листе PLAN. Тэги позволяют нам разбить расходы по статьям. В теории можно сделать выбор тэга из списка, но я решил, что буду писать их руками — 3-4 буквы не страшно. После двоеточия можно писать любой комментарий, чтобы потом не забыть или проанализировать, стоит делать для значимых и больших покупок.
— источник
лично мне для анализа важно понимать состояние по кэшу и каждой моей кредитке, поэтому я выбираю, откуда я плачу каждую сумму. При это дебетовую карту и кэш я приравниваю к кэшу.

В итоге моя форма с айфона выглядит вот так:
image
Купил продукты — открыл айфон — сделал запись. Все просто.

Сверху — напоминалка, какие у меня Тэги, ссылка на последние 10 записей, ссылка на лист с анализом — обо всем этом по ходу текста ниже.

Далее эти данные обрабатываются на вкладке «F-1», но о ней чуть позже. Сначала давайте подумаем и спланируем наши расходы и доходы.
Тут еще одно из моих ноу-хау (ну по крайней мере я такого подхода еще не видел). Как планировать, скажем, расходы на авто, куда мы хотим включить не только постоянные ежемесячные расходы на бензин и мойку, а еще и ремонт (ТО), страховку и налоги + техосмотр? Расходы постоянные просто — 3400 в месяц, а вот в марте у нас страховка на 32 000, в августе и ноябре будет ТО на 7К и 11К. Ясно, что есть непредвиденные расходы на тот же автомобиль — но на то они уже и непредвиденные :), что их нельзя спланировать.

Решение: я сделал таблицу с коэффициентами на каждый месяц. Вот так:
image

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

Конечно, можно выделить страховку авто в отельную статью, так же как и ТО-40000 и ТО-50000 и еще налог, но как я уже писал выше — мне не нужно анализировать расходы на ТО, а хочется видеть расходы на авто в целом. А вам? Google docs позволит настроить это под себя самостоятельно:)

Идем дальше и перед тем, как перейти к хитрой логике в «F-1», посмотрим, что мы хотим в итоге получить.
Основная страница (лист) для анализа текущего «финансового благополучия» — «A month», что означает Анализ месяца. Вот так компактно на одной страничке я вижу свое текущее положение и могу посмотреть историю по месяцам.
image

1. Сверху мы выбираем год и месяц для анализа. (P.S. Год пока только один и выбор не работает)
Недавно в Гуглодоксах появился выбор из списка
image

2. Ниже у нас автоматически проставляется планируемое значение расхода «PLANNED» и фактическая ситуация «FACT».
image
Все собирается с формы «F-1» и при добавлении записи — через секунду уже обновляется.
Потом идет процент выполнения плана — если значение подходит к критическим 93% — подкрашиваем красным.
image
(Можно и целую градацию сделать — добавив 3-4 правила: зеленый, желтый, красный, коричневый — как пробки на Яндексе)

3. Самая нижняя ячейка CORR — это корректировка.
image
Часто бывает нужна для внеплановых ситуаций. Допустим вы заплатили по карточке за телевизор друга, а он отдал вам наличные. Вроде деньги с карточки потратили, а при этом наличка пришла. Или по итогам месяца поняли, что на самом деле у вас на 2000 рублей больше в кошельке, чем в системе — нужно корректировка.
Кстати такой статьи как корректировка я редко встречал в «специализированных системах». У меня она показывает сколько вы скорректировали в плюс, а сколько в минус.

4. Справа (сверху) 5 строчек под заголовком «Last 10 records» показывают 5 последних записей.
image
Почему пять а не 10? Догадайтесь сами :)
Алгоритм тут хитрый — зашит он на вкладке «OTH», куда сваливается вся служебная инфа и расчеты.
image

ищем последнюю запись (каждая нумеруется в возрастающем порядке) — т.е. нужно найти максимум и сопоставить его со строчкой в таблице «F-1». Потом сделать посмотреть 5 строчек вверх от последней (нижней) записи.

Зачем это нужно? я расшариваю лист «A month» как web-страницу и диапазон ячеек под last 10 records — можно быстро и дешево в плане трафика посмотреть. Допустим забыли вы, ввели в бюджет коньки купленные вчера или нет — вам поможет Last 5 records.

5. Ниже справа — мое состояние по карточкам и наличке.
image
Видно сколько какому банку я должен в рублях и процентах. Ежемесячно я делаю сверку и смотрю по каждой карте и налику — сколько есть по факту и сколько в системе — и делаю корректировку.

Ну вот и все про лист планирования — перейдем к логике в «F-1»
Выглядит она вот так:
image
Давайте разберем по колонкам:

1. столбцы с «B» по «F» тупо дублируют записи из листа сырых данных «F-0». Хотя не совсем тупо. Гуглодокс почему-то не захотел принимать просто формулу типа "=A1!F-0" и сдвигал записи в листе F-1 как только добавлялась новая в F-0, поэтому пришлось исхитриться и формула для «тупого» забора данных из листа F-0 выглядит так:
image
где в A2 стоит ROW(), которая возвращает номер строки :) — так мы обошли автоматическое сдвигание формулы при добавлении новой записи в форме.

2. В столбце «H» я делаю свою любимую фичу в экселе — вспомогательный столбец,

который закрывает дыру в Гуглодоксах, что у него нет формулы SUMIFA, которая позволяет суммировать значения учитывая не один, а несколько критериев. В итоге в столбце «H» я получил уникальное значения для совокупности «год», «месяц», «Тэг», что дает мне право суммировать по нему в листе анализа:
image

3. В столбце SUM — просто суммирую доход и расход, чтобы в анализе смотреть на один и тот же столбец.
Задумался… а зачем мне тогда два разных поля в форме «income» & «expense»… ведь каждая сумма тэгируется и ясно это доход или расход! Ладно — поправим позже :)

4. В столбце «REC No for Last 10» — как раз происходит добавления номера записи по смешной формуле:
=IF(C3+D3>0;J2+1;J2) Она мне ловит «пустые» записи.

5. В столбце «Test wrong category» — идет настоящая проверка на дурака — вводя Тэг просто ошибиться — тут важно подмигнуть красным, если ввел не тот тэг.
image

6. Дальше идет отнесению суммы к тому или иному источнику (карты, кэш, конверты) — сумма просто ставится со знаком плюс или минус и на листе Анализа суммируется. Кстати первоначальные значения денег на момент начала бюджета так же заложены в отдельные ячейке в листе план.

Вот на листе анализа система сначала смотрит на первоначальное значение на СИТИБанке а потом суммирует все значения из колонки CITI
image

Казалось так долго и сложно все это обдумывал и создавал архитектуру решение — а описать получилось за час. На закуску:

1. Расшаренная web-страница файла (без формул к сожалению) spreadsheets.google.com/pub?key=0Aht4cFRJGkY7dEhHb3RqdzhUMTZCMXlPbzJ1T3kxZHc&output=html

2. копия файла в экселе — там формулы видны — narod.ru/disk/2677466001/HABR%20Budget%202011.xls.html
Кстати почему-то формула IFERROR (ЕСЛИОШИБКА) — не конвертируется в русский эксель — пришлось руками перебивать :)

Наверное что-то забыл или недорассказал, — но для этого у нас комментарии и UPD. в постах — поэтому буду рад обсудить и принять все ваши мнения на счет личных финансов.
Tags:
Hubs:
+90
Comments 90
Comments Comments 90

Articles