Annual Report and Financial Statements

One headache that every finance team member has the period when they need to create the financial statements for the year. This is when any misposted or wrongly allocated funds come to haunt them. This is the period when the Auditors become the arch-enemies of the finance team. This is the period when the statement "not balancing" is synonymous with "you are dead". This is the period when having a sleeping bag in the office is a necessity for the finance team.

Some of the causes of the stress involved in the production of finance statements can be reduced or eliminated by ensuring data from the financial system to Excel (where the financial statements are prepared) is transfered with as little manipulation by staff as possible. One solution we have offered a number of organizations for this purpose is creating an Excel template that uses an import of the TB from the financial system to give the P&L, Balance sheet and Cash flow by using formulas linking these reports to the TB.

The value add for this template to the process is that any changes made during the audit process can quickly be included into the final accounts without having to lose "balance" in the balance sheet.

Have a great new year and an easy reporting process.

Advanced LOOKUP in Excel

INDEX and MATCH are two very powerful functions for performing advanced lookups in Excel. Using VLOOKUP, HLOOKUP or LOOKUP, you are restricted to performing lookups on either a column range or row range and NOT BOTH in the same formula. This can be quite restrictive especially when the value to be looked up is dependant on using both column range and row range simultaneously.

INDEX and MATCH can perform lookups where the other lookup functions either cannot work or can work by creating a very complex formula. For example if your data layout is in the form of a matrix (x cols by y rows) and you need to extract certain selected values based on their col position and row position, then the above two functions will perform this process in one straight forward formula.

Dashboard Tools

Any organization that has an MIS would most probably want to make it easy for the top management to get reports structured so as to show performance trends based on their KPIs. There are many tools that help produce such reports using various illustrations, the oldest and most broadly used being MS Excel. For organizations with large information systems producing 100,000+ transactions, using Excel as their core dashboard would be pushing it too far. "Dundas Dashboards" is a tool that makes it quite a breeze to meet the dashboard needs for such an organization.

Dundas is a server based dashboard tool that integrates well with most large databases (Oracle, SQL, MY SQL) and also allows for integration with smaller datasources like MS Access, Excel, Text files ect. This implies that with this tool you will not only have a dashboard system that links to your monstrous databases, but you will have the flexibility of integrating your transactional data from the large systems with other metrics that you have collected using the good old Excel spreadsheet.

For example to create a dashboard that shows trends in your sales and expenses and relating this to trends in macro economic parameters like GDP and inflation is not only possible but will always be upto date through connections to your data sources. Dundas also gives you lots of different illustration objects that make the dashboard not only look appealing but also easy to inteprete for top management team that is always short of time.

Check this tool out in their website - http://www.dundas.com/dashboard/

The dashboard tool for Excel lovers

Xcelsius is a very powerful tool for developing dashboards using an embeded Excel spreadsheet. You can also link it directly to other external data sources from databases or other spreadsheets.
This tool enables the user to export their dashboard to pdf, flash, powerpoint etc.

The feature that I believe makes this dashboard tool a great one is that the final product in flash uses the power of flash files quite well. That is the dashboard graphics are very high quality and also incorporates the movie style available in flash files. You can actually create a movie showing the trend movements that loops continually over a period you have specified.

This tool is a must have for dashboard ethusiasts that love Excel.

Two axis graph in Excel 2003 and 2007

Graphs are a very useful means of enabling interpretation of data. Sometimes you may need a graph that shows trends of Volumes and Market Share. The disparity of values in Volumes and Market Share makes it very difficult to have both trends based on one y-axis in a graph. This is usually solved by introducing another y-axis in the graph such that volume has its own axis with larger values while market share has another y-axis with smaller values. In Excel 2003 creating a two axis graph was restrictive in terms of the series to be placed on which axis, this was dependant on the arrangement of the data. In Excel 2007 their is great flexibility in how you decide what data series to put in which axis. This means that you do not have to worry about how you arrange the data series in order to place it in a particular axis.

Creating Performance Dashboards in Excel

I have met many managers having various Excel reports that they need to analyse periodically so as to make decisions on the way forward to improve performance. This can be quite a laborious process depending on how it is performed. Excel is a very powerful tool and is able to make this process more efficient. With the use of micro-graphs and conditional formatting, it is possible to develope a one pager that gives the manager a comprehensive view of the key performance indicators. This can easily be placed into powerpoint for a presentation.

Developing efficient management reports

Most management reports are produced using Excel either weekly or monthly. Raw data is imported from other business systems so as to be used in Excel to produce the reports. To make it easier to produce the reports each week or month, the Excel report needs to have a structure that allows easy and quick update of the reports.

Ensure that you designate one or more sheets to be the raw data sheets and have the management reports sheets linked to the raw data using formulas. This will then mean that every week or month, you only need to refresh the raw data sheet(s) and the management report sheet(s) will automatically be updated. The process of producing the management reports then becomes much simpler and faster during the report cycle period.