Simplify Your Financial Processes with Our Cash Receipt Template Excel for Facilities

Cash receipt template excel for Facilities

[Music] hi guys in this video I would like to show you how to create an automated payment voucher template in Excel from scratch payment voucher is a form of supporting document used to record payments made to supplier vendor or other creditor it is usually documented together with other documents such as invoices and supplier statement first we would need to create a list to record the details for each payment voucher the important columns we need are payment voucher number payment date recipients of the payment items for payment amount of payment method reference number of the payment which can be check number or transaction number we can also include the issuer of the payment voucher an approver of the payment voucher let's fill in some example of the details then we can convert the list into table for easier formatting now let's move forward to design our payment voucher template add the header of the voucher followed by the company name address and contact then based on the columns we have made in the record tab let's make the template for details to be filled into payment voucher [Music] thank you after designing the template we should link the template to the record tab firstly we need to select a cell as the cell reference for the linkage thus we make this cell as the space to populate the payment voucher number which allows the linkage of corresponding data from the record tab after that we can use flick up function to link data for example when we choose a payment voucher number we would like to have the item automatically populated at the cell thus we put this cell as lookup value that the table name as the table array since the first item is located at the fourth column we put 4 as the column index number then we put false as we would like to have the exact matching data when we choose certain payment voucher number then we can repeat the similar way for the remaining items however when the column is blank in the record tab we will see the item displayed as 0. to make it displayed as blank when corresponding column is not populated we can use combination of three functions which are if Len and vlookup functions if isn't function that allows you to specify a logical test and display results a certain value when the test is true or false on the other hand Len function Returns the number of characters in a text string in this case The Logical test is set true if the Len function returns zero we want the result to be displayed as an empty string when the test outcome is true also we want the value filled at the item column to be displayed if the test outcome is false the formula which gives us the desired effect let's apply it to other rows we can then apply the same concept to the other space in the payment voucher template so when the column in the record tab is blank it will be displayed as blank also in the payment voucher generated for instance we would like to have the sequence number displayed at the left column when the right column is filled we can modify the existing formula by replacing the last argument of the IF function with count a function the count a function counts the number of cells that are not empty in a Range in this case we should set the cell range of the five items as the range drag the formula for the remaining cells and change the respective column index number in the vlookup function now let's set up formula for the other space in this payment voucher template for some of the amount simply use the sum function for the relevant cell range link the amount of each item with the corresponding column in the record tab then do the same for other details like pay name payment method reference number and date of payment voucher if the date is not displayed correctly we can change to the correct format in the number section apply the same for remarks issue a name approver name and recipient we can add the spin button beside the payment voucher number the button can be found in the developer tab at the ribbon if you can't find your developer tab you can go to the options customized ribbon and take the option for developer link the spin button to the payment voucher number then we may choose the payment voucher to generate by just clicking the button this saves our time to populate the payment voucher number whenever we want to generate a certain voucher now the whole payment voucher template is almost done we can make some further adjustment to make sure the format of the voucher is as we want then we can print out a certain payment voucher by selecting the corresponding payment voucher number and click print that's all for this video thanks for watching