The power of MS Excel

Good day reader,

I assume some of you are wondering why I choose MS Excel as the topic today or… what a boring topic to share. Don’t jump the gun yet. Wait until you finish reading this.

Most of working people knew what is Excel and how to use it. But don’t be surprised… most of us did not have a formal Excel training. We learnt through other people such as friends, family and colleagues. For those who are not familiar with Excel, feels free to read the explanation that I copied from About.com.

“Excel is an electronic spreadsheet program that can be used for storing, organizing and manipulating data. When you look at the Excel screen (refer to the example on this page) you see a rectangular table or grid of rows and columns. The horizontal rows are identified by numbers (1,2,3) and the vertical columns with letters of the alphabet (A,B,C). For columns beyond 26, columns are identified by two or more letters such as AA, AB, AC”.
 

 

 

Excel normally use for managing data (in numbers). based on the data that we have, we can transform the numbers into graphs, comparison analysis and reports. In short, that is the usual function of MS Excel.

My profession as an accountant requires me to use a lot of Excel spreadsheets for monthly reporting, budgeting and performance analysis etc. I stored a lots of Excel files in my PC or server and some of the files size are huge. For example for monthly reporting, I need store at least 12 files in a year (according to the month). Yeah! Just 12 files, what a big deal? The problem arises when I need to make comparison analysis between two periods. For example, this month result vs. budget, this month result vs. last month result, this month result vs. last year results so on and so forth.  Beside the data, I need to plot graphs, input some explanation and even worst when the report should comprise the company level and operational level.

 Handling data has constraints. I use to do sorting, filtering, sub-totaling, highlighting and files linkages. Usually the data get mixed up, the linkage is broken, formula result error and produced wrong output. Then I need to re-fix all the errors. No wonder people say “accountant loves numbers a lot”. What a mess…. I need to improve my way of doing things.

 

In June 2012, I attended a MS Excel workshop called i3 Business Analytics and Reporting – Intelligent Excel Models or in short i3Bar. The program was arranged by Malaysian Institute of Accountants (MIA), 4 days at Hotel Armada, Petaling Jaya at RM 4k+.

There are four (4) modules in this program and basically one module per day. Day 1 – Proficiency in core Excel functions, day 2 – Integrate multiple data source, day 3 – Build and present interactive reports and day 4 – Budget simulation and intelligence.

Excel has more than 300 built in formula functions. As a normal Excel user, we do not need to master all of it. In i3Bar we only focus on 9 formula functions. Among the functions are Vlookup, Hlookup, Index and Sumif. We use the ‘simple but powerful’ formulas.

This workshop has thought me to develop a dynamic and integrated report where with just a single click; the report will be changed and updated accordingly. For example, currently I am viewing the report for the month of April 2012.

On the right hand sight of column K is the scroll bar button. When I click on the upper scroll bar, the data will changed from the month of April 2012 to May 2012. All data in the table, pictorials and texts changed and updated accordingly. Can you spot the differences?
 

 

 
Display and hide information

Another plus mark is this report can be viewed in Summary or Details mode. Currently, the table is displaying the report in Summary mode. When I click on Detail (selection beside the scroll bar), the table will be expanded and displaying the detail information of the division’s revenue. You can see the details information in pinky color bar.

 

 

 No need Power Point

 

Normally for monthly presentation to our bosses, we use MS Power Point. Creating power point slides for the report is very time consuming. Whenever we make changes on the source data, we need to update the affected slides. Sometimes during the presentation, unexpectedly, the boss would like to see the previous two months report. For example, now you are presenting June 2012 report and suddenly the bosses wanted to see April 2012 report. Most probably we will reply like this “I don’t have it now and will email to you later” (with smiling) or your have to start searching on your notebook of the required report. It will take time and you know that bosses are always busy and they can’t wait any longer.

 

From the workshop I learnt on how to make a presentation straight from Excel. You do not need to copy and paste the data from spreadsheet to power point. Just with some formulas, the presentation is ready to shoot. Furthermore, you can immediately turn the report from current month to previous month or a month before in just a single click (depending on your available data). The picture below shows the Menu Page in the normal view of Excel. All the menus, columns and rows are displayed on the screen.

 

 

 

When I click on the presentation button, the Excel hides all unnecessary things on the screen and the Menu page is look like this. On the left hand side, there is the report selection button. When I click on Revenue Report, Excel will display the report as what you saw earlier.

 

 

 Cost Report

On the Cost Report, I build the similar concept but with different selection on the left hand side. Whenever I make selection on the row of the information will be highlighted and the graphs are updated accordingly.

 

Report shows the information of Admin Cost of the selected month.

 

When I make selection for Direct Cost, the row is highlighted and the graphs are changed accordingly. The button Back is for return to the Menu page.

 

With the dynamic and integrated report I only need to keep one file of the twelve months data and surprisingly the file size is less than 1MB. Every month end after the final report is downloaded from the Accounting Information System (AIS), I just need to cut the data from system report and paste on the respective column of this Excel sheet. For example the fifth month data is pasted on the fifth column (highlighted in green) and the report will be self updated. We do not need to do sorting, re-formatting, totaling etc. Finally, the report is ready for presentation within less than one hour. Amazing right?

 

I presume some of you might not believe on “the power of MS Excel”. You may retrieve the information from this link http://www.i3bar.com/ and watch videos of the similar work.

 Advantages

i3Bar is a super power concept on the common IT application. The concept is not limited to financial report but can be applied to other reporting as well. As a credit to i3Bar, I listed down some advantages that I received from it.

1.       Generate a dynamic, integrated and interactive report. I don’t have to create and store so many files in my PC and save storage size.

2.      The data is kept precise as I don’t need to reformat, sort or filter.

3.      The updating step is simple and short. What I need is to just copy and paste to the appropriate cells.

4.      Only need to master nine (9) simple formulas.

5.      No need to create the power point slides for presentation

6.      Easy to view the previous information or next information of the report

7.      Save time and less troublesome in preparing reports.

8.      Can be applied to any kind of repetitive report.

9.      Can include simple Macros for presentation purpose.

10.   Report can be in colors for pictorials and highlights.

Until today, I do apply the i3Bar concept on all my Excel files even for non-repetitive reports.  It helps me to speed up my way of doing things. Special thanks to my boss, trainers and MIA for the opportunity of learning the effective methods of presenting information.