99.1% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
Microsoft Excel Features and Functions for Accountants
Wed 4th October 2006
Basic arithmetic functions such as SUM() and border formatting will enable you to produce useful models and perform some quite complex calculations. By expanding your knowledge of just some of the many other functions, Excel can become a much more versatile tool.
There are many financial functions available for carrying out interest and investment calculations which can greatly simplify the long formulae normally required for e.g. loan repayment. Care needs to be taken to check that the functions work in the same way as your own textbook formula but, once you have tested the functions in a variety of situations and confirmed the results, they can greatly simplify tasks such as determining the net present value.
If you wish to only perform calculations on items within your data that meet specific criteria, the Database and Array functions are very useful. The SUMPRODUCT() function is also a very good alternative. It is able to extract all sorts of values from a table of data and can be used as an alternative to a considerable number of functions.
Many Excel users are not familiar with Pivot Tables, which is one of the most powerful features of Excel. A pivot table is a great reporting tool that will sort and sum data independent of the original data layout in the spreadsheet. By dragging and dropping column headings around you can change the way the data is viewed. For example, take a list of rows each containing an employee name, week number, expense item description and expense amount. Using pivot tables, the data can be easily transformed into a table summing the total for each employee week by week or the total for each expense item by employee. The options are almost endless and can moved between at the touch of a button.
Auditing and Checking Features
Accountants have a professional responsibility to ensure they are presenting accurate data. To remove the chance of critical errors they should ensure that spreadsheets have been well designed and rigorously checked. Use of the IF() and TEXT() functions help by allowing error checking messages to be incorporated and demonstrate that the models have been reconciled and are performing properly.
Other features such as the validation tool, to check data falls within specified ranges and the audit toolbar, which will highlight the cells which combine to produce an error are also powerful tools in ensuring that data is presented accurately.
Protection of Data
It is always helpful to make your models user-friendly. Protecting or restricting the values that can be placed in cells can help to prevent unforeseen errors. Options are available in Excel to allow you to protect all or just selected cells within a worksheet. This allows you to restrict the cells where data can be input and prevent your formulas being accidentally overwritten or amended. Using drop down boxes is another good option available to limit the values that can be entered by users.
Whatever you are using Excel for, it is important that you attain the knowledge required to enable you to complete your everyday tasks. Excel software training can give you this knowledge and equip you with the skills you need to work more accurately and efficiently.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
Imperial College London
Bianca Bailey Wilson
Excel Forecasting and Data Analysis
This course could be combined with the advanced charts for a two day course - may help to consolidate your knowledge
CIT Real Estate LLP
Overall very happy with the teacher and course!
Royal Academy Of Dance
Examinations Resources Manager
Very helpful and enjoyable course - thank you!