Комп’ютер у підприємця. Путівник по світу автоматизованого обліку. Частина 1 «МS Excel»

Путівник по світу автоматизованого обліку. Частина 1 «MS Excel»

Цією публікацією ми відкриваємо нову підрубрику «Комп'ютер у підприємця». Тут підприємці зможуть знайти цікаві новини та поради щодо застосування програм з обліку доходів і витрат та складання звітності (у тому числі електронної), опис корисних можливостей, якими можна користуватися за наявності підключення до Інтернету, а також важливі секрети роботи з ПК.

Безперечно, найцікавіше — це програми, що дозволяють підприємцю автоматизувати свій облік: які вони? які їх функції? скільки коштують? і якій із них віддати перевагу? Пропонуємо вашій увазі путівник. А починаємо, звісно, з найдешевшого варіанта — облік доходів і витрат у MS Excel (безкоштовна програма своїми руками).

Віталіна МІРОШНИЧЕНКО, консультант газети «Власне Діло»

Документи статті

КУпАП

Закон про ПДВ

Порядок № 599

Інструкція № 12

Випередити конкурентів

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

Тим, у кого на день до 5 — 10 операцій, начебто немає сенсу купувати спеціальну програму обліку. Проте навіть у таких обставинах хочеться автоматизації. Для цього варто скористатися хоча б стандартним додатком MS Excel. За його допомогою можна швидко й безпомилково підрахувати необхідні підсумки, знайти потрібний запис, увести важливу примітку. Усе це заощадить час, гроші та збереже нерви.

На сьогодні частка підприємців, які бажають автоматизувати свій облік, перевищує 90 %. У відповідь на такий попит з'явилося безліч програм обліку та рекомендацій для самостійної його автоматизації: від елементарного обліку в MS Excel до серйозного в «1С:8.0». Про них, власне, і йтиметься.

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

Книга ф. № 10 (паперова)

Перш ніж розпочинати розмову про програми обліку, слід нагадати, що

Проте дані електронної Книги (як, власне, і всього електронного обліку) будуть доступні тільки підприємцю, оскільки податківцям при перевірці досить надати тільки паперову Книгу ф. № 10. Адже до обов'язків підприємця:

— належить ведення паперової Книги ф. № 10 (з додатком документів, що підтверджують витрати);

— не належить ведення бухгалтерського обліку кожної операції (підприємець здійснює електронний облік виключно за бажанням; у принципі, податківці можуть про його наявність і не здогадуватися).

Отже, перевіряти дані електронного обліку в підприємця податківці не будуть (не мають права).

MS Excel — для єдиноподатників

Почнемо з найпростішого — організації обліку в MS Excel.

Але універсальність MS Excel полягає саме в тому, що, побачивши хоча б один приклад автоматизації, підприємець зможе легко змінити та підстроїти його під себе. Тому зараз

Облік єдиноподатника, на перший погляд, простий.

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

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

— звіт єдиноподатника;

— залишок коштів у касі та на розрахунковому рахунку на цей момент;

— залишок товарів (кількість) на цей момент;

— суму своєї заборгованості перед постачальниками;

— борги покупців;

— дані для заповнення декларації з ПДВ.

На рис. 1 і 2 покажемо, як це виглядатиме в результаті, а потім детально розповімо, яким чином реалізувати таку ідею в MS Excel. Витративши на створення запропонованих таблиць день (максимум два), надалі ви заощадите значно більше часу та оціните переваги автоматизації. Проте можна навіть скачати

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

Рис. 2. Автоматичне формування інформації про кошти на розрахунковому рахунку, залишки товарів, про розрахунки з контрагентами, а також про ПЗ та ПК з ПДВ у розрізі контрагентів (для заповнення додатка 5 до декларації з ПДВ)

Створення Журналу операцій

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

Уручну в цій таблиці необхідно буде заповнювати такі колонки:

— «Дата»;

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

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

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

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

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

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

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

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

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

Для того щоб швидко заповнювати ці комірки, створимо випадний список з варіантами. Як створювати короткий випадний список, покажемо на прикладі графи «Ставка НДС». Отже, у нас є такі варіанти:

— ставка 20 %;

— ставка 0 %;

— операції звільнено від ПДВ;

— операції не є об'єктом оподаткування;

— відбувається друга подія;

— товар отримано/передано неплатнику ПДВ.

Вибираємо першу комірку графи «

Рис. 3. Створення простого випадного списку

Графа «

Графа «

Зробивши випадний список у перших комірках, далі копіюємо їх униз за стовпчиками таблиці.

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

При заповненні зазначених комірок важливо не допускати помилок, оскільки дані з них використовуються в подальших розрахунках. Щоб уникнути помилок, необхідно створити випадний список і надалі вибирати потрібний варіант. Але список товарів, контрагентів, операцій може бути дуже великим, до того ж періодично до нього додаються нові елементи. Як поводитися в такому разі? Розберемося на прикладі графи «Операции».

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

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

Як відомо, виручку єдиноподатник відображає у момент надходження грошей до каси або на розрахунковий рахунок, а витрати — у момент оплати грошей. Усі інші операції: відвантаження товару покупцю або отримання від постачальника — це допоміжні операції, які теж важливо врахувати ^{довідка 1}.

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

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

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

$B$60);1)

$B$5 — абсолютне значення комірки, в якій розміщено найменування першої операції;

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

Далі все просто: відкриваємо лист із Журналом операцій, вибираємо комірку в стовпчику «Операция», йдемо до пункту меню

Рис. 4. Створення довгого випадного списку за допомогою довідника

Зробивши випадний список у першій комірці, далі копіюємо її вниз за стовпчиком таблиці.

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

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

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

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

Справочники!$B$5:$C$60;2;0));””;ВПР(Т(B10);Справочники!$B$5:$C$30;2;0))

ВПР () — функція, що шукає в Довіднику ознаку, яка відповідає назві операції, та видає її у відповідній комірці Журналу операцій;

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

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

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

2 — номер стовпчика в таблиці «Справочник операций», в якому зазначено ознаку операції: «В» — виручка, «Р» — витрати або «ВО» — допоміжна операція (стовпчик з назвою операції вважається першим);

0 — необхідність точного збігу в назві операції при пошуку;

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

Як працює формула, наведено на рис. 5.

Заповнивши першу комірку, копіюємо її вниз на весь стовпчик.

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

Цей стовпчик також заповнюється з використанням функції ВПР:

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

Справочники!$E$5:$I$55;3;0));””;ВПР(Т(C10);Справочники!

$E$5:$I$55;3;0))

ВПР () — функція, що шукає в Довіднику контрагентів номер контрагента за його назвою та видає його в Журналі операцій;

C10 — комірка в Журналі операцій, в якій зазначено назву контрагента;

Справочники!$E$5:$I$55 — довідник контрагентів на листі «Справочники»;

3 — номер стовпчика в таблиці «Справочник контрагентов», в якому зазначено податковий номер контрагента (найменування контрагента вважається стовпчиком № 1, а податковий номер у довіднику контрагентів зазначено в 3-му стовпчику);

0 — вказує на необхідність точного збігу в назві контрагента.

Заповнивши першу комірку, копіюємо її вниз на весь стовпчик.

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

Зазначені стовпчики заповнюються просто (див. табл.)

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

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

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

Створення Книги ф. № 10

Єдиноподатники ведуть Книгу обліку доходів і витрат за формою, зазначеною в

Спершу на окремому листі створимо Книгу ф. № 10.

У ній потрібно буде заповнити колонки № 1, 3, 6 і 7.

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

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

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

Єдиноподатники заповнюють ці графи за підсумками дня, отже, потрібно підсумовувати всю виручку та всі витрати, здійснені протягом кожного дня. Робиться це за допомогою двох критеріїв так:

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

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

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

обоим критериям))}

Важливо: вводити фігурні дужки { } з клавіатури не потрібно, вони з'являються автоматично, якщо після набору формули замість Enter натискувати комбінацію Ctrl+Shift+ Enter

Покажемо наочний приклад на рис. 8.

Увівши зазначену формулу до першої комірки, потім її слід скопіювати вниз за стовпчиком.

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

Значення чистого доходу підприємець може підраховувати раз на місяць або раз на квартал — як йому зручно. Для цього можна підбивати в Книзі ф. № 10 щомісячні підсумки, а потім із загальної суми виручки віднімати загальну суму витрат ^{довідка 2}):

= ЕСЛИ((F37-C37)>0;F37-C37;0)

F37

C37

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

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

Автоматичний облік коштів у касі та на розрахунковому рахунку

Для підприємця важливо знати, скільки конкретно грошей зараз є в касі або на розрахунковому рахунку. Для цього достатньо вести простий облік: скільки грошей було на початок дня, скільки надійшло протягом дня та скільки вибуло.

Уся ця інформація є в Журналі операцій, тільки її необхідно звідти вибрати за певними критеріями. Як це зробити, розповімо на прикладі розрахункового рахунку.

Отже, на окремому листі, який назвемо «Расч_счет» створимо таку таблицю (фрагмент аркуша «Расч_счет»):

«Дата»

Заповнюється так само, як і «Период учета» Книги ф. № 10.

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

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

Визначимося з критеріями, що нам допоможуть відібрати необхідні суми в Журналі операцій.

Для того щоб записати це в MS Excel, знову скористаємося зручною формулою масиву:

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

Важливо:

Покажемо, як працюють зазначені формули, на рис. 9.

Заповнивши формули в перших комірках, далі копіюємо їх униз за стовпчиком.

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

Ця графа розраховується просто: залишок на початок дня плюс сума, що надійшла від контрагентів та з каси, мінус сума, витрачена (сплачена й знята до каси):

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

F9 = F8+B9+C9-D9-E9 (цю формулу копіюємо вниз за стовпчиком).

Після того як створено таблицю, що розраховує залишок коштів на розрахунковому рахунку, потрібно скопіювати її на окремий лист, який назвемо «Касса».

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

Далі відредаговані формули копіюємо вниз за стовпчиками. Останнє — вводимо залишок грошових коштів на початок року в касі.

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

Автоматичний облік товарів (за кількістю)

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

Маємо ситуацію, схожу на розрахунковий рахунок і касу. Отже, розпочнемо зі створення таблиці на новому листі, який назвемо «Товары» (фрагмент таблиці «Наличие товаров (количественный учет)»).

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

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

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

Щоб відібрати кількість товару, що надійшов на склад протягом дня, необхідно взяти з Журналу ті операції, які відповідають трьом критеріям:

— здійснені на певну дату;

— є надходженням товару (у Журналі операцій це ознака надходження «П»);

— належать до конкретного найменування товару.

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

Таким чином, знову скористаємося формулою масиву.

Увага: не забудьте, вводячи формулу, натискувати комбінацію Ctrl+Shift+ Enter.

«Остаток»

Після того як перший рядок таблиці заповнено, копіюємо формули комірок униз за відповідними стовпчиками.

Далі можна піти одним із двох шляхів:

1) або вибирати необхідне найменування товару та отримувати за ним дані;

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

Що отримаємо в результаті, див. на рис. 10.

Автоматичний облік розрахунків з контрагентами

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

На окремому аркуші, який назвемо «Контрагенти», позначаємо таблицю такого виду:

Ідея заповнення комірок та сама, що й при обліку товару та руху грошових коштів: шукаємо потрібні операції в Журналі операцій за певними критеріями. Після того як операції знайдено, підсумовуємо комірки «Сумма, грн.».

Критеріїв три:

1) дата;

2) найменування контрагента;

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

— відвантаження (О) — відвантажено товар;

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

— витрати (Р) — сплачено;

— надходження (П) — отримано товарів.

Автоматичний розрахунок боргів наведено на рис. 11.

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

Звіт єдиноподатника

Автоматичне заповнення цього звіту зробити зовсім нескладно. Необхідно тільки записати до комірки «

Для цього використовуються такі формули:

Автоматичний облік ПДВ

Заповнити декларацію з ПДВ, а також додаток 5 до неї буде набагато простіше, якщо скористатися даними з Журналу операцій, відфільтрувавши їх за контрагентами, ставками ПДВ, датою та іншими критеріями. Зробити це дуже просто.

Досить виділити шапку таблиці, потім перейти до пункту меню

Далі отримані дані можна фільтрувати за датою, у тому числі задаючи умову (місяць, квартал тощо).

На рис. 12 наведено, як працює автофільтр.

Висновок

От, власне, і все, що ми хотіли розповісти про основні прийоми автоматизації обліку в MS Excel. Ще раз нагадуємо, що готовий файл з усіма описаними формулами та розрахунками ви можете скачати за адресою

Ну а для тих підприємців, в яких обсяг операцій набагато більший, ніж 5 — 10 на день, необхідні спеціальні програми обліку. Про них ми розповімо в наступному номері.

Довідкова інформація (довідка)

¹ Ознаки ВО (+) і ВО (-) дозволяють прослідкувати рух грошових коштів з каси на розрахунковий рахунок і навпаки.