Create a Proforma Invoice in Excel for Product Management Seamlessly

Proforma invoice in excel for Product Management

proforma invoices are essential for communicating with customers preventing misunderstandings and securing payments if you are looking to make proforma invoices in Excel to manage your clients and save time then you've come to the right place hi there welcome to excel Demi where you can learn to use Excel and solve Excel VBA related problems I'm israq kader and in today's video I'll be discussing a step-by-step guide on how to create proforma invoice in Excel so let's get started [Music] for this tutorial I'll be using Microsoft Excel 365 before we begin let's quickly go through what is a proforma invoice a proforma invoice represents an initial bill for requesting payment from the customer before the items are supplied in general a proforma invoice contains shipping details and illustration of the products the total payable amounts and other necessary information regarding the transaction I'll begin by formatting the shipping section of the proforma invoice but first let me expand the ribbon to do this I'll right click on any of the tabs in the ribbon then uncheck collapse the ribbon option now our ribbon is visible I'll go to the insert tab then I'll click on the illustrations drop down I'll choose shapes this shows a list of all the shapes but first let me resize this window this seems about okay now if we scroll down we can see lots of shapes which we can choose in my case I'll go to the stars and Banners section and choose horizontal scroll click on it then hold the left Mouse button and draw the shape after that I'll right click on the shape and choose edit text I'll hold down the shift key and type proforma invoice after that I'll choose the text go back to the Home tab then I'll increase the font size to 16. I'll also change the alignment to Center and middle align I'll make the text bold we can drag the shape in position we can resize the shape and that's it our proforma invoice title is ready next I'll select from B5 to D5 range in the alignment section I'll click on margin Center to merge all the cells after that I'll click on the formula bar I'll hold down the shift key and type shipper details in all caps press enter select the shipper details text in the fonts section click on fill color drop down here you can choose any color ing to your liking I'll choose dark blue again in the font section click on font color drop down and choose a suitable color I'll choose white background one after that I'll increase the font size to 12 and make the text bold you can also press Ctrl B to make your text bold now select from C6 to D6 cell click on margin Center then apply the fill handle tool all the way up to row 11. after that select from B5 to d11 range in the fonts section click on borders drop down and select outside borders afterward I'll add cheaper details field starting from the B6 cell I'll type name then insert a colon in a similar way I'll type the other fields dress telephone email and lastly gstin I'll select all the fields and press Ctrl B to make them bold now I'll select from B5 to d11 range press Ctrl C to copy I'll go to the E5 cell and press Ctrl V to paste similarly I'll also paste in the H5 cell afterward I'll select the E5 cell I'll replace shipper wake on Zoomer press enter and lastly I'll replace shipper with shipping press enter I have changed the shipping detail fields and added some placeholder texts lastly I'll click on the insert tab then I'll click on illustrations drop down I'll select pictures and choose this device this will open up the file explorer here I'll choose the downloads folder then I'll select the picture click on insert this will insert our company's logo you can drag the logo in position you can also resize it and that's it we've added all our shipping information in Step 2 I'll format the product details section here I have already inserted the column headers in the B13 to j13 range and apply the same formatting as shown before now I'll go to the j14 cell press equal type the round function for the number argument I'll select h14 then insert multiplication operator which is the asterisk symbol then I'll choose i14 cell reference insert a comma for the number digits argument I'll enter 0 then close the parenthesis here we are multiplying the quantity and rate values in the h14 and i14 cells to get the price then we are asking the round function to return the price to zero decimal places or to the nearest Dollar I'll press enter then I'll apply the fill handle tool to copy the formula all the way up to j18 cell I'll select from i14 to j18 in the number section I'll click on this drop down and choose accounting now our rate and amount values are formatted properly following this I'll select from b14 to j14 range click the Home tab in the style section I'll click on Cell Styles drop down here you can choose various color and cell Styles in my case I'll choose 20 accent 1 we can apply this cell style to every other Row for better readability to do this I'll double click on the paint brush icon which is format painter next I'll select from B16 to j16 and drop the format painter similarly I'll select from B18 to j18 and drop the format painter to apply the same formatting if I press Escape we can exit the format painter lastly I'll apply the outside borders so I'll select each of the fields in the fonts section I'll click on borders drop down and choose outside border in a similar way I'll apply the outside border to rest of my table finally the formatting of our product details section is complete in step 3 I'll estimate the final bill but first we'll have to collapse the ribbon in order to make room so I'll right click on any of the tabs in the ribbon and choose collapse the ribbon option now we can see the entire window as you can see that I have already added subtotal amount free charges State goods and services tax which for this case I've considered 10 percent Central goods and services tax which I've also considered at 10 percent in addition we have the insurance legal fees and some other fees lastly I'll calculate the total amount of all the fees but first I'll have to calculate the sub total amount I'll go to the j20 cell press equal type the sum function for the number one argument I'll select from j14 to j18 range which contains all the prices close the parenthesis and press enter afterward I'll go to the j22 cell press equal then I'll type the round function for the number argument I'll select j20 cell which contains the subtotal amount I'll press f for once to lock in both the row and column references then I'll insert the multiplication operator which is the asterisk symbol and type 10 percent insert a comma for the number of digits argument I'll insert 0. to round off the answer to the nearest integer close the parenthesis and press enter then apply the fill handle tool to copy the formula into the j23 cell press equal type the sum function for the number one argument I'll choose from j20 all the way to j27 cell this includes all the charges close the parenthesis and press enter now I'll add the shippers and consumer signature section I'll go to the c31 cell in the formula bar I'll type shippers signature press enter then I'll select the c31 cell again click the Home tab in the fonts section I'll click on borders drop down here I'll choose top border after that I'll select the H and i31 ranges click the Home tab in the alignment section I'll click on margin Center then I'll add top border after that in the formula bar I'll type consumers signature press enter lastly I'll add the disclaimer to do this I'll click on the insert tab then I'll go to illustrations drop down and choose shapes I'll select rectangular shape then I'll draw the shape just below the product details we can see a shape format tab appear I'll go to the shape format tab here in the shape style section I'll click on shape field drop down and choose no fill again I'll click on shape outline drop down and choose no outline afterward I'll right click on the shape then I'll choose the edit text option here I'll paste my disclaimer since the default font color is white so the disclaimer text is now invisible press Ctrl a to select all the text then click on the Home tab in the font section click on font color drop down and select a suitable font color here I'll choose black after that I'll reduce the font size to 8. make any necessary adjustments to the rectangular shape and that's it our proforma invoice is now ready let's enter some dummy data into the invoice if we go to the sample invoice worksheet we can find a complete proforma invoice where a sample shipping information and product details have been added here the prices will be calculated based on the quantity and rate values all the prices are then added together to calculate the subtotal amount which is 941 US Dollars afterward we have to enter the free charges Insurance fees and other fees while the taxes will be calculated based on the subtotal amount lastly the subtotal amount and all other charges are added to calculate the total amount which is 1329 US dollars in this demonstration I have shown you a detailed guide to making a proforma invoice in Excel hopefully you can apply this knowledge to construct your own proforma invoice ing to your requirement and convenience don't forget to download the practice workbook from the video description try it out for yourself it's a great way to improve your Excel skills if this video helped you give it a thumbs up if you have have any queries suggestions or feedback leave a comment down below for more information you can also visit Excel demi.com also to see more helpful content like this please consider subscribing to our Channel hope to see you next time bye