In Microsoft Excel, we can create the customized format with Excel Invoice Formula to use later as a template. It makes our daily work more convenient. In this article, we will demonstrate to you how we can create a transport bill format in excel. Using this kind of format, we can avoid repetitive kinds of work.Â
What is Excel Transport Bill Format?
A transport bill is a type of receipt. Generally, the transport service provider issues it. The bill is known as a transport invoice. The main purpose of this bill format is to request people to pay their transportation bills. We have an overview of this format in the following figure. This figure represents different parts of the bill format. We will discuss every part in detail in the following section of this article.
Main Details of Transport Bill Format in Excel
There are five basic details in a transport bill format in excel. These are:
- Company Name
- Customer’s Information
- Transport Invoice
- Description of Data
- SUBTOTAL Calculation
Types of Information Used in Transport Bill Format
Generally, a transport bill consists of information regarding the transportation company. From the format, we get an idea about the possible ways of transportation from one place to another. Also, this format gives a clear calculation of cost for different ways of transportation.
4 Steps of Creating Transport Bill Format in Excel
In this section we will see the formation of a transport bill format in the following 4 steps:
Step-1: Input the Details of a Company
- In the beginning, we will create the segment for Company Name. This section will consist of the address, Phone, Email, and Website of the company.
Step-2: Customer’s Details in the Transport Bill Format
In step two, we can see the Billing section. We have to input the details of the customer in this section. This section will contain the customer’s Name, Company, Address, Phone, Email, etc.
- We have to also notice in the Transport Invoice section. It’s very important to fill this in properly because it contains the information about the customer.
Step-3: Use Description of Transportations
In this step, we will add detailed transportation information. Like we can add different transportation ways in the TRAVEL DESCRIPTION section. We will add the values considering whether the journey is one way or two way, corresponding amount. By adding the columns ONE WAY/RETURN & ONE WAY AMOUNT we get the TOTAL transportation cost for a single method.
Step-4: Calculate the SUBTOTAL
Next, we will create the SUBTOTAL section like the following figure. This part contains Service Tax, Paid amount. We get the statement for the Total Due amount from this section.
Additional Segment
We will need to add two additional segments.
- The first one is the Comments section regarding the transportation process.
- Another one is the section to give signatures for suppliers and customers to keep the records for delivery or receiving any goods via transportation.
Demo Calculations of Creating Transport Bill Format in Excel
Now, we will see a demo calculation using the above format that we have used in this article so far. Let’s see how we can do this:
- Firstly, let us assume the values for the transportation details. We can see our assumed value in the below figure. You can use values in those columns at your convenience.
- Now, insert the following formula in the first cell of the TOTAL column for calculating the total transportation cost By Road.
=IFERROR([@[ONE WAY/RETURN]]*[@[ONE WAY AMOUNT]], "")- Now add the corresponding formula for the other two ways of transportation in the Total column.
- By doing this we get the total transportation cost in the SUBTOTAL section.
- We have to add Service Tax with the SUBTOTAL amount to get the actual transportation cost.
- Finally, we will subtract the paid amount from TOTAL to get the amount of Total Due.
🔎 How Does the Formula Work?
- [@[ONE WAY/RETURN]]: This part takes the value 1 under the column ONE WAY/RETURN.
- [@[ONE WAY AMOUNT]]: This part takes the value $550 under the column ONE WAY AMOUNT.
- IFERROR([@[ONE WAY/RETURN]]*[@[ONE WAY AMOUNT]], ” “): Returns the product of ONE WAY/RETURN and ONE WAY AMOUNT. It will return blank if any we any invalid value.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
In this article, we have tried to show you how to create a transport bill format in excel. Hopefully, the above examples will help you to understand the function of a transport bill format. Download the practice workbook added with this article and practice yourself. If you feel any confusion just leave a comment in the below box. We will try to answer as soon as possible.
