Теми статей
Вибрати теми статей
Сортувати за темами

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

Редакція ВД
Власне Діло Жовтень, 2009/№ 19
Друк
Стаття

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

 

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

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

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

 

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

КУпАП

— Кодекс України про адміністративні правопорушення від 07.12.84 р. № 8073-X.

Закон про ПДВ

— Закон України «Про податок на додану вартість» від 03.04.97 р. № 168/97-ВР.

Порядок № 599

— Порядок видачі Свідоцтва про сплату єдиного податку, затверджений наказом ДПАУ від 29.10.99 р. № 599.

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

— Інструкція про оподаткування доходів фізичних осіб від зайняття підприємницькою діяльністю, затверджена наказом ГДПІУ від 21.04.93 р. № 12.

 

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

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

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

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

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

 

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

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

електронний облік не звільняє підприємця від необхідності вести Книгу ф. № 10 у паперовому вигляді (див. «Вісник податкової служби України», 2005, № 32, с. 45). Інакше кажучи, підприємці на загальній системі та єдиноподатники зобов'язані заповнювати паперову Книгу ф. № 10, незалежно від того, чи є її електронна копія чи ні (може не бути або й бути, але іншої форми — зручна для підприємця).

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

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

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

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

 

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

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

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

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

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

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

Порядок № 599 вимагає заповнювати 4 графи Книги ф. № 10: «Дата», «Витрати», «Дохід (виручка)» і «Чистий дохід». Як правило, перші три графи єдиноподатники заповнюють за підсумками дня — загальною сумою, а останню — раз на місяць. Але якщо такої інформації вистачає для перевіряючих, то для самого підприємця її потрібно значно більше, інакше деякі рішення (скільки товару є на складі та скільки можемо відвантажити, скільки товару необхідно замовити, кому час сплатити рахунки, а кому ще можна почекати тощо) доведеться приймати просто наосліп. Для того щоб у будь-який момент отримати реальні дані за касою, розрахунковим рахунком, товарами і боргами, слід знати про кожну операцію. А це зручно реалізувати в MS Excel.

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

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

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

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

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

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

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

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

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

готовий файл з розробленою програмою на сайті http://nibu.factor.ua/info/download/, вибравши правою кнопкою миші пункт «Сохранить как...», і трохи підкоригувати його з урахуванням своїх даних.

 

img 1

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

 

img 2

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

 

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

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

 

Дата

Операция

Контрагент

№ документа

Сумма, грн.

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

Признак платежа: наличный — касса (К); безналичный — расчетный счет (РС)

Товарный учет (услуги)

Номер налоговой накладной (входящей или исходящей)

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

Сумма без НДС, грн.

Ставка НДС

НДС, грн.

Использование в хоздеятельности или услуги, регулируемые пунктом 6.5 Закона об НДС

НО

НК

Поступление (П) или отгрузка (О)

Наименование

Количество (ед. изм.)

Сумма, грн.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

 

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

— «Дата»;

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

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

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

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

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

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

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

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

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

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

— ставка 20 %;

— ставка 0 %;

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

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

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

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

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

Ставка НДС» та йдемо до пункту меню «Данные» — «Проверка» — вкладка «Параметры». Тип даних вибираємо «Список», а в полі «Источник» пишемо через знак «;» необхідні варіанти: «20 %; 0 %; друга подія; не платник; звільнені; не об'єкт» (рис. 3).

 

img 3

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

 

Графа «

Признак платежа» має два варіанти: готівкові розрахунки (К — каса) і безготівкові (РС — розрахунковий рахунок). Ознака платежу стане у нагоді надалі для того, щоб підрахувати залишок грошей окремо в касі та на розрахунковому рахунку.

Графа «

Использование в хоз. деятельности» необхідна для обліку ПДВ. Вона має такі варіанти: «-; Использ. в хозд.; Не использ. в хозд; пункт 6.5 Закона об НДС».

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

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

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

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

 

 

B

C

<…>

4

Содержание операции

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

5

Выплата зарплаты работникам

Р

6

Доплата взноса в ПФ «за себя»

Р

7

Зачисление на р/сч наличных из кассы

ВО (+)

8

Изъятие наличных из кассы для зачисления на р/сч

ВО (-)

9

Личные расходы

ВО

10

Оплата единого налога

Р

11

Оплата от покупателя за товар (услуги)

В

12

Оплата поставщику за основные средства

ВО

13

Оплата поставщику за товары (услуги)

Р

14

Отгрузка товара

ВО

15

Передача результата услуг заказчику

ВО

16

Покупка основных средств (получение)

ВО

17

Получение в кассу наличных с р/сч

ВО (+)

18

Получение услуг от исполнителей

ВО

19

Приход товара

ВО

20

Снятие наличных с р/с для зачисления в кассу

ВО (-)

21

Уплата иных налогов

Р

22

Уплата НДФЛ и соцвзносов с ЗП наемных работников и подрядчиков

Р

23

Услуги банка

Р

24

<…>

<…>

 

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

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

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

Операции». У полі «Формула» заносимо такий вираз:

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

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

$B$60);1)

, де

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

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

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

«Данные» — «Проверка» — вкладка «Параметры». Тип даних вибираємо «Список», а в полі «Источник» пишемо «=Операции» (тобто ім'я нашого довідника). Що вийшло, див. на рис. 4.

 

img 4

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

 

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

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

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

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

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

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

, де

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

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

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

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

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

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

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

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

 

img 5

Рис. 5. Автоматичне заповнення ознаки операції на підставі функції ВПР

 

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

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

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

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

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

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

, де

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

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

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

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

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

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

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

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

 

Столбец

Формула

«Поступление или отгрузка»

=ЕСЛИ(Т(B10)=”Отгрузка товара”;”О”;ЕСЛИ(Т(B10)=”Передача результата услуг заказчику”;”О”;ЕСЛИ(Т(B10)=”Приход товара”;”П”;ЕСЛИ(Т(B10)=”

Получение услуг от исполнителей”;”П”;”-”))))

«Сумма, грн. (товарный учет)»

=ЕСЛИ(ИЛИ(H10=”О”;H10=”П”);E10;”-”)

«НДС, грн.»

=ЕСЛИ(ИЛИ(O10=20 %;O10=0 %);N10*O10;”-”)

 

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

 

img 6

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

 

img 7

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

 

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

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

додатку 10 до Інструкції № 12. Щоправда, податківці на місцях можуть вносити зміни до форми Книги (як правило, додають додаткові колонки). Але єдиноподатників це хвилювати не повинно, оскільки відповідно до пункту 8 Порядку № 599 у Книзі ф. № 10 вони зобов'язані заповнювати тільки 4 графи: «Період обліку», «Витрати на виробництво продукції», «Сума виручки (доходу)» та «Чистий дохід». Як це зробити автоматично, зараз і розберемося.

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

 

Период учета (день, неделя, месяц, год)

Количество изготовленной продукции, предоставленных услуг

Расходы на производство продукции, грн.

Количество проданной продукции, предоставленных услуг (ед. изм.)

Цена продажи продукции (услуг), грн.

Сумма выручки (дохода) (грн.)

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

1

2

3

4

5

6

7

 

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

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

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

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

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

 

Критерій 1

Критерій 2

Результат

Графа Книги ф. № 10

Значення в графі «Период учета» Книги ф. № 10 повинно збігтися зі значенням у графі «Дата» Журналу операцій

У графі «Признак операции» Журналу операцій повинно бути вказано «Р» (витрати)

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

«Витрати на виробництво»

Значення в графі «Период учета» Книги ф. № 10 має збігтися із значенням в графі «Дата» Журналу операцій

У графі «Признак операции» Журналу операцій має бути зазначено «В» (виручка)

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

«Сума виручки»

 

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

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

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

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

 

Графа Книги ф. № 10

Формула

«Расходы на производство»

{=СУММ((Операции!$A$10:$A$514=A20)*(Операции!$F$10:$F$514=”Р)* (Операции!$E$10:$E$514))}

«Сумма выручки»

{=СУММ((Операции!$A$10:$A$514=A20)*(Операции!$F$10:$F$514=”В)* (Операции!$E$10:$E$514))}

Операции!$A$10:$A$514 — стовпчик «Дата» в Журналі операцій («Операции» — назва листа, на якому розташовано Журнал операцій);

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

Операции!$F$10:$F$514 — стовпчик «Признак операции» Журналу операцій («Операции» — назва листа, на якому розташовано Журнал операцій);

Операции!$E$10:$E$514 — стовпчик «Сумам, грн.» Журналу операцій, дані якого підсумовуються («Операции» — назва листа, на якому розташовано Журнал операцій)

 

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

(формулу буде сприйнято як формулу масиву)!

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

 

img 8

Рис. 8. Автоматичне заповнення Книги ф. № 10 за допомогою формули масиву

 

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

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

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

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

, де

F37

— щомісячна сума виручки =СУММ(F6:F36);

C37

— щомісячна сума витрат =СУММ(C6:C36).

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

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

 

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

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

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

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

 

 

A

B

C

D

E

F

<…>

7

Дата

Поступление

Поступление из кассы

Расходование

Снятие в кассу

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

8

<…>

<…>

<…>

<…>

<…>

<…>

 

«Дата»

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

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

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

 

надійшла на розрахунковий рахунок

вибула з розрахункового рахунку

— від контрагентів (виручка);

— з каси (ВО(+))

— контрагентам, бюджету тощо (витрати);

— до каси (ВО(-))

 

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

 

Критерій 1

Критерій 2

Критерій 3

Результат

Графа

«Дата» таблиці «Розрахунковий рахунок» має збігатися з «Датою» Журналу операцій

У графі «Признак платежа» Журналу операцій зазначено «РС» (операцію проведено через розрахунковий рахунок)

У графі «Признак операции» Журналу операцій зазначено «В»

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

«Поступление»

У графі «Признак операции» Журналу операцій зазначено «В (+)»

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

«Поступление из кассы»

У графі «Признак операции» Журналу операцій зазначено «Р»

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

«Расходование»

У графі «Признак операции» Журналу операцій зазначено «В (-)»

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

«Снятие в кассу»

 

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

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

 

Графа

Формула

1

2

«Поступление»

{=СУММ((Операции!$A$10:$A$510=A8)*(Операции!$G$10:$G$510=”РС)*

(Операции!$F$10:$F$510=В)*(Операции!$E$10:$E$510))}

«Поступление из кассы»

{=СУММ((Операции!$A$10:$A$510=A8)*(Операции!$G$10:$G$510=”РС)*

(Операции!$F$10:$F$510=ВО(+))*(Операции!$E$10:$E$510))}

«Расходование»

{=СУММ((Операции!$A$10:$A$510=A8)*(Операции!$G$10:$G$510=”РС)*

(Операции!$F$10:$F$510=Р)*(Операции!$E$10:$E$510))}

«Снятие в кассу»

{=СУММ((Операции!$A$10:$A$510=A8)*(Операции!$G$10:$G$510=”РС)*

(Операции!$F$10:$F$510=ВО(-))*(Операции!$E$10:$E$510))}

Операции!$A$10:$A$510 — стовпчик «Дата» в Журналі операцій («Операции» — назва листа, на якому розташовано Журнал операцій);

A8 — комірка стовпчика «Дата» таблиці «Розрахунковий рахунок», в якій зазначено необхідну дату;

Операции!$G$10:$G$510 стовпчик «Признак платежа» Журналу операцій;

Операции!$F$10:$F$510 стовпчик «Признак операции» Журналу операцій;

Операции!$E$10:$E$510 стовпчик «Сумма, грн.» Журналу операцій, дані якого підсумовуються

 

Важливо:

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

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

 

img 9

Рис. 9. Фрагмент Журналу операцій за 15.01.2010 р. і 16.01.2010 р. та автоматичне визначення
залишку коштів на поточному рахунку за цими датами

 

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

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

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

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

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

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

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

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

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

 

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

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

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

 

 

A

B

C

D

E

F

G

H

I

J

<…>

4

Дата

Количество, ед. изм.

5

Товар 1

Товар 2

Товар 3

6

Приход

Отгрузка

Остаток

Приход

Отгрузка

Остаток

Приход

Отгрузка

Остаток

7

Остаток на начало

 

 

<…>

 

 

<…>

 

 

<…>

8

<…>

<…>

<…>

<…>

<…>

<…>

<…>

<…>

<…>

<…>

 

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

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

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

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

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

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

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

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

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

 

Графа

Формула

«Приход»

{=СУММ((Операции!$A$10:$A$510=A8)*(Операции!$H$10:$H$510=”П)*

(Операции!$I$10:$I$510=$B$5)*(Операции!$J$10:$J$510))}

«Отгрузка»

{=СУММ((Операции!$A$10:$A$510=A8)*(Операции!$H$10:$H$510=”О)*

(Операции!$I$10:$I$510=$B$5)*(Операции!$J$10:$J$510))}

Операции!$A$10:$A$510 — стовпчик «Дата» в Журналі операцій («Операции» — назва листа, на якому розташовано Журнал операцій);

A8 — комірка стовпчика «Дата» таблиці «Наличие товаров (количественный учет)», у якій зазначена поточна дата;

Операции!$H$10:$H$510 — стовпчик «Признак: поступление (П) или отгрузка (О)» Журналу операцій;

Операции!$I$10:$I$510 — стовпчик «Наименование товара» в Журналі операцій;

$B$5 — вибрана з випадного списку назва товару в 5-му рядку таблиці «Наличие товаров (количественный учет)»;

Операции!$J$10:$J$510 — стовпчик «Количество» Журналу операцій, дані якого підсумовуються

 

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

«Остаток»

товарів на поточну дату розраховується просто: залишок на початок плюс прихід мінус відвантаження.

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

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

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

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

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

 

img 10

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

 

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

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

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

 

Дата

Покупатели

Поставщики

Наименование покупателя

Наименование поставщика

Отгружено товара (передано услуг), грн.

Получена оплата, грн.

Долг на текущую дату

Уплачено, грн.

Получено от него товаров (услуг), грн.

Долг на текущую дату

НАМ

МЫ

НАМ

МЫ

Остаток на начало

 

 

<…>

 

 

 

 

<…>

<…>

<…>

<…>

<…>

<…>

<…>

<…>

<…>

<…>

 

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

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

1) дата;

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

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

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

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

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

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

 

Графа

Формула

«Отгружено товара (передано услуг), грн.»

{=СУММ((Операции!$A$10:$A$510=B8)*(Операции!$H$10:$H$510=”О)*

(Операции!$C$10:$C$510=$C$4)*(Операции!$E$10:$E$510))}

«Получена оплата, грн.»

{=СУММ((Операции!$A$10:$A$510=B8)*(Операции!$F$10:$F$510=”В)*

(Операции!$C$10:$C$510=$C$4)*(Операции!$E$10:$E$510))}

«Уплачено, грн.»

{=СУММ((Операции!$A$10:$A$510=B8)*(Операции!$F$10:$F$510=”Р)*

(Операции!$C$10:$C$510=$G$4)*(Операции!$E$10:$E$510))}

«Получено от него товаров (услуг), грн.»

{=СУММ((Операции!$A$10:$A$510=B8)*(Операции!$H$10:$H$510=”П)*

(Операции!$C$10:$C$510=$G$4)*(Операции!$E$10:$E$510))}

«Долг на текущую дату» МЫ

=ЕСЛИ(($C8-$D8+$E7-$F7)<0;(0-($C8-$D8+$E7-$F7));0)

«Долг на текущую дату» НАМ

=ЕСЛИ(($C8-$D8+$E7-$F7)>0;$C8-$D8+$E7-$F7;0)

 

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

 

img 11

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

 

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

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

Выручка с начала года» щомісячні підсумкові суми з Книги ф. № 10. Крім того, можна зробити додаткову комірку, в якій контролювати, чи не перевищила виручка 500 тис. грн.

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

 

Графа

Формула

«Выручка с начала года»

='Книга 10'!$F$37+'Книга 10'!$F$66+'Книга 10'!$F$98+'Книга 10'!$F$129+'Книга 10'!$F$161+'Книга 10'!$F$192+'Книга 10'!$F$224+'Книга 10'!$F$256+'Книга 10'!$F$287+'Книга 10'!$F$319+'Книга 10'!$F$350+'Книга 10'!$F$382

Контрольна комірка

=ЕСЛИ(H35>500000;”Выручка на едином налоге превысила 500 тыс. грн.!!!;””)

Н35 — комірка, в якій розраховується сума виручки наростаючим підсумком;

'Книга 10'!$F$37, …, 'Книга 10'!$F$382 — щомісячний підсумок виручки, починаючи з січня по грудень поточного року. Береться з листа, на якому розташовано Книгу ф. № 10

 

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

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

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

«Данные» — «Фильтр» — обрати «Автофильтр». Біля кожної комірки шапки з'явиться трикутничок з випадним списком умов фільтру. Так, вибравши в полі «Контрагенти» певного покупця (постачальника), отримаємо всі дані про суми ПЗ або ПК за цим контрагентом.

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

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

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

 

img 12

Рис. 12. Операції, відфільтровані за умовою «Контрагент», — «МКС (ООО)», а також сума ПЗ
за ПДВ щодо цього контрагента за січень 2010 року

 

Висновок

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

http://nibu.factor.ua/info/download, вибравши правою кнопкою миші пункт «Сохранить как...». Сподіваємося, що наведений приклад допоможе швидко створити власну базу даних, вчасно отримувати необхідну інформацію та заощадити надалі час.

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


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

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

2

У цьому випадку враховуються всі сплачені витрати, а не тільки пов'язані з отриманим у цьому місяці доходом. Але для єдиноподатника такий незначний відступ не страшний, оскільки сума чистого доходу на суму єдиного податку не впливає. Якщо вже податківці й прискіпуватимуться, то можуть хіба що накласти адмінштраф за неналежне ведення обліку доходів і витрат (51 — 136 грн., стаття 1641 КУпАП).
App
Завантажуйте наш мобільний додаток Factor

© Factor.Media, 1995 -
Всі права захищені

Використання матеріалів без узгодження з редакцією заборонено

Ознайомитись з договором-офертою

Приєднуйтесь
Адреса
м. Харків, 61002, вул. Сумська, 106а
Ми приймаємо
ic-privat ic-visa ic-visa
Powered by
Factor Web Solutions
Ми використовуємо cookie-файли, щоб зробити сайт максимально зручним для вас та аналізувати використання наших продуктів та послуг, щоб збільшити якість рекламних та маркетингових активностей. Дізнатися більше про те, як ми використовуємо ці файли можна тут.
Дякуємо, що читаєте нас Увійдіть і читайте далі