Tips for Creating Basic Accounting Spreadsheets
April 17, 2012
•General
• 0 Comments
Professional accountants use professional tools—fully loaded accounting software packages complete with their own 800-page user’s manuals and quarter-long headaches.
But if you own a small business or just want to set up a simple accounting spreadsheet, you’ll want something a little simpler. There are plenty of easy-to-use spreadsheet systems that will help you keep track of your personal finances (programs like Microsoft Excel give you essential tools at a reasonable price).
These programs are generally easy to learn once you get a hang of a few spreadsheet basics. Read the three tips below on how to create effective financial spreadsheets.
Micro and Macro Organization
Spreadsheets make your job easier by using calculations to automatically update balances. They do this by linking to cells within a spreadsheet and plugging that cell’s data into the calculation. This seems simple enough when you’re working with one or two calculations, but when you have pages of data, things get a little tricky. It gets harder to keep track of everything, and one little mistake could affect dozens of calculations, throwing off all of your totals.
Organization becomes key in spreadsheeting. Not only do you want to think about how to order cells within one spreadsheet but also how to manage pages within one document or within several documents that work together.
Before you create your spreadsheet, get your system going. Think about how you want to categorize data and what terms you’ll use for category headers. Stick with set terms for every page you create (i.e., using “balance” v. “total,” or “cost” v. “expense”). Title all of your documents using a similar pattern (i.e., Date.TypeOfDocument.DocumentName).
Getting yourself into this habit of organization will help you create one style for your spreadsheets. The headers and the titles become invisible; the data stands out. You’ll be able to analyze the numbers faster and catch errors more easily.
Functions to Know
Accounting degree programs sometimes devote entire classes to teaching software functions, but you will only have to memorize a few for smaller accounting purposes:
- Sum. Adds cells in a range. Type “=SUM(A1:A10)” to add up cells A1 through A10.
- Average. Calculates the average in a cell range. Type “=AVERAGE(A1:A10)”.
- Rate. Gives an interest rate per period for investments and loans. Find the function in the Function Library on Excel to enter the data.
- FV. Returns the future value of an investment. Find this function in the Excel’s Function Library.
Formatting Considerations
Finally, formatting your spreadsheet will make your document easier to read and understand. Learn how to draw and erase borders around cells when printing your spreadsheet. Choose one style of number formatting (e.g., General, Accounting, Currency, Percentage, etc.) and use it throughout. Use only one font style.
These tips introduce you to the basics of spreadsheeting, but there’s still a lot more information out there. If analyzing numbers has always been a skill of yours, consider getting an accounting degree. Find a credible school in your area and enroll in an accounting degree program today.
This article is presented by Briarcliffe College. Contact us today if you’re interested in developing marketable knowledge and career-relevant skills with an industry-current degree program from Briarcliffe College.