Finally the first issue of Not Just Numbers - the ezine for Finance Directors who know it's not just about the numbers, has been emailed out to the initial subscribers.
It contains articles on automating your management accounts preparation, funding growth through a recession and email security.
Sign up to receive your free copy at http://www.notjustnumbers.co.uk/.
Tuesday 9 September 2008
Advanced Management Reporting in Microsoft Excel
If you are based in the North East of England (or could get there on the 21st October), you may be interested in the workshop described below. I rarely do this but it is very poular when I do.
"Thanks so much for running [the course] for our company recently.
It has certainly increased my own productivity. I now have no need to rely upon the rather unfriendly reporting abilities of our [accounting and MRP package] as I can extract the data and manipulate it in Excel much more efficiently.
The others that attended the course are also using it almost daily and training their colleagues.
The few people who were versed in the supplementary reporting package purchased alongside our current accounting and MRP package have now virtually abandoned it in favour of the superior and simpler functionality offered by Excel. Had I attended the course prior to purchasing the software in 2001 I certainly would not have bought the reporting package or any of the associated and costly training days.”
Ian Batchelor, Finance Director, Analox Group Ltd
Advanced Management Reporting in Microsoft Excel®
One-Day Intensive Workshop
with Glen Feechan
21st October 2008
You know that you have all of the information you need in your accounting and management systems, however every day, week and month you (and your team) spend time pulling it from different sources, tweaking it and presenting it in a format that you (and the rest of the management team) can use.
What if you had the skills to automatically populate your existing spreadsheets and/or create new ones that give you all of the information you need, in a few clicks, when you want it.
Glen Feechan is a chartered accountant and expert in this field. This one-day workshop is a rare opportunity to learn his unique approach to automating all of your reports (from simple sales reports to complex management accounts). This is possible, no matter what accounting and management software you use in your business.
For just £275 plus VAT per delegate, you will learn how to:
To find out more, call 0845 6439693 or email excel@feechan.co.uk. Alternatively, please complete and return the BOOKING FORM.
Sunday 7 September 2008
Not Just Numbers e-zine coming soon
The first issue of the Not Just Numbers e-zine is now only days away, and should be launched within the next week.
Sign-up on this blog or at http://www.notjustnumbers.co.uk/ and also receive your free topical report, "10 Tips for dealing with hard times", while you wait.
Sign-up on this blog or at http://www.notjustnumbers.co.uk/ and also receive your free topical report, "10 Tips for dealing with hard times", while you wait.
Monday 1 September 2008
Do your management accounts take weeks, days, hours, minutes…or seconds to prepare?
Using only Microsoft Excel and pretty much any accounting package on the market, I have rarely found a company that could not be set up to complete their management accounts preparation (from trial balance) in seconds. However, almost every company I come across is taking hours, if not days or weeks - every month - to do this job. In addition, when the process is automated, very often they discover errors in the accounts prepared under their previous laborious method. Below I will give some simple tips to achieve this in your business. The rewards are huge, as the focus switches from grinding out the numbers to understanding and interpreting them, thereby adding real value to the business.
Key to achieving this is understanding that once you can produce a trial balance from your accounting package, the rest of the preparation can (and should) then be automated. You have all the numbers – you are doing the same things with them every month. This is what spreadsheets are for – automating the analysis and presentation of numbers.
The instructions below are intended to provide someone with a good working knowledge of Excel with enough information to complete the setup, alternatively please contact me if you would like to discuss getting some help (it rarely involves more than two or three days consultancy to set it all up from scratch).
Importing the trial balance
First of all you will need to pull the trial balance into Excel. Having done this once it can be refreshed (with one click) every month and/or every time you make a change to the data in the accounts package as a result of errors spotted when you see the information in its final format. Every modern accounts package I am aware of has odbc links into the data. Excel can import data from the tables in the accounts package into Excel using Microsoft Query (this comes on the Excel disk and you may already have it installed – if not, you will be prompted to put the disk in when you try to “Import External Data”.
Cross-reference table
Once you have the trial balance in Excel you need to populate your management accounts with this data. I know many companies that have imported the trial balance and then tried to automate this step directly and got into difficulties when there are changes to the trial balance, such as new or deleted accounts codes. The key to doing this is to introduce a step in between that is a lookup table between your nominal ledger codes and the final management accounts. Give each line in your final management accounts layout a code and then copy your nominal ledger codes (from the imported trial balance) onto a separate worksheet in the same spreadsheet. You then need to put the management accounts code that you want the nominal ledger code to appear in next to each nominal ledger code in this list. You can then use a look-up formula to pull the correct management accounts code alongside each row in the trial balance.
Analysing the data
Once this step is complete, you can use a Pivot Table to analyse the trial balance by management accounts code rather than by nominal ledger code. This Pivot Table will contain all of the numbers that will go in your final accounts. If there are any new nominal ledger codes, there will be a line in the pivot table with a management accounts code of #NA, as the nominal ledger code will not be found in the lookup table and therefore the lookup formula will have returned this error for the lines containing the new codes. Simply double-click on the total of #NA to get a list of the new codes. These can then be pasted to the bottom of the cross-reference table and given a management accounts code. One simple click to refresh the Pivot Table and these codes are distributed into their rightful homes.
Presentation
If you already use Excel to present your management accounts then you can copy the existing layout into this spreadsheet (on a separate sheet). You will need to insert a column to the left of the management accounts to enter the management accounts code for each line. You can then use the GETPIVOTDATA formula to look up the financial value for each code and pull it into the accounts. Typically you can copy the same formula for each line. I also tend to insert a second column to the left of the accounts which I use to enter a 1 or –1 against each code. The result can then be multiplied by this column to allow you to use the same formula even when you want to reverse the sign (for sales and cost of sales for example).
Two clicks
Every month you can now complete the management accounts (from trial balance) in two clicks – one to refresh the data from the accounts package, one to refresh the pivot table. This also allows you to view the final layout very early in the process so that you can use this to review for accruals and prepayments, etc., make your changes and then refresh again.
I hope this has given you some ideas on how to free you and your team from crunching numbers to enable you to provide valuable financial insights to the rest of the board.
If you wish to discuss this further, please give me a call on 0845 6439693 or email glen@feechan.co.uk.
If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.
Key to achieving this is understanding that once you can produce a trial balance from your accounting package, the rest of the preparation can (and should) then be automated. You have all the numbers – you are doing the same things with them every month. This is what spreadsheets are for – automating the analysis and presentation of numbers.
The instructions below are intended to provide someone with a good working knowledge of Excel with enough information to complete the setup, alternatively please contact me if you would like to discuss getting some help (it rarely involves more than two or three days consultancy to set it all up from scratch).
Importing the trial balance
First of all you will need to pull the trial balance into Excel. Having done this once it can be refreshed (with one click) every month and/or every time you make a change to the data in the accounts package as a result of errors spotted when you see the information in its final format. Every modern accounts package I am aware of has odbc links into the data. Excel can import data from the tables in the accounts package into Excel using Microsoft Query (this comes on the Excel disk and you may already have it installed – if not, you will be prompted to put the disk in when you try to “Import External Data”.
Cross-reference table
Once you have the trial balance in Excel you need to populate your management accounts with this data. I know many companies that have imported the trial balance and then tried to automate this step directly and got into difficulties when there are changes to the trial balance, such as new or deleted accounts codes. The key to doing this is to introduce a step in between that is a lookup table between your nominal ledger codes and the final management accounts. Give each line in your final management accounts layout a code and then copy your nominal ledger codes (from the imported trial balance) onto a separate worksheet in the same spreadsheet. You then need to put the management accounts code that you want the nominal ledger code to appear in next to each nominal ledger code in this list. You can then use a look-up formula to pull the correct management accounts code alongside each row in the trial balance.
Analysing the data
Once this step is complete, you can use a Pivot Table to analyse the trial balance by management accounts code rather than by nominal ledger code. This Pivot Table will contain all of the numbers that will go in your final accounts. If there are any new nominal ledger codes, there will be a line in the pivot table with a management accounts code of #NA, as the nominal ledger code will not be found in the lookup table and therefore the lookup formula will have returned this error for the lines containing the new codes. Simply double-click on the total of #NA to get a list of the new codes. These can then be pasted to the bottom of the cross-reference table and given a management accounts code. One simple click to refresh the Pivot Table and these codes are distributed into their rightful homes.
Presentation
If you already use Excel to present your management accounts then you can copy the existing layout into this spreadsheet (on a separate sheet). You will need to insert a column to the left of the management accounts to enter the management accounts code for each line. You can then use the GETPIVOTDATA formula to look up the financial value for each code and pull it into the accounts. Typically you can copy the same formula for each line. I also tend to insert a second column to the left of the accounts which I use to enter a 1 or –1 against each code. The result can then be multiplied by this column to allow you to use the same formula even when you want to reverse the sign (for sales and cost of sales for example).
Two clicks
Every month you can now complete the management accounts (from trial balance) in two clicks – one to refresh the data from the accounts package, one to refresh the pivot table. This also allows you to view the final layout very early in the process so that you can use this to review for accruals and prepayments, etc., make your changes and then refresh again.
I hope this has given you some ideas on how to free you and your team from crunching numbers to enable you to provide valuable financial insights to the rest of the board.
If you wish to discuss this further, please give me a call on 0845 6439693 or email glen@feechan.co.uk.
If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.
Subscribe to:
Posts (Atom)