Дебет-Кредит
Украинский бухгалтерский еженедельник
#17 '2001: Практичная бухгалтерия - Автоматизация бухучета: Excel

Автоматизация бухучета:
Бухгалтерский учёт с помощью Excel.
10-й урок

Автоматизация составления финансовой отчетности малого предприятия

Владимир ЛАВРЕНОВ


Предыдущие уроки "Автоматизации бухучёта: Excel"

24 февраля 2001 года приказом Министерства финансов №101 были внесены изменения и дополнения в Положение (стандарт) бухгалтерского учета 25 "Финансовый отчет субъекта малого предпринимательства". Существенно изменился Отчет о финансовых результатах.

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

Как внести изменения в форму отчета о финансовых результатах

Изменения в форме №2-м, внесенные приказом Минфина №101, носили кардинальный характер, поэтому вам проще будет создать ее заново. Оставим в неизменном виде только верхнюю часть отчета. Итак, откройте лист "Финанс", выделите ячейки А11:G33 и в контекстном меню выберите команду "Очистить содержимое". Не снимая выделение с ячеек А11:G33, на вкладке "Выравнивание" (окно "Формат ячеек") в поле "Объединение ячеек" снимите флажок и активизируйте флажок "Переносить по словам" (рис. 1), а на панели инструментов выберите из палитры типов границ "Нет границы", рис. 2.

Рисунок 1.

Теперь лист "Финанс" готов для внесения новых данных согласно последней редакции Стандарта 25. Те, кто имеет возможность использовать электронную версию обновленной формы №2-м, скопируйте данные графы 1, начиная со статьи "інші операційні доходи", и вставьте эти данные в ячейку А11. Далее, последовательно выделяя ячейки А11:D11, А12:D12 и т. д., активизируйте флажок в поле "Объединение ячеек". Аналогичным образом в ячейку Е11 вставьте новые коды скопированных статей. Осталось удалить ненужные строки в "шапке" формы. Форматы ячеек, которые были установлены в прежней форме, при таком копировании будут применены и для новой формы. Не забудьте выделить обновленную часть отчета и установить для него соответствующий тип границы (рис. 2). После внесения в новые ячейки формул форма отчета о финансовых результатах будет готова к использованию.

Рисунок 2.

Отредактируйте формулу имени "КодРядФинанс".

Вызовите меню "Присвоение имени", в окне введенных имен выберите имя "КодРядФинанс",
а в строке формул установите формулу "=Финанс!$E$9:$E$28" (проверьте, в уроке 7 для этого имени вы устанавливали "=Финанс!$E$9:$E$34").

Как получить финансовые результаты за предыдущий год

Рассказывая о составлении отчета о финансовых результатах, мы пока обходили вниманием автоматическое заполнение 4­й графы - "Результаты за аналогичный период предыдущего года". Дело в том, что для реализации этой операции необходимо, по крайней мере, иметь систематизированные данные за такой период. Для упрощения понимания вам была предложена структура составления финансовой отчетности, которая предполагала использование составленной в Еxcel Главной книги, обеспечивающей учет данных только за год.

Поэтому для автоматического заполнения 4­й графы отчета необходимо добавить в Главную книгу раздел, в котором будут группироваться данные года, предшествовавшего текущему. Графически это будет такая же таблица, как показанная на рис. 3, для получения которой можно просто скопировать ячейки, находящиеся в столбцах С и BL, в ячейку ВМ.

Откройте лист "ГлКнига" (рис. 3). Перед копированием необходимо будет скорректировать формулы массива, введенные в ячейки столбцов "дебет" и "кредит" (для открытых на рис. 3 блоков "январь" и "декабрь" это формулы в столбцах F, G, BI и BJ).

Рисунок 3.

На предыдущем уроке в ячейки дебетовых столбцов всех месяцев были введены формулы массива, подобные формуле в ячейке F7 (январь):

{=СУММ(ЕСЛИ(МЕСЯЦ(ДатаОпер)=МЕСЯЦ($Е$3);
ЕСЛИ(ДебетЖ=СчетГКТек;ВсегоЖ)))},

и формуле в ячейке G7:

{=СУММ(ЕСЛИ(МЕСЯЦ(ДатаОпер)=МЕСЯЦ($Е$3);
ЕСЛИ(КредитЖ=СчетГКТек;ВсегоЖ)))}.

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

Итак, формула в ячейке F7 должна иметь такой вид:

{=СУММ(ЕСЛИ(ГОД(ДатаОпер)=ГОД($Е$3);
ЕСЛИ(МЕСЯЦ(ДатаОпер)=МЕСЯЦ(($Е$3);ЕСЛИ(ДебетЖ=СчетГКТек;ВсегоЖ))))},

а в ячейке G7:

{=СУММ(ЕСЛИ(ГОД(ДатаОпер)=ГОД($Е$3);
ЕСЛИ(МЕСЯЦ(ДатаОпер)=МЕСЯЦ(($Е$3);
ЕСЛИ(КредитЖ=СчетГКТек;ВсегоЖ))))},

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

Как видите, добавлено еще одно условие: ЕСЛИ(ГОД(ДатаОпер)=ГОД($Е$3). Обратите внимание на то, что аргумент для второй функции "ГОД" (в приведенной формуле это "$Е$3") должен соответствовать адресу ячейки, в которой записана дата периода. Например, для блока "декабрь" добавленное условие будет иметь вид: "ЕСЛИ(ГОД(ДатаОпер)=ГОД($ВН$3)" (рис. 3).

Редактирование формулы массива было проведено только для блока "январь". Аналогичное редактирование проведите в соответствующих ячейках всех месяцев, но только для первой строки Главной книги.

Внимание! Каждое редактирование этих формул обязательно заканчивайте нажатием Ctrl-Shift-Enter.

А теперь выделите ячейки F7:BL7 и скопируйте их во все строки Главной книги. Кстати, эту операцию можно производить другим способом - набрать интервал выделяемых ячеек F7:BL7 в поле имен и нажать Enter (рис. 4).

Рисунок 4.

Не снимая выделение, потяните за маркер заполнения (нижний правый угол ячейки BL7) до строки 106 (на прошлом уроке мы приняли именно такой размер Главной книги).

Данная корректировка формул массива Главной книги позволит теперь систематизировать данные журнала хозяйственных операций независимо от года введения той или иной проводки. Для этого достаточно будет только ввести соответствующие даты в ячейки "дата периода" (E3, J3, O3, T3, Y3 и т. д.).

Как уже было сказано выше, для хранения данных предыдущего года на листе "ГлавКнига" создадим копию рабочей зоны основной таблицы Главной книги. Для этого выделите ячейки C3:BL5, скопируйте и вставьте их в ячейку BN3 (при этом используйте обычную команду "Вставить" в контекстном меню или меню "Правка").

Так как во второй таблице будут храниться данные прошлого года, то в ее ячейках должны быть записаны только значения, а не формулы, как в первой таблице. Эта операция состоит из двух этапов.

I этап. Выделите ячейки С6: BL106 и скопируйте их в буфер обмена. Установите курсор в ячейку BN6 и откройте через контекстное меню окно "Специальная вставка", в котором включите опцию "форматы" и нажмите ОК.

II этап. Еще раз щелкните ЛКМ на ячейке BN6, опять откройте окно "Специальная вставка", в котором включите опцию "значения" и нажмите ОК (рис. 5).

Рисунок 5.

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

Для переноса остатков по счетам, то есть для заполнения в первой таблице графы "Залишок на початок року", введите в ячейку С7 формулу "=DW7", а в ячейку D7 - формулу "=DX7" (рис. 6).

Рисунок 6.

Выделите ячейки С7:D7 и скопируйте их во все ячейки указанной графы.

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

Корректируем рабочий лист отчета о финансовых результатах

Последние изменения, внесенные приказом Минфина в Стандарт 25, затронули также и порядок определения основных показателей отчета - чистого дохода и чистой прибыли.

В связи с этим необходимо будет скорректировать рабочий лист "РЛФинанс". Так как из перечня статей, входящих в расчет чистой прибыли, исключена статья "Себестоимость реализованной продукции", ее блок в рабочем листе можно использовать для учета данных нововведенной статьи "Увеличение (уменьшение) остатков незавершенного производства и готовой продукции" (рис. 7).

Рисунок 7.

Автоматическое заполнение ячеек этого блока требует использования сложной формулы, поэтому, чтобы не усложнять урок, мы рекомендуем данные по этому блоку вносить вручную. Так как в новой форме №2-м отчета изменились коды статей, необходимо в каждом блоке рабочего листа откорректировать коды строк, записанные в столбце А (рис. 8).

Рисунок 8.

Например, в соответствии с последними изменениями в Стандарте, статья "Прочие операционные доходы" теперь имеет код 040, а в созданном вами рабочем листе этот блок имеет код 060. Поэтому отредактируйте ячейки А31:А36 (рис. 8).

Соответствующие изменения внесите и в остальные блоки.

Автоматизация заполнения рабочего листа

В 9-м уроке вы узнали, как переносятся данные из Главной книги в рабочий лист финансового отчета. Формулы, используемые в рабочем листе отчета о финансовых результатах, подобные.

Итак, на листе "РЛФинанс" для блока "Дохід (виручка) від реалізації продукції (товарів, робіт послуг)" в ячейку D8 (рис. 9) необходимо внести такую формулу массива:

{=СУММ(ЕСЛИ(СчетГК=Код020РЛФинансТек;СальдоКредит1;0))}

(после ввода второй закрывающей круглой скобки не забудьте нажать Ctrl-Shift-Enter).

Рисунок 9

Как видите, синтаксис формулы такой же, как и при заполнении ячеек рабочего листа финансового отчета. Незначительное отличие заключается в аргументах. Второй аргумент ("Код020РЛФинансТек") формулы имеет явное указание на то, что он связан с листом "Финанс". В связи с этим, как и для ячеек листа "Отчет", необходимо будет соответствующим ячейкам листа "Финанс" присвоить подобные имена.

Для того чтобы имя "Код020РЛФинансТек" выполняло свою функцию в формуле, следует создать массив ячеек D6:К6 (рис. 9) и присвоить ему имя, например "Код020РЛФинанс". После этого установите курсор в ячейку D6 и вызовите окно "Присвоение имени" (меню "Вставка" - "Имя" - "Присвоить"). В верхнюю строку окна введите выбранное имя, а в строку "Формула" - "=РЛФинанс!D$6".

Обратите внимание на то, чтобы был удален знак доллара "$" перед буквой D (рис. 10).

Рисунок 10.

Скопируйте в ячейки D9:D11 формулу, которую вы записали в ячейку D8, и отредактируйте ее в каждой ячейке следующим образом:

ячейка D9 (доход (выручка) от реализации продукции (товаров, работ, услуг) за полугодие) -

{=СУММ(ЕСЛИ(СчетГК=Код020РЛФинансТек;СальдоКредит2;0))};

ячейка D10 (доход от реализации за 9 месяцев) -

{=СУММ(ЕСЛИ(СчетГК=Код020РЛФинансТек;СальдоКредит3;0))};

ячейка D11 (доход от реализации за год) -

{=СУММ(ЕСЛИ(СчетГК=Код020РЛФинансТек;СальдоКредит4;0))}.

Формулы, которые нужно будет ввести в каждом блоке в ячейки "попередній рік" (для блока на рис. 9 это D7:K7), значительно отличаются от приведенных выше вследствие иного характера данных 4­й графы формы №2-м ("за аналогичный період попереднього року).

{=СУММ(ЕСЛИ(МЕСЯЦ(ДатаОтчета)=4;
ЕСЛИ(СчетГК=КодРЛФинанс010Тек;СальдоКредит1П);
СУММ(ЕСЛИ(МЕСЯЦ(ДатаОтчета)=7;
ЕСЛИ(СчетГК=КодРЛФинанс010Тек;СальдоКредит2П);

СУММ(ЕСЛИ(МЕСЯЦ(ДатаОтчета)=10;
ЕСЛИ(СчетГК=КодРЛФинанс010Тек;СальдоКредит3П);

СУММ(ЕСЛИ(СчетГК=КодРЛФинанс010Тек;СальдоКредит4П))))}

Чтобы было более наглядно, разберем в формуле только ее часть для одного отчетного периода:

СУММ(ЕСЛИ(МЕСЯЦ(ДатаОтчета)=4;
ЕСЛИ(СчетГК=КодРЛФинанс010Тек;СальдоКредит1П).

Как видно из приведенной формулы, она состоит из четырех групп, три из которых включают по два подобных условия. Поясним их синтаксис на примере первой группы условий (будем исходить из того, что формула записана в ячейку D7:

если месяц даты отчета (даты, на которую составлен финансовый отчет; на листе "Отчет" она введена в ячейку D16) равен 4, то выполняется второе условие;

в ячейку записывается значение кредитового сальдо первого квартала предыдущего года по счету 701.

Внимание! Так как в формулах могут быть вложены друг в друга в качестве значений аргументов не более 7 функций "ЕСЛИ", данная формула не содержит аргумента "ЕСЛИ(МЕСЯЦ(ДатаОтчета)=1", который уточнял бы принадлежность данных к дате отчета "на 1 января 200х года" (ячейка D16, лист "Отчет"). Однако этого и не требуется - при невыполнении предыдущих условий формула выберет данные только из этого интервала.

Кредитовые сальдо предыдущего года записаны во вторую таблицу Главной книги. Для того чтобы данная формула выбрала сальдо счета из отчетного периода предыдущего года, в формуле использованы новые имена типа "СальдоКредит1П" (имя для первого квартала предыдущего года). Поэтому необходимо будет ввести еще 8 имен для интервалов ячеек - дебетовые и кредитовые сальдо для каждого отчетного периода (в строке 020 "Непрямі податки та інші вирахування з доходу" используется дебетовое сальдо субсчета 704). Порядок действий по присвоению этих имен следующий:

1. Откройте окно "Присвоение имени" и выберите в перечне уже введенных имен имя "СальдоДебет1".

2. Отредактируйте его в верхней строке на "СальдоДебет1П".

3. В строке "формула" отредактируйте "=ГлКнига!$R$6:$R$106" на "=ГлКнига!$CD$6:$CD$106". (При редактировании курсор перемещайте с помощью мыши.)

4. Щелкните кнопку "Добавить".

5. Используя данные таблицы, повторите то же для остальных имен, начиная с пункта 2.

Имя Формула
СальдоДебет1П =ГлКнига!$CD$6:$CD$106
СальдоКредит1П =ГлКнига!$CE$6:$CE$106
СальдоДебет2П =ГлКнига!$CS$6:$CS$106
СальдоКредит2П =ГлКнига!$CT$6:$CT$106
СальдоДебет3П =ГлКнига!$DH$6:$DH$106
СальдоКредит3П =ГлКнига!$DI$6:$DI$106
СальдоДебет4П =ГлКнига!$DW$6:$DW$106
СальдоКредит4П =ГлКнига!$DX$6:$DX$106

Теперь вы имеете все наборы формул, обеспечивающие автоматическое составление финансовой отчетности.

После ввода формул в первом блоке рабочего листа (рис. 9) выделите ячейки D8:D11 и скопируйте их еще в два столбца - Е и F (на сколько столбцов будут скопированы формулы, зависит от количества используемых на предприятии субсчетов при расчете той или иной статьи отчета.

В каждом из 14 блоков рабочего листа проведите такое же редактирование, учитывая код статьи. Например, после того как вы скопируете формулы ячеек D8:D11 в ячейку D31 (рис. 8, блок "Прочие операционные доходы"), соответственно отредактируйте формулы:

в ячейке D31 -

{=СУММ(ЕСЛИ(МЕСЯЦ(ДатаОтчета)=4;
ЕСЛИ(СчетГК=КодРЛФинанс040Тек;
СальдоКредит1П); СУММ(ЕСЛИ(МЕСЯЦ(ДатаОтчета)=7;
ЕСЛИ(СчетГК=КодРЛФинанс040Тек;СальдоКредит2П);

СУММ(ЕСЛИ(МЕСЯЦ(ДатаОтчета)=10;
ЕСЛИ(СчетГК=КодРЛФинанс040Тек;СальдоКредит3П);

СУММ(ЕСЛИ(СчетГК=КодРЛФинанс040Тек;СальдоКредит4П))))}

в ячейке D32 -

{=СУММ(ЕСЛИ(СчетГК=Код040РЛФинансТек;СальдоКредит1;0))};

в ячейке D33 -

{=СУММ(ЕСЛИ(СчетГК=Код040РЛФинансТек;СальдоКредит2;0))};

в ячейке D34 -

{=СУММ(ЕСЛИ(СчетГК=Код040РЛФинансТек;СальдоКредит3;0))};

в ячейке D35 -

{=СУММ(ЕСЛИ(СчетГК=Код040РЛФинансТек;СальдоКредит4;0))}.

Аналогичную операцию проведите и в остальных блоках.

Два способа заполнения отчета о финансовых результатах

Заполнять отчетные формы предприятия можно двумя способами:

- с помощью рабочих листов;

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

Предвидим вопрос: зачем было создавать рабочие листы? Дело в том, что использование второго способа оправдано, когда во вводимых в отчет формулах используется немного аргументов (например, "Матеріальні витрати", "Витрати на оплату праці тощо"). К тому же составление рабочих листов дает дополнительную аналитическую информацию.

В 6-м уроке ("ДК" №7) были приведены формулы для заполнения финансового отчета. Синтаксис формул отчета о финансовых результатах (графа "За звітний період" подобный.

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

{=СУММ(ЕСЛИ(ГОД(ДатаОтчета)=ГОД(ДатаРЛФинанс) ;
ЕСЛИ(ДатаОтчета-1=ДатаРЛФинанс;
ЕСЛИ(КодРядФинансТек=КодРядРЛФинанс;
ВсегоРЛФинанс))))}.

Если у вас в рабочем листе для всех статей отчета созданы блоки, а в них введены формулы для переноса данных из Главной книги, тогда в ячейку F9 записывайте эту формулу и копируйте ее в ячейки интервала F9:F26.

Формулы, которые нужно ввести в итоговые ячейки F11, F15, F27, F28, понятны из содержания соответствующих им статей (рис. 11).

Рисунок 11

С появлением в отчете статьи "Увеличение (уменьшение) остатков незавершенного производства и готовой продукции" субъекты предпринимательской деятельности могут теперь всю сумму расходов операционной деятельности списывать в дебет счета 79. Это дает возможность упростить заполнение строк 090 - 120 отчета и заполнять их без использования рабочего листа, вводя в соответствующие ячейки формулы. Для этого в ячейку F17 ("Материальные расходы отчетного периода") введите формулу:

{=СУММ(ЕСЛИ(ГОД(ДатаОпер)=ГОД(ДатаОтчета-1);
ЕСЛИ(МЕСЯЦ(ДатаОпер)<=МЕСЯЦ(ДатаОтчета-1);
ЕСЛИ(ДатаОпер+92>=ДатаОтчета; ЕСЛИ(ДебетЖ=79;
ЕСЛИ(ОТБР(КредитЖ/10)=80;ВсегоЖ))))}.

Первые три условия ограничивают интервал операций, которые относятся к отчетному периоду. Четвертое и пятое условия определяют, обороты каких счетов используются в балансе данной статьи. Комбинация в формуле этих двух условий обеспечивает выборку проводок Д-т 79 К-т 80Х.

В формуле появилась новая функция "ОТБР(ДебетЖ/10)=80". В бухгалтерском учете такую функцию можно применять, когда необходимо выбрать проводки по всем субсчетам, относящимся к одному счету. Приведенный аргумент означает, что будут выбраны все кредиты субсчетов, которые начинаются на 80.

Скопируйте приведенную выше формулу в ячейки F18, F19, F20, F21, F24, отредактировав только в каждой формуле второй аргумент последнего условия:

для ячейки F18:

"ЕСЛИ(ОТБР(КредитЖ/10)=81;ВсегоЖ)";

для ячейки F19:

"ЕСЛИ(ОТБР(КредитЖ/10)=82;ВсегоЖ)";

для ячейки F20:

"ЕСЛИ(ОТБР(КредитЖ/10)=83;ВсегоЖ)";

для ячейки F21:

"ЕСЛИ(ОТБР(КредитЖ/10)=84;ВсегоЖ)";

для ячейки F24:

"ЕСЛИ(ОТБР(КредитЖ/10)=85;ВсегоЖ)".

Для заполнения 4-й графы отчета необходимо ввести формулу:

{=СУММ(ЕСЛИ(ГОД(ДатаОтчета)=ГОД(ДатаРЛФинанс);
ЕСЛИ(МЕСЯЦ(ДатаРЛФинанс)=1;
ЕСЛИ(КодРядФинансТек=КодРядРЛФинанс;ВсегоРЛФинанс))))}.

Внимание! При использовании рабочего листа в ячейке, в которой записана условная дата (для блока "Доход от реализации" это В7, рис. 12), должен быть записан январь отчетного года.

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

Если вы хотите заполнить ячейки F17, F18, F19, F20, F21, F24, используя только формулы, введите в ячейку F17:

{=СУММ(ЕСЛИ(ГОД(ДатаОтчета-1)-ГОД(ДатаОпер)=1ЕСЛИ(МЕСЯЦ
(ДатаОпер)<=МЕСЯЦ(ДатаОтчета-1);ЕСЛИ(ДатаОпер+457>=
ДатаОтчета;ЕСЛИ(ДебетЖ=79;ЕСЛИ(ОТБР(КредитЖ/10)=80;ВсегоЖ))))))}.

В аргументе "ЕСЛИ(ДатаОпер+457>=ДатаОтчета" число 457 значит 92 + 365 (длительность года).

Скопируйте формулу в ячейки F18, F19, F20, F21, F24, отредактируйте ее, как показано выше, - и программа готова работать.

Итак, приведены все основные формулы для заполнения финансовой отчетности малого предприятия. Мы не претендуем на полноту подачи материала такой тематики. Надеемся, что вы сможете улучшить наш вариант использования Excel для создания рассмотренных отчетных форм.

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

Рисунок 12.


Следующий урок

© 2001
"Дебет-Кредит"
Редакция: debet-kredit@gc.kiev.ua