Міністерство науки і освіти України
Національний університет “Львівська політехніка”
Кафедра “Системи Автоматизованого Проектування”
Методичні вказівки
до лабораторної роботи № 6
по курсу: “Комп’ютерні системи менеджменту в малому та середньому бізнесі”
АВТОМАТИЗАЦІЯ АНАЛІЗУ ГОСПОДАРСЬКИХ ОПЕРАЦІЙ
ІЗ ЗАСТОСУВАННЯМ СИСТЕМИ EXCEL
Для фахової підготовки інженерів за спеціальністю „Комп‘ютерні системи проектування” – 7.080402
Львів 2002
Мета роботи: засвоїти методи автоматизації окремих сторін бухгалтерського обліку з ціллю отримання оперативної інформації про діяльність підприємства.
Теоретичні відомості
Для виконання даної роботи студенти повинні повторно пригадати лекційні матеріали стосовно структури підприємства, основних понять менеджменту та бухгалтерського обліку, використати знання по застосуванню електронних таблиць.
В ході виконання даної роботи студенти повинні засвоїти методи автоматизації окремих сторін бухгалтерського обліку з ціллю отримання оперативної інформації про діяльність підприємства.
В даній роботі використовується така спрощена схема аналізу діяльності підприємства.
Ввід основних господарських операцій в головний журнал.
Оформлення головного журналу у відповідності до законодавства України.
Формування головної книги.
Аналіз фінансово-господарського стану підприємства.
Побудова фінансових звітів, що характеризують найбільш важливі сторони діяльності підприємства.
В основі поданих низче відомостей лежать матеріали подані в [2]. Назви малюнків і таблиць в даних методичних вказівках співпадають з їх назвами в [2]. Рекомендується, у випадку виникання трудностей у засвоєнні матеріалу звернутись до вказаної літератури, а з запитаннями до викладача. В комп”ютерному варіанті даних методичних вказівок застосовано вставку електронної таблиці в текст, так що ви можете в одному документі два рази “клікнувши” мишкою на таблиці отримати електронну таблицю і пересуваючись по її комірках, побачити формули, що містяться у них. Для виконання даної роботи в електронних таблицях EXCEL використайте файл CHAP01_U.xls
В ході знайомства з теоретичними відомостями і виконання даної роботи згідно індивідуального завдання, використавши літературні джерела замініть у відповідності до ваших господарських операцій рахунки в головному журналі на існуючі в Україні.
Від головного журналу до фінансового звіту
Правильне ведення справ передбачає щоденний запис господарських операцій в головний журнал. Такі записи дозволяють відстежувати кожну окрему операцію по кожному рахунку, будь це операції з готівкою, операції з рахунками дебіторів (наприклад, продаж) або з рахунками, що підлягають оплаті (купівля матеріалів), і т.п.
Як створити головний журнал в Excel
Для збереження і запису інформації такого роду написано немало програм. Деякі з них надають користувачеві план бухгалтерських рахунків. Тому багато які бухгалтери вважають за краще вести облік щоденних операцій саме з їх допомогою, не звертаючись до Excel. Excel надає у ваше розпорядження могутні і гнучкі засоби для введення, зберігання і пошуку відомостей про окремі операції. Однак, якщо ви вирішите використати Excel для цієї мети, вам доведеться створювати власні категорії для всіх операцій.
Якщо введення і зберігання даних про індивідуальні кошти ви здійснюєте за допомогою інших програм, то можете зіткнутися з труднощами при імпортуванні файлів в Excel. Хоч більшість програм цього класу забезпечена опціями для експортування даних в текстовий формат (ASCII-формат), структура даних, що експортуються може виявитися не зовсім відповідною для впровадження в Excel. Деякі програми, наприклад, перетворюють кожну частину запису в окремий рядок ASCII-файла: перший рядок номер рахунку, друга назва рахунку, третя кількість операцій і т.д.
Звичайно, такий тип структури також можна імпортувати в робочий лист Excel, причому без великих особливих зусиль, за допомогою команди файл (Відкрити). Однак потім з'явиться необхідність вмістити назву рахунку і кількість операцій в той же рядок, в якому знаходиться номер рахунку. Якщо при цьому ви маєте справу з великою кількістю господарських операцій, то вам швидко набридне виконувати одноманітну процедуру вирізування-вставки. Доведеться писати макрос, який примусить Excel самостійно виконувати перенесення даних з комірки в комірку.
Як вводити дані головного журналу в головну книгу
У обох випадках, коли ви вводите дані головного журналу безпосередньо в Excel або імпортуєте їх з іншої програми, вашим наступним кроком буде перенесення цих даних у відповідні розділи головної книги. На мал. 1.5 показаний приклад записів в головному журналі, а на мал. 1.6 демонструється, як ці записи виглядають після перенесення в головну книгу.
Для полегшення перенесення записів з головного журналу в головну книгу в робочому листі першого введені чотири імена діапазонів: EntryDate (стовпчик А), AccountNumber (стовпчик З), Journal Debits (стовпчик D) і Journal Credits (стовпчик Е). Всі ці імена і пов'язані з ними стовпці ви можете бачити в головному журналі на мал. 1.5.
У головній книзі присутні два додаткових імена: LadgerDate, що відноситься до комірки $А$6, і GLAccount, що використовується в різних посиланнях. (Значення цих посилань більш детально буде пояснене нижче в цьому розділі.)
У робочому листі головної книги в стовпці Debit для збору відповідних записів з журналу реєстрації використовується наступна формула:
= СУММ(ЕСЛИ)МЕСЯЦ(EntryDate) = МЕСЯЦ(LedgerDate);1;0)* ECЛИ(AccountNumber = GLAccount; 1; 0) *JournalDebits)
Для вибору відповідних кредитів служить формула:
= СУММ(ЕСЛИ) МЕСЯЦ(EntryDate) = МЕСЯЦ(LedgerDate); 1;0)*
ECЛИ(AccountNumber = GLAccount;l;0)*JournalCredits)
Примітка
Обидві ці формули потрібно вводити, як формули масиву. Набравши формулу, не поспішайте натискати <Enter>; спочатку натисніть комбінацію клавіш <Ctrl+Shift>. Ви побачите, що тепер формула взята в фігурні дужки. Це означає, що Excel прийняла вашу формулу як формулу масиву. Не треба вводити фігурні дужки вручну (з клавіатури), оскільки в цьому випадку Excel сприйме формулу як текст.
Формула в Excel виконує наступну процедуру.
1. Порівнює кожний запис в діапазоні EntryDate головного журналу з місяцем в даті в головній книзі. При відповідності цих значень повертає значення 1; в іншому випадку значення 0.
2. Оцінює кожний запис в діапазоні AccountNumber головного журналу. Якщо номер рахунку там співпадає з номером поточного рахунку головної книги, то повертається значення 1; в іншому випадку значення 0.
3. Множить результат, отриманий в п. 1 на результат п. 2. Тільки в тому випадку, якщо виконані обидва попередніх умови, результат буде рівний 1; в іншому випадку 0.
4. Множить результат, отриманий в п.3, на записи журналу реєстрації в діапазоні JournalDebit (або на діапазон JournalCredits, якщо використовується друга з двох приведених вище формул).
5. Повертає значення при виконанні п. 4.
Чітко уясніть собі значення імен, що використовуються в формулах. По-перше, передбачається, що робочі листи головного журналу і головної книги належать до однієї робочої книги. Якщо головний журнал є частиною робочої книги під назвою Gj.xls, то визначення імен в формулах будуть ідентифіковані як посилання на цю книгу, наприклад:
= 'C:\EXCEL7\[GJ.XLS]JournalSheet'!$А$4:$А$27
МАЛ. 1.5. Записи в головному журналі в хронологічному порядку фіксують всі операції по дебету і кредиту
МАЛ. 1.6. Записи в головній книзі нагромаджують на спеціальних рахунках дані про кожну господарську операцію з головного журналу
По-друге, зверніть увагу на визначення імен LedgerDate і GLAccount. LedgerDate посилається на осередок $А$6, оскільки це абсолютне посилання (на це вказують два символи долара), LedgerDate повертає одне і те ж значення незалежно від розташування комірки, в якиу вона введена.
GLAccount посилається на комірку $D6. Це змішане посилання: зафіксований тільки стовпчик, а рядок може змінюватися в залежності від того, куди вводиться посилання на GLAccount.
Тому формула:
=СУММ(ЕСЛИ(МЕСЯЦ(EntryDate) = МЕСЯЦ(LedgerDate);1;0)* ECЛИ(AccountNumber = GLAccount;1;0)*JournalCredits
введена в комірку F6 (див. мал. 1.6), використовує для GLAccount значення 1 (комірка D6). Але якщо формула введена в комірку F8, вона використовує значення 21 (комірка D8). Оскільки ім'я GLAccount відноситься до змішаного посилання $D6, воно само діє як змішане посилання.
За допомогою цих формул всі окремі операції переносяться з головного журналу в головну книгу у відповідності з рахунком, на який вони були вміщені, і з правильною датою поточного запису в головній книзі.
Як вводити дані головної книги в фінансовий звіт
Приведену вище методику можна використати для розміщення інформації, що зберігається в головній книзі, в фінансовий звіт.
У процедурі, що пропонується застосовується той же спосіб, що і при складанні плану бухгалтерських рахунків. Передбачимо, ви вирішили нумерувати всі рахунки, які стосуються фіксованих адміністративних витрат, використовуючи числа в діапазоні від 20 до 29. Якщо в головній книзі ви присвоїли діапазону, що містить всі номери рахунків, ім'я LedgerAccount, діапазону, що містить кредит, ім'я LedgerCredits, а діапазону, що містить дебет, ім'я LedgerDebits, то формула:
= СУММ(ЕСЛИ(ОТБР (LedgerAccount/10) =2;)( LedgerCredits-LedgerDebits; 0))
повертає всю необхідну інформацію по відповідній комірці фінансового звіту. (Не забудьте, що цю формулу треба водити як формулу масиву, тобто потрібно натиснути комбінацію клавіш <Ctrl+Shift+Enter>.)
Формула масиву спочатку оцінює елементи в діапазоні LedgerAccount і ділить їх значення на 10. Потім, якщо ціла частина значення, що вийшло рівна 2 (це буде відбуватися з всіма номерами рахунків від 20 до 29), формула повертає значення, що складає різницю між дебетом і кредитом по рахунках.
Цю формулу можна застосувати для введення запису в фінансовий звіт, якщо в ньому є частина, що відноситься до фіксованих адміністративних витрат. Точно так само, якщо ваш план бухгалтерських рахунків призначає всім рахункам, що відносяться до фіксованих витрат на виробництво, двозначні номери, що починається з цифри 3 (30, 31,. ... 39), то можна використати формулу:
= СУММ(ЕСЛИ(ОТБР(LedgerAccount/10)=3;)(LedgerCredits-LedgerDebits;0))
ПРИМІТКА
Процедура, що пропонується значно полегшується, якщо вмістити головний журнал, головну книгу і фінансовий звіт в одну робочу книгу. Однак Excel не дозволить вам використати одне і те ж локальне ім'я декілька разів в одній робочій книзі, якщо всім трьом елементам не привласнені імена листів. У межах однієї робочої книги ви не зможете використати, наприклад, ім'я Debits як локальне ім'я як для головного журналу, так і для головної книги. Але можна використати імена типу General Journal!Debits, де GeneralJournal це ім'я робочого листа. Інший шлях (ви зустрілися з ним в попередньому прикладі) ввести ім'я JournalDebits, щоб відрізнити цей діапазон від LedgerDebits.
Аналогічним чином ви можете ввести в фінансовий звіт більшість записів з головного журналу в головну книгу, а з неї в фінансовий звіт. Особлива увага потрібно приділяти двом типам записів рахункам, що підлягають оплаті, і рахункам дебіторів (вони обидва пов'язані з поточним бухгалтерським обліком). І ті, і інші, впливають на визначення того моменту, в який ваша компанія отримає фактичний прибуток. Про поточний бухгалтерський облік мова піде в наступному розділі, а амортизації присвячений розділ 16.
Випадок з життя: статті, що коректуються
Martin Consulting невелика компанія, яка робить послуги за оцінкою якості грунтових вод. На мал. 1.7 показано декілька прикладів того, як Martin Consulting використовує статті, що коректуються для реєстрації операцій, оплачуваних по переліку.
У зв”язку із застосуванням реальної таблиці EXCEL, даніі об”єднано із двох малюнків в одній таблиці таким чином:
МАЛ. 1.7. Статті, що коректуються допомагають визначати доходи і витрати за звітний період (стовпці А-G)
МАЛ. 1.8. Статті копіюються з скорегованого попереднього балансу встовпці фінансового і балансового звітів(стовпці А,H-K)
У кінці липня Martin Consulting підготувала цей робочий лист на основі фінансового і балансового звітів. 1 липня був придбаний страховий поліс для про, щоб забезпечити компанію у випадку, якщо вона дасть клієнтам неточну раду. Поліс дійсний протягом дванадцяти місяців. Вартість поліса занесена в дебетову частину рахунку активів в комірку В5 робочого листа. Коли в кінці місяця підводилися підсумки по робочому листу, 1/12-я частина суми поліса ia списана: саме ця сума стала тепер витратою, яка мала місце на протязі липня місяця. У комірці Е5 міститься число $57 це кредитна стаття, що коректується, яка відображає той факт, що була витрачена 1/2-а частина чий суми поліса. У комірці F5 показана сума даного активу, що залишилася, тобто $627. Дебетна стаття, що Коректується з'являється в комірці D17.
Протягом того ж періоду Martin Consulting використовує офісне обладнання (канцелярські обладнання, матеріали для фотокопіювання, друку і т.д.) на суму $136 для створення прибутку. У комірці Е6 з'являється ще одна стаття, що коректується на суму $136, що відображає зменшення первинної суми витрат на зміст офісу ($592), і застосовується проти того ж значення в комірці F6, показуючи значення, що залишилося $356 на кінець місяця. Дебетна стаття, що Коректується з'являється в комірці D18
Неозброєним оком видно, наскільки раціонально використати статті, що коректуються. 1 липня компанія придбала страховий поліс на дванадцять місяців, а 31 липня одиннадцяти місячну страховку. 1 липня вона закупила офісні обладнання на суму $592, а 31 липня на суму $456. Цими сумами Martin Consulting може легко оперувати. Іншими словами, дана компанія без великих особливих зусиль зможе виявити розмір збитку або витрат протягом липня.
Але якщо мова піде про офісне обладнання, то проблема ускладнюється. На початку липня компанія придбала офісне обладнання на суму, що спочатку становила $3 470 (комірка В7) Яка частина цієї суми була використана при експлуатації обладнання в цьому місяці? Обладнання нікуди не зникло:
комп'ютер продовжує створювати електронні таблиці, копіювальний апарат робити копії, телефон дзвонить як і раніше Але все-таки якась частина первинної вартості обладнання була використана для отримання прибутку. Щоб відповісти на поставлене питання, треба зрозуміти, що таке амортизація.
Амортизація це списання, яке виконується згідно з вимогами оподаткування і бухгалтерського обліку
Для оцінки суми офісного обладнання, "витраченої" протягом липня, Martin Consulting може вибрати один з декількох методів обчислення амортизації Більш детально про ці методи розказується в розділі 16. Поки ж нехай Martin Consulting використовує прямий метод обчислення амортизації. Передбачається, що офісне обладнання розраховане на роботу протягом трьох років. Тоді можна передбачити, що в кінці кожного місяця вартість обладнання меншає на 1/36-ю частину його первинної вартості: виходить, що кожний місяць обладнання знецінюється на 1/36, або на $96. Ця кредитова стаття, що коректується знаходиться в комірці Е8, а дебетова стаття, що коректується в комірці D22.
ПОРАДА
Щоб уникнути явних помилок при відображенні в електронних таблицях значень, що отримуються при множенні або розподілі, використайте функцію ОКРУГЛ(), наприклад: =ОКРУГЛ(3470/36,0)
Оцінюючи розмір щомісячної амортизації, можна визначити суму, що витрачається на офісне обладнання протягом місяця. Тоді цю витрату можна зв'язати з прибутком і отримати більш ясне уявлення про місячний прибуток Тут знов присутній принцип збалансованості.
Статті, що коректуються необхідні для збільшення не тільки витратної, але і дохідної частини. Передбачимо, що в кінці липня Martin Consulting підписала угоду і отримала розрахунок готівковий за проведення 8-часової консультації по ціні $60 в годину. Повна сума $480 записана як прихід на рахунок активів під назвою Unearned Consulting Fees (Невідпрацьований гонорар за консультації). До кінця місяця Martin Consulting провела один з восьми оплачених часів консультації Фактично, виконання цієї роботи переводить частину невідпрацьованого гонорару в статус відпрацьованого статті, що Коректуються, що знаходяться в осередках D10 і в Е19, показують, яка саме частина ($60) невідпрацьованого гонорару була відпрацьована в липні.
Чотири вище описані статті, що коректуються відносяться до діяльності, яка починається і закінчується під час звітного періоду: наприклад, використання канцелярських приладдь на суму $136 починається 1 липня і закінчується 31 липня. Стаття, що коректується також може бути використана для фіксування діяльності, яка виходить за рамки звітного періоду. Передбачимо, Martin Consulting підготувала чек на виплату співробітнику платні за дві попередні тижні, починаючи з останнього тижня місяця. Співробітник отримує платню тільки за один тиждень поточного місяця, з 25 по 31 липня. Щоб показати, що ця виплачена платня є витратою, що відноситься до липня, а не до серпня, Martin Consulting створює статтю, що коректується в комірці D14. А щоб показати, що це заборгованість, яка буде виплачена пізніше (приблизно в серпні), стаття, що коректується вводиться як кредит в комірку Е21.
З допомогою Excel значно спрощується перенесення попереднього балансу і статей, що коректуються у відкоректований попередній баланс. Формули, що використовуються в стовпцях F і G, що на мал. 1.7 (відкоректовані залишки по дебету і кредиту), більш детально освітлюються в розділі 5 [1].
Підсумки статей, що коректуються і відкоректованих статей показані в діапазоні D22:G22. Рівність підсумкових сум дебету і кредиту показує, що статті збалансовані.
ПОРАДА
Excel 7 пропонує декілька форматів підкреслення, включаючи формати Одинарне, по комірці і Подвійне, по комірці Вони застосовуються відповідно для проміжної і для загальної сум Щоб отримати доступ до цих форматів підкреслення, виберіть команду Формат(Комірці, а потім вкладку Шрифт Після цього відкрийте список, що розкривається Підкреслення
Нарешті, настав час перенести цю інформацію в фінансовий звіт (мал. 1.8).
Рядки з 3 по 11 і рядок 21 представляють рахунки активів і заборгованості. Вони скопійовані з відкоректованого попереднього балансу в колонки робочого листа балансу. Рядки з 12 по 20 представляють рахунки прибутків і витрат; вони скопійовані в стовпчик фінансового звіту. Далі, в діапазоні Н22:К22 підводяться підсумки дебету і кредиту. Зверніть увагу, що вони більше не збалансовані, так цього і не повинне відбуватися. У липні прибутки компанії виявилися вище за витрати, і ця різниця складає виробничий прибуток. Щоб дізнатися її точну суму, треба відняти із загального прибутку (комірка 122) загальну витрату ($4 680 в комірці Н22). Результат ($1 630) знаходиться в комірці Н23; він і є виробничим прибутком компанії за липень. Склавши його із загальними витратами $4 680, ми отримаємо $6 130, що дорівнює загальному прибутку за місяць.
Той же метод використовується, щоб отримати інформацію для балансового звіту. Зверніть увагу, що цей завершальний етап копіювання інформації з скорегованого попереднього балансу в фінансовий і балансовий звіти являє собою просто відділення даних про прибутки і витрати від даних про активи і заборгованість. Перші йдуть в фінансовий звіт, другі в балансовий.
Резюме
У цьому розділі розглядалися різні форми фінансових звітів. Структури звітів можуть значно відрізнятися один від одного в залежності від того, для кого або для обгрунтування чого вони складені.
Тут також розказувалося про те, як використати Excel для ведення головного журналу, як перенести дані з цього журналу в головну книгу і в фінансовий звіт.
Крім того, описувалися поточний бухгалтерський облік і принцип збалансованості, який дозволяє зв'язувати вартість з тими прибутками, які вона приносить, а також декілька підходів до питання розрахунку амортизації.
Фінансовий звіт головний документ, необхідний для розуміння основ діяльності компанії. Його можна використати по-різному і в формах, число яких, фактично, нескінченно. Структура звіту і його форма залежать від того, кому він буде представлений і для якої мети він створюється.
Існують основні принципи формування фінансового звіту, але тільки від вас залежить, які саме дані ви в нього внесете. Ви можете використати свій звіт для визначення конкретних цифр або для надання вкладникам, але його головне призначення допомогти вам приймати виробничі рішення. Для цього потрібно будувати фінансовий звіт так, щоб в ньому була представлена вся необхідна інформація.
Порядок виконання лабораторної роботи
Ознайомтесь з таблицями Excel, поданими в теоретичних відомостях. Опишіть нові оператори і дії, які ви зустріли, даючи відповіді на контрольні запитання і завдання.
Опишіть в загальному структуру підприємства та представте послідовність автоматизації окремих сторін обліку його діяльності
Погодьте з викладачом виконане завдання згідно п.2.
Оформіть результати роботи
Контрольні запитання і завдання
Що таке ім”я в ЕХСEL.
Як ввести ім’я комірки, діапазону, стовпчика, рядка в EXCEL.
Для чого застосовують імена в EXCEL.
Який фізичний і логічний вміст імені ДатаВвода (EntryDate) в таблиці (Мал.1.5).
Який фізичний і логічний вміст імені НомерСчета (AccountNumber) в таблиці (Мал.1.5).
Який фізичний і логічний вміст імені КнигаДебет (JournalDebits) в таблиці (Мал.1.5).
Який фізичний і логічний вміст імені КнигаКредит (JournalCredits) в таблиці (Мал.1.5).
Які ще імена задано в поданому вам файлі CHAP01_U.XLS.
Ознайомтесь із планом рахунків бухгалтерського обліку в Україні (дивись дискету, файли plan.doc, instruc.doc).
Перелічіть основні класи плану рахунків.
Знайдіть аналоги рахунків згідно законодавства України рахункам, поданим в таблиці (мал. 1.5 в колонці „Номер рахунку”).
Поясніть подані вам, як зразок, операції (мал. 1.5.) згідно законодавства України.
Як замінити формат даних в колонках „Дебе” і „Кредит” на грошовий формат, прийнятий в Україні (*грн *коп.). Пригадайте „Форматування даних в EXCEL”.
Які додаткові імена використовуються у головній книзі (мал. 1.6.).
Поясніть кожен оператор і операнд формули в стовпчику „Дебет” (мал. 1.6.).
Поясніть кожен оператор і операнд формули в стовпчику „Кредит” (мал. 1.6.).
Опишіть словесно і графічно алгоритм роботи процедури, представленої формулами в таблиці (мал. 1.6.).
Поясніть, що представляє собою масив в EXCEL.
Як задати формулу роботи з масивом.
Переробіть таблиці (мал. 1.5.) і (мал.1.6.), що містяться в книзі CHAP01_U.XLS в окремі книги GJ<прізвище студента>.XLS (мал.1.5.) – журнал операцій та GL< прізвище студента >.XLS – головна книга. Поясніть, як зміняться адреси в іменах.
Напишіть формулу, з допомогою якої переносяться і групуються дані по операціях із головного журналу в суми по дебету і кредиту відповідних рахунків в гооловній книзі.
Опишіть словесно і графічно алгоритм роботи процедури переносу даних по операціях із журналу операцій в головну книгу.
Що означає значення в стовпчику „Дата” мал. 1.6.
Як згрупувати дані по субрахунках і рахунках, класах з допомогою EXCEL.
Опишіть алгоритм роботи процедури (мал. 1.7.-1.8.) для ґрунтування даних по видах витрат. Покажіть, які є види витрат згідно законодавства України.
Як потрібно змінити імена в EXCEL при умові об”єднання декількох книг, в яких є одинакові імена.
Представте таблицю фінансового звіту, побудовану вами з врахуванням вимог законодавства України.
Література .
О.Колесников,А.Пробитюк «Excel 7.0 для Windows 95» Київ , BHV , 1996 рік .
К. Калберг Бизнес-анализ с поиощью Excel.: Пер. С английского.-К.: Диалектика, 1997. - 448с.
Пархоменко В.Н. Трансформация бухгалтерского учета в Украине. - К.: ЦСП “Компас”, 1997. - 95с.
Ткаченко Н.М. Бухгалтерський фінансовий облік на підприємствах України.- К.:А.С.К., 2000. - 784с.