Preparation of Invoice, Receipts, Voucher

An invoice is a document that describes the goods and services that a company offers to a customer and specifies the customer’s responsibility to pay for those products and services. Invoices are the foundation of a small business’ accounting system. An invoice details how much your client owes you when payment is due and what services you rendered.

Invoices are the business records that allow companies to get paid for their services, so invoicing is critical for small businesses. Invoice can be defined as “a list of goods sent or services provided, with a statement of the sum due for these; a bill.”, as per the Oxford English Dictionary.

Types:

  • A standard invoice is the most common form of invoice used mainly by small businesses and flexible to fit in most industries and billing cycles.
  • credit notes and debit notes. It is also known by names such as credit memo and debit memo. Credit and debit notes are used for decreasing value and increasing values of previously raised invoices, respectively.
  • For instance, credit notes are used while the business wants to pass on a discount or provide a refund to its customers or buyers. On the other hand, debit notes are used to increase the quantity or value of the original invoice.
  • Another type of invoice is the pro forma invoice. A pro forma invoice is an estimation that a company sends to a customer before delivering services. A pro forma invoice gives the client an estimation of the cost of the work that needs to be done. When a project is completed, pro forma invoices need to be adjusted to represent the hours worked accurately.
  • A commercial invoice is a document provided by a company for products it sells to consumers worldwide. Commercial invoices provide information about the sale that is required to calculate customs duties for cross-border transactions.
  • A timesheet is an invoice used when a company or employee bills depending on the number of hours they work and their hourly rate of pay. Contract workers who are paid hourly by their contractor use timesheets.
  • There are other types of the invoice such as expense report, interim invoice, final invoice, past due invoice, recurring invoice and e-invoice.

An ideal invoice will have the following contents:

  • A header with your business name and logo
  • Invoice number or a unique identifier
  • Your business location and information
  • Invoice date
  • Description of goods or services sold and quantity
  • Additional charges, fees or taxes
  • Total amount due
  • Payment terms
  • Due dates

A tax invoice should have the following components:

  • Name, address, and GSTIN of the supplier or seller
  • Name, address, and GSTIN of the recipient or buyer, if it’s registered under GST
  • HSN code or SAC for goods and services
  • Invoice number, serially numbered and unique in every financial year
  • Type of invoices such as a tax invoice, supplementary invoice or revised invoice
  • Description of goods or services supplied
  • Units or quantity of goods and services
  • Tax rate for every item on the invoice
  • Amount of CGST, SGST, IGST or UTGST in separate columns
  • State of supply and place of supply
  • Total amount of goods and services supplied
  • Delivery address, in case it is not the same as the place of supply
  • If a reverse charge is applicable, then it must be duly mentioned
  • Digital signature of the supplier or any authorised person

Receipts

A receipt is a written acknowledgment that something of value has been transferred from one party to another. In addition to the receipts consumers typically receive from vendors and service providers, receipts are also issued in business-to-business dealings as well as stock market transactions. For example, the holder of a futures contract is generally given a delivery instrument, which acts as a receipt in that it can be exchanged for the underlying asset when the futures contract expires.

Types:

  • Gross receipts such as cash register tapes, deposit information (cash and credit sales), receipt books, invoices, forms 1099-MISC
  • Receipts from purchases and raw materials (These should show the amount paid and confirm that they were necessary business purchases; documents could include cancelled checks or other documents that identify the payee, amount, and proof of payment/electronic fund transfers.)
  • Cash register tape receipts
  • Credit card receipts and statements
  • Invoices
  • Petty cash slips for small cash payments

Invoice Voucher

A voucher is a bond of the redeemable transaction type which is worth a certain monetary value and which may be spent only for specific reasons or on specific goods. Examples include housing, travel, and food vouchers. The term voucher is also a synonym for receipt and is often used to refer to receipts used as evidence of, for example, the declaration that a service has been performed or that an expenditure has been made. Voucher is a tourist guide for using services with a guarantee of payment by the agency.

The term is also commonly used for school vouchers, which are somewhat different.

Account voucher

A voucher is an accounting document representing an internal intent to make a payment to an external entity, such as a vendor or service provider. A voucher is produced usually after receiving a vendor invoice, after the invoice is successfully matched to a purchase order. A voucher will contain detailed information regarding the payee, the monetary amount of the payment, a description of the transaction, and more. In accounts payable systems, a process called a “payment run” is executed to generate payments corresponding to the unpaid vouchers. These payments can then be released or held at the discretion of an account’s payable supervisor or the company controller.

The term can also be used with reference to accounts receivable, where it is also a document representing intent to make an adjustment to an account, and for the general ledger where there is need to adjust the accounts within that ledger; in that case it is referred to as a journal voucher.

Any documentary evidence supporting the entries recorded in the books of accounts, establishing the arithmetic accuracy of the transaction, may also be referred to as a voucher for example, a bill, invoice, receipt, salary and wages sheet, memorandum of association, counterfoil of paying-in slip, counterfoil of cheque book, or trust deed.

Vouchers are used in the tourism sector primarily as proof of a named customer’s right to take a service at a specific time and place. Service providers collect them to return to the tour operator or travel agent that has sent that customer, to prove they have given the service. So, the life of a voucher is as below:

  • Customer receives vouchers from tour operator or travel agent for the services purchased.
  • Customer goes to vacation site and forwards the voucher to related provider and asks for the service to be provided.
  • Provider sends collected vouchers to the agent or operator that sends customers from time to time, and asks for payment for those services.
  • Uncollected vouchers do not deserve payment.

This approach is most suitable for free individual tourist activities where pre-allocation for services is not necessary, feasible or applicable. It was customary before the information era when communication was limited and expensive, but now has been given quite a different role by B2C applications. When a reservation is made through the internet, customers are often provided a voucher through email or a web site that can be printed. Providers customarily require this voucher be presented prior to providing the service.

Data validation in excel

Select the cells you want to create a rule for.

Select Data >Data Validation

On the Settings tab, under Allow, select an option:

  • Whole Number: To restrict the cell to accept only whole numbers.
  • Decimal: To restrict the cell to accept only decimal numbers.
  • List: To pick data from the drop-down list.
  • Date: To restrict the cell to accept only date.
  • Time: To restrict the cell to accept only time.
  • Text Length: To restrict the length of the text.
  • Custom: For custom formula.

Under Data, select a condition.

Set the other required values based on what you chose for Allow and Data.

Select the Input Message tab and customize a message users will see when entering data.

Select the Show input message when cell is selected checkbox to display the message when the user selects or hovers over the selected cell(s).

Select the Error Alert tab to customize the error message and to choose a Style.

Select OK.

Now, if the user tries to enter a value that is not valid, an Error Alert appears with your customized message.

Text Functions: LEN, TRIM, PROPER, UPPER, LOWER, CONCATENATE

Function Description
ASC function Changes full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters
BAHTTEXT function Converts a number to text, using the ß (baht) currency format
CHAR function Returns the character specified by the code number
CLEAN function Removes all nonprintable characters from text
CODE function Returns a numeric code for the first character in a text string
CONCAT function Combines the text from multiple ranges and/or strings, but it doesn’t provide the delimiter or IgnoreEmpty arguments.
CONCATENATE function Joins several text items into one text item
DBCS function Changes half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters
DOLLAR function Converts a number to text, using the $ (dollar) currency format
EXACT function Checks to see if two text values are identical
FIND, FINDB functions Finds one text value within another (case-sensitive)
FIXED function Formats a number as text with a fixed number of decimals
LEFT, LEFTB functions Returns the leftmost characters from a text value
LEN, LENB functions Returns the number of characters in a text string
LOWER function Converts text to lowercase
MID, MIDB functions Returns a specific number of characters from a text string starting at the position you specify
NUMBERVALUE function Converts text to number in a locale-independent manner
PHONETIC function Extracts the phonetic (furigana) characters from a text string
PROPER function Capitalizes the first letter in each word of a text value
REPLACE, REPLACEB functions Replaces characters within text
REPT function Repeats text a given number of times
RIGHT, RIGHTB functions Returns the rightmost characters from a text value
SEARCH, SEARCHB functions Finds one text value within another (not case-sensitive)
SUBSTITUTE function Substitutes new text for old text in a text string
T function Converts its arguments to text
TEXT function Formats a number and converts it to text
TEXTJOIN function Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
TRIM function Removes spaces from text
UNICHAR function Returns the Unicode character that is references by the given numeric value
UNICODE function Returns the number (code point) that corresponds to the first character of the text
UPPER function Converts text to uppercase
VALUE function Converts a text argument to a number

Perform calculations by using MIN and MAX function

Select a cell below or to the right of the numbers for which you want to find the smallest number.

On the Home tab, in the Editing group, click the arrow next to AutoSum Button image, click Min (calculates the smallest) or Max (calculates the largest), and then press ENTER.

If the cells are not in a contiguous row or column

To do this task, use the MIN, MAX, SMALL, or LARGE functions.

Example

Copy the following data to a blank worksheet.

1 A
  Data
2 10
3 7
4 9
5 27
6 0
7 4
  Formula Description (Result)
  =MIN(A2:A7) Smallest number in the range (0)
  =MAX(A2:A7) Largest number in the range (27)
  =SMALL(A2:A7, 2) Second smallest number in the range (4)
  =LARGE(A2:A7,3) Third largest number in the range (9)

Perform calculations by using the AVERAGE function

Returns the average (arithmetic mean) of the arguments. For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.

Syntax

AVERAGE(number1, [number2], …)

The AVERAGE function syntax has the following arguments:

Number1    Required. The first number, cell reference, or range for which you want the average.

Number2, …    Optional. Additional numbers, cell references or ranges for which you want the average, up to a maximum of 255.

  • Arguments can either be numbers or names, ranges, or cell references that contain numbers.
  • Logical values and text representations of numbers that you type directly into the list of arguments are not counted.
  • If a range or cell reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
  • Arguments that are error values or text that cannot be translated into numbers cause errors.
  • If you want to include logical values and text representations of numbers in a reference as part of the calculation, use the AVERAGEA function.
  • If you want to calculate the average of only the values that meet certain criteria, use the AVERAGEIF function or the AVERAGEIFS function.
  • Average, which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.
  • Median, which is the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median. For example, the median of 2, 3, 3, 5, 7, and 10 is 4.
  • Mode, which is the most frequently occurring number in a group of numbers. For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.

To locate the Show a zero in cells that have a zero-value check box:

On the File tab, click Options, and then, in the Advanced category, look under Display options for this worksheet.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Data
10 15 32
7
9
27
2
Formula Description Result
=AVERAGE(A2:A6) Average of the numbers in cells A2 through A6. 11
=AVERAGE(A2:A6, 5) Average of the numbers in cells A2 through A6 and the number 5. 10
=AVERAGE(A2:C2) Average of the numbers in cells A2 through C2. 19

Perform calculations by using the COUNT function

Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.

In its simplest form, COUNTIF says:

=COUNTIF(Where do you want to look?, What do you want to look for?)

For example:

=COUNTIF(A2:A5,”London”)

=COUNTIF(A2:A5,A4)

Examples

To use these examples in Excel, copy the data in the table below, and paste it in cell A1 of a new worksheet.

Data Data
apples 32
oranges 54
peaches 75
apples 86
Formula Description
=COUNTIF(A2:A5,”apples”) Counts the number of cells with apples in cells A2 through A5. The result is 2.
=COUNTIF(A2:A5,A4) Counts the number of cells with peaches (the value in A4) in cells A2 through A5. The result is 1.
=COUNTIF(A2:A5,A2)+COUNTIF(A2:A5,A3) Counts the number of apples (the value in A2), and oranges (the value in A3) in cells A2 through A5. The result is 3. This formula uses COUNTIF twice to specify multiple criteria, one criteria per expression. You could also use the COUNTIFS function.
=COUNTIF(B2:B5,”>55″) Counts the number of cells with a value greater than 55 in cells B2 through B5. The result is 2.

Perform calculations by using the SUM function

If you need to sum a column or row of numbers, let Excel do the math for you. Select a cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter, and you’re done.

When you click AutoSum, Excel automatically enters a formula (that uses the SUM function) to sum the numbers.

Here’s an example. To add the January numbers in this Entertainment budget, select cell B7, the cell immediately below the column of numbers. Then click AutoSum. A formula appears in cell B7, and Excel highlights the cells you’re totalling.

Press Enter to display the result (95.94) in cell B7. You can also see the formula in the formula bar at the top of the Excel window.

Perform logical operations by using the AVERAGEIF function

AVERAGEIF(range, criteria, [average_range])

The AVERAGEIF function syntax has the following arguments:

  • Range Required. One or more cells to average, including numbers or names, arrays, or references that contain numbers.
  • Criteria Required. The criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as 32, “32”, “>32”, “apples”, or B4.
  • Average_range Optional. The actual set of cells to average. If omitted, range is used.

Remarks

  • Cells in range that contain TRUE or FALSE are ignored.
  • If a cell in average_range is an empty cell, AVERAGEIF ignores it.
  • If range is a blank or text value, AVERAGEIF returns the #DIV0! error value.
  • If a cell in criteria is empty, AVERAGEIF treats it as a 0 value.
  • If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value.
  • You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
  • Average_range does not have to be the same size and shape as range. The actual cells that are averaged are determined by using the top, left cell in average_range as the beginning cell, and then including cells that correspond in size and shape to range. For example:
If range is And average_range is Then the actual cells evaluated are
A1:A5 B1:B5 B1:B5
A1:A5 B1:B3 B1:B5
A1:B4 C1:D4 C1:D4
A1:B4 C1:C2 C1:D4

Average: Which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.

Median: Which is the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median. For example, the median of 2, 3, 3, 5, 7, and 10 is 4.

Mode: Which is the most frequently occurring number in a group of numbers. For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.

Example

Region Profits (Thousands)
East 45678
West 23789
North -4789
South (New Office) 0
MidWest 9678
Formula Description Result
=AVERAGEIF(A2:A6,”=*West”,B2:B6) Average of all profits for the West and MidWest regions. 16733.5
=AVERAGEIF(A2:A6,”<>*(New Office)”,B2:B6) Average of all profits for all regions excluding new offices. 18589

Perform logical operations by using the IF function

The IF function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect.

So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

For example, =IF(C2=” Yes”,1,2) says IF(C2 = Yes, then return a 1, otherwise return a 2).

Common problems

Problem What went wrong
0 (zero) in cell There was no argument for either value_if_true or value_if_False arguments. To see the right value returned, add argument text to the two arguments, or add TRUE or FALSE to the argument.
#NAME? in cell This usually means that the formula is misspelled.

Perform logical operations by using the SUMIF function

SUMIF(range, criteria, [sum_range])

The SUMIF function syntax has the following arguments:

range   Required. The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. The selected range may contain dates in standard Excel format (examples below).

Criteria Required. The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. Wildcard characters can be included – a question mark (?) to match any single character, an asterisk (*) to match any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.

For example, criteria can be expressed as 32, “>32”, B5, “3?”, “apple*”, “*~?”, or TODAY().

sum_range   Optional. The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).

Sum_range should be the same size and shape as range. If it isn’t, performance may suffer, and the formula will sum a range of cells that starts with the first cell in sum_range but has the same dimensions as range. For example:

range sum_range Actual summed cells
A1:A5 B1:B5 B1:B5
A1:A5 B1:K5 B1:B5

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Category Food Sales
Vegetables Tomatoes $2,300
Vegetables Celery $5,500
Fruits Oranges $800
Butter $400
Vegetables Carrots $4,200
Fruits Apples $1,200
Formula Description Result
=SUMIF(A2:A7,”Fruits”,C2:C7) Sum of the sales of all foods in the “Fruits” category. $2,000
=SUMIF(A2:A7,”Vegetables”,C2:C7) Sum of the sales of all foods in the “Vegetables” category. $12,000
=SUMIF(B2:B7,”*es”,C2:C7) Sum of the sales of all foods that end in “es” (Tomatoes, Oranges, and Apples). $4,300
=SUMIF(A2:A7,””,C2:C7) Sum of the sales of all foods that do not have a category specified. $400

error: Content is protected !!