If you are a veteran numbers cruncher, you remember the old calculator and stubby-pencil method when it came to keeping books. You may have since discovered the power and convenience of accounting software, but you may have noticed that having your own Excel spreadsheet files still helps. Check out the following accounting tips to put Excel accounting functions to work for you.
Getting Even More Creative
Accounting programs do what they do based solely on structured user input. The reports you get are what those applications are programmed to do, and they do it well. Try to go the ad-hoc or what-if route for more insight and planning, and you just might be on your own.
The road map to the additional insight you need is through creative spreadsheet analysis and accounting in Excel can help. Often times, you can export a table of data into the rows and columns of Excel and look at it in new ways.
Excel Has Many Financial Functions
Excel is programmed with 53 financial functions, from ACCRINTto YIELDMAT.For example, ACCRINT returns an accrued interest amount for an investment paying periodic interest; YIELDMATfinds the annual yield of a security that pays interest at maturity. In between the latter two are a wide array of financial functions that the experienced accountant will quickly recognize.
Syntax and Arguments Complicate Matters
The trick – and the problem for non-experts – is that any function that you program into an Excel spreadsheet cell has to have proper syntax – i.e., order of presentation. Average users are familiar with the basic functions, like SUM,with its simple syntax of =sum(A1:A5), for example. When using the more complicated functions like VLOOKUP, you have to add certain arguments – ranges of tables, cells and TRUE and FALSEvalues.
Look Around for Excel Templates
Fortunately, for those not into wrestling with complicated spreadsheet programming, Excel provides a number of financial spreadsheet templates. The templates come pre-formatted and with some rather complicated functions already programmed.
Download those templates and adapt them for your own purposes, or reverse engineer them to learn the syntax and arguments for the embedded function statements. Three examples:
- A payroll calculator – This template has several lookup formulas and does some fancy paystub generation.
- An income statement – Look at the formulas in the grayed out cells on the right and see an example of an IF function.
- A profit and loss statement – Download this financial spreadsheet for an example of the IFERRORfunction as well as an embedded line graph.
On the other hand, if your choice between dealing with stubborn accounting software and figuring out syntax and arguments in Excel spreadsheet functions is “none of the above,” you might want to outsource all this. Give us a call; accounting services is what we do.