Accounting

Accountants use Microsoft Excel more than almost any other tool when they share and analyse financial information. In fact, many small businesses that cannot afford specialised accounting software use Excel to fulfil their accounting needs.

Excel may become a very versatile accounting tool and even if you come across a function you would like to use, and Excel does not have it, you could often combine other functions to obtain the function you want. You may create detailed models and use Excel’s mathematical functionality to perform complex calculations.

Even if you prefer to perform certain calculations on items that meet specific criteria only, Excel’s Array and Database functions or SUMPRODUCT function would allow you to do this. Since accountants have a legal responsibility to accurately report on financial matters, Excel incorporates validation procedures and error checking messages to demonstrate how the different models have been reconciled.

Charts are useful for demonstrating accounting models graphically, but they need to remain relatively simple to keep them useful and to enable you to read off values accurately. You may sometimes forget to update chart titles that incorporate period information if you regularly update a chart. Therefore, it is preferable to link your chart titles to the cell contents that can be updated automatically.

The OFFSET function in Excel allows you to select data for either a cumulative or any separate period. Financial Excel functions such as PMT, PPMT, IPMT and FV enable you to easily and quickly calculate outstanding principal amounts, interest and loan repayments. If you install the Analysis Toolpack, you could load special add-ins that contain many more functions.

It is always sound accounting practise to lock certain parts of your models that should not be altered. Excel provides security functionality to lock worksheets, parts of worksheet of even only certain cells in worksheet.