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.
