Za tu svrhu smo odabrali standardni KL obrazac kao Kalkulacija prodajne cene robe u Excel-u.
Ona omogućava malim firmama da pojednostave izračunavanje prodajnih cena za robu ili usluge i da potrebna dokumenta izrađuju na propisanom obrascu KL, što ujedno čini osnovu za izračunavanje poreskih obaveza i popunjavanje propisanog obrasca PK-1 – Poslovna knjiga prihoda i rashoda. Nemojte da vas buni oznaka PK – Poreski kredit za ulaganja u osnovna sredstva namenjen za privredna društva.
Kalkulacija prodajne cene robe izradom KL obrasca
Propisani izgled obrasca je podrazumevao da se svi podaci prikupljaju iz papirnih dokumenata. Dokumenti iz kojih se preuzimaju podaci za izradu prodajnih cena su fakture/računi i otpremnice dobavljača.
Na ulaznim dokumentima se nalaze podaci kao što su: Naziv robe, jedinica mere, količina, cena po jedinici mere, vrednost robe, odobreni pojedinačni ili ukupni rabat, poreska stopa, iznos PDV, šifra, kao i drugi podaci u vezi robe.
Roba za daljnju prodaju se prima u magacin i tom prilikom se izrađuje Dostavnica ili Prijemnica robe u magacin. Razlike u količini i vrednostima mogu postojati kao posledica oštećenja robe (kalo ili raastur robe).
Evidentiranje podataka u KL obrascu
U evidenciju obrasca KL – Kalkulacija prodajne cene, roba (proizvodi) se unose hronološki po istim stopama PDV za svaku fakturu dobavljača. Podaci se unose na sledeći način:
- 1 kolona – redni broj robe (proizvoda), pri čemu se za svaku kalkulaciju cena redni broj upisuje počev od broja jedan;
- 2 kolona – naziv robe iz fakture, otpremnice, prijemnice, odnosno drugog dokumenta o nabavci robe, pri čemu se roba grupiše prema istim stopama PDV;
- 3 kolona – jedinica mere nabavljene robe (kilogram, litar, metar, komad i dr.);
- 4 kolona – nabavljene količine robe;
- 5 kolona – cena nabavljene robe po jedinici mere, pri čemu se, ako je roba primljena sa rabatom, cena umanjuje za iznos odobrenog rabata;
- 6 kolona – vrednost nabavljene robe, kao proizvod iznosa iz kol. 4 i 5;
- 7 kolona – iznos zavisnih troškova (troškovi prevoza, utovara, istovara, skladištenja, ležarina i dr.); zbir iznosa iz kol. 6 i 7 unosi se u kolonu 12 poslovne knjige PK-1;
- 8 kolona – iznos razlike u ceni koja se dobija kada se na nabavnu vrednost robe (vrednost robe iz kolone 6 uvećana za zavisne troškove iz kolone 7) primeni odgovarajući procenat marže, odnosno iznos rabata odobrenog od strane dobavljača; ukupan iznos iz kolone 8 unosi se u kolonu 14 poslovne knjige PK-1;
- 9 kolona – prodajna vrednost robe bez PDV, kao zbir iznosa iz kol. 6 – 8 (osnovica za obračun PDV);
- 10 kolona – propisana stopa PDV;
- 11 kolona – iznos obračunatog PDV koji se dobija množenjem stope iz kolone 10 sa prodajnom vrednošću robe iz kolone 9; ukupan iznos iz kolone 11 unosi se u kolonu 15 poslovne knjige PK-1;
- 12 kolona – prodajna vrednost robe sa PDV, kao zbir iznosa iz kol. 9 i 11, pri čemu se prodajna vrednost robe po istim stopama PDV iskazuje u posebnim iznosima – podzbirovima; ukupan iznos iz kolone 12 unosi se u kolonu 16 poslovne knjige PK-1;
- 13 kolona – prodajna cena robe po jedinici mere koja se dobija kada se prodajna vrednost robe sa PDV iz kolone 12 podeli nabavljenom količinom robe iz kolone 4; preduzetnici koji nisu obveznici PDV u kalkulaciji za nabavnu cenu unose bruto vrednost fakture (nabavna vednost sa uključenim PDV), pri čemu se i zavisni troškovi unose kao bruto vrednost iskazana u fakturi.
Obrazac KL je prateći dokument fakture nabavljene robe ili drugog dokumenta na osnovu kojeg je izvršena nabavka robe (prijemnica, dostavnica, i dr.); u gornjem desnom uglu evidencije KL upisuje se redni broj pod kojim je izvršeno knjiženje u poslovnoj knjizi PK-1 Poslovna knjiga prihoda i rashoda.
To nije jedini zadatak izrade kalkulacije. Naime, poslovno računovodstvo treba da u saradnji sa pogonskim knjigovodstvom, statistikom i analizom dijagnosticira i razjasni različite pojave, oblike rada i funkcije u organizaciji. One predstavljaju pouzdane informacije za donošenje poslovnih odluka i preduzimanje planskih ili korektivnih akcija, koje će doprineti boljem poslovanju i budućnosti.
Kalkulacija služi kao osnov za:
- određivanje prodajne cene proizvoda i usluga,
- posmatranje i analiziranje strukturnih promena u poslovanju.
Kalkulacijom koja služi kao baza za izračunavanje prodajne cene proizvoda i usluga raščlanjavaju se troškovi na proporcionalne i fiksne.
Kakulacija – obračun
Cilj izrade Kalkulacija – obračun jeste da se putem nje utvrdi cena koštanja proizvodnje određenog proizvoda ili usluge. Ona čini osnovu za izračunavanje prodajnih cena roba ili usluga svake organizacije.
U standardnom obrascu prvih 7 kolona su podaci koji se uglavnom preuzimaju iz Prijemnice robe u magacin, koja se radi na osnovu Dostavnice ili Otpremnice robe.
Glavno pitanje je na koji način dolazimo do iznosa u koloni 8, Iznos razlike u ceni.
lzračunavanje razlike u ceni
Razlika u ceni se dobija kada se na nabavnu vrednost robe (vrednost robe iz kolone 6 uvećana za zavisne troškove iz kolone 7) primeni odgovarajući procenat marže, odnosno iznos rabata odobrenog od strane dobavljača.
Znači, dobijanje konačne prodajne cene se može postići na dva načina:
- Uvećanjem nabavne cene za procenat prodajne marže (5%, 10%, 15%, 30%, 50% …) koju izračunavamo procentnim obračunom;
Formula: Razlika u ceni=Nabavna Cena x Marža[%]-100
Prodajna cena = Nabavna cena + Razlika u ceni - Prodajom po nabavnoj ceni gde je iznos odobrenog rabata od strane dobavqača ili proizvođača, a na osnovu našeg poslovnog odnosa. To može biti bilo koji iznos od 0 do teoretskog iznosa 99%;
Formula: Razlika u ceni=Prodajna Cena x Rabat[%]-100
Prodajna cena = Nabavna cena
- Određivanjem konačne prodajne cene koju proizvod treba da ima na konkurentskom tržištu. To je situacija kada želimo odrediti tača nominalni iznos prodajne cene.
Formula: Razlika u ceni=Prodajna cena – Nabavna Cena
Ukupan iznos iz kolone 8 unosi se u kolonu 14 poslovne knjige PK-1.
Kriterijumi za izračunavanje razlike u ceni
Pošto u suštini imamo tri načina obračuna potrebno je odrediti kriterijume za određivanje formule po kojoj izračunavamo razliku u ceni. Sam izbor formule koja će biti primenjena može se zasnivati na kombinaciji ulaznih podataka. Npr. ako nam je Marža veća od nule znači prvi obračun, Ako nam je rabat veći od nule, drugi obračun i ako su marža i rabat jednaka nuli, a prodajna cena veća od nabavne cene onda je u pitanju treći obračun.
Isto tako možemo napraviti novu kolonu koja ne treba da se štampa na dokumentu, ali koja treba da omogući korisniku da izabere vrstu obračuna.
Zaštita od izmene zaključavanjem polja sa formulama
Postoje kolone koje se popunjavaju ili čiji sadržaj se preuzima iz drugih tabela. Isto tako postoje kolone koje se izračunavaju kao npr. kolona br. 6 Vrednost robe se izračunava množenjem vrednosti iz kolone 4. količina i kolone 5. cena po jedinici mere. Da ne preskočim ali u 1. koloni Redni broj ubačena je formula koja glasi: =IF(IFNA(C8;1)=0;””;B7). Njen rad se zasniva na ispitivanju da li je u koloni 2. Naziv robe postoji vrednost, ako ne postoji funkcija IFNA će vratiti vrednost 1. Ta vrednost se ispituje funkcijom IF i ako je različita od nule za prvi red se ispisuje redni broj 1. a za sve ostale redove se uvećava prethodni broj za 1. Kao što možemo videti na sledećoj slici.
Ovo se može rešiti na više načina, evo još jednog=IF(IFNA(C8;1)=0;””;ROW()-7) gde se koristi funkcija ROW() rednog broja tabele radnog prostora.
Sada selećtijte sve ćelije koje nakon zaključavanja radne tabele trebaju da budu otključane to su u ovom slučaju sve naranžaste kolone i pritisnete kombinaciju Ctrl+1. Dobićete sledeći sadržaj.
Sada izaberite karticu Protection i skinite štiklu sa opcije Locked.
Nakon ove akcije zaključavanje celog sheets ostaviće ove ćelije otključane za unos.
Preuzimanje podataka iz drugog dokumenta i validacija podataka
Kako dokument pored glavne tabele ima i polja sa podacima poslovnog subjekta u kojem koristimo ovaj dokument potrebno je popuniti zaglavlje, duplim klikom na element zaglavlja koji će nas odvesti direktno na stranicu za popunjavanje.
Stranica za popunjavanje podataka u zaglavlju.
Štampanje ili izrada PDF dokumenta
Na kraju, sada treba štampati dokument i potpisati ga. To možemo obaviti na dva načina, u elektronskoj formi i fizičkoj ili papirnoj formi.
Ako radimo sve elektronski, prvo izvršimo elektronsko potpisivanje i snimimo u odgovarajući folder u originalnom formatu Excela. Naravno elektronski potpisi se poništavaju u bilo kom trenutku kada se promeni neki podatak u samoj excel datoteci. Drugi način može biti takav da prvo ga snimimo u PDF formatu u opciji File/Save as i potom elektronski potpišemo samu PDF datoteku.
Sa druge strane, ako sve radimo u fizičkom obliku ili želimo dokumenta čuvati u papiru, prvo ćemo štampati dokument i potom fizički potpisati dokumenta.
Preuzimanje excel datoteke
NASTAVAK SLEDI![:en]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![:]