everydayExcel Issue - 5 Nov 09

Restoring Order in Customer Order Management with Excel

Many people have the impression that Microsoft Excel is just a spreadsheet used for capturing records and performing basic calculation. For them, Microsoft Excel is called a computer calculator. Unknown to them, Microsoft Excel can be used to perform some of the most complex functions which some software applications cannot even handle. One of them is to use it as a customer order management system. Surprised? Well, if you have attended our Excel course "Sales Performance Analytics with Excel", you will readily recognize this and agree with me that this is possible.

Take for example a shop that takes in orders for home-made Chinese New Year goodies. Their customers call or go into the shop one to two months before Chinese New Year to place their orders. Each day, there will be several customers ordering different type of cookies in different quantities. And customers who place their orders on the same day are likely to collect their Chinese New Year cookies on different days. If this is done using paper and pen, the shop will have manually tabulate the orders for each day and inform the chef how many containers of each type of cookie he has to make on a particular day. The number will increase each day and the tabulation will continue until they stop taking the orders. Even if they are using a Point-of-Sales (POS) system, they still have to consolidate the orders each day. Surely, there must be a more efficient way of doing thing!?

Yes, with Excel, you can easily capture each order as they come in. Record the Date, Customer Name, Contact no, Product, Quantity, Collection Date, Amount Paid, Balance and Others.

With all these details (also required in the paper and pen method) captured in Excel, you can easily create a Pivot Table to show the total orders for the day. Pivot Table allows you to conveniently include any details you have captured and summarizes them for you. Just by using the mouse to drag the name next to the order date, you will easily find the customers who have ordered for that day.

When you are done with this information, you can drag and replace the order date with the collection date. This format will show you the customers who will be collecting the Chinese New Cookies for a particular day.

Painless! No longer do you have to re-compile your records over and over again to get different sets of information. All the information you need is all within Microsoft Excel Pivot Table!