Why Modern Accountants And Bookkeepers Should Also Be Spreadsheet Experts

The efficient use of spreadsheets can be of immense benefit to both basic bookkeepers and high level accountants, as well as to all levels in-between. Given today’s bureaucratic requirements and the need for fast, accurate output from the accounting department, to meet the demands of top management, governmental regularity bodies and, in the case of listed companies, current and potential shareholders, a good to great knowledge of how Excel spreadsheets can be of assistance in meeting all requirements in a timely manner is an immense asset.
This General Knowledge Goes As Far As The Need For A Knowledge Of Macros, Especially In Two Main Areas Of the accounting process.

There are two main areas where a skill in the use of spreadsheets will prove to be beneficial to bookkeepers and accountants of all levels. These areas can be classed as “Pre General Ledger” and “Post General Ledger”. In both areas, a knowledge of V.B.A. (Visual Basic for Applications) will be very useful to automate repetitive tasks, although, even without macros, well designed spreadsheets with relevant formulas will simplify and speed up many tasks.

Lotus 123 macros, were much easier to create than V.B.A. macros, but now Excel seems to have become the industry standard, the creation of macros requires a knowledge of the more difficult V.B.A. However, it is recommended that, in an organization of any size, with an accounting department of (say) five people or more, at least one staff member, who need not necessarily be the department head, should be given the opportunity to gain the skill of writing macros using V.B.A.

1) Pre General Ledger

Some examples of tasks in this area of accounting are set out below.

  • Salaries and Wages
  • Spreadsheets can be a great substitute for specially written software for the calculation of wages and salaries, especially if the payroll is not particularly large, say less than 50 employees. The main reason why spreadsheets are better suited to smaller payrolls is that there is a problem with the accumulation of each employee’s individual history from one pay period to the next. However, if required, a skilled Visual Basics creator, could solve this problem without too much difficulty.

    Click Here If You Would Like To Download A Copy Of This Spreadsheet (.xls format)

    These two images represent the top few rows of a single payroll spreadsheet. I have only split it in two to allow it to fit into the space available, while maintaining its readability. The top row of the lower image would normally be to the right of the top row of the upper image. The full width of the spreadsheet would fit on to A4 paper in landscape format. Note the cells with the yellow backgrounds. I always use a pale color to indicate to users that these are the only cells into which they can enter data.

  • Cashbooks
  • Now that online-banking has become so popular, many banking sites give the user the option to download transaction lists in Excel readable formats. Quite often the format used is “.CSV” (Comma Separated Values) but Excel can easily convert this to the normal “.xls” or the more up-to-date “.xlsx”. In all probability quite a lot of formatting will be required to make the spreadsheet more readable, but a V.B.A. macro could be written to do this automatically. Very good spreadsheet designers can even automate the allocation of common types of expenditure into individual columns to reduce the amount of posting into the general ledger, and, indeed, some more sophisticated general ledger accounting programs can accept entries directly from spreadsheets.

    Click Here If You Would Like To Download A Copy Of This Spreadsheet (.xls format)

    An actual spreadsheet designed to be a cashbook would, of course, have more columns to the left of the “Code” column, for commonly used accounts. Also the “Miscellaneous” column would have another column to its right, to explain exactly the nature of the expenditure. I have only limited the columns so the example can fit into the available space. Note again the use of yellow shaded cells.

  • Fixed Asset Registers
  • Fixed Asset Registers are ideally suited to spreadsheets. The calculation of depreciation can be done with formulas in whatever style the organization’s accounting policies demand, and journal entry formulation can be automated.

    Click Here If You Would Like To Download A Copy Of This Spreadsheet (.xls format)

    I have introduced a new cell shading color here, blue. This represents cells that can be adjusted by pre-authorized personnel, who will therefore know the password to unlock locked cells. Also notice the “100% at the top of this column. By changing this percentage fixed asset registers can easily be accurately produced at any time during a financial year.

Conclusion To Pre General Ledger uses Of Spreadsheets

These three examples are only the tip of the iceberg when it comes to tasks that can be simplified and quickened up with the use of well-designed spreadsheets, whether they use V.B.A. macros or not. The pricing of goods, jobs, or services is another area where spreadsheet skills can come to the fore. But it’s not only pre general ledger tasks that can benefit from the use of spreadsheets. Read on for examples of tasks that can be simplified after whatever system is in use to produce the trial balance.

2) Post General Ledger

Many of the more sophisticated computerized general ledger packages include an integrated report generator. These vary in flexibility and ease of use. In many cases it is easier and more flexibility can be obtained by exporting a trial balance into a spreadsheet and then, either manually or using a V.B.A. macro, format reports using the spreadsheet software. Another benefit of using spreadsheets to generate reports is that it is easy to produce graphs to highlight important points.

Some reports that derive their figures from general ledgers and can be formatted using spreadsheet technology are:

  • All aspects of End of Financial Year Accounts
  • Periodic Management Accounts showing current period and year to date figures
  • Periodic Management Accounts showing what percentage each category of sales and expenses are of total sales
  • Periodic Management Accounts showing budgets and comparing actual figures to budgeted figures either as positive or negative amounts or percentages

So the list goes on, limited only by the imagination of the users and the needs of the organization.

Overall Conclusion

This article has only scratched the surface of why it is important that finance department staff should be good spreadsheet operators. For example it has not touched on the production of budgets, the production of cash flow forecasts and many other aspects that are important to any well-run enterprise. These topics are covered in detail in other articles within my blog, under the category “General Business And Financial Advice”.

[BannerCampaign id=3]

Return To Administrative Aids Menu

Copyright © 2015 to the present day, Phil Ramage of International Internet Icons

1 thought on “Why Modern Accountants And Bookkeepers Should Also Be Spreadsheet Experts”

Leave a Comment

Your email address will not be published. Required fields are marked *

CommentLuv badge
Scroll to Top