How to Create Proforma Invoice for Advance Payment in Excel

Get FREE Advanced Excel Exercises with Solutions!

A Proforma invoice is used to request payments from the dedicated buyer for any items, goods, services and etc before supply. It includes details of the items or goods or products along with the amount. Advance payment is required when any vendor applies the conditions of advance payment while exporting and importing. In this article, I will explain the step-by-step procedure of how to create a proforma invoice for advance payment in Excel.


How to Create Proforma Invoice for Advance Payment in Excel: 5 Easy Steps

Step-1: Inserting Company Details

Before creating a proforma invoice it will be helpful if you create a sheet with your company details so that you can use the same proforma invoice for multiple companies.

Here, I inserted the company details.

Information of Proforma Invoice for Advance Payment in Excel

Then to make the details more accessible I will use the Data Validation tool.

Here, I will insert multiple company names.

  • First, select cell C5.
  • Then open the Data tab >> go to Data Tools >> then from Data Validation >> select Data Validation

Using Data Validation to Create Proforma Invoice for Advance Payment in Excel

A dialog box of Data Validation will appear.

  • First, select List from Allow.
  • Then, type the company names with commas. Here, I inserted ABC, XYZ, and CDK.
  • Finally, click on OK.

  • Now, you can select any name of your choice.

  • By following the same procedure, I created a list for Company Tagline.

  • Next, by following the same procedure, I created a list of Currency.

  • Again, by following the same procedure, I created a list for VAT/Sales Tax.

list o colors to use in proforma invoice for advance payment

Read More: Proforma Invoice Format in Excel with GST


Step-2: Information of Proforma Invoice

Now let’s create another sheet to insert all information required to create a proforma invoice for advance payment.

  • Insert all the details address of the vendor under Bill To.
  • Then, insert all the details under Ship To.
  • Next, keep the track of the Invoice Number, Customer ID, and Date.

Billing information of Proforma Invoice for Advance Payment in Excel

As it’s a proforma invoice it is required to keep Export & Import information. To keep that information I created two fields one is Shipment Information and another is Additional Information for Customs.

This section will hold all the details of the goods, items, or products along with the Description, Unit of Measurement, Unit Price, Quantity, VAT/Sales Tax, and Total Amount.

This part will contain the calculations of all types of charges and payments including Advance Payment.

Payments information of Proforma Invoice for Advance Payment in Excel


Step-3: Formatting Proforma Invoice for Advance Payment

Let’s format the Invoice to make it look better and customer friendly.

  • To begin with, you can use any Fill Color of your choice. Here, I used Blue color.
  • Then, I used Thick Outside Borders in all the Headings.

Formatting of Proforma Invoice for Advance Payment in Excel

Next, used some Fill colors in the Product Description rows.

Here, is the Final format of the proforma invoice for advance payment.

Formatted Proforma Invoice for Advance Payment in Excel

Read More: Tally Sales Invoice Format in Excel


Step-4: Using Conditional Formatting to Automate Colors

Now, I will automate the Fill Color by using Conditional Formatting and will link the details sheet with this sheet.

  • First, select any cell outside of the invoice.
  • Then, type the following formula and press ENTER.
=Details!$C$25

Where it shows Blue cause Blue is selected in the Details sheet Color.

Next, I’m going to use Conditional Formatting to change the color of the Headings based on another sheet.

  • First, open the Home tab >> from Conditional Formatting >> select New Rule

Using Conditional Formatting in Proforma Invoice for Advance Payment

A dialog box of Conditional Formatting will pop up.

  • From there select Use a formula to determine which cells to format.
  • Then, type the following formula.
=IF($P$9="Blue",TRUE,FALSE)

Here, the IF function will check whether the P9 cell contains Blue or not. If the cell contains Blue then it will Format the Fill Colors with Blue.

  • Next, click on Format.

Formula to apply fill color in proforma invoice for advance payment

A dialog box of Format Cells will appear.

  • From there select the color of your choice. Here, I used Bule based on my formula.
  • Then, click OK.

Now, open the Conditional Formatting Rules Manager.

  • Select Applies to.

Here, I selected all the cell ranges where I want to apply the Conditional Formatting.

  • Insert the following range.
=$B$7:$C$7,$E$7:$F$7,$B$14:$O$14,$B$20:$O$20,$B$28:$O$28,$B$35:$I$35

Where I selected those ranges where I want to apply the Conditional Formatting.

cell ranges to apply fill color in proforma invoice for advance payment

  • Finally, click on OK.

  • Next, I followed the formula of Conditional Formatting of Fill Color for the K9 cell also.
  • Then, it will format only the Font color of the Invoice of the K9 cell.

Here, is the Rule for all the colors I used in the Details sheet.

All Rules for all the colors of proforma invoice

  • Now, go back to your Details sheet.
  • From there select any color from the list. I selected Red.

Therefore, the Fill Color of the Invoice sheet is changed automatically.


Step-5: Using Formulas to Automate Advance Payment in Excel

To automate all the payments and options here, I will different Formulas and will link the details sheet with this sheet.

=TODAY()

Here, this function will insert the date automatically.

  • Then, press ENTER.

Using TODAY function for Proforma Invoice for Advance Payment in Excel

  • Next, as the Date of Expiry, I will use the following formula in cell L9.
=TODAY()+30

Here, I added 30 days with the TODAY function. If you want, you can change the days as per your need.

  • Next, press ENTER.

If you have multiple companies but you want to keep the invoice format the same for all then to Insert the name of your company, you can use the formula to refer company name from the Details sheet list.

  • First, select any place of your choice.
  • Then type the following formula and press ENTER.
=Details!$C$5

It will update the company name based on the Details sheet information.

  • Again, select the N28 cell.
  • Then type the following formula and press ENTER.
=Details!$C$20

It will update the type of VAT based on the Details sheet information.

Using sheet reference for Using TODAY function for Proforma Invoice for Advance Payment in Excel

Now, I will calculate the Total Amount.

  • Insert the following formula and press ENTER.
=L29*M29

Here, I multiplied Unit Price with the Quantity. Also, used the Fill Handle to AutoFill the formula in the rest of the cells.

As, it’s a proforma invoice it is very much possible you may have to deal with different types of currency from different countries.

For that, I listed different currencies in the Details sheet so that I can change Currency automatically.

  • Type the following formula and press ENTER.
=IF(ISBLANK($K35),"",Details!$C$19)

Here, I used the IF function whereas logical_test used the ISBLANK function to check whether the K35 cell is blank or not. If the condition is TRUE, then it will drag the currency symbol from the Details sheet.

  • Also, used the Fill Handle to AutoFill the formula in the rest of the cells.

Again, using the SUM function will calculate the Subtotal.

  • Insert the following formula in cell O35 then press ENTER.
=SUM(O29:O33)

Using SUM function in Proforma Invoice for Advance Payment in Excel

Type the following to calculate Subject to VAT.

=SUMIF(N29:N33,"v",O29:O33)

Here, I used the SUMIF function to sum the vat applied products where the criteria are v which denotes VAT is applicable.

  • To calculate total VAT, insert the following formula in cell O37 and press ENTER.
=O36*N37

Where I multiplied Subject to VAT and VAT Rate.

Now, I will calculate the Total bill.

  • Type the following formula in cell O41 and press ENTER.
=SUM(N35:O40)

Here, I summed up all the expenditures.

Then, I will calculate the Advance Payment Amount.

  • Type the following formula in cell O43 and press ENTER.
=O41*O42

Where I multiplied the Advance Payment % percentage with the Total.

Proforma Invoice Advance Payment in Excel

Finally, I will calculate the Due amount.

  • Type the following formula in cell O44 and press ENTER.
=O41-O43

By subtracting the Advance Payment Amount from the Total, you will get the Due amount.

Just to make the invoice more eye-catching you can hide any unwanted columns or rows.

Finally, I will hide the color column.

  • First, select the entire column P then right-click on that cell.
  • Then, from there select Hide.

Read More: How to Create a Tally VAT Invoice Format in Excel


Example of Using Proforma Invoice for Advance Payment in Excel

Here, I inserted values to the required fields to show you how it works. These fields are editable. You can insert field name cell values as per your choice.

Example of Proforma Invoice for Advance Payment in Excel

This is the proforma invoice for advance payment in Excel. Let’s change the color again this time I’ve chosen Green color.


Download Practice Workbook

Download the template from here.


Conclusion

In this article, you will get step-by-step ways along with formulas to create a proforma invoice for advance payment in Excel. Feel free to comment down below if you have any types of queries.


Related Articles

<< Go Back to Excel Invoice Templates | Accounting Templates | Excel Templates

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shamima Sultana
Shamima Sultana

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo