everydayExcel Times

Use MicrosoftÃ‚Â® Excel Differently to Gain a Productive Edge

Issue - Nov 2009

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!

To find out what else you could do with Microsoft Excel, check out our Excel
courses at
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.

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.