6.2. Data collection from sale

The data collection for the sale is aimed at the industry. Various solutions have been developed. They are presented here. Cuba does not know of the European value added tax. Instead, according to Law No. 113 on the Taxation Act of 21.11.2012), there are the sales tax under Articles 132 to 139, the special tax on products and services under Articles 140 to 145 and the tax on services under Articles 146 to 151 The tax charges the provider; According to Art. 139, sentence 2, it may not be added to the price of the goods. The retail rate (Art 138) and services (Art 149 + 150) is 10%. There is no deduction of input tax, which is why the tax card on the bill is unnecessary. Nevertheless, the entrepreneurs have to generate the tax and thus take into account their price demands.

With a strong division of labor, this leads to higher taxation, which hinders small business owners, who limit themselves to small sections of value added. According to Karl Marx (Das Kapital, p. 81), the price of a commodity is determined by the labor power objectified in it. If this amount were 100 and if it were only provided on one production level, the price of the goods would have to be 111.11 so that 100 remains after deduction of the tax. The tax rate would be 11.1 instead of 10%. If two entrepreneurs split this amount equally, then the first entrepreneur would have to offer his services for 55.56. The second entrepreneur would have to offer his product for 117.28, leaving him with 50 after deducting the tax of 17.73 and the advance payment of 55.56. The tax rate would have increased by 55.6%. For 7 entrepreneurs in the value chain, all other things being equal, the tax burden would have been 55.8%. This tax disadvantage over large (state) enterprises must compensate the Cuentapropistas with higher effectiveness.

Craftsmen take over jobs, most repairs, to households or other businesses. For larger orders, the scope of the work, the estimated price and the date of completion are set first. The order management of craftsmen (artesanos) is facilitated with the file ventasA19.

Order management is listed in the worksheets. In a journal, only the document numbers are managed and data is summarized from the worksheets. There are 20 processes per file. After that, a follow-up file must be used. The document is released when a document number is assigned. Before that, only drafts are created. The journal assigns the quotation, the order, the delivery note and the invoice to a transaction. If an offer is created without an order, the remaining documents remain empty. For partial deliveries two delivery notes can be filled out. Then this offer, order and invoice would remain empty.

In the "productos" workbench, the craftsman can list his material, which he regularly uses and has to buy. He can mark the columns for the article number, the name and the individual price and copy it into his offer. On the right, he notes his purchase price and calculates the margin of this type of material.

Fig. 53: article master data for material

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

In the worksheet "oferta" he prepares the offer for the customer. As a small businessman, he will work out this in conversation. Then he should insert some empty columns in the "productos" worksheet between the selling price and the purchase price of the material, so that the customer can not recognize the margin. He could also delete these columns for the conversation and copy them back later.

In an area for the address of the customer, the salutation, name, street, zip code and city are entered. Next the date of the offer is entered. Under the heading "oferta" the entrepreneur fills in how many days he can fulfill the task. From this, the delivery date is calculated.

Fig. 54: Offer

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

There are 22 lines available to list the required material that the entrepreneur can copy from the productos worksheet. After that he has to enter the amount. He can change the unit price. At the end, 3 lines are planned for the work to be done. There are three qualification levels. The hourly wages are specified in the worksheet "diario". Also this price can be changed in the negotiations. The terms of payment at the end of the offer are also taken from the worksheet "diario". The worksheet "calculación" allows the entrepreneur to evaluate the profit prospects of the possible contract.

Fig. 55: Calculation

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

Fig. 56: Order confirmation

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

The latest status of the offer negotiated with the customer should be printed out and a copy signed by the customer. Then the data of the offer are marked and copied into the worksheet "orden" in the same line area. While the offer is still a non-binding information of the entrepreneur about his prices, a contract is concluded with the order confirmation.

Also, the delivery date is now not a mere assessment, but a part of the contract. The number and the date of the offer are automatically read; the date of the order must be completed. The order number must be given in the worksheet "diario".

The form for the delivery note is largely free. Address and order number are accepted. The delivered materials and the work done are copied from the order. For extensive partial services, several delivery notes can be filled out; Offer, order and invoice will remain free. In the worksheet "diario" no numbers may be assigned for these rows. For smaller partial services, the service date of the item can also be noted in the delivery note.

After completing the job, the data from the worksheet "orden" is copied to the corresponding lines of the factura worksheet. If there are deviations from the order, the invoice will be adjusted. The deviations must then be documented with delivery notes.

This worksheet also contains an area where a letterhead is defined as a header for the entrepreneur. It can also be a footer designed for more information.

Fig. 57: Invoice

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

In the worksheet "diario", the orders are combined in a table.

Fig. 58: Journal I

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

The sales are differentiated into the groups material and work. Behind the invoice amount the payment date and the amount are noted. After that it is calculated whether the invoice or a part of it is open, and whether the open amount agrees with the allowed discount deduction. This determines the sales and the outstanding receivables.

Fig. 59: Journal II

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

These services are of great importance because of the poor state of public transport as well as the poor long-distance connections and bureaucratic obstacles to getting tickets. There are rickshaws in the cities as a bicycle taxis for a maximum of 2 people and shorter sticks, motorcycle taxis (mostly from DDR production) for one person, and car taxis, which also take trips to more distant destinations. Whilst uniform prices are charged for simple inner-city journeys, prices for cross-country or longer periods are different and negotiable. There are mainly vehicles from Soviet production used, which repair the driver if necessary, even if necessary. The professional group also has little interest in fully documenting their economic circumstances. It is to be expected that fewer passengers will appear in the records than were actually transported. An effective cost-effectiveness check would require that, in addition to the revenue, the km-levels of operational use would also be recorded. This could be done with the file ventasC19 (coche = car). It starts with a logbook for each month:

Fig. 60: Logbook

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

After the date, the km stand is entered at the beginning and at the end of the activity. From this, the daily driven kilometers can be calculated and added at the end of the month. At the same time, from the km level at the beginning and at the end of the month, the total mileage of the vehicle can be calculated. This results in a quota of operational use. For city driving, recording the number of trips is sufficient. If different prices are taken for day and night travel, different columns are needed. The regular daily income results from multiplying the number of trips with the price.

For special trips a special record should be made. In addition to the turnover and the destination, the mileage at the beginning and at the end of the trip should be noted. In another part of the table, the expenditure on petrol with the quantity (liters) recorded and other expenditure with an indication of the purpose should be recorded.

In an annual overview, the totals of the individual months are summarized in a statistic:

Fig. 61: Statistics

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

Below this a profit calculation is added in which the private portion of the variable costs such as income is taken into account and a contribution margin is determined. From this, the fixed costs reduced by the private share and the imputed entrepreneurial wage are deducted.

Fig. 62: Profit calculation

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

Fig. 63: Fixed costs

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

To calculate and document the fixed costs, another table is maintained in the Statistics worksheet.    

Cuentapropistas are not allowed to be wholesalers. However, the distinction between wholesale and retail is a matter of definition. If you are not guided by the amount traded then the retail trade is characterized by selling on anonymous markets. The retailer has salesrooms and is visited there by the customers. The prices are not negotiated and the goods are immediately paid and taken.

A wholesaler knows his customers who buy the goods for their company. He accepts orders that will be delivered later. He brings the goods to the customers, they have to pay only after a few days. The cuentapropitas operating in these businesses can procure special goods for other companies. The wholesale (mayoreo) defined in this way is supported with the file ventasM19.

The jobs are recorded in the worksheet "ventas".

Fig. 64: Order acceptance

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

With the indication of the customer no. (A50) the name (cliente50) and the address (dir-1-50 and dir-2-50) are controlled. In the worksheet "estatistica" the orders and the billed sales are displayed next to a table with this data. The number of the seller who processes the order can also be used for another differentiator.

Next, the article no. and the amount entered. Product description and unit price are automatically read in like the customer data from a table in the worksheet "estatistica". The amount per line is calculated automatically.

Fig. 65: Order processing

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

The data is transferred to the worksheet "entrega", with the quantity from the order first being zeroed out with the "?" Column. If the seller has checked or organized the delivery capability of this item, he deletes this clearing item and thus releases the item for the invoice. If all ordered items can be delivered and have been activated, the worksheet "estatistica" can show the invoice no. be forgiven. This can be the delivery.

The worksheet also updates the inventories. It starts with the article no., Article designation and the price request. These data are automatically entered with the order no. read. The price claim can be changed in negotiations with the customer. The following columns show the sales from the orders and invoices.

In the following part, the initial inventory of the various goods is first presented. This column can also be copied to the previous file. The next column shows the deliveries received. The withdrawals can be imported from the settled orders and the current stock can be calculated. Thereafter, the already ordered but not yet delivered goods can be entered. Behind this the withdrawals from the orders are deducted. To ensure that goods already delivered are not deducted twice, this item is adjusted. It then results in the fictitious final inventory. If this is negative, this is indicated by the message "buy".

Fig. 66: Article master data and article statistics

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

The data from the worksheet "entrega" are read into the print version of the invoice in the worksheet "imprime". This allows the invoice to be printed. The first 6 lines contain the letterhead and are repeated on each printed page. Each invoice can be printed selectively by displaying the customer's serial number in the worksheets ventas and entrega on the customer no. is specified as the print area "from page ... to page ...".

Fig. 67: Invoice printing

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

In a table in the worksheet "estatistica", in addition to the control of the customer numbers and article numbers with an evaluation of the sales, the release of the invoices with an assignment of the invoice number is controlled. The note "trabajando" shows that the order is still being processed and still no invoice number. was awarded. Two additional columns indicate the date of payment and the amount paid.

Fig. 68: Administration of invoice numbers

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

In addition, there is an evaluation for the seller with the orders and the billed sales.

The table "calculación" contains formulas that should not be changed.

For each day a copy of the file is created. For this purpose, month and day are added to the name "ventasR19", e.g. "VentasR190330" for March 30, 2019.

Every food and drink gets a code in the menu. He can be chosen freely. A letter with a number should suffice. A short code reduces the risk of erroneous capture. The codes with a long name and price are stored in the table "estatistica" in the fields J11 to L109. There are 99 options available.

Fig. 69: Recording orders

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

The following columns show the sold quantity and the daily turnover. In the area B51 to H102 the turnover for each customer is displayed with the invoice number, the number of persons and the number of the table. In the area B37 to F47 there is a table with the turnover per table. If the waiters associate the customers with the categories A (eg often comes), B (eg a familiar face, occasionally) and C (eg unknown face or rarely), an evaluation according to customer groups can be made in the table R9 to X60 , Customer satisfaction is a high priority for most restaurants. The division into customer groups allows an observation of this goal.

Fig. 70: Example of orders entered

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

The tables B9 to G31 help with the daily billing of the cash register. In the two lines above it is calculated whether there is a difference between the daily income and the turnover after the tables.

The waiters enter the orders in the table "ventas" in the color-coded fields. In line 1, the current date and the last invoice number from the last file must be entered. In columns C and D code and quantity are entered. In column B, a category of customers (A, B or C) can be made. For this, the number of the table and the number of persons is entered.

Fig. 71: Invoice printing

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

In column K, the status of processing is noted. When a job is done, the waiter enters a letter there. Every waiter can get his own letter. Then you can track who served the customer.

Fig. 72: Statistics 1

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

The waiter can record the order with code and number on a simple note, then enter into the computer and give the note in the kitchen. If the invoice is to be printed, the waiter must briefly change to the worksheet "imprime" and print out the page, using the page with the customer no. must limit, as e.g. for the customer 28, the print area from page 28 to page 28. A copy of the invoice is printed on the left and the invoice on the right on a sheet of paper. The leaf can be separated in the middle. The right half gets the customer, the left half remains as a copy in the restaurant. Lines 1 to 6 contain the data of the restaurant. They are repeated on every expression. The paper size is set to Letter (US Standard Letter) = 215.9 × 279.4 mm.

Fig. 73: Statistics 2

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

There are 48 customers with 10 positions per customer and 2 customers (# 49 + 50) with 16 positions available. If more customers arrive on individual days, a second file would have to be produced.

Fig. 74: Statistics 3

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

The table "calculación" contains formulas that should not be changed.

In the short-term rental apartments or individual rooms are rented day by day to individual tourists who have no interest in the fringe benefits of hotels. It is supported by the file ventasT19. The bookings are managed in the worksheet "diario". There are also 26 worksheets with the letters A to Z and a billing for the customers. These sheets refer to the 26 rows of a table in "diario", which is shown in the following figures. The columns have the following meaning:

hoja:                      Worksheet, labeled with letters A to Z.
codigo:                  3 apartments with the numbers 1 to 3 are supported.
apartamiento:     Name of the apartment
factura:                 Invoice no.                              del ...:               Invoice date
llegada:                 Arrival                                      días:                 Duration of stay
salida:                   Departure
pers .:                   number of guests
desayuno:            is marked with "x", if additionally a breakfast is desired.
precio:                  price of the room for the entire stay
desay .:                 Calculation of breakfast for the entire stay
otro:                      other services provided
total:                     invoice amount for all services
pago:                    data of payments received
fecha:                   date of payment:                     tarjeta:              credit card
banco:                  Bank Transfer                          effectico:           Cash

Fig. 75: Data acquisition

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

Fig. 76: Addresses + Scheduling

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

abierto:                    open amount when creating the bill
Nombre 1:               first part of a long name, or salutation
Nombre 2:               second part of a long name, or normal name
Calle, no .:                street and house number
Cuidad:                     postcode and city
país (exranjeros):    country of origin, with foreigners

Right next to it is a table with calendar data. The days for which reservations are available can be deleted. This displays the days the room is available. Upon a request from a customer, e.g. on the phone, the necessary data is visible. If a customer wants to reserve a room, his address and the period can be entered immediately.

Fig. 77: Invoice printing

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

Billing is printed by going to the relevant worksheet in the file and specifying the current worksheet in the print command. The leaf can be separated in the middle. The right half is for the customer and the left half for the own documents.

Retailers who use sales offices to operate an anonymous market receive cash payments from customers. They do not write their customers bills. You could also accept credit or debit cards as a means of payment. But that would require an electronic cash register system and a problem solution by spreadsheet unnecessary. Therefore, this section does not address this possibility.

Fig. 78: recording the stock and calculation of sold quantities

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

It is assumed that the retailer will be provided at regular intervals, e.g. Once a week, checks its inventory to buy missing items. The table "ventas" offers the possibility to record the results of this statement. First of all, the article number, article description, sales price and purchase price are recorded. These data must be updated as needed. Thereafter, the initial inventory is recorded. At regular intervals, the purchases are recorded in quantity and amount as well as the final inventory in units of measure. From this, the quantity sold, the resulting extrapolated sales and raw profit are calculated. This part can be copied several times next to each other. For the sales, purchases and raw profits a sum is formed. This part of the table can be copied several times next to each other. In the example, 5 time periods with 9 articles were formed. The number of articles can be extended as required by inserting empty lines and copying the formulas before the last line. Fixed periods of 7 days were assumed. However, the entry of the final inventory should be made immediately before the purchase of new goods, regardless of specific time periods. The purchases should be entered immediately after the purchase.

The simple method of recording revenue is based on daily revenues, which can be determined indirectly from the differences in cash on hand. The withdrawals must be adjusted. A table for calculating the cash balance taking into account both national currencies in the worksheet "caja" should avoid addition errors. The results are entered in a table every day.

Fig. 79: Cash position determination

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

Fig. 80: Cash register control

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

The worksheet "control" records sales, purchases, and gross profits, which are extrapolated from sales and usual margins. This results in the change in inventories.

A control table, in which the deposits and withdrawals from the intake of cash on hand are read in, indicates possible differences. It is assumed that the payment of goods purchases from the till takes place. If this is not the case, the difference is explained.

In the case of short-term rentals, there is the peculiarity that the service is not provided at one time, but in a period of time. The periods are to be demarcated and often to disassemble individual reservation periods. Advance payments can also be collected. For this purpose, a formula work was included in the file, which assigns the sales accordingly. A comparison of the arrival, departure and payment date with the end dates of the calendar months allows an assignment to the months.

The attendance days are calculated for the arrival and the departure month. If there is a full month in between, all days are taken into account. Subsequently, the turnover is distributed in relation to the days per month. Because a tourist visa is valid for a maximum of 30 days and can only be extended to a maximum of 60, longer periods were not planned.

The payment takes place at a time. It is to be distinguished whether the achievement was still paid in the same month, whether a demand arose and still exists at the end of the month, or whether an advance payment took place.

For taxi drivers, restaurants and retailers, revenues can be derived from daily revenues. There are no claims and advance payments here.

The craftsmen and wholesalers write bills and receive their money a few days after the service has been provided. With them there are changes to demands. There could also have been an advance payment. The sales are calculated from the invoices. As with short-term rentals, it must then be assigned on the basis of the invoice and payment date. A distinction is then made as to whether the invoice and payment are in the same month, whether the payment takes place in a later month (claim), or whether it was paid before the invoice was created (down payment received).