Issue - 17 Mar 2009
Hilary has a Microsoft Access file that is used to download data from the company's SAP system. She has to export the raw data into MS Excel and use the payment dates to classify the records in MM/YY format, e.g 03/09 if the payment date is in Mar 2009. She'll need to sort the payment dates in ascending order, identify the records for each month, and manually type in MM/YY for each month of records. Imagine how much time she would have to spend if the records are required for 1 year, 2 years, 3 years or more!
At the same time, Hilary is required to classify each payment to its respective financial quarter. Her company's financial year starts from 1 Apr and therefore, she has to classify those records from Apr to Jun as Quarter 1, July to Sep as Quarter 2, Oct to Dec as Quarter 3 and Jan to Mar the following year as Quarter 4.
Next, she is expected to run through the records and classify them on a half monthly basis. For example, she would have to classify records from 1 to 15 Jan as "1 - 15 Jan" and those in the 2nd half of Jan as "16 - 31 Jan", taking care of whether each month ends with 28 (for Feb), 29 (for Feb in Leap years), 30 or 31 days.
Then, she'll have to identify the currency type of each payment and enter the appropriate exchange rate into each line and multiply the exchange rate by the payment amount to convert to local currency.
Finally, she is asked to split the raw data into four seasons: spring, summer, autumn, and winter, one season per worksheet.
The whole process described above has to be repeated 2 to 3 times a week during the peak season. Just estimate the amount of time you'll need every week to do this if you were Hilary!
What if I tell you that you can actually set up the entire process only once upfront and Excel will do it over and over again without any further intervention, in other words, maintenance-free. No macro is needed at all, in case you are thinking along that direction. All you need are: five formulas, one Excel function and a good understanding of the concepts we cover in our course.
How's that. How much time can you save by knowing the above? How much is the time saved worth to you?
If you want to experience the power of Miscrosoft Excel like Hilary, sign up for our Excel course now! If you are overseas and there is no way for you to come to our courses, you can consider attending our online Excel course.
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.
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.
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.