Here you will learn how to make a usable table in Excel from one formally outdated form. Many Excel users often wonder if what they know is enough to get started and be successful in jobs where good knowledge is expected. The aim of this article is to guide you through a typical process of creating records, processing and analyzing this data, and finally printing or creating PDF files in the standard format as provided by our regulations.
For this purpose, we have chosen a standard form known as KL-form for Sales Price Calculation in Excel.
It enables small companies to simplify the calculation of sales prices for goods or services and to prepare the necessary documents on the prescribed form KL, which also forms the basis for calculating tax liabilities and filling in the prescribed number PK-1 – Business Book of Income and Expenditure. Don’t be alarmed by the sign PK – Tax credit for investments in fixed assets intended for companies.
Cost calculation form
The prescribed layout of the form meant that all data was collected from paper documents. The documents from which the data for the production of sales prices are taken are the invoices/invoices and delivery notes of the supplier.
Incoming documents contain information such as Name of goods, unit of measure, quantity, price per unit of measure, the value of goods, approved individual or total rebate, tax rate, amount of VAT, code, and other data related to goods.
Goods for resale are received in the warehouse and on that occasion, an Invoice or Receipt of goods is made in the warehouse. Differences in quantity and value may exist as a result of damage to the goods.
-
Recording data in the KL form
In the records of the form KL – Calculation of the selling price, goods (products) are entered chronologically at the same VAT rates for each invoice of the supplier. The data is entered as follows:
- 1 column – ordinal number of goods (products), where for each price calculation the ordinal number is entered starting from number one;
- 2 column – the name of the goods from the invoice, delivery note, receipt, or other document on the procurement of goods, where the goods are grouped according to the same VAT rates;
- 3 column – unit of measure of purchased goods (kilogram, liter, meter, piece, etc.);
- 4 column – purchased quantities of goods;
- 5 column – the price of purchased goods per unit of measure, whereby, if the goods were received with a rebate, the price is reduced by the amount of the approved rebate;
- 6 column – the value of purchased goods, as a product of the amount from col. 4 and 5;
- 7 column – the amount of dependent costs (costs of transport, loading, unloading, storage, storage, etc.); sum of amounts from col. 6 and 7 are entered in column 12 of the business book PK-1;
- 8 column – the amount of the difference in price obtained when the appropriate value of the margin, ie the amount of the rebate approved by the supplier, is applied to the purchase value of goods (value of goods from column 6 increased by dependent costs from column 7); the total amount from column 8 is entered in column 14 of the business book PK-1;
- 9 column – sales value of goods without VAT, as the sum of the amount from col. 6 – 8 (basis for VAT calculation);
- 10 column – prescribed VAT rate;
- 11 column – the amount of calculated VAT obtained by multiplying the rate from column 10 with the sales value of goods from column 9; the total amount from column 11 is entered in column 15 of the business book PK-1;
- 12 column – sales value of goods with VAT, as the sum of the amount from col. 9 and 11, where the sales value of goods at the same VAT rates is expressed in special amounts – subtotals; the total amount from column 12 is entered in column 16 of the business book PK-1;
- 13 column – selling price of goods per unit of measure obtained when the selling value of goods with VAT from column 12 is divided by the purchased quantity of goods from column 4; Entrepreneurs who are not VAT payers enter the gross value of the invoice (purchase statement with VAT included) in the purchase price calculation, whereby the dependent costs are also entered as the gross value stated in the invoice.
Form KL is an accompanying document of the invoice of the procured goods or other document on the basis of which the procurement of goods was made (receipt, delivery note, etc.); in the upper right corner of the KL records, enter the ordinal number under which the entry was made in the business book PK-1 Business Book of Revenues and Expenditures .
This is not the only task of making a calculation. Namely, business accounting should, in cooperation with operational accounting, statistics and analysis, diagnose and clarify various phenomena, forms of work and functions in the organization. They provide reliable information for making business decisions and taking planned or corrective actions, which will contribute to better business and the future.
The calculation serves as a basis for:
- determining the selling price of products and services,
- observing and analyzing structural changes in business.
The calculation that serves as a basis for calculating the selling price of products and services breaks down costs into proportional and fixed.
Caculation – calculation
The goal of making a calculation – calculation is to determine the cost of production of a particular product or service. It forms the basis for calculating the selling prices of goods or services of each organization.
In the standard form of the first 7 columns are data that are mainly taken from the Receipt of goods to the warehouse, which is done on the basis of the Delivery or Delivery of goods.
The main question is how do we arrive at the amount in column 8, The amount of the price difference.
Calculating the difference in price
The difference in price is obtained when the purchase value of goods (value of goods from column 6 increased by dependent costs from column 7 ) is applied to the appropriate percentage of margin, ie the amount of rebate approved by the supplier.
So, getting the final selling price can be achieved in two ways:
- By increasing the purchase price by the percentage of sales margin (5%, 10%, 15%, 30%, 50%…) which we calculate by percentage calculation;
Formula: Price difference = Purchase price x Margin [%] – 100
Sales price = Purchase price + Price difference - By selling at the purchase price where the amount of the rebate approved by the supplier or manufacturer, is based on our business relationship. It can be any amount from 0 to a theoretical amount of 99%;
Formula: Price difference = Sales price x Discount [%] – 100
Sales price = Purchase price
- By determining the final selling price that a product should have in a competitive market. This is the situation when we want to determine the exact nominal amount of the selling price.
Formula: Price difference = Sales price – Purchase price
The total amount from column 8 is entered in column 14 of the business book PK-1 .
Criteria for calculating the price difference
Since we basically have three ways of calculating, it is necessary to determine the criteria for determining the formula by which we calculate the difference in price. The choice of the formula to be applied can be based on a combination of input data. Eg if our Margin is higher than zero, it means the first calculation, If our rebate is higher than zero, the second calculation and if the margin and rebate are equal to zero, and the selling price is higher than the purchase price, then it is the third calculation.
We can also create a new column that should not be printed on the document, but which should allow the user to choose the type of calculation.
Protection against modification by locking the formula field
There are columns that are populated or whose content is taken from other tables. There are also columns that are calculated as e.g. column no. 6 The value of the goods is calculated by multiplying the values from column 4. quantity and column 5. price per unit of measure. Not to skip, but in the 1st column of the ordinal number, the formula is inserted which reads: = IF (IFNA (C8; 1) = 0; ””; B7) . Its work is based on testing whether there is a value in column 2. The name of the goods exists, if there is no IFNA function it will return the value 1. This value is tested by the IF function and if it is different from zero, the ordinal number 1 is printed for the first line, and the previous number is increased by 1 for all other lines. As we can see in the following figure.
This can be solved in several ways, here is another one = IFNA (IFNA (C8; 1) = 0; ””; ROW () – 7) where the ROW () function of the workspace table number is used.
Now select all the cells that need to be unlocked after locking the worksheet, in this case all the orange columns, and press Ctrl + 1 . You will receive the following content.
Now select the Protection tab and remove the heel from the Locked option .
After this action, locking the entire sheets will leave these cells unlocked for input.
Download data from another document and validate data
As the document, in addition to the main table, also has fields with the data of the business entity in which we use this document, it is necessary to fill in the header, double-clicking on the header element that will take us directly to the page to fill.
Page to populate the header.
Print or create a PDF document
Finally, now you need to print the document and sign it. We can do this in two ways, in electronic form, and in physical or paper form.
If we do everything electronically, we first perform the electronic signing and save it in the appropriate folder in the original Excel format. Of course, electronic signatures are revoked at any time when some data in the excel file itself changes. Another way can be to first save it in PDF format in the File / Save as option and then electronically sign the PDF file itself.
On the other hand, if we do everything in physical form or want to keep the documents on paper, we will first print the document and then physically sign the documents.
Download excel file
CONTINUED TO FOLLOW!
