How to keep a company’s accounting in Microsoft Excel? step by step guide

It is true that there are softwares that allow you to keep the accounting of a businessbut Microsoft Excel is an ideal tool for this task.so you can replace those programs without problem.

This is due to the large number of functions available to create templates and achieve control of the operations that are carried out.being the facility to obtain the data a fundamental characteristic.

If you do not want to spend more money and you want to have an accurate tracking of your company’s registrations, We will tell you in the following paragraphs how you should keep accounts with Excel.

What accounts of my company can I take in Excel to handle accounting?

There are different accounts where you can use Excel to handle the accounting of your company.

We will show you below, all the accounts that you can reflect in a spreadsheet:

Expenses and incomes

When talking about income and expenses, it should be borne in mind that this account includes a large number of concepts that it can cover. So that, income could be separated into ordinary and extraordinarythe first being those generated by sales (which we will see later). As for extra income You can find sales of cars, vehicles, machinery and any other good that is not originated by the normal course of business.

In a template Excel You will be able to carry this type of information without any problem. If we refer to expenses you will have to divide them into two groups. Those that belong to the cost of the product and the expenses that are necessary for the company to function. In the first groupraw materials, direct labor, electricity, fuel, freight and any other concept without which the product or service could not be sold must be included.

Within the second group, you will be able to incorporate expenses related to labor that belong to secondary sectors, such as administration. You should also include in this group the expenses for work carried out for maintenance, telephony, security and many others. Taxes should be considered as belonging to the two groups. But it is a more complicated issue that an accountant will be able to handle better.

Sales

This income statement belongs to the income group that we mentioned before. In it you must register all the products or services that you sell to third parties regardless of whether you have charged it or not. You have to keep in mind that the amounts must be entered without VAT or any other taxsince these are not part of the sale itself, and by legal provisions, they must be registered in special accounts.

In case there are customer returns, you will have to reflect the documents that justify the credit to be able to subtract the amount of the sale. Every time you make a transaction you will have to register it in the “To have” and every time you make credit notes, to reduce the balance, you must do it in the “Must”.

Budget

You will be able to carry in an Excel spreadsheet different budgets that you deliver to your clients. Therefore, you will be able to have control and follow-up of the delivery date, the person to whom you deliver and all the products involved with their respective prices in that future transaction.

Accounting

With Microsoft Excel you can create different types of templates that will help you record all the operations you carry out in your companyregardless of its size. Therefore, you will have to have a little concentration to be able to link all the spreadsheets with their different concepts, origins and destinations. In this way you will be able to establish efficient cross control in all sectors.

What are the most useful functions to keep my company’s accounting in Excel?

The functions that you can use to keep accounts in Excel depend on the degree of knowledge you have, since you will be able to use logical tools that will help you work with very extensive templates. You will also be able to use dynamic tables in which you can add search functions.

But if you want to get started with automated accounting to track your business, the features you’ll need to use are as follows:

• ADDITION
• SUMPRODUCT
• PRODUCT
• SEARCH
• SEARCHH
• SEARCHV
• TODAY
• MONTH
• AVERAGE
• HYPERLINK

Steps to create an accounting system in Excel for any type of company

Next, we will show you the steps you must take so that you can create a reliable accounting system in Excel and in this way you can use it in any type of company:

invoice system

The necessary steps are:

• To create an invoice system The first thing you should do is enter all the products or services you sell. To do this, you will have to write them in a column in a sheet which we will call “Products”.
• Then you will need to enter in the next column the prices of each item.
• If appropriate, you can occupy another column to add special discounts.
• It’s time to create another sheet who should you call “Customers”.
• on this sheet Create a column to incorporate the names or business name of each client.
• Next to of this you can include the address and any specifications you need.
• The last What you must do on this sheet is to incorporate all the fiscal data of each client. The NIF different types of retentions that can suffer.
• You are able to create a template for invoicing. Therefore, you must add a template in a new sheet and incorporate your personal or business data, NIF and any other relevant element.
• Then you will need to link this sheet with the previous two so that you can automatically have the products and the clients.

Income and expenses

To create an accounting system for income and expenses, you must follow this guide:

• Open a spreadsheet. Enter a column in which you must write down every day of the month.
• Next, link each cell to the invoice you created in the previous section. In this way you can automatically have all the income every time you make an invoice.
• Then you must include different expenses in each column. But pay attention because you will have to combine them by means of a title that will be found above them and you will call “Bills”.
• To end In this system, you must total the columns and then subtract income and expenses. This will determine the cash flow you have had during the period and you can control it with the real balance you have of cash.
• bind a column of “Bills” with the blade “General payroll of employees” to incorporate the cost of labor.

Budget

It is a useful tool that you can link with the Billing Systems template. For this you will have to download a budget template (which you can choose from the state that we leave you in the following paragraphs) the prices, with the products and the clients.

Next, you can create an automatic numbering in which you can reflect a control of budget deliveries in a given column. This will help you keep track of each client you have sent to and their expiration dates.

Employee Tracking

What you will need to do now is have a precise control of the hours worked, the leaves of the employees, the sector to which they belong and the cost of the hour of each one.

For this you will have to do this step by step:

• The first thing you should do is create a sheet for each employee. In this register the personal data in a column. Next, you must incorporate the cost per hour, the sector and the absenteeism you have in the month.
• In this way you will have every day movement in each of the leaves.
• Next, You must link all these sheets with one that gathers all the data in a general way. You can call this sheet “General payroll of employees”. In it you will be able to see all the data that interests you and thus you will be able to correctly determine the salary that you must pay.

accounting records

The objective you need at this stage is to record all the movements you’ve had over a given period on a single sheet. This will allow you to establish what your Assets, Liabilities and therefore the Equity, a General Balance of Sums and Balances and a Statement of Results. To achieve this, you will need to download a template that will help you incorporate all the information so that you can link it with the rest of the templates that you have designed.

List of the best templates to automate your accounting system in Excel

In the following paragraphs you will find a complete guide so you can choose the best template to automate your company’s accounting system using Microsoft Excel:

Bills

We show you below different template designs and you can use for the issuance of invoices:

Corrective

When you have to modify data that was issued by another document, you can use this templatewhich will help you to comply with all the requirements of the law and also to register the corrections of the common invoice.

Download Corrective Template for Excel

Professional

This template is designed so you can incorporate a logo and letterhead of your company and then write the client’s data in a very simple way. In addition, it allows you to carry out all the calculations automatically, so you will have to enter the data and prices of the products or services.

Download Professional Template for Excel

Sales

His name says it all. With this design you will be able to have the entire database of your articles loaded and establish a relationship with the prices per unit. So, at invoice You can load it automatically without having to worry about updating costs and other items.

Download Sales Template for Excel

Total Bill

You can use this design when you want to automate the issuance of invoices. It is an ideal template for SMEs Since, thanks to its personalization, which you will do only once, you will only have to modify the customer’s data and the products or services that you have delivered.

Download Total Invoice Template for Excel

With financial charge

As you know, the vouchers for the issuance of services or the sale of articles contain financial charges that directly impact the total amount. This is why you will need to have a file that automatically calculates all the taxes and additional costs that your products or services have..

Download Template With Finance Charge for Excel

Budgets

The designs of the templates that you will see below were selected to help you in the task of carrying out budgets:

Simple Budget

with this model You can give customers a quote for the products or services they perform. You will be able to use it in a simple way, since you will be able to incorporate filters and functions that allow you to search the database for prices according to the characteristics of the goods. Calculate the total and other amounts that you must add.

Download Simple Budget Template for Excel

for works

It is ideal to deliver engineering budgets, or any other construction that you want to carry out in a home. It is segmented by different areas and concepts necessary to carry out the task. It is ideal to have control of each item that has been budgeted.

Download Template for Excel Works

Party

If what you’re looking for is have a budget for different items of this activity, such as catering, music, services and tableware, this template is ideal for you. You will be able to have a graph that will help you understand how the different segments impact the final price.

Download Party Template for Excel

Budget for Purchase Order

This template design will help you deliver to all the people you need a form. You can share it online or physically in which you can incorporate customer data and a description of each product or service. Then it will automatically calculate the different concepts that will increase the cost for the client.

Download Budget Template for Purchase Order for Excel

payroll

You can choose the best Excel templates to control employee payroll:

Work shifts

Is for use in companies where there are rotating shifts and production or any sector of the company cannot be neglected. Therefore, it is necessary to have a daily control of all the activities and schedules so that they can be assigned to the different workers.

Download Work Shift Template for Excel

Employee Absence

This template is designed to have precise control of each employee and their personal situation according to different casualties that you may suffer during the month. This will help you to have a correct salary settlement.

Download Employee Absence Template for Excel

Auxiliary Calculations

It is important know all the perceptions and deductions and has the worker. Therefore, using a template that helps you with multiple functions to have a correct salary calculation is a good idea. This template meets all the objectives you are looking for to obtain the correct amount of salary settlement.

Download Auxiliary Calculations Template for Excel

Income and expenses

When you are looking to have an automated income and expense system for your business, you should consult the following Excel templates that will help you with this task:

Income and Expenditure Control

with this template you will be able to have a correct follow-up of all the income that occurs on a daily, monthly or weekly basis, as well as of the bills. In this way, you will know precisely the true financial profit of your company. In addition, it will help you to control your personal and family expenses.

Download Income and Expenditure Control Template for Excel

unified

The design of this Excel template allows you to have a global knowledge of all the income and costs that you have had in a given period. You will be able to have graphics and keep track of the flow of funds so that you can permanently know what the cash balance is.

Download Unified Template for Excel

Monitoring of Expenses and Income

This template allows you to have a global and also detailed control of all the income and expenses that you will have in a certain period. In this way, you will be able to know on a daily basis and for each concept the different references and amounts that have impacted the cash flow of your business.

Download Income and Expense Tracking Template for Excel

Total Sums

In case of look for a spreadsheet that allows you to have an overview of all the income and expenses that you have suffered in a week, month or year, this template is ideal for you. It has functions that will help you quickly determine the financial balance point.

Download Total Sums Template for Excel

Cash Calculation

If what you are looking for is a template that helps you have exact control of the cash balance, this model is perfect. You will have the possibility to find the errors in the registration in a simple way, it will also help you to detect if you have not made a payment or did not reflect a sale.

Download Cash Calculation Template for Excel

Accounting

If what you are looking for is to have control of the different accounting accounts, we present the best templates so that you can automate the accounting information system of your commercial enterprise:

Income Statement

For CAccurately know the amount of profits or losses that you have suffered in a certain period, it is not only necessary to control the financial income and expenses, but also to carry out a calculation of Amortization and other items that significantly affect profits. This template is ideal for tracking and recording all these concepts.

Download Income Statement Template for Excel

Balance sheet

Everytime that register in different accounts of your company, the balance of these operations must be reflected in a single sheet. That is why it will be necessary for you to have this template at hand, which will help you have everything you need at a glance.

Download Balance Sheet Template for Excel

Check Template

So that you can keep track of all the checks you write and the balance of the bank account you will need to record the movements in this template. It is ideal to use it online.

Download Check Template for Excel

General Accounting

With this spreadsheet you can have control over all the operations you have in the company and in this way you will obtain the numbers that will reflect the real results to which you can compare them with the budgeted ones.

Download General Accounting Template for Excel