Лабораторні роботи № 3-5

«Побудова Загального журналу, Головної книги та фінансового звіту малого підприємства засобами MS Excel»

Умови:

1. Засобами MS Excel створити Загальний журнал щоденних видатків і поступання малого підприємства в якому містяться такі реквізити:

а) дати записів;

б) назви рахунків (операцій);

в) номери рахунків;

г) дебет, грн.;

д) кредит, грн.

2. На основі записів Загального журналу створити Головну книгу в якій містяться такі реквізити:

а) назви рахунків (операцій);

б) номери рахунків;

в) дебет, грн.;

г) кредит, грн.

3. На основі записів Головної книги створити Фінансовй звіт в якому містяться записи про:

а) номери записів 1-9, 10-19, 20-29, 30-39, 40-49.

б) оборот, грн.

 

Розв’язок: Створюємо електронну форму Загального журналу на окремому аркуші електронної таблиці з назвою «Загальний журнал».

 

	A	B	C	D	E
		Загальний журнал
	Дати	Назви рахунків	Номер рахунку	Дебет,грн	Кредит,грн
	01.03.08	Меблі
	01.03.08	Комп'ютери
	02.03.08	Оренда
	03.03.08	Каса
	04.03.08	Дебіторська забогованість
	04.03.08	Розпродаж
	05.03.08	Оплата комун. послуг
	05.03.08	Кредиторська заборгованість
	06.03.08	Страхування
	08.03.08	Заробітня плата
	09.03.08	Каса
	12.03.08	Кава для персоналу
	13.03.08	Каса

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

На наступному аркуші електронної таблиці з назвою «Головна книга» рис. 2 показано записи головної книги, які нагромаджують дані на спеціальних рахунках про кожну господарську операцію на протязі місяця, наприклад, березня. У комірку А6 Уведемо одну із дат місяця березня, наприклад, 01.03.08.

 

	A	B	C	D	E
		Головна книга
	Дати	Назви рахунків	Номер рахунку	Дебет,грн	Кредит,грн
	01.03.08	Каса
		Кава для персоналу
		Меблі
		Комп'ютери
		Заробітня плата
		Оплата комун. послуг
		Оренда
		Дебіторська забогованість
		Кредиторська заборгованість
		Страхування
		Розпродаж

 

Для полегшення перенесення записів з робочого листа Загального журналу введено чотири імені діапазонів: EntryDate (діапазон А5:А26), AccountNumber (діапазон С5:С26), Journal Debits (діапазон D5: D26) і, Journal Credits (діапазон E5:Е26). При виконанні лабораторної роботи назви діапазонів комірок можна змінити.

В робочому листі головної книги введено імена LedgerDate для комірки $A$6 i GLAccount для комірок $С6:$C26. У діапазон комірок С6:С26 уводимо номери рахунків, що фігурують в Загальному журналі,по яких необхідно знайти підсумки по кредиту і дебету за місяць березень. Для знаходження підсумків рахунків по дебету виділимо діапазон комірок D6:D26 в робочому листі головної книги і уведемо уведемо формулу

=СУММ(ЕСЛИ(МЕСЯЦ(EntryDate)=МЕСЯЦ(LedgerDate);1;0)*ЕСЛИ(AccountNumber = GLAccount;1;0)*JournalDebits)

Після цього необхідно натиснути одночасно комбінацію трьох клавіш “Ctrl”+ “Shift”+ “Enter”, остання формула є формулою масиву.

Зауважимо, що остання формула часто не виконується (неякісне програмне забезпечення комп’ютера, або невитримані всі вимоги до формули массиву із іменами діапазонів комірок). Для вирішення такої проблеми небхідно у формулі замінити GLAccount на С6. Після цього скопіювати вмістиме комірки D6 на D7:D26

Для вибору відповідніх кредитів небхідно виділити діапазон комірок Е6:Е26 і увести формулу:

=СУММ(ЕСЛИ(МЕСЯЦ(EntryDate)=МЕСЯЦ(LedgerDate);1;0)*ЕСЛИ(AccountNumber = GLAccount;1;0)*Journal Credits)

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

Зауваження 2: Нагадаємо щераз, що останні формули необхідно вводити як формули масиву. Набравши формулу не спішіть натисккати <Enter>; спочатку натисніть комбінацію клавіш <Ctrl+Shift>. Ви побачите, що тепер формула поміщена у фігурні дужки. Це означає, що це вже є формула масиву. У звіті про лабораторну роботу небхідно детально описати дію цих формул.

На основі записів Головної книги створимо Фінансовй звіт.

 

Нехай всі рахунки, що мають відношення до фіксованих адміністративних витрат мають номери від 20 до 29. Якщо в головній книзі присвоїти діапазону, що містить всі номери рахунків, ім”я LedgerAccount, діапазону, що містить кредит ім”я LedgerCredits, а діапазону, що містить дебет, - ім”я LedgerDebits, то формула:

 

=СУММ(ЕСЛИ(ОТБР(LedgerAccount/10)=2; LedgerCredits- LedgerDebits;0))

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

Якщо план бухгалтерських рахунків призначає всім рахункам, що відносяться фіксованих затрат на виробництво двохзначні номери, які починаються з цифри 4 (40, 41,…,49), то щоб вибрати такі рахунки досить використати таку формулу:

 

=СУММ(ЕСЛИ(ОТБР(LedgerAccount/10)=4; LedgerCredits- LedgerDebits;0))

За допомогую приведених формул вибрати із головної книги рядки з фіксованими адміністративними витратами і фіксованими затратами на виробництво. В нашому випадку Фінансовий звіт має такий вигляд:

 

	A	B	C	D
		Фінансовий звіт
		Номер рахунку	Оборот,грн

 

Література:

1.Карлберг, Конрад. Бизнес-анализ с помощью EXCEL.: Пер с англ. – К.: Диалектика, 1997.- 448с.: ил.

2. Клименко О.Ф.,Головко Н.Р.., Шарапов О.Д. Інформатика та комп’ютерна техніка: Навч.-метод. посібник / За заг. ред. О.Д. Шарапова. – К: КНЕУ. 2002. 534 с.

3.Тхір Ігор, Калушка Володимир, Юзьків Андрій Посібник користувача ПК. Видання третє.- Тернопіль: Підручники і посібники, 2006. – 1024 с.; іл.