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

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

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

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


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

Наприклад, відповідно до останніх змін у Стандарті, стаття "Інші операційні прибутки" тепер має код 040, а у створеному вами робочому аркуші цей блок має код 060. Тому відредагуйте комірки А31:А36 (мал. 8).

Малюнок 8.

Відповідні зміни внесіть і до інших блоків.

Автоматизація заповнення робочого аркуша

У 9-му уроці ви дізналися, як переносяться дані з Головної книги до робочого аркуша фінансового звіту. Формули, що використовуються в робочому аркуші звіту про фінансові результати, подібні.

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

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

(після введення другої закриваючої круглої дужки не забудьте натиснути Ctrl-Shift-Enter).

Як бачите, синтаксис формули такий само, як і при заповненні комірок робочого аркуша фінансового звіту. Незначна відмінність полягає в аргументах. Другий аргумент ("Код020РЛФинансТек") формули має явну вказівку на те, що він пов'язаний з аркушем "Финанс". У зв'язку з цим, як і для комірок аркуша "Отчет", необхідно буде відповідним коміркам аркуша "Финанс" присвоїти подібні імена.

Малюнок 9.

Для того щоб ім'я "Код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), має бути записано січень звітного року.

Малюнок 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, до можливостей з її допомогою автоматизувати рутину бухгалтерського обліку, значить, ми досягли своєї мети. Чекаємо ваших пропозицій щодо тематики подальших публікацій. На наступному уроці ми почнемо створювати платіжні документи з автоматичним їх заповненням.


Наступний урок

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