How to Create Proforma Invoice for Advance Payment in Excel

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.

Download Practice Workbook

Download the template from here.

Steps to Create Proforma Invoice for Advance Payment in Excel

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.

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

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.

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

Read More: How to Calculate Monthly Payment with APR in Excel

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.

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.

Read More: How to Create Payment Advice Format in Excel (with Easy Steps)

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.

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

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

Read More: How to Calculate Monthly Payment in Excel (2 Handy Ways)

Similar Readings

• How to Calculate a Lease Payment in Excel (4 Easy Ways)
• Calculate Monthly Mortgage Payment in Excel (2 Ways)
• How to Calculate Balloon Payment in Excel (2 Easy Methods)
• Calculate Loan Payment in Excel (4 Suitable Examples)
• How to Calculate Monthly Payment on a Loan in Excel (2 Ways)

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.

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

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.

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.

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.

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

• 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.

• 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.

Read More: Create Progressive Payment Calculator in Excel (with Easy Steps)

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.

• To insert the invoice Date, I will use the TODAY function will in cell L8.

Here, this function will insert the date automatically.

• Then, press ENTER.

• Next, as the Date of Expiry, I will use the following formula in cell L9.

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.

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.

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

Now, I will calculate the Total Amount.

• Insert the following formula and press ENTER.

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.

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.

Type the following to calculate Subject to VAT.

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.

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.

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.

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

Finally, I will calculate the Due amount.

• Type the following formula in cell O44 and press ENTER.

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 Make a Cash Payment Voucher 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.

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

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

• How to Create Rent Payment in Excel Spreadsheet
• Make Petty Cash Payment Voucher Format in Excel
• How to Create Interim Payment Certificate Format in Excel
• Create Annual Loan Payment Calculator in Excel (3 Ways)
• How to Calculate Car Payment in Excel (with Easy Steps)
• Calculate Auto Loan Payment in Excel (with Easy Steps)
• How to Calculate Coupon Payment in Excel (4 Suitable Examples)