Microsoft Excel is a great choice if you need a price quotation template. But like anything there are a number…Read more
Microsoft Excel is a great choice if you need a price quotation template. But like anything there are a number of best practice methods so that the template you produce can be used for many years to come. At Bespoke Excel, we have substantial experience of creating Excel templates including those for price quotations.
Your starting point should be a clear plan of what your solution. If you don’t understand the process behind the quotation then it’s difficult to create a template to do it for you. Make sure you think about all the steps involved and consider how the data is likely to change. How many new items are added each year and what about deleting old ones?
We normally start with a list of the steps and the variables that we need to produce a quotation. We plan what happens when a quotation is calculated, whether the quotation is linked to the creation of a PDF or even an automatic email to the customer.
Once we have the information, different tabs are set up using a proper naming structure. Names are much easier to understand than sheet numbers. We add labels to ranges and use these rather than just cell information in formulas. If information could change – either be added to or deleted – we use tables. These are beneficial as they will expand and contract as needed and you can use structured referencing. Formulas in tables self replicate so all rows will have the same calculations.
At Bespoke Excel, we never hard code numbers into formulas where possible. A hard coded number is difficult to see. It’s much better to have a cell or range reference where the number is visible and can be changed if needed. We’ve found that this is always the best approach, even if a client says that a number will never change. A simple example is Value Added Tax. The general rate in the UK has been at 20% since 2011 and for 20 years before that it was 17.5% with the exception of a single year during that period when it was temporarily reduced to 15%. It may be tempting to hard code the rate of VAT but it is still a variable.
After we have completed the steps and the design, we work out the best way to calculate the formulas. We can use Excel lookup functions or macros and we can adapt the computer code behind them. Macros are especially useful if you want to generate a PDF or send a quote via email.
Prices are usually worked out by taking information from other cells or workbooks within the same spreadsheet or from linked spreadsheets. These can be maintained independently and in some cases can be shared.
While we are checking the data, we always consider how it can be validated and also whether Excel could change it automatically. Item codes are one example. If we have an item with a code of, say SEP14, that could be changed to a date by Excel.
Another issue that we consider is rounding. This can become a problem if you have a lot of small value items where you could start seeing differences creeping in. Again, the most obvious example relates to VAT – for most B2B transactions, the UK government allows you to round down to the penny below but this isn’t always reflected in accountancy programs. You should ensure your quotation system works in the same way as your accounting system.
Once we create the template, we ensure data validation is used wherever possible. Rather than free typing data, users are encouraged to choose from drop down lists.
The next stage is protection of the cells and ranges in the spreadsheet so that a user can’t change the data or calculations.
Testing is the final stage – both automated and manual.
The actual process of creating price quotation templates in Excel is not too difficult, but if it’s something you’re not doing on a regular basis, it may be a lot easier and quicker to consult an Excel professional.
Get in touch with Bespoke Excel today and we’ll give you a free no obligation quote