software

How to keep to company accounting in microsoft excel? Step by step guide

It is true that there are softwares that allow you to keep the accounting of a business, but 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 ease of obtaining the data a fundamental characteristic.

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

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

There are different accounts in which you can use Excel to manage the accounting of your company.

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

Expenses and income

When it comes to income and expenses, it should be borne in mind that this account includes a large number of concepts that it can cover. Therefore, income could be separated into common and extraordinary, the first being those generated by sales (which we will see later). As for the extra income, you can find sales of cars, vehicles, machinery and any other good that is not originated by the usual line of business.

In an Excel template you can carry this type of information without any problem. If we refer to expenses, you will have to divide them into two groups. Those that pertain to the cost of the product and the expenses that are necessary for the company to function . In those of the first group, the raw materials, direct labor, electricity, fuel, freight and any other concept without which the product or service could not be sold should 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 expenses for work carried out for maintenance, telephony, security and many others. Taxes should be considered as belonging to both groups . But it is a more complicated subject that an accountant will be able to handle better.

Sales

This income account belongs to the income group that we have 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 tax, since these are not part of the sale itself, and by legal provisions, they must be registered in special accounts.

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

Budget

You will be able to carry in an Excel spreadsheet different budgets that you give to your clients . Therefore, you will be able to have control and monitoring 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 company, regardless 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 an efficient cross control in all sectors.

What are the most useful functions for keeping my business accounting in Excel?

The functions that you can use to keep the accounting 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 start with an automated accounting to keep track of your company, the functions that you will need to use are the following:

  • SUM
  • SUMPRODUCT
  • PRODUCT
  • SEARCH
  • SEARCHH
  • VLOOKUP
  • 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 must do is enter all the products or services that you sell. To do this, you will have to write them in a column on a sheet which we will call “Products” .
  • Then, you must enter the prices of each item in the next column.
  • If applicable, you can occupy another column to add special discounts.
  • It is time to create another sheet that you should call “Clients” .
  • On this sheet create a column to include the names or business name of each client.
  • Next to this you can include the address and any specification you need .
  • The last thing you should do on this sheet is to enter all the tax data for each client. The NIF different types of withholdings that may suffer.
  • You are in a position to create a billing template . So you must add a template to a new sheet and incorporate your personal or business data, NIF and any other relevant element.
  • Then you must link this sheet with the previous two so that you can automatically have the products and customers.

Income and expenses

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

  • Open a spreadsheet. Enter a column in which you should 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 each 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 “Expenses” .
  • To finish this system you must total the columns and then make a subtraction between income and expenses. This will determine the cash flow that you have had during the period and you can control it with the actual balance you have of cash.
  • Link an “Expenses” column to the “General Employee Payroll” sheet to incorporate the cost of labor.

Budget

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

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

Employee tracking

What you will need to do now is to have a precise control of the hours worked, the losses 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 enter the cost per hour, the sector and the absenteeism that it has in the month.
  • In this way you will have movement in each of the leaves every day.
  • Next, you will have to 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 interest you and thus you will be able to correctly determine the salary you must pay.

Accounting records

The objective you need at this stage is to record all the movements that you have had during a certain period on a single sheet. This will allow you to establish what your Assets, Liabilities and therefore the Net Worth, a General Balance of Sums and Balances and an Income Statement are . To achieve this, you will need to download a template that helps 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 that you can choose the best template to automate your company’s accounting system using Microsoft Excel:

Invoices

We show you different template designs below that you can use to issue invoices:

Rectifying

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

Download Rectification Template for Excel

Professional

This template is designed so that you can incorporate a logo and letterhead of your company and then write the customer data in a very simple way . In addition, it allows you to perform all the calculations automatically, so you must 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 can have the entire database of your articles loaded and establish a relationship with the prices per unit. Therefore, you can load the invoice automatically without having to worry about updating costs and other concepts.

Download Sales Template for Excel

Total Invoice

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 customization, 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 receipts for the issuance of services or sale of items 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 Financial 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 will be able to give clients a budget for the products or services they carry out . You can use it in a simple way, since you can 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 a control of each item that has been budgeted .

Download Template for Excel Works

Party

If what you are looking for is to have a budget for different areas 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 a form to all the people you need . 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 customer.

Download Budget Template for Purchase Order for Excel

Payroll

You can choose the best Excel templates to keep track of employee payroll:

Work shifts

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

Download Work Shift Template for Excel

Absence of Employees

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

Download Employee Absence Template for Excel

Auxiliary Calculations

It is important to know all the earnings and deductions and has the worker . So using a multi-functional template that helps you get a correct salary calculation is a good idea. This template meets all the goals you are looking for to get the correct amount of paycheck.

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 Expense Control

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

Download Income and Expense 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 graphs 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 Expenditures and Income

This template allows you to have global and detailed control of all the income and expenses that you will have in a given 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 on the cash flow of your business.

Download Expense and Income Tracking Template for Excel

Total sums

In case you are looking 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 break-even point.

Download Totals 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 you the best templates so that you can automate the accounting information system of your business venture:

Profit and Loss Account

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

Download Income Statement Template for Excel

Balance sheet

Every time you register in different accounts of your company, the balance of those operations should be reflected on a single sheet . This is why it will be necessary for you to have this template on hand, which will help you to 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 that you pay and the balance of the bank account, it will be necessary 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

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button