everydayExcel Newsletter - 17 Mar 2009

How Much Would It Cost You If You Don't Know This?

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.

Upcoming Course Details

Date: 27 Mar & 3 Apr 2009 (Mon & Tue)
Time: 9.00 am to 5.00 pm
Venue: 10 Anson Road #26-02A International Plaza S(079903)
>>Click here for Excel Course Details

Date: 20, 21, 27 & 28 Apr 2009 (Mon & Tue)
Time: 9.00 am to 5.00 pm
Venue: 10 Anson Road #26-02A International Plaza S(079903)
>>Click here for Excel Budgeting Course Details

On the Lighter Side

"How much water should you drink a day?"

We often find ourselves getting into discussions on how much of this food and that food we should eat, what is moderate and what is excessive, and of course including this all-too-common question - "How much water should we drink every day?"