Sales Template
The initial sales template I had was a simple Excel worksheet called “Orders” which we use to capture the online orders from our benefits of honey website. In this template, I have date of order, product code, product description, unit of measure, quantity, revenue, cost of goods sold (COGS) and the profit generated from the order. Updating the details was prety straight forward and I usually do it in less than a minute for each order.
But as the no of orders and product
range increase, I found that the updating become too manual. I have to find the price for the product, the cost and other information by scanning up and down the list. Using the FIND function in Excel shorten the time slightly. Not wanting to be bogged down by these manual tasks, which also increase the risk of data entry errors, I decided to insert a new worksheet called “Selling Price” and entered the details for all the products into the worksheet. Then all I have to do is to link this worksheet with the orders worksheet using the VLOOKUP formula. The VLOOKUP formula will help me lookup the pricing details based on the product code I have entered into my orders worksheet. The 2 worksheets work well together until we have to change the selling prices for some of our products, as a result of promotion, price revision, etc. The problem I encountered with the updating is on over-writing the existing selling prices in the “Selling Price” worksheet with our new prices. If I overwrtie the prices, the prices of our past orders will be updated with the new selling price.
Most people would choose to convert all the formulas used in the past records into numbers and then update the prices in the “Selling Price” with the latest price. I find that it is too risky to do that because I have to constantly remind myself to remove the formulas while keeping a row or two so that I could copy the formula to the new orders. It is too risky for me. If I ever forget to convert the formulas to number, just one time, I would have to check through all the past records to quantify the damage due to one oversight that happened in the past. So I decided to find a formula that is intelligent enough to update the selling prices in the “Orders” worksheet ccording to the date of the order.
=INDEX(‘Selling Price’!$E$1:$E$17,LARGE(IF((‘Selling Price’!$B$2:$B$17=A2)*(‘Selling Price’!$D$2:$D$17<;=E2),ROW(‘Selling Price’!$D$2:$D$17)),1))*D2
In brief, this formula is able to pick the latest price of the related product and use it for the latest order. It will not affect the historical records because it will make sure that the selling price used is the latest before the order date but not newer than the order date. That takes care of the old prices. And all I have to do now is to make sure that I maintain the new prices of the products in the “Selling Price” worksheet and date stamp the effective date. In the orders worksheet, I wouldn’t have to remove any formula. All I have to do is to make sure that the order is stamped with the correct date and copy the formulas from one of the old orders to the new row. With this setup, I wouldn’t have to spend too much time on maintaining the prices any more.
Entering the precise description is an essential condition for the price to be picked up from the “Selling Price” worksheet. This might prove to be challenging to the user. To resolve this problem, we could create a drop down list so that users do not have to type in the product description, just choose from the drop down list. This will eliminate the possibility of the our selling price formula returning an error on the selling price of the product. This can be easily resolved by copying the entire product description list to an unused range and apply the “Remove Duplicate” function to the list. Then make the unique list as the source for data validation.
But I was very keen to explore the possibility of creating this unique list automatically so that all the user need to focus on is updating the prices. I googled and found that this can be done.
