The majority small and medium enterprises (SME) all tend optimal cost management, commissioning, particularly in the array of accounting. Therefore, Excel – familiar tools, flexible – often selected as the platform to build the system of bookkeeping original. With the ability to handle spreadsheets powerful, easy to customize, no cost copyright, Excel become “temporary solution” to help businesses set up accounting process, from the number of 0.
However, to how to do bookkeeping on Excel effective, businesses need to understand the principles of accounting, system design, data items, especially knowing how to apply the Excel tool to the accounting profession. In this article, AccNet will help you to understand deeply about how guide to bookkeeping on Excelfrom the concept condition to the implementation steps in detail. The end of the article, you will also be hints when should switch to accounting software professional for system optimization financial accounting for business.
1. Clarify the concept of accounting on Excel from the perspective practice
Accounting what is accounting system business?
Ledger is where record the entire economic and arise are related to assets, capital, revenue, cost... of the business. The recording right, full book accounting is grounds for financial reporting, tax settlement. Under the provisions of circular no. 200/2014/TT-BTC and circular 133/2016/TT-BTC, the system accounting include:
- General.
- Ledger accounts.
- Window detail according to each subject accounting.
- Balance sheet arises, the synthesis report.
Guide to bookkeeping on Excel is the:
- Design templates for accounting using Excel spreadsheet.
- Self-built system, recipe data link between the sheet to simulate the activity record window, synthesis, for lighting, such as in accounting software professional.
Any business should apply accounting on Excel?
This method is particularly suitable for:
- Doanh nghiệp vừa thành lập hoặc quy mô nhỏ (ít nghiệp vụ, <300 chứng từ/tháng).
- The business transformation business model.
- The business no investment budget software or are in the testing phase process, internal accounting.
Important note: The instructions do bookkeeping on Excel should only be the beginning, help businesses become familiar with accounting principles, before turning to specialized software – where provides full-featured books, reports, connected with electronic invoicing, banking, tax...
Why business should be interested in the guide to bookkeeping on Excel?
Here is the solution to save cost, easy to deploy for small business. According to a survey by Clutch (2024), there are up to 38% of small businesses still use Excel for bookkeeping in the initial startup phase. This reflects the trend to the fact that Excel is still the popular choice for businesses that are not eligible investment accounting software for professional use.
A number of benefits when applied guide to do bookkeeping on Excel:
- No cost software copyright.
- Easily customized to model the operation of the business.
- Can take advantage of internal resources (personnel know how to use Excel) without the need to hire accounting outside.
However, Excel is not the solution long-term. Excel is very powerful, but does not have the ability to control internal to develop input errors, difficult to expand when the data increases. So, to know how to do bookkeeping on Excel in a professional manner will help your business:
- Increase the accuracy and transparency of data.
- Create a platform to easily switch up accounting software to business development.
Read more:
2. Guide to bookkeeping on Excel detailed step by step
This is the core part of the article – help business can start implementing right without being in-depth accounting.
Step 1: design of the original item
The categories accounting is the foundation for the entire data operation. You should build the table:
- The category accounting accounts: Code – Name – Properties Owe/Have.
- The items customers / vendors: Code – Name – MST – Address.
- List of goods: item Code – Name – unit – Group – row- Price.
Note: the encryption to be logical, systematic, to avoid duplication – for example KH001 for customers, NCC001 for suppliers.
Step 2: Input the certificate from arising daily
Create the table to enter data in a form familiar:
- Receipts, voucher.
- Purchase invoices and sales orders.
- Votes import – export warehouse.
You can use:
- Dropdown list automatically from the list to reduce the error type.
- Recipe hints accounting (apply with repeat transactions).
- Automatically numbered voucher using the formula ="PT"&TEXT(ROW(A2)-1,"000").
Step 3: Establishment general
Windows common log is where sum up the whole of the service entered.
Structure: On – voucher – interpretation – TK Debt – TK Are – the amount Of money.
You can use the function:
- FILTER() or QUERY() (on Google Sheets) to auto filter from sheet stock from.
- Calculate the total according to each type of business for comparison.
Step 4: Set up ledger account
- Each account will have a panel ledger separately, including: Day – description – amount-Debt – Has – balance end of the period.
- Dùng SUMIFS() để tổng hợp số liệu theo từng tài khoản từ Nhật ký chung: =SUMIFS('NKC'!F:F, 'NKC'!D:D, "111", 'NKC'!A:A, ">=01/01/2024", 'NKC'!A:A, "<=31/01/2024")
- Should put the main account (111, 112, 131, 331, 511, 621, 632...) on the sheet individually to easy-to-follow.
Step 5: Set up the balance Sheet arising, financial reporting
Aggregate balance beginning of the period, arisen, ending balance of each account. Formula used:
- SUMIFS() to arise.
- IFERROR() to handle the line is empty or error.
- Conditions filter by month/quarter/year.
Create table:
- The balance sheet arising.
- Report business results.
- Balance sheet accounting.
3. Conditions to do bookkeeping on Excel for business
To the instructions to do bookkeeping on Excel no tangle, wrong data or wrong rules, businesses need to ensure a number of conditions following platforms:
Understand & apply the right accounting system according to circular 200 or circular 133
Here are two modes of accounting most popular in Vietnam today:
- Circular 133/2016/TT-BTC: for small and medium enterprises.
- Circular no. 200/2014/TT-BTC: for business, medium-sized and above.
Businesses need to clearly define ourselves are applying mode how to design, window fitting, avoid wrong account system, group, service, structure books. You can download free templates standard of both circular from the website of the Ministry of Finance to use as a background document.
The structure data Excel accounting must be designed science
A file window, good accounting is not simply the sheet discrete, which must be a strong link between the tables to cater for:
- Data entry professional.
- Synthesis report.
- Data access according account, object, month, quarter, year.
Optimal structure include:
- Sheet category: account, accounting, customers, suppliers, shipping code, voucher code.
- Sheet stock from: receipts, voucher, enter the warehouse, stock, bills of sale.
- Sheet book: general journal, ledger, ledger details.
- Sheet statements: the balance sheet arising financial statements.
Skills using Excel's accountant must meet certain qualifications
To build window accounting standard accountant need to know:
- Function Excel: IF, VLOOKUP/XLOOKUP, SUMIFS, INDEX/MATCH, FILTER, TEXT, ROUND, ISERROR, etc.
- Advanced technical: name the test data (Data Validation), conditional formatting (Conditional Formatting), create dynamic table (Pivot Table).
- Link to the sheet using the formula instead of copy-paste manually.
Lack of this skill easily lead to false data when the number of vouchers grew.
4. Template file ledger on Excel – Download free & structure analysis
Download template bookkeeping Excel free
In order to save time designing from scratch, you can download the template ledger Excel is standardized according to circular 133 or 200. These generally include:
- The category accounts, customers, goods.
- General journal, ledger, ledger details.
- Financial statements basic.
You can download the template bookkeeping Excel for small business here:
Analyze the template structure to apply instructions bookkeeping on Excel efficiency
A sample file good need to ensure:
- The structure closely linked the middle of the sheet.
- Restrict data entry craft many times, increased automation using the formula.
- Easy to customize by type of business: commercial, service, production...
Hint: Businesses can rely on this form to:
- Start implementing accounting internal.
- Training accounting personnel practice on sample data.
- Do platform that converts data into accounting software later.
5. The common fault when making guide bookkeeping on Excel
Use Excel to do accountants make mistakes. Below are the common errors:
Error recipe – link wrong calculations, wrong
For example: link data from the general journal to the ledger is lack of conditions that day, or account. How to fix:
- Always use the SUMIFS instead of SUMIF.
- Check the reference skills, put the name of the region to be easy to manage.
- Create table test to collate results.
Coincident vouchers or lack of coding reasonable
Type the voucher code, easy to duplicate, lack of control. How to fix:
- The self-code auto like: ="PT"&TEXT(ROW(A2)-1,"000")
- Use Data Validation in combination dropdown list to limit the import error.
Do not lock the car data, anyone can edit
Data changed to cause deviation window. How to fix:
- Protect sheet function “Protect Sheet” in Excel.
- Just to empty the input box and materials required, the rest should lock the whole.
Read more:
6. When should I transfer from Excel to accounting software for professional use?
- When the number of vouchers exceeds 500/month, Excel becomes slow, error, formula, difficult to control, cash flow, debts and inventory. This is when businesses need to turn to specialized software.
- When many people do accounting or need internal control, then cyoke guide to bookkeeping on Excel doesn't have functions authorized users. Anyone can edit the original data, causing the risk of losing control or errors to report.
- When businesses need extended features (property, payroll, inventory, budget), but Excel can't manage fixed assets, depreciation, cost allocation, payroll, fixed level of production, profitability analysis according to each order.
- When businesses need to synchronize data between electronic invoices, banking and tax system, something that Excel can't do. Then accounting software such as AccNet Cloud can fully meet the efficiency with the following features:
- Automatic accounting from the electronic invoice.
- Integrated digital bank (MB Bank, VIETCOMBANK, BIDV,...).
- Connect API with the tax system to report quickly.
ACCNET CLOUD – ACCOUNTING SOFTWARE ONLINE FOR SMALL AND MEDIUM ENTERPRISES Thousands of businesses SME've been saving 200-500 million per year thanks to standardized accounting work with AccNet Cloud
Still using Excel is accepted wrong number, and late-term >>> Go through AccNet Cloud – compact, standard, no delay deadline.
SIGN UP CONSULTATION AND DEMO TODAY
Way guide to bookkeeping on Excel is a savings option, logical, with the new business established. However, when business development, accounting data increasingly complex, the amount of stock from rising demand financial control become stricter, Excel will gradually reveal the limitations in terms of accuracy, security, interoperability. Continued how to make crafts can lead to risks skewed data, loss costs, even influence the process of business decisions.
That is the reason why many smart business was soon switch to the accounting software as professional as AccNet Cloud – solution modern accounting is designed specifically for the business environment in Vietnam. With the ability to automate the process of accounting, deviation alerts, access reports, instant, flexible management all types of businesses
Let's experience the demo for free AccNet Cloud todayto see that a accounting software powerful is no longer the privilege of the big business, which is the essential tool for anybody who wants to effective governance right from the first day!
CONTACT INFORMATION:
- ACCOUNTING SOLUTIONS COMPREHENSIVE ACCNET
- 🏢 Head office: 23 Nguyen Thi huynh, Ward 8, Phu Nhuan District, ho chi minh CITY.CITY
- ☎️ Hotline: 0901 555 063
- 📧 Email: accnet@lacviet.com.vn
- 🌐 Website: https://accnet.vn/
Theme: