Компьютер у предпринимателя. Путеводитель по миру автоматизированного учета. Часть 1 «MS Excel»

Путеводитель по миру автоматизированного учета. Часть 1 «MS Excel»

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

Вне сомнений, самое интересное — это программы, позволяющие предпринимателю автоматизировать свой учет: какие они? каковы их функции? сколько стоят? какую из них предпочесть? Предлагаем вашему вниманию путеводитель. А начинаем, как водится, с наиболее дешевого варианта — учет доходов и расходов в MS Excel (бесплатная программа своими руками).

Виталина МИРОШНИЧЕНКО, консультант газеты «Собственное Дело»

Документы статьи

КУоАП

Закон об НДС

Порядок № 599

Инструкция № 12

Опередить конкурентов

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

Тем же, у кого в день до 5 — 10 операций, вроде как нет смысла покупать специальную программу учета. Тем не менее даже в таких обстоятельствах хочется автоматизации. Для этого стоит воспользоваться хотя бы стандартным приложением MS Excel. С его помощью можно быстро и безошибочно подсчитать необходимые итоги, найти нужную запись, ввести важное примечание. Все это экономит время, деньги и нервы.

На сегодняшний день доля предпринимателей, желающих автоматизировать свой учет, превышает 90 %. В ответ на такой спрос появилось множество программ учета и рекомендаций для самостоятельной его автоматизации: от элементарного учета в MS Excel до серьезного в «1С: 8.0». О них, собственно, и пойдет речь.

Ну а тем, кто продолжает придерживаться бумажно-калькуляторного варианта, стоит задуматься, не рискуют ли они отстать от конкурентов.

Книга ф. № 10 (бумажная)

Прежде чем начинать разговор о программах учета, нужно напомнить, что

Зато данные электронной Книги (как, собственно, и всего электронного учета) будут доступны только предпринимателю, так как налоговикам при проверке достаточно предоставить всего лишь бумажную Книгу ф. № 10. Ведь в обязанности предпринимателя:

— входит ведение бумажной Книги ф. № 10 (с приложением документов, подтверждающих расходы);

— не входит ведение бухгалтерского учета каждой операции (предприниматель осуществляет электронный учет исключительно по желанию; в принципе налоговики могут о его наличии вовсе не догадываться).

Следовательно, проверять данные электронного учета у предпринимателя налоговики не будут (не вправе).

MS Excel — для единоналожников

Начнем с самого простого — с организации учета в MS Excel.

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

Учет единоналожника на первый взгляд прост.

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

— заполненную Книгу ф. № 10;

— отчет единоналожника;

— остаток средств в кассе и на расчетном счете на текущий момент;

— остаток товаров (количество) на текущий момент;

— сумму своей задолженности перед поставщиками;

— долги покупателей;

— данные для заполнения декларации по НДС.

На рис. 1 и 2 покажем, как это будет выглядеть в итоге, а затем подробно расскажем, каким образом реализовать такую идею в MS Excel. Потратив на создание предложенных таблиц день (максимум два), в дальнейшем вы сэкономите гораздо больше времени и оцените преимущества автоматизации. Впрочем, можно даже скачать

Рис. 1. Журнал операций и автоматически заполненная Книга ф. № 10

Рис. 2. Автоматическое формирование информации о средствах на расчетном счете, об остатке товаров, о расчетах с контрагентами, а также о НО и НК по НДС в разрезе контрагентов (для заполнения приложения 5 к декларации по НДС)

Создание Журнала операций

Журнал операций представляет собой таблицу, которую нужно заполнять в течение всего года. Шапка таблицы имеет следующий вид:

Вручную в этой таблице нужно будет заполнять следующие колонки:

— «Дата»;

— «№ документа»;

— «Сумма, грн.»;

— «Количество» (товарный учет);

— «Номер налоговой накладной»;

— «Сумма без НДС, грн.»;

— «НО» (налоговое обязательство);

— «НК» (налоговый кредит).

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

«Признак платежа»; «Ставка НДС»; «Использование товара в хоздеятельности»

Для того чтобы быстро заполнять данные ячейки, создадим выпадающий список с вариантами. Как создавать короткий выпадающий список, покажем на примере графы «Ставка НДС». Итак, у нас есть такие варианты:

— ставка 20 %;

— ставка 0 %;

— операции освобождены от НДС;

— операции не являются объектом налогообложения;

— происходит второе событие;

— товар получен/передан неплательщику НДС.

Выбираем первую ячейку графы

Рис. 3. Создание простого выпадающего списка

Графа

Графа

Сделав выпадающий список в первых ячейках, далее копируем их вниз по столбцам таблицы.

«Операции»; «Контрагент»; «Наименование (товарный учет)»

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

На отдельном листе, который назовем «Справочники», создадим

Признак операции нам понадобится в дальнейшем для того, чтобы автоматически считать остаток денег в кассе или на текущем счете, а также для автоматического заполнения Книги ф. № 10.

Как известно, выручку единоналожник отражает в момент поступления денег в кассу или на расчетный счет, а расходы — в момент оплаты денег. Все остальные операции — отгрузка товара покупателю или получение от поставщика — это вспомогательные операции, которые тоже важно учесть ^{справка 1}.

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

=СМЕЩ(Справочники!$B$5;0;

0;СЧЁТЗ(Справочники!$B$5:

$B$60);1)

$B$5 — абсолютное значение ячейки, в которой находится наименование первой операции;

$B$60 — абсолютное значение ячейки, в которой будет находиться наименование последней операции. (Поскольку операции со временем могут добавляться, желательно на будущее предусмотреть несколько пустых строчек. Для этого в формуле следует указать ячейку ниже, чем та, в которой на данный момент записана последняя операция. Так, в примере имеется 19 операций и 36 резервных строк.)

Дальше все просто: открываем лист с Журналом операций, выбираем ячейку в столбце «Операция», идем в пункт меню

Рис. 4. Создание длинного выпадающего списка с помощью справочника

Сделав выпадающий список в первой ячейке, далее копируем ее вниз по столбцу таблицы.

Точно так же создаем списки в столбцах «Контрагенты» и «Наименование товара» (предварительно создав на листе «Справочники» таблицы «Справочник контрагентов» и «Прайс»).

«Признак операции (выручка (В), расходы (Р) или вспомогательная операция (ВО))»

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

=ЕСЛИ(ЕНД(ВПР(Т(B10);

Справочники!$B$5:$C$60;2;0));

””;ВПР(Т(B10);Справочники!

$B$5:$C$30;2;0))

ВПР () — функция, которая ищет в Справочнике признак, соответствующий названию операции, и выдает его в соответствующей ячейке Журнала операций;

В10 — ячейка в Журнале операций, в которой указано название операции;

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

Справочники!$B$5:$C$60 — справочник с названиями операций и их признаками, который находится на листе «Справочники» (с учетом пустых резервных строчек);

2 — номер столбца в таблице «Справочник операций», в котором указан признак операции: «В» — выручка, «Р» — расходы или «ВО» — вспомогательная операция (столбец с названием операции считается первым);

0 — необходимость точного совпадения в названии операции при поиске;

ЕСЛИ, ЕНД — вспомогательные функции, которые выводят пробел, если нужных совпадений не найдено.

Как работает формула, показано на рис. 5.

Заполнив первую ячейку, копируем ее вниз на весь столбец.

«Налоговый номер контрагента»

Данный столбец также заполняется с использованием функции ВПР:

=ЕСЛИ(ЕНД(ВПР(Т(C10);

Справочники!$E$5:$I$55;3;0));””;ВПР(Т(C10);Справочники!

$E$5:$I$55;3;0))

ВПР () — функция, которая ищет в Справочнике контрагентов номер контрагента по его названию и выдает его в Журнале операций;

C10 — ячейка в Журнале операций, в которой указано название контрагента;

Справочники!$E$5:$I$55 — справочник контрагентов на листе «Справочники»;

3 — номер столбца в таблице «Справочник контрагентов», в котором указан налоговый номер контрагента (наименование контрагента считается столбцом № 1, а налоговый номер в Справочнике контрагентов указан в 3-м столбце);

0 — указывает на необходимость точного совпадения в названии контрагента.

Заполнив первую ячейку, копируем ее вниз на весь столбец (см. рис. 6).

«Поступление или отгрузка»; «Сумма, грн. (товарный учет)»; «НДС, грн.»

Указанные столбцы заполняются просто (см. табл.).

Для наглядности приведем рис. 7.

Рис. 7. Пример автоматического заполнения столбцов «Признак: поступление или отгрузка»;
«Сумма, грн.» (товарный учет); «НДС, грн.»

Создание Книги ф. № 10

Единоналожники ведут Книгу учета доходов и расходов по форме, указанной в

Для начала на отдельном листе создадим Книгу ф. № 10:

В ней нужно будет заполнить колонки № 1, 3, 6 и 7.

«Период учета»

Данную графу проще всего заполнить так: в первой ячейке задать 01.01.2010 г., затем выделить весь столбец и в пункте меню «Формат» — «Ячейки» — вкладка «Число» выбрать «Дата». Затем достаточно скопировать ячейку вниз по столбцу. При этом автоматически в каждой следующей ячейке будет указываться следующая дата.

«Расходы на производство»; «Сумма выручки»

Единоналожники заполняют данные графы по итогам дня, значит, нужно суммировать всю выручку и все расходы, произведенные в течение каждого дня. Делается это с помощью двух критериев так:

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

{=СУММ((Критерий 1)

*(Критерий 2)*(Столбец, ячейки которого нужно суммировать, если они находятся в строках, одновременно отвечающих обоим критериям))}

Важно!

Покажем наглядный пример на рис. 8:

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

«Чистый доход, грн.»

Значение чистого дохода предприниматель может подсчитывать раз в месяц или раз в квартал — как ему удобно. Для этого можно подводить в Книге ф. № 10 ежемесячные итоги, а затем из общей суммы выручки вычитать общую сумму расходов ^{справка 2}:

= ЕСЛИ((F37-C37)>0;F37-C37;0),

F37 — ежемесячная сумма выручки

C37 — ежемесячная сумма расходов

Функция ЕСЛИ используется для того, чтобы в случае убытка в поле чистого дохода отображалось значение «0,00» (так как предприниматель не имеет права отражать убытки).

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

Автоматический учет средств в кассе и на расчетном счете

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

Вся эта информация есть в Журнале операций, только ее нужно оттуда выбрать по определенным критериям. Как это сделать, расскажем на примере расчетного счета.

Итак, на отдельном листе, который назовем «Расч_счет», создадим следующую таблицу (фрагмент листа «Расч_счет»):

«Дата»

Заполняется так же, как и «Период учета» Книги ф. № 10.

«Поступление»; «Поступление из кассы»; «Расходование»; «Снятие в кассу»

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

Определимся с критериями, которые нам помогут отобрать нужные суммы в Журнале операций:

Для того чтобы записать это в MS Excel, снова воспользуемся удобной формулой массива:

{=СУММ((Критерий 1)

*(Критерий 2)*(Критерий 3)

*(Столбец, ячейки которого нужно суммировать, если они находятся в строках, одновременно отвечающих трем критериям))}

Важно!

Покажем, как работают указанные формулы, на рис. 9.

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

«Остаток на текущую дату»

Данная графа рассчитывается просто: остаток на начало дня плюс сумма, поступившая от контрагентов и из кассы, минус сумма, израсходованная (уплаченная и снятая в кассу):

F8 = $C$5+B8+C8-D8-E8;

F9 = F8+B9+C9-D9-E9 (эту формулу копируем вниз по столбцу).

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

Затем подкорректируем формулы первой строки таблицы так: во всех формулах заменим «РС» на «К». Таким образом отберутся аналогичные операции (приход денег и их расходование), но только по кассе.

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

Вот и все, в полученной таблице автоматически отобразятся данные по кассе.

Автоматический учет товаров (по количеству)

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

Получается ситуация, похожая на расчетный счет и кассу. Итак, начнем с создания таблицы на новом листе, который назовем «Товары» (фрагмент таблицы «Наличие товаров (количественный учет)»):

Для того чтобы не заполнять названия товаров вручную, сделаем выпадающий список. Для этого выделяем строку 5 (ячейки B, E, H) и выберем пункт меню «Данные» — «Проверка» — вкладка «Параметры», в поле «Тип данных» выбираем «Список», а в поле «Источник» указываем «=Прайс».

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

«Приход»; «Отгрузка»

Для того чтобы отобрать количество товара, который поступил на склад в течение дня, необходимо взять из Журнала те операции, которые отвечают трем критериям:

— осуществлены на определенную дату;

— являются приходом товара (в Журнале операций это признак поступления «П»);

— относятся к конкретному наименованию товара.

То же и в отношении количества отгруженных товаров, только признак в Журнале операций будет «О» (отгрузка).

Таким образом, снова воспользуемся формулой массива:

Внимание: не забудьте, вводя формулу, нажать комбинацию Ctrl+Shift+ Enter.

«Остаток»

После того как первая строка таблицы заполнена, копируем формулы ячеек вниз по соответствующим столбцам.

Далее можно пойти по одному из двух путей:

1) либо выбрать необходимое наименование товара и получить по нему данные;

2) либо сделать еще несколько столбцов, чтобы в дальнейшем получать остатки товара сразу по нескольким наименованиям. Для этого скопируем столбцы по Товару 1 и вставим под Товар 2 и Товар 3 и т. д., а также немного отредактируем формулы.

Что в итоге получается, см. на рис. 10.

Автоматический учет расчетов с контрагентами

Задача следующая: узнать, какова наша задолженность перед поставщиками и какова задолженность покупателей перед нами. Как обычно, все это можно узнать из Журнала операций. Но для того чтобы не отбирать операции и не просчитывать их вручную, создадим таблицу, в которой все долги будут рассчитываться автоматически.

На отдельном листе, который назовем «Контрагенты», обозначаем таблицу следующего вида:

Идея заполнения ячеек та же, что и при учете товара и движения денежных средств: ищем нужные операции в Журнале операций по определенным критериям. После того как операции найдены, суммируем ячейки «Сумма, грн.».

Критериев три:

1) дата;

2) наименование контрагента;

3) вид операции:

— отгрузка (О) — отгружено товара;

— выручка (В) — получена оплата;

— расходы (Р) — уплачено;

— приход (П) — получено товаров.

Автоматический расчет долгов показан на рис. 11.

Рис. 11. Автоматический расчет долгов по данным Журнала операций

Отчет единоналожника

Автоматическое заполнение данного отчета сделать совсем несложно. Необходимо всего лишь записать в ячейку

Для этого используются такие формулы:

Автоматический учет НДС

Заполнить декларацию по НДС, а также приложение 5 к ней будет куда проще, если воспользоваться данными из Журнала операций, отфильтровав их по контрагентам, ставкам НДС, дате и иным критериям. Сделать это очень просто.

Достаточно выделить шапку таблицы, затем перейти к пункту меню

Далее полученные данные можно фильтровать по дате, в том числе задавая условие (месяц, квартал и т. п.).

На рис. 12 показано, как работает автофильтр:

Заключение

Вот, собственно, и все, что мы хотели рассказать об основных приемах автоматизации учета в MS Excel. Еще раз напоминаем, что готовый файл со всеми описанными формулами и расчетами вы можете скачать по адресу

Ну а для тех предпринимателей, у которых объем операций куда больше чем 5 — 10 в день, нужны специальные программы учета. О них мы расскажем в следующем номере.

Справочная информация (справка)

¹ Признаки ВО (+) и ВО (-) позволяют проследить движение денежных средств из кассы на расчетный счет и наоборот.