6.4. Further data acquisition

In the "maestra" worksheet, 26 codes were first entered in capital letters, which can be used to define common uses. Underneath the account no. and account description deposited. Underneath, the bookings currently accumulated in this file are displayed.

Fig. 82: Master data Account

(Source: download file - There is only a Spanish version.)

In the worksheet "entrada" this code is entered, whereupon account no. and account names are automatically displayed behind it. You can also enter a two-digit cost center number and a three-digit cost unit number that can be entered from the table in column N to a five-digit cost center no. be merged for data transfer into an accounting program. Without data transfer, the data can also be sorted according to the entered codes. But the data area should be copied to another file. The own invoice no. will continue automatically. For this, the last invoice no. from the previous file.

Fig. 83: Invoice entry

(Source: download file - There is only a Spanish version.)

For purchases on account the payment has to be organized. In Europe, this happens with electronic banking. In Kenya, for example, enforced the system M-Pesa, with which the credit on a mobile phone card can be used like a bank account (see: https://www.welt.de/wirtschaft/bilanz/ article162694583/Afrika-zeigt-der-Welt-wie-mobiles-Bezahlen-geht.html) 

In anticipation of the digitization strategy of the Cuban government, the file "factura.ods" already assumes a digital payment, but is based on European processes. For this, the IBAN (international bank account number) and the name of the creditor must be deposited and entered with the creditor no. be called. Next, the invoice no. entered by the creditor and later used for payment. The name and the IBAN are stored under this number in the maestra worksheet.

Fig. 84: Vendor master data

(Source: download file - There is only a Spanish version.)

In addition to the table of recorded invoices, there is a table with the codes, account no. and designation as well as the bookings currently accumulated in this file. This part helps to specify the correct letter as the code for the account number. Right next to it is a table with the names of the vendors and the IBAN, which next to the invoice number. of the creditor and the amount needed for the payment.

Fig. 85: Payment data

(Source: download file - There is only a Spanish version.)

With the immediately paid purchases can be paid with cash, a bank card or a credit card. These three payment methods must be separated and the current cash holdings or the remaining credit limit for the credit card updated. The intended use can be shortened again with 26 letters. Because these are usually specific expenses that must be paid immediately by cash or a card, the deposited bank account numbers may differ from the invoices. It therefore makes sense to place an explanation of the short codes next to the data entry.

The definition as cash, bank or card payment is also made with a letter. But it can also be a digit used. Two further definitions are to be provided for the transfer of bank credit to the credit card as well as cash withdrawals from the bank account.

Fig. 86: Cash register, part 1

(Source: download file - There is only a Spanish version.)

The document no. will be continued automatically. It makes sense to keep a separate file for each month, whereby the period can be incorporated into the document number and otherwise restart each month.

Fig. 87: Cash register, part 2

(Source: download file - There is only a Spanish version.)

For the profitability of a company the right investment is of central importance. The investment properties and their remaining useful life should also be observed if small businesses do not want to afford a sophisticated investment controlling.

Any item above a materiality limit should be registered with the acquisition date, cost, and expected life. These assumptions remain unchanged for the balance sheet and the tax return. For internal purposes, in particular cost accounting, the expected remaining useful life and the current value should also be re-evaluated, especially in the case of older items, differently from the data in the balance sheet. First, a cell reference can be set to the book value. In case of a different assessment, the number would be overwritten by the revaluation.

The worksheet "depreciación" of the file inversión.ods proposes four groups of capital goods with their own inventory number ranges. If an existing object is extended, the extension should be inserted under the same inventory number as a new line. The useful life of the extension is then the remaining useful life of the extended object. For a departure, the date of departure is entered in the column "fecha de dejando". In case of a partial departure, a new line with the same inventory no. are inserted into which then the originally purchased position is divided into the departed and de-used part.

Fig. 88: Entering the data

(Source: download file - There is only a Spanish version.)

In a second part of the table, the data of the asset level are then calculated automatically. Reliably the book values from the table of the earlier period must be transferred to the column "ultimo" and the evaluation date must be entered in the heading of the column "valor contable".

Fig. 89: Evaluation

(Source: download file - There is only a Spanish version.)

With the separation of historic cost and accumulated depreciation, the age of capital goods and the reinvestment needs of the nearest future can be estimated. The depreciations are included in the income statement, the book values in the balance sheet.

The creditos worksheet presents templates for loan repayment schedules. First, the interest rate (per year) and the term (in months) are entered in the head of the table. From this a factor is calculated for the determination of the monthly installments or the loan amount. From this a factor is calculated for the determination of the monthly installments or the loan amount. For a given amount, this is divided by the factor to determine the monthly rate. At a given rate, this is multiplied by the factor to then calculate the possible loan amount. The table below then divides each monthly installment into the interest and principal portion. The column "capital" indicates the remaining debt at the end of the period.

Fig. 90: Repayment plans

(Source: download file - There is only a Spanish version.)

The respective month is defined continuously and according to the calendar. For several loans, as in the table on the right, a total of all loans can be calculated for the respective calendar month. With this data, the interest expense and the repayment can be automatically transferred to the accounting department.(Source: download file - There is only a Spanish version.)

The respective month is defined continuously and according to the calendar. For several loans, as in the table on the right, a total of all loans can be calculated for the respective calendar month. With this data, the interest expense and the repayment can be automatically transferred to the accounting department.

In discussions with small business owners, there was no willingness to cooperate on the issue of recording working time and wages. It gives the impression that this part of the company's activity should not be documented. In reality, there should be workers who should not officially exist. For their payment, there must also be revenue that does not exist officially.

Because the clarification of these backgrounds would not be productive and should also weigh heavily on the atmosphere of conversation, only a Spanish translation of a file is presented here, with which according to the German requirements for payroll accounting of personnel costs could be organized.

The file "salario.ods" has 12 worksheets for each month and one for the whole year. There, in tables for each employee, the pay slips of the individual months are provided with data. There are 8 employees. In the other 12 worksheets, on the other hand, there are payslips.

To the right of the first and second payslips of each month, there is a table with the same row order as the tables in Worksheet 2019 (for the whole year), which juxtaposes the sum for the month and the amounts for individual workers. It is also supplied with data from the worksheet for the whole year. This table is repeated for all 8 employees.

Fig. 91: Payroll

(Source: download file - There is only a Spanish version.)

Fig. 92: Wage journal

(Source: download file - There is only a Spanish version.)

The fields for the individual months only contain summary formulas. However, it is not problematic, e.g. Insert a column with a percentage contribution rate before the totals column and enter a formula in the Social Security fields. On these then very German calculations should be omitted at this point. In the fields for the number of hours worked could be branched to a statistic.

There are many more options for extending this file.

The small business owners first observe the cash flows. Therefore, here is a table to be presented, with which an original cash flow statement can be created from the bank accounts according to the direct method.

First, the amounts with the date of payment are transferred to a table in the worksheet "entrada", with deposits and withdrawals in two separate columns. This can also be done by means of data import. In the column "balance" the current account balance is calculated. As with incoming invoices, the payments are assigned with a letter to the different payment streams. In addition, a comment can be deposited. Most payments will be allocated to groups A (de clientes) and B (a proveedores). For investments, group M can also be defined. With them, the invoice no. detected. The allocation to the cash flows already results in a separation into incoming and outgoing invoices.

Fig. 93: Allocation of payments

(Source: download file - There is only a Spanish version.)

Several bank accounts can be processed. A table with the current totals of the cash flows arranged next to the data collection table is controlled from the maestra worksheet.

From the data of the bank accounts, the data of the cash flow statement result in the worksheet "presentación":

Fig. 94: Cash flow statement

(Source: download file - There is only a Spanish version.)

They are then completed by the cash register data, which can be fully allocated to groups A (cash register receipts) and B (small cash payments in a subsidiary cash register).

The original procedure has the advantage that an evaluation is possible very quickly without the detour via the accounting department. The data collected here can rather be imported into the accounting department.

The central tasks in the annual financial statements are the inventory of inventories, the valuation of receivables, the accrual of time-related expenses and income, the updating of provisions and the tax calculation.

During the year, the purchase of goods or materials is immediately treated as an expense, while the initial inventory of goods is treated as a size that does not change throughout the year. Alternatively, the effort can also be calculated retrograde from the quantity of products sold (and therefore produced) multiplied by a bill of material of the average installed material.

Despite the simplifications, a permanent inventory is being used, which has already been addressed in retailer sales. The dealers or restaurant operators record the remainder of their goods in order to determine the quantity needed for the next purchase of goods. But this is about a pure quantity recording. For the annual financial statements, these holdings must additionally be valued at the cost of acquisition. This is usually the last purchase prices used. In addition, the amount of damaged material and a percentage by which the value is reduced are then recorded. The same procedure applies to finished products in production plants.

Fig. 95: Inventory

(Source: download file - There is only a Spanish version.)

The annual financial statements also include the valuation of receivables from the past year, which were not paid at the time the balance sheet was prepared. In addition to the amount and the due date, the percentage of this receivable is recorded. The reasons for impairment must be recorded in a log. From this a value adjustment is calculated as a correction amount.

Fig. 96: Demarcation

(Source: download file - There is only a Spanish version.)

A second worksheet calculates the accrual of time-related expenses and income. In principle, income and expenses are realized at a time. For a reference to a period, the expense or income relating to two different years must be distributed over these years. The time of payment is not important. In addition to effort and income, it must be distinguished whether the payment is made before or after the service. This determines whether another receivable or payable, or an asset or liability is accounted for.

This worksheet also calculates tax back payments or refunds. The table provided for this purpose can be refined in the specific case.
 

Fig. 97: Provisions

(Source: download file - There is only a Spanish version.)

Provisions include uncertain liabilities that must be estimated. For the updates, the provisions of the previous year are updated. Most of the obligations are fulfilled, with residual amounts remaining. In these cases one speaks of a consumption of the provision. Remaining residual amounts or provisions that have lost the reason for the formation are reversed to increase profits. However, it may also happen that the provision must be increased. Finally, new provisions can be formed in the current year.

The tables of the file valoración.ods ask the small business owners the relevant questions in order to make the necessary evaluations for the annual accounts. The fields of the tables can be linked to other tables in which more complex calculations can be made individually.