1055 1

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

«Компьютер у предпринимателя» интересные новости и советы, касающиеся применения программ по учету доходов и расходов и составления отчетности (в том числе электронной), описание полезных возможностей, которыми можно воспользоваться при наличии подключения к Интернету, а также секреты работы с ПК. Начинаем, как водится, с наиболее дешевого варианта — учет доходов и расходов в MS Excel (бесплатная программа своими руками).
Виталина МИРОШНИЧЕНКО, консультант газеты «Собственное Дело»

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

КУоАП — Кодекс Украины об административных правонарушениях от 07.12.84 г. № 8073-X.

Закон об НДС — Закон Украины «О налоге на добавленную стоимость» от 03.04.97 г. № 168/97-ВР.

Порядок № 599 — Порядок выдачи Свидетельства об уплате единого налога, утвержденный приказом ГНАУ от 29.10.99 г. № 599.

Инструкция № 12 — Инструкция о налогообложении доходов физических лиц от занятия предпринимательской деятельностью, утвержденная приказом ГГНИУ от 21.04.93 г. № 12.

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

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

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

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

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

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

Прежде чем начинать разговор о программах учета, нужно напомнить, что электронный учет не освобождает предпринимателя от необходимости вести Книгу ф. № 10 в бумажном виде (см. «Вестник налоговой службы Украины», 2005, № 32, с. 45). Иными словами, предприниматели на общей системе и единоналожники обязаны заполнять бумажную Книгу ф. № 10 независимо от того, есть ли ее электронная копия или нет (может и не быть или быть, но другой формы — удобной для предпринимателя).

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

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

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

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

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

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

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

Учет единоналожника на первый взгляд прост. Порядок № 599 требует заполнять четыре графы Книги ф. № 10: «Дата», «Расходы», «Доход (выручка)» и «Чистый доход». Как правило, первые три графы единоналожники заполняют по итогам дня — общей суммой, а последнюю — раз в месяц. Но если такой информации достаточно для поверяющих, то для самого предпринимателя ее нужно гораздо больше, иначе некоторые решения (сколько товара есть на складе и сколько можно отгрузить, сколько товара нужно заказать, кому пора оплатить счета, а кому еще можно подождать, и т. п.) придется принимать просто вслепую. Для того чтобы в любой момент получить реальные данные по кассе, расчетному счету, товарам и долгам, нужно знать о каждой операции. А это удобно реализовать в MS Excel.

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

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

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

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

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

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

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

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

На рис. 1 и 2 покажем, как это будет выглядеть в итоге, а затем подробно расскажем, каким образом реализовать такую идею в MS Excel. Потратив на создание предложенных таблиц день (максимум два), в дальнейшем вы сэкономите гораздо больше времени и оцените преимущества автоматизации. Впрочем, можно даже скачать готовый файл с разработанной программой на сайте http://nibu.factor.ua/info/download (выбрав правой клавишей мыши пункт «Сохранить как...») и немного подкорректировать его с учетом своих данных.

Ф-10-1.GIF

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

Ф-10-2.GIF

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

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

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

Дата

Операция

Контрагент

№ документа

Сумма, грн.

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

Признак платежа: наличный — касса (К); безналичный — расчетный счет (РС)

Товарный учет (услуги)

Номер налоговой накладной (входящей или исходящей)

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

Сумма без НДС, грн.

Ставка НДС

НДС, грн.

Использование в хоздеятельности или услуги, регулируемые пунктом 6.5 Закона об НДС

НО

НК

Поступление (П) или отгрузка (О)

Наименование

Количество (ед. изм.)

Сумма, грн.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

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

— «Дата»;

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

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

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

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

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

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

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

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

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

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

— ставка 20 %;

— ставка 0 %;

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

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

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

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

Выбираем первую ячейку графы «Ставка НДС» и идем в пункт меню «Данные» — «Проверка» — вкладка «Параметры». Тип данных выбираем «Список», а в поле «Источник» пишем через знак «;» необходимые варианты: «20 %; 0 %; второе событие; неплательщик; освобождены; не объект» (рис. 3).

Ф-10-3.GIF

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

Графа «Признак платежа» имеет два варианта: наличные расчеты (К — касса) и безналичные (РС — расчетный счет). Признак платежа пригодится в дальнейшем для того, чтобы подсчитать остаток денег отдельно в кассе и на расчетом счете.

Графа «Использование в хоздеятельности» необходима для учета НДС. Она имеет такие варианты: «-; Использ. в хозд.; Не использ. в хозд; пункт 6.5 Закона об НДС».

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

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

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

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


B

C

<…>

4

Содержание операции

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

5

Выплата зарплаты работникам

Р

6

Доплата взноса в ПФ «за себя»

Р

7

Зачисление на р/сч наличных из кассы

ВО (+)

8

Изъятие наличных из кассы для зачисления на р/сч

ВО (-)

9

Личные расходы

ВО

10

Оплата единого налога

Р

11

Оплата от покупателя за товар (услуги)

В

12

Оплата поставщику за основные средства

ВО

13

Оплата поставщику за товары (услуги)

Р

14

Отгрузка товара

ВО

15

Передача результата услуг заказчику

ВО

16

Покупка основных средств (получение)

ВО

17

Получение в кассу наличных с р/сч

ВО (+)

18

Получение услуг от исполнителей

ВО

19

Приход товара

ВО

20

Снятие наличных с р/с для зачисления в кассу

ВО (-)

21

Уплата иных налогов

Р

22

Уплата НДФЛ и соцвзносов с ЗП наемных работников и подрядчиков

Р

23

Услуги банка

Р

24

<…>

<…>

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

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

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

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

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

$B$60);1), где

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

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

Дальше все просто: открываем лист с Журналом операций, выбираем ячейку в столбце «Операция», идем в пункт меню «Данные» — «Проверка» — вкладка «Параметры». Тип данных выбираем «Список», а в поле «Источник» пишем «=Операции» (т. е. имя нашего справочника). Что получилось, см. на рис. 4.

Ф-10-4.GIF

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

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

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

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

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

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

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

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

$B$5:$C$30;2;0)), где

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

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

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

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

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

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

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

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

Ф-10-5.GIF

Рис. 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).

Ф-10-6.GIF

Рис. 6. Автоматическое заполнение налогового номера контрагента

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

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

Столбец

Формула

«Поступление или отгрузка»

=ЕСЛИ(Т(B10)=”Отгрузка товара”;”О”;ЕСЛИ(Т(B10)=”Передача результата услуг заказчику”;”О”;ЕСЛИ(Т(B10)=”Приход товара”;”П”;ЕСЛИ(Т(B10)=”Получение услуг от исполнителей”;”П”;”-”))))

«Сумма, грн. (товарный учет)»

=ЕСЛИ(ИЛИ(H10=”О”;H10=”П”);E10;”-”)

«НДС, грн.»

=ЕСЛИ(ИЛИ(O10=20 %;O10=0 %);N10*O10;”-”)

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

Ф-10-7.GIF

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

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

Единоналожники ведут Книгу учета доходов и расходов по форме, указанной в приложении 10 к Инструкции № 12. Правда, налоговики на местах могут вносить изменения в форму Книги (как правило, добавляют дополнительные колонки). Но единоналожников это волновать не должно, поскольку в соответствии с пунктом 8 Порядка № 599 в Книге ф. № 10 они обязаны заполнять только 4 графы: «Период учета», «Затраты на производство продукции», «Сумма выручки (дохода)» и «Чистый доход». Как это сделать автоматически, сейчас и разберемся.

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

Период учета (день, неделя, месяц, год)

Количество изготовленной продукции, предоставленных услуг

Расходы на производство продукции, грн.

Количество проданной продукции, предоставленных услуг (ед. изм.)

Цена продажи продукции (услуг), грн.

Сумма выручки (дохода) (грн.)

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

1

2

3

4

5

6

7

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

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

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

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

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

Критерий 1

Критерий 2

Результат

Графа Книги ф. № 10

Значение в графе «Период учета» Книги ф. № 10 должно совпасть со значением в графе «Дата»Журнала операций

В графе «Признак операции» Журнала операций должно быть указано «Р» (расходы)

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

«Расходы на производство»

Значение в графе «Период учета» Книги ф. № 10 должно совпасть со значением в графе «Дата»Журнала операций

В графе «Признак операции» Журнала операций должно быть указано «В» (выручка)

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

«Сумма выручки»

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

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

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

Графа Книги ф. № 10

Формула

«Расходы на производство»

{=СУММ((Операции!$A$10:$A$514=A20)*(Операции!$F$10:$F$514=Р)

*(Операции!$E$10:$E$514))}

«Сумма выручки»

{=СУММ((Операции!$A$10:$A$514=A20)*(Операции!$F$10:$F$514=В)

*(Операции!$E$10:$E$514))}

Операции!$A$10:$A$514 — столбец «Дата» в Журнале операций («Операции» — название листа, на котором расположен Журнал операций);

A20 — ячейка столбца «Период учета» Книги ф. № 10 с искомой датой;

Операции!$F$10:$F$514 — столбец «Признак операции» Журнала операций («Операции» — название листа, на котором расположен Журнал операций);

Операции!$E$10:$E$514 — столбец «Сумма, грн.» Журнала операций, данные которого суммируются («Операции» — название листа, на котором расположен Журнал операций)

Важно! Вводить фигурные скобки { } с клавиатуры не нужно: они появляются автоматически, если после набора формулы вместо Enter нажать комбинацию Ctrl+Shift+ Enter (формула будет воспринята как формула массива).

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

Ф-10-8.GIF

Рис. 8. Автоматическое заполнение Книги ф. № 10 с помощью формулы массива

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

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

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

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

F37 — ежемесячная сумма выручки =СУММ(F6:F36);

C37 — ежемесячная сумма расходов =СУММ(C6:C36).

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

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

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

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

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

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


A

B

C

D

E

F

<…>

7

Дата

Поступление

Поступление из кассы

Расходование

Снятие в кассу

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

8

<…>

<…>

<…>

<…>

<…>

<…>

«Дата»

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

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

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

поступила на расчетный счет

выбыла с расчетного счета

— от контрагентов (выручка);

— из кассы (ВО(+))

— контрагентам, бюджету и т. п. (расходы);

— в кассу (ВО(-))

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

Критерий 1

Критерий 2

Критерий 3

Результат

Графа

«Дата» таблицы «Расчетный счет» должна совпадать с «Датой» Журнала операций

В графе «Признак платежа» Журнала операций указано «РС» (операция проведена через расчетный счет)

В графе «Признак операции» Журнала операций указано «В»

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

«Поступление»

В графе «Признак операции» Журнала операций указано «ВО (+)»

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

«Поступление из кассы»

В графе «Признак операции» Журнала операций указано «Р»

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

«Расходование»

В графе «Признак операции» Журнала операций указано «ВО (-)»

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

«Снятие в кассу»

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

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

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

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

Графа

Формула

1

2

«Поступление»

{=СУММ((Операции!$A$10:$A$510=A8)*(Операции!$G$10:$G$510=РС)

*(Операции!$F$10:$F$510=В)*(Операции!$E$10:$E$510))}

«Поступление из кассы»

{=СУММ((Операции!$A$10:$A$510=A8)*(Операции!$G$10:$G$510=РС)

*(Операции!$F$10:$F$510=ВО(+))*(Операции!$E$10:$E$510))}

«Расходование»

{=СУММ((Операции!$A$10:$A$510=A8)*(Операции!$G$10:$G$510=РС)

*(Операции!$F$10:$F$510=Р)*(Операции!$E$10:$E$510))}

«Снятие в кассу»

{=СУММ((Операции!$A$10:$A$510=A8)*(Операции!$G$10:$G$510=РС) *(Операции!$F$10:$F$510=ВО(-))*(Операции!$E$10:$E$510))}

Операции!$A$10:$A$510 — столбец «Дата» в Журнале операций («Операции» — название листа, на котором расположен Журнал операций); A8 — ячейка столбца «Дата» таблицы «Расчетный счет», в которой указана необходимая дата;

Операции!$G$10:$G$510 — столбец «Признак платежа» Журнала операций;

Операции!$F$10:$F$510 — столбец «Признак операции» Журнала операций;

Операции!$E$10:$E$510 — столбец «Сумма, грн.» Журнала операций, данные которого суммируются

Важно! Вводить фигурные скобки { } с клавиатуры не нужно: они появляются автоматически, если после набора формулы вместо Enter нажать комбинацию Ctrl+Shift+ Enter (формула будет воспринята как формула массива).

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

Ф-10-9.GIF

Рис. 9. Фрагмент Журнала операций за 15.01.2010 г. и 16.01.2010 г. и автоматическое определение остатка средств на текущем счете по этим датам

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

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

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

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

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

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

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

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

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

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

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

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


A

B

C

D

E

F

G

H

I

J

<…>

4

Дата

Количество, ед. изм.

5

Товар 1

Товар 2

Товар 3

6

Приход

Отгрузка

Остаток

Приход

Отгрузка

Остаток

Приход

Отгрузка

Остаток

7

Остаток на начало



<…>



<…>



<…>

8

<…>

<…>

<…>

<…>

<…>

<…>

<…>

<…>

<…>

<…>

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

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

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

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

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

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

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

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

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

Графа

Формула

«Приход»

{=СУММ((Операции!$A$10:$A$510=A8)*(Операции!$H$10:$H$510=П)*(Операции!$I$10:$I$510=$B$5)*(Операции!$J$10:$J$510))}

«Отгрузка»

{=СУММ((Операции!$A$10:$A$510=A8)*(Операции!$H$10:$H$510=О)*(Операции!$I$10:$I$510=$B$5)*(Операции!$J$10:$J$510))}

Операции!$A$10:$A$510 — столбец «Дата» в Журнале операций («Операции» — название листа, на котором расположен Журнал операций); A8 — ячейка столбца «Дата» таблицы «Наличие товаров (количественный учет)», в которой указана текущая дата;

Операции!$H$10:$H$510 — столбец «Признак: поступление (П) или отгрузка (О)» Журнала операций;

Операции!$I$10:$I$510 — столбец «Наименование товара» в Журнале операций;

$B$5 — выбранное из выпадающего списка название товара в 5-й строке таблицы «Наличие товаров (количественный учет)»;

Операции!$J$10:$J$510 — столбец «Количество» Журнала операций, данные которого суммируются

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

«Остаток» товаров на текущую дату рассчитывается просто: остаток на начало плюс приход минус отгрузка.

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

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

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

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

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

Ф-10-10.GIF

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

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

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

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

Дата

Покупатели

Поставщики

Наименование покупателя

Наименование поставщика

Отгружено товара (передано услуг), грн.

Получена оплата, грн.

Долг на текущую дату

Уплачено, грн.

Получено от него товаров (услуг), грн.

Долг на текущую дату

НАМ

МЫ

НАМ

МЫ

Остаток на начало



<…>





<…>

<…>

<…>

<…>

<…>

<…>

<…>

<…>

<…>

<…>

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

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

1) дата;

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

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

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

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

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

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

Графа

Формула

«Отгружено товара (передано услуг), грн.»

{=СУММ((Операции!$A$10:$A$510=B8)*(Операции!$H$10:$H$510=О)

*(Операции!$C$10:$C$510=$C$4)*(Операции!$E$10:$E$510))}

«Получена оплата, грн.»

{=СУММ((Операции!$A$10:$A$510=B8)*(Операции!$F$10:$F$510=В)

*(Операции!$C$10:$C$510=$C$4)*(Операции!$E$10:$E$510))}

«Уплачено, грн.»

{=СУММ((Операции!$A$10:$A$510=B8)*(Операции!$F$10:$F$510=Р)

*(Операции!$C$10:$C$510=$G$4)*(Операции!$E$10:$E$510))}

«Получено от него товаров (услуг), грн.»

{=СУММ((Операции!$A$10:$A$510=B8)*(Операции!$H$10:$H$510=П)

*(Операции!$C$10:$C$510=$G$4)*(Операции!$E$10:$E$510))}

«Долг на текущую дату» МЫ

=ЕСЛИ(($C8-$D8+$E7-$F7)<0;(0-($C8-$D8+$E7-$F7));0)

«Долг на текущую дату» НАМ

=ЕСЛИ(($C8-$D8+$E7-$F7)>0;$C8-$D8+$E7-$F7;0)

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

Ф-10-11.GIF

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

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

Автоматическое заполнение данного отчета сделать совсем несложно. Необходимо всего лишь записать в ячейку «Выручка с начала года» ежемесячные итоговые суммы из Книги ф. № 10. Кроме того, можно сделать дополнительную ячейку, в которой контролировать, не превысила ли выручка 500 тыс. грн.

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

Графа

Формула

«Выручка с начала года»

='Книга 10'!$F$37+'Книга 10'!$F$66+'Книга 10'!$F$98+'Книга 10'!$F$129+'Книга 10'!$F$161+'Книга 10'!$F$192+'Книга 10'!$F$224+'Книга 10'!$F$256+'Книга 10'!$F$287+'Книга 10'!$F$319+'Книга 10'!$F$350+'Книга 10'!$F$382

Контрольная ячейка

=ЕСЛИ(H35>500000;Выручка на едином налоге превысила 500 тыс. грн.!!!;””)

Н35 — ячейка, в которой рассчитывается сумма выручки нарастающим итогом;

'Книга 10'!$F$37, ..., 'Книга 10'!$F$382 — ежемесячный итог выручки начиная с января по декабрь текущего года. Берется с листа, на котором расположена Книга ф. № 10

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

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

Достаточно выделить шапку таблицы, затем перейти к пункту меню «Данные» — «Фильтр» — выбрать «Автофильтр». Возле каждой ячейки шапки появится треугольничек с выпадающим списком условий фильтра. Так, выбрав в поле «Контрагенты» определенного покупателя (поставщика), получим все данные о суммах НО или НК по этому контрагенту.

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

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

Ф-10-12.GIF

Рис. 12. Операции, отфильтрованные по условию «Контрагент» — «МКС (ООО)», а также сумма НО по НДС по этому контрагенту за январь 2010 года

Заключение

Вот, собственно, и все, что мы хотели рассказать об основных приемах автоматизации учета в MS Excel. Еще раз напоминаем, что готовый файл со всеми описанными формулами и расчетами вы можете скачать по адресу http://nibu.factor.ua/info/download (выбрав правой клавишей мыши пункт «Сохранить как...»). Надеемся, что показанный пример поможет быстро создать собственную базу данных, вовремя получать необходимую информацию и сэкономить в дальнейшем массу времени.

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


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

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

2 В данном случае учитываются все оплаченные расходы, а не только те, которые связаны с полученным в данном месяце доходом. Но для единоналожника такое незначительное отступление не страшно, поскольку сумма чистого дохода на сумму единого налога не влияет. Если уж налоговики и придерутся, то могут разве что наложить админштраф за ненадлежащее ведение учета доходов и расходов (51 — 136 грн., статья 1641 КУоАП).



Помітили помилку? Виділіть її та натисніть Ctrl+Enter, щоб повідомити нас про це
загрузка...
Коментарі (1)
Сергей
05.01.2015
По ссылке нету предложенного в тексте файла. Спасибо.