Excel templates with macros needed for KRA returns by corporate entities in Kenya

Kenya has been going digital in various government departments and Kenya Revenue Authority (KRA) has been at the fore-front in digitizing it's information management. Over the last 5 years KRA has implemented an on-line system for making various tax returns with the aim of eliminating paper based records for tax returns. The system that was introduced 5 years ago is ITMS that is now being replaced by iTax. The greatest value add in iTax system is that, the tax payer is able to view his/her ledger that shows ALL transactions done with KRA. This will make it easy for tax payers to discuss their tax matters with KRA without having conflicting details regarding the tax payers records at KRA.

The purpose of this blog is not to give you all the details regarding the iTax system, but is to highlight to Kenyan tax payers the need to enhance their Excel skills if they are to be able to use the system well. The iTax system does not allow users to work on-line when inputting details in their tax returns forms. iTax has been designed to only accept data that has been inputted off-line in a standard Excel template that has macros to generate the zipped upload file. To fill in some of the data into the Excel template, the user can import csv files into the Excel template so as to reduce time taken typing data into the template. The template does not allow users to copy or paste data within it, and therefore it has given options for users to import data from csv files.

This therefore means that corporate tax payers will have to design their own templates that link to their operational systems and extracts data for tax returns and then creates csv files to be used when importing data into the iTax template. This will mean that initially corporate organizations need to develop various templates with macros that ensure the process of loading data into the iTax templates is as efficient and error free as possible. Excel experts are going to be of great assistance in this process. Sunesis consulting is already working on templates with macros that can be used for this purpose and is ready to work with organizations to implement their templates.

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.

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.

Data imported from SAP converts dates to text

It is a common problem to find that when you import data from other data sources into Excel, the dates are converted to text. Most times this is data being retrieved for the monthly reporting cycle and there are very strict deadlines to produce the management reports. Most Business Analysts spend their nights in the office to address such mundane problems.

Date problems are best addressed using a combination of Excel Date and Text functions. Sometimes you may need to incorporate a Excel Boolean functions. An example is =IF(ISERROR(DATE(YEAR(D2),MONTH(D2),DAY(D2))),DATE(RIGHT(D2,4),LEFT(D2,2),MID(D2,4,2)),DATE(YEAR(D2),MONTH(D2),DAY(D2)))

Advanced Excel Training

Have you been using Excel for years and do not seem to have completely solved your reporting processes bottlenecks? Do you believe there is a better way to perform some tasks in Excel and yet you have not been able to discover? My expertise in Excel is now easily available in our Advanced Excel Training programs. Excel will never be the same again once you get to attend our training programs. Follow this blog as I give tips on some of the tasks you can do in Excel using more efficient methods.