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

Автоматизація бухобліку: Урок 7.
Бухгалтерський облік за допомогою EXCEL

Автоматизація складання фінансової звітності малого підприємства

Володимир ЛАВРЄНОВ


Попередні уроки "Автоматизації бухобліку: 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:B124;

- ім'я "ВсегоРЛФинанс" - коміркам 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). Однак захист комірок не буде діяти доти, поки не буде захищений весь аркуш. Якщо ви вирішили захистити від доступу тільки певні комірки на аркуші (наприклад, комірки F9 і G9 на аркуші "Финанс", мал. 4), зніміть захист з інших комірок аркуша.

Малюнок 9

Для цього виділіть весь аркуш, клацнувши ЛКМ у верхньому лівому кутку робочого вікна, і на вкладці "Защита" вимкніть перемикач "Защищаемая ячейка", натиснувши "ОК". Після цього виділіть комірки, які потрібно захистити (наприклад, 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