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

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

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

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


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

Итак, в вашей рабочей книге под названием "Баланс" находятся два рабочих листа: "Отчет", в котором помещена форма баланса, и "РЛОтчет" - инструмент для заполнения финансового отчета малого предприятия. В состав финансовой отчетности малого предприятия входит также отчет о финансовых результатах, составление которого мы рассмотрим в этом уроке.

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

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

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

Итак, в книге "Баланс" создадим еще два листа с именами:

- "РЛФинанс" - рабочий лист для заполнения отчета о финансовых результатах;

- "Финанс", в который поместим форму отчета о финансовых результатах.

Чтобы, так сказать, "с нуля" не создавать рабочий лист "РЛФинанс", скопируйте на листе "РЛОтчет" ячейки А4:L12 и вставьте их в ячейку А4 листа "РЛФинанс". Затем, скопировав на нем ячейки А5: L12, последовательно вставляйте этот блок в первую свободную ячейку столбца А (А13, А21, А29 и т. д.). Для рабочего листа "РЛФинанс" понадобится 15 рабочих блоков. После этого отредактируйте названия статей и их составляющие.

В каждом таком блоке замените текст "на початок року" на текст "попередній рік". Они находятся в ячейках С7, С15, С23 и т. д. Остальные отличия в структуре рабочего листа несущественны, и их можно понять, используя рис. 1.

Рисунок 1

Заполнять рабочий лист "РЛФинанс" можно, используя те же приемы, которые были приведены в прошлом уроке.

Отличительной особенностью отчета о финансовых результатах является наличие в нем двух частей: "Финансовые результаты" и "Элементы операционных затрат".

В первую часть в основном входят кредитовые обороты счетов класса "Доходы и результаты деятельности", и поэтому нетрудно определиться, какие составляющие будут входить в ту или иную ее статью. Заполнение второй части отчета для предприятий, использующих 8-й класс счетов "Расходы по элементам", достаточно простое. Предприятия, использующие 9-й класс, для определения входящих в статьи составляющих могут воспользоваться приведенной ниже таблицей.

В ней использованы следующие условные обозначения:

ОК - оборот по кредиту счета;

ОД - оборот по дебету счета;

ОБ (Дn, Km) - оборот по проводкам с дебета счета n и в кредит счета m.

Код рядка Статті, найменування показників Показники рахунків, що входять у статті звіту
010 Дохід (виручка) від реалізації продукції (товарів, робіт, послуг) ОК 701 + ОК 702 + ОК 703
020 Непрямі податки та інші вирахування з доходу ОД 704 + ОБ (Д70, К641)
040 Собівартість реалізованої продукції (товарів, робіт, послуг) ОК 901 + ОК 902 + ОК 903
060 інші операційні доходи ОК 71 - ОБ (Д71, К641)
090 інші операційні витрати ОК 92 + ОК 93 + ОК 94
130 інші звичайні доходи ОК 72 + ОК 73 + ОК 74
160 інші звичайні витрати ОК 95 + ОК 96 + ОК 97
200 Надзвичайні витрати ОК 75
205 Надзвичайні доходи ОК 99
210 Податок на прибуток ОК 98
230 Матеріальні витрати ОБ (Д23 К20 + Д90 К20 + Д91 К20 + Д92 К20 + Д93 К20 + Д94 К20)
240 Витрати на оплату праці ОБ (Д23 К661 + Д90 К661 + Д91 К661 + Д92 К661 + Д93 К661 + Д94 К661)
250 Відрахування на соціальні заходи ОБ (Д23 К65 + Д90 К65 + Д91 К65 + Д92 К65 + Д93 К65 + Д94 К65)
260 Амортизація ОБ (Д23 К13 + Д90 К13 + Д91 К13 + Д92 К13 + Д93 К13 + Д94 К13)
270 інші операційні витрати ОБ (Д 23 (90, 91, 92, 93, 94) К372 (377, 63, 685)

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

Рабочие блоки раздела "Элементы операционных расходов" будут содержать данные оборотов по определенным проводкам. Как видно из таблицы, это обороты с кредита одного счета в дебет нескольких счетов (например, для статьи "Затраты на оплату труда" подсчитываются обороты с кредита счета 661 в дебет счетов 23, 90, 91, 92, 93, 94). Поэтому в раздел "Составляющие статей" необходимо внести обороты дебетов этих счетов, корреспондирующих с соответствующими счетами элементов операционных затрат (рис. 2).

Рисунок 2

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

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

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

Для этого на листе "РЛФинанс" присвоим имена таким ячейкам:

- имя "КодРядРЛФинанс" - ячейкам А7:А124;

- имя "ДатаРЛФинанс" - ячейкам В7:В124;

- имя "ВсегоРЛФинанс" - ячейкам L7:L124.

Способ присвоения имени был нами уже рассмотрен, поэтому не будем останавливаться на этом подробно.

Во время операции присвоения имен не забудьте, что интервалы ячеек, на которые делается ссылка в этих именах, должны быть одинаковыми по длине (в нашей задаче интервал ограничен строками №7 и №124). Обратите внимание на то, что в формуле имени используются абсолютные ссылки на все ячейки интервала. Например, для имени "КодРядРЛФинанс" используется формула "=РЛФинанс!$А$7:$А$124" (рис. 3).

Рисунок 3

Теперь присвойте соответствующим ячейкам имя "ДатаРЛФинанс". Для этого выделите нужные ячейки, используя прием сворачивания структуры таблицы, который рассматривался на предыдущем уроке. Ввести имя можно через поле имен, подтвердив ввод нажатием клавиши "ОК". Проконтролируйте, чтобы интервал выделенных ячеек был равен по длине интервалу, используемому в первом имени, то есть от 7-й строки до 124-й. Для этого убедитесь, что в окне "Присвоение имени" в поле "Формула" будет записано:

=РЛФинанс!$B$7:$B$124.

Аналогичную операцию присвоения проведите для интервала ячеек L7:L124 (графа "Всього").

Создание формы №2

Теперь перейдем к созданию формы отчета о финансовых результатах. На лист "Финанс", используя известный вам из 4-го урока прием, скопируйте из П(С)БУ 25 форму "Отчет о финансовых результатах". Вставьте скопированные из П(С)БУ фрагменты формы отчета в ячейки, показанные на рис. 4.

Рисунок 4

Как сказано выше, в формуле массива будут использованы пять имен интервалов ячеек листов "РЛФинанс" и "Финанс", трем из которых имена уже присвоены.

Осталось присвоить имена двум группам ячеек на листе "Финанс":

- имя "КодРядФинанс" - ячейкам Е9:Е34;

- имя "КодРядФинансТек" - ячейке Е9.

Выделите на листе "Финанс" интервал Е9:Е34 и введите соответствующее имя в поле имен (рис. 4).

Присвоить имя ячейке Е9 можно обычным способом, вводя его в поле имен. Однако не забудьте обязательно отредактировать формулу после его ввода. Аргумент "КодРядФинансТек" будет выполнять в формуле массива позиционирующую функцию, о сути которой было рассказано в прошлом уроке. Для редактирования формулы, введенной в ячейку Е9 при присвоении имени "КодРядФинансТек", откройте в меню "Вставка" - "Имя" - "Присвоить" окно "Присвоение имени". В этом окне в поле "Формула" отредактируйте ее, удалив знак доллара "$" после буквы Е, чтобы формула приняла вид: "=Финанс!$Е9" (рис. 5).

Рисунок 5

Итак, мы ввели уже два имени, которые обеспечивают эффект, условно названный позиционированием.

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

1. Перед началом операции присвоения имени активной ячейкой должна быть первая ячейка интервала, в котором будет осуществляться операция позиционирования. Например, для интервала "КодРядФинанс" - Е9:Е34, такой ячейкой является Е9, а для интервала "КодРядОтчет" - ячейка Е21 на листе "Отчет".

2. В формуле "позиционирующей" ячейки должна быть записана смешанная ссылка. Например, для вертикального интервала ячеек (таких как "КодРядФинанс" и "КодРядОтчет") в формуле используется абсолютная ссылка на имя столбца и относительная ссылка на номер строки (в формуле "=Финанс!$Е9" $Е - абсолютная, а 9 - относительная ссылка). Для горизонтального интервала (например, D6:K6, рис. 1) в формуле "позиционирующей" ячейки будет относительная ссылка на имя столбца и абсолютная ссылка на номер строки (например, "=РЛФинанс!D$6").

Как связать финансовые результаты с датой баланса

Для обеспечения интерактивной связи периода, за который составляется отчет о финансовых результатах, с датой, на которую составлен баланс, в ячейки С4 и Е4 на листе "Финанс" введем формулы. Для записи периода отчета в ячейку С4 введем формулу:

=ЕСЛИ(МЕСЯЦ(ДатаОтчета)=4;"1 квартал";
ЕСЛИ(МЕСЯЦ(ДатаОтчета)=7;"1 півріччя";
ЕСЛИ(МЕСЯЦ(ДатаОтчета)=10;"9 місяців";
ЕСЛИ(МЕСЯЦ(ДатаОтчета)=1;" ")))).

В данной формуле последовательно выполняются логические условия с помощью известной вам функции "ЕСЛИ".

Разберем на примере первого фрагмента формулы ее синтаксис. Итак, вы записали начало формулы "=ЕСЛИ(МЕСЯЦ(ДатаОтчета)=4;"1 квартал"", что означает:

- если месяц даты, на которую составлен баланс, равен 4, то в ячейку будет записан текст "1 квартал". Как видно из рис. 6, баланс составлен на 1­е число 4­го месяца, следовательно, финансовые результаты определяются за 1­й квартал. Если первое условие не выполняется (месяц даты баланса не равен 4), последовательно будут выполняться остальные условия.

Рисунок 6

Обратите внимание, что завершают формулу четыре круглые скобки, по числу логических функций "ЕСЛИ", функция "МЕСЯЦ" должна быть записана прописными буквами.

Если вы хотите, чтобы при выполнении логического условия в ячейку был внесен какой-либо текст (например, "1 квартал"), обязательно заключите его в двойные кавычки. А если необходимо обеспечить отсутствие в ячейке текста, то есть пробел, его тоже надо заключить в кавычки. Например, при выполнении условия "ЕСЛИ(МЕСЯЦ(ДатаОтчета)=1;" "" в ячейке С4 текста не будет (отчет за год), поэтому в формуле стоят только две двойные кавычки.

Для автоматического заполнения ячейки, в которой указывается год отчета, в ячейку Е4 (рис. 7) необходимо внести формулу "=СЦЕПИТЬ(ГОД(ДатаОтчета);" рік")". В этой формуле мы впервые используем функцию "СЦЕПИТЬ". Эта функция относится к категории текстовых и позволяет объединить текстовые элементы, записав их в одну ячейку.

Рисунок 7

Элементами текста могут быть текстовые строки, числа или ссылки, которые ссылаются на одну ячейку. В нашем случае используются два элемента: порядковый номер года и слово "рік". Первый элемент "ГОД(ДатаОтчета)" представляет собой ссылку на ячейку "ДатаОтчета", которая обрабатывается функцией "ГОД" (функция находится в меню "Вставка функции" в категории "Дата и время"). Второй элемент "рік" заключен в кавычки с одним интервалом, с тем чтобы номер года и слово "рік" не сливались.

Необходимое условие для использования этой функции - разделение входящих в нее элементов без интервала точкой с запятой ";".

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

Например, при составлении баланса на 1 июля отчет о финансовых результатах будет составлен за 1­е полугодие (рис. 8).

Рисунок 8

Кстати, вместо функции "СЦЕПИТЬ" для объединения текстовых элементов можно использовать оператор "&" (амперсант). Наша формула в этом случае будет иметь такой вид:

=ГОД(ДатаОтчета)&" рік".

Перенос данных из рабочего листа

Заполнение отчета о финансовых результатах в принципе не отличается от способа, использованного при заполнении баланса. Источником данных пока по-прежнему является рабочий лист "РЛФинанс" (графа "Всього").

Для заполнения 3­й графы листа "Финанс" в ячейку F9 введите следующую формулу массива:

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

Структура этой формулы точно такая же, как та, которую вы вводили в форму 1 (баланс). Отличие только в используемых аргументах. Для упрощения ввода формулы можно открыть лист "Отчет", скопировать из ячейки G22 (рис. 6) введенную в нее формулу и вставить ее в ячейку F9.

Замените в каждом аргументе формулы (за исключением аргумента "ДатаОтчета") символы "Отчет", "Отчета" на символы "Финанс" и завершите ее ввод нажатием комбинации клавиш Ctrl - Shift - Enter.

Если вы предварительно ввели в рабочий лист значения финансовых результатов за предыдущий год, в ячейке G9 появится числовое значение.

Внимание! Может случится, что после ввода этой формулы в ячейке появится сообщение об ошибке "#ИМЯ?". Такая ситуация может возникнуть в том случае, если используемое имя или имена отсутствуют в списке введенных в ваш файл имен.

Откройте в поле имен список и, последовательно выбирая используемые в формуле имена, убедитесь, что они правильно заданы (помните? Если щелкнуть ЛКМ на выбранной строке в поле имен, выбранная область будет выделена инверсным цветом). Если найдете расхождения в именах, вручную отредактируйте их в формуле.

Для заполнения 4­й графы листа "Финанс" в ячейку G9 введите следующую формулу:

{=СУММ(ЕСЛИ(МЕСЯЦ(ДатаРЛФинанс)=1;1;0)*

ЕСЛИ(КодРядФинансТек=КодРядРЛФинанс;1;0)*ВсегоРЛФинанс}

Для этого, как и в рассмотренном выше случае, можно скопировать подобную формулу с листа "Отчет". Проведите необходимое редактирование, заменив в ней символы "Отчет" на символы "Финанс". Скопируйте эти формулы в ячейки соответствующих граф.

В ячейку F11 введите формулу "=F9-F10" и скопируйте ее в ячейку G11 (рис. 4).

В ячейку F22 ("Чистый финансовый результат, прибыль") введите формулу:

=ЕСЛИ(F11-F12+F13-F14+F15-F16+F18-F19-
F20<0;0;F11-F12+F13-F14+F15-F16+F18-F19-F20),

которая даст возможность в случае отрицательного результата в ячейку F22 записать 0 (нуль). Приведенную формулу скопируйте в ячейку G22.

В ячейку F23 ("Чистый финансовый результат, убыток") введите формулу:

=ЕСЛИ(F11-F12+F13-F14+F15-F16+F18-F19-F20>0;0;F11-F12+F13-F14+F15-F16+F18-F19-F20)

и скопируйте ее в ячейку G23.

При внесении этих формул обратите внимание на знаки неравенства.

В ячейку F34 введите формулу "=F29+F30+F31
+F32+F33"
и скопируйте ее в ячейку G34. На этом ввод формул в отчет о финансовых результатах закончен.

Защита отчетных форм от изменений

Составление любых документов, в том числе финансовой отчетности, в которых для ввода данных используются формулы, сопряжено с возможной потерей данных. Что имеется в виду? Как вы знаете, ячейки, в которых записаны формулы, обрабатываются автоматически, и введение в них каких-либо данных (после подтверждения ввода нажатием клавиши "ОК") приводит к замене прежних данных новыми.

Представьте, что произойдет, если в ячейки графы 3 или 4 финансового отчета вы введете вручную какое-нибудь числовое значение, забыв, что там записана формула массива? Формула будет заменена этим числом, и вы лишитесь возможности заполнять финансовую отчетность автоматически. Конечно, программа Excel имеет возможность вернуться к прежнему состоянию, воспользовавшись командой "Отменить ввод". Однако такой вариант не всегда может спасти от последствий поспешных действий, так как с помощью этой команды вы сможете вернуться только на 16 шагов назад, и ваш труд может оказаться напрасным.

Чтобы избежать возможных проблем с восстановлением потерянных данных, Excel может защитить их от изменений. Это бывает также актуальным, если ваши экселевские документы необходимо защитить от другие пользователей, имеющих к ним доступ.

По умолчанию все ячейки в Excel закрыты для доступа. Это вы можете проверить, открыв окно "Формат ячеек", где на вкладке "Защита" включен переключатель "Защищаемая ячейка" (рис. 9).

Рисунок 9

Однако защита ячеек не будет действовать до тех пор, пока не будет защищен весь лист. Если вы решили защитить от доступа только определенные ячейки на листе (например, ячейки F9 и G9 на листе "Финанс", рис. 4), снимите защиту с остальных ячеек листа. Для этого выделите весь лист, щелкнув ЛКМ в верхнем левом углу рабочего окна, и на вкладке "Защита" выключите переключатель "Защищаемая ячейка", нажав "ОК". После этого выделите ячейки, которые необходимо защитить (например, F9 и G9) и опять активизируйте переключатель "Защищаемая ячейка". В меню "Сервис" выберите строку "Защита" - "Защитить листѕ" и нажмите клавишу "ОК" (рис. 10). После этих действий выделенные вами ячейки станут недоступными для изменений.

Рисунок 10

Внимание! Установка защиты листа приведет также к невозможности произведения операций по форматированию.

Если такая необходимость возникнет, тем же способом снимите защиту листа. Кстати, для защиты от несанкционированного изменения защищенного листа можно применить пароль (рис. 10). Будьте осторожны с использованием пароля, так как, забыв его, вы потеряете возможность внести любые изменения в рабочий лист.

Печать отчетных документов

Итак, вы имеете формы, которые можно использовать для сдачи финансовой отчетности. Теперь надо сделать так, чтобы при их распечатывании они имели вид, определенный П(С)БУ 25.

Начнем с финансового отчета. По объему он занимает 2 страницы, поэтому "шапку" отчета и актив баланса необходимо расположить на одной странице, а пассив баланса - на другой. Для этого используйте такие элементы форматирования:

для слов "Фінансовий звіт":

шрифт - Times New Roman Cyr, начертание - полужирный, размер - 14;

для остальных символов:

шрифт - Times New Roman Cyr, начертание - обычный, размер - 11 (начертание "полужирный" используйте, как показано на рис. 6). Для правильного размещения отчета на листах отформатируйте высоту строк и ширину столбцов.

Для этого выделите весь лист "Отчет", щелкните ПКМ на выделенной зоне, и в контекстном меню вызовите окно "Высота строки", в котором введите значение 15 (рис. 11).

Рисунок 11

Затем увеличьте высоту строки "Фінансовий звіт" до значения 24, а строки, в которых расположены "шапки" разделов "Актив" и "Пассив" (строки 19 и 48), - до значения 60.

Выделите столбцы А, В, С, D, E, F, G, щелкните ПКМ на выделенной зоне и в контекстном меню вызовите окно "Ширина столбца". Введите в поле значение 8,5 и нажмите кнопку "ОК". Затем увеличьте ширину столбца D до 22, потянув курсором за границу столбцов D и E (ширину столбца можно контролировать по всплывающему окошку ).

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

Для строки 3 ("2. Звіт про фінансові результати"), строки 6 ("I. Фінансові результати", рис. 6) и строки 25 ("II. Елементи операційних витрат", рис. 4):

шрифт - Times New Roman Cyr, начертание - полужирный, размер - 14, высота строки - 20.

для остальных строк:

шрифт - Times New Roman Cyr, начертание - обычный, размер - 11, высота строк 7 и 27 - 75, высота строк 11 и 21 - 26 (рис. 4).

Ширина всех столбцов - 8,5 (кроме столбца D - 22,00).

Такие установки позволят оптимально разместить финансовый отчет и отчет о финансовых результатах для печати.

Перед печатью документа обязательно используйте команду "Предварительный просмотр" (меню "Файл"), выводящую на экран документ в том виде, в котором он будет напечатан (рис. 12).

Рисунок 12

В этом режиме пунктирными линиями указаны границы зоны печати и позиции табуляции, перемещая которые ЛКМ можно изменять размеры столбцов, поля и колонтитулы.

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

Итак, вы готовы печатать. В этом же окне нажимайте на кнопку "Печатьѕ" (кстати, многоточие, стоящее после любой команды, говорит о том, что перед выполнением команды будет открыто дополнительное окно). Появится знакомое вам окно "Печать" (рис. 13).

Рисунок 13

Установите в нем необходимое число копий (например, 3), а в зоне "Вывести на печать" включите опцию "выделенные листы".

Проследите, чтобы была выключена опция "всю книгу", иначе на печать может быть послан не один десяток страниц.

Если все настройки вас устраивают, нажимайте на кнопку "ОК" - и отчет готов.

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


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

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