everydayExcel Newsletter Header

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.

raw data format for order management system

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.

Report by order date

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!

To find out what else you could do with Microsoft Excel, check out our Excel courses at http://www.everydayexcel.com/excel-courses.php


Subscribe to our newsletter

If you received this newsletter from a friend and would like to be included in our mailing list, please go to our Excel Today's page.


Follow us at Facebook, LinkedIn

If you have a facebook account, you can like us at facebook or linkedln. From facebook or LinkedIn, you will be notified on mini cases and ideas how we use Excel on a day to day basis. These tips may not be published in our newsletter.


Yours Sincerely,

Jason Khoo
Chief Trainer
everydayExcel Business Lab Pte Ltd
Author of "Hidden Secrets of Data Analysis with Excel" and 
"Excel Secrets for Highly Effective Marketers"

 

Received this newsletter from a friend? You can request to be included in our mailing list by signing up at our Excel Today's page. If you have friends whom you think might be interested in this newsletter, feel free to send it to them.