Get Your Free Estimate Template Word for Management Today

Free estimate template word for Management

Does your business need to send out quotations ? and does this seem to take ages to do !  Well if that's the case, then why not  create your own quotation system template   using an excel spreadsheet - so that  you can automate the entire process !  Hello, and welcome to another  Mr. Spreadsheet video.  In this short presentation we  will create a Quotation System   using an Excel Spreadsheet that includes a  Customer and a Products or Services database.  The completed template is easy  to use and can be either printed   or saved as a pdf for you to  email to your potential customers.  The finished template uses many Excel  functions, commands and techniques:  We will insert graphics and text boxes, We name ranges,  We create a nested formula combining the  CONCATENATE and the XLOOKUP functions,  and we format the final  template for printing an email.  All of these techniques are  explained in the presentation.  Now, if you would like to get a  copy of the completed template   then all we ask is that you SUSCRIBE to our  channel and give this video a GIG Thumbs Up. OK, let's get started. In a new workbook make sure that you   have three worksheets and then name them: Quotation, Customers and Products.  Navigate to the Quotation worksheet  and from the Page Layout Ribbon   deselect the View Gridlines box. Now, from the Insert Ribbon select   the Illustrations Too,l and then the Icons Tool  and from there I have selected the abacus icon.  Now click on the Insert Button the  Icon now appears on your worksheet.  With the icon still selected,  pick up the Graphics Fill Tool   and choose light green from the color palette. From the Graphics Outline Tool select dark grey.  Now resize the icon and place it roughly  in the same position as shown on screen.  Using this routine, you may wish to select an  icon that is more suited to your business needs.  Uncheck the icon and from the Insert Ribbon select  the TEXT option, and then the WORD ART option.  Now choose the orange with bold outline style,   and from the Text Fill palette choose light green,  and from the Outline palette select dark green.  Enter in your business name and then resize the  text and then roughly place the text box to the   position shown on screen. De-select this text box,   and then create a new text box, but, this  time choose the Ordinary Text Box style.  Drag out a shape and enter in your business  strap line or any other appropriate narrative.  Make this green and place it directly  below the business name as shown.  Now, create a third Text Box  and enter in the word Quotation.  Make the text black and increase the font size  and position it roughly as shown on screen. Highlight both of these two text  boxes by clicking on each one of them   whilst holding down the Shift key.  Now, right click your mouse, and from the  sidebar that opens select the Format Shape tool.  From the Format Shape dialog box that opens look  for the Line Option, and then select No Line.  This eliminates the borders from our text boxes. OK, that's the graphics and the text boxes sorted,   we can now move on to create the other  elements in our quotation document. Highlight the range N8:N9 and from the Home  Ribbon choose the Merge and Center tool,   enter in the number of 1035 make  this bold and increase the font size. Highlight the range D10:N10 and from the Borders  Tool on the Home Ribbon, select the Bottom Border.  In cells D11:N11 enter in the quotation  headings of Code, Product, Qty, Price and Total.  Format the text to a mid green and make bold. That's great our Quotation header is done. Let's quickly create the Quotation footer.  Select the range D26:N26 and from the Borders  Tool, select this time, the Top Border option.  Merge and Center the range D27:N27 and enter  in the business contact details as shown. We can now create our two databases.  One for our Customer details, and  the other for our Product details. Firstly, on the Products worksheet,   copy in the data as shown, and please use  the same coordinates starting in cell B4. You can pause the video whilst  you complete this process. Now let's move to the Customers worksheet. In the range C3:J3   copy in the customers table headings as shown. and then, copy in your first line of data.  Highlight the range C3:J25, and from the  Insert Ribbon pick up the Insert Table tool.  Make sure that the 'My Table has Headers box'  is checked and then choose a table color style   to your liking, and finally copy in  the three customer records as shown. OK, that's great. We can now return to the   Quotation worksheet and create the formulae  to help us automate the quotation process.  In cell D12 enter in the value W101.  We want both the Product description and the Price  to be retrieved directly from the Products Table.  We can use the XLOOKUP command to do this. The XLOOKUP command will call on the Stock Code,   the Product description and the Price  columns in the products database.  So let's quickly give a  NAME to these three ranges. On the Products worksheet, highlight the  range B4:D23 and from the Formulas Ribbon   select the 'Create from Selection' option. Make sure that the Top Line option is the ONLY   box checked, and then click OK to save and close. Navigate back to the Quotation worksheet,   and with cell E12 selected, enter in the formula =XLOOKUP(D12,StockCode,Product)  E12 should populate correctly with  a description of Widgets Grey. Now skip a few columns across to cell L12 and  enter in a quantity of 5, and, in M12 we can enter   in our second XLOOKUP command, which becomes, =XLOOKUP(D12,StockCode,Price)  This should return product W101's price of 17.56.  and finally, on the first row let's  enter in the line total in cell N12. The formula is simply L12 x M12  to give a line value of 87.80. Now with the range D12:N12 highlighted, copy  and drag down the formulas through to cell N22.  You can check that all the values have been  correctly retrieved and calculated correctly.  Now AutoSum the Total Value column, in cell N24 increase the font size   and make this bold, and in cell K24 enter the narrative 'Total   Quote Value' and then copy in my cell formatting. Move to cell D24 and enter in the formula  =TODAY() This will retrieve the current date in cell E24.  Enter in a suitable narrative  containing the sender's details. format d24 and e24 in a matching green  text make bold and resize as necessary   and finally we need to enter the contact  details and salutations for the quotes recipient Go to cell C9, and enter in the Code A101. A101 is the code for our customer ABC Ltd  whose details are in the Customer's Database.  Now, move across to cell D9. We are going to use a combination   of the XLOOKUP command and the CONCATENATE  command to create an 'automated salutation'. Firstly, as before, let's NAME the  ranges that our XLOOKUP command will use. Select the Customer's worksheet  and highlight the range C4:C25. Now type in the word Code in the name box  just the left of the Insert Function tool. Do the same with the range D4:D25,  and in the Name Box call this 'Name'.  With E4:E25 highlighted, give  this a Name of 'Contact'. Return to the Quotation worksheet  and in cell D9 type in the formula: =CONCATENATE("To: ",XLOOKUP(C9,Code,Contact),"  at ",XLOOKUP(C9,Code,Name)) This formula joins together the Salutation  of 'To:' and then picks up your Contacts name   of Joe Cooper from the Customers database, then  adds the word 'at' and then finally retrieves   the customer's Business Name  again from the Customers Database. Now select cell C9 and change the font color to  white, such that the contents are not visible. This NESTED FORMULA is reasonably complex,   and some users may simply want to just  type in a 'Salutation' of their own. Let me know in the Comments  Section below which you prefer. Let's put a Border around the whole document,   highlight the range C2:O28 and from the  Borders tool, select Outside Borders. Play around with the formatting  and positioning of the graphics   to ensure that the completed  template looks presentable. Finally let's set the document's  printing co-ordinates.   Select File from the Menu Bar at the top  of your screen, and then choose Print.  In the Print Dialog box that opens  click on the Page Setup link.  From this check the 'Change the  Scaling' option to fit to 'One Page',   or indeed, choose a scaling and  a page layout to your liking. You can now send your output directly to  the printer or save as a pdf for emailing. If you do choose to email then please make sure  that you save the pdf with a unique reference. and that completes our work, we now have an  easy to use template with which we can send   professional looking Quotations to our customers. We do hope that you enjoyed watching this video   and that there was lots of content that  you found both useful and informative. If you would like a copy of this spreadsheet  template then please do SUBSCRIBE to our Channel   and give this video a BIG Thumbs Up.  You can also visit us on one of our channels on: Facebook,  Instagram, or Twitter. Now, if you are a Small Business,   and, you want to keep your Accounting Records  using a spreadsheet, then why not watch our ' How to keep your Accounts in Excel video' and, if you would like to get a  COPY of this Quotation Spreadsheet,   then please watch our 'Free Mr. Spreadsheet Templates' video. Thank you once again for watching.