Accounting Magic – What You Need to Know About Accounting Excel Formulas

Accounting is more than just debits and credits. It is the calculation and analysis of those debits and credits that generate accounting information an organization can act upon. Let’s take a look at two types of reports a professional in the Accounting Department might be asked to generate. Then, we’ll see how that information can be used to produce ratios which can be used to determine how a company is doing compared to others of its kind, if their data is known.

Tip: If you compete with organizations that are publicly held, you can find their financial filings on http://www.sec.gov/edgar.shtml

Balance Sheet

Depending upon the industry and make-up of the organization, the balance sheet might contain different line items. However, by and large, the top part of the balance sheet contains the assets of the organization, followed by the liabilities. The difference between these is known as Owner’s or Stockholder’s Equity. There can be several subsections, for example, Fixed Assets, Current Assets, Goodwill and Intangibles. We would add up all the items classified as current assets, then all the items classified as fixed assets to arrive at total assets.

We do the same for our current liabilities and long-term liabilities. Current liabilities would be things like payroll and income taxes. Long-term liabilities would include long-term debt. We would add up all the items under current liabilities, then long term liabilities, then the total of those.

If you report several years together, you can utilize Spark Lines to show growth or decline over the reporting periods. You can also create additional columns to calculate the percentage growth or decline for more impact. If you are generating a report template, you may have no figures entered yet. In that case, your percentage formulas may show #DIV/0 errors. Of course, you know they’re not really errors, there’s just no data yet. You can use the IFERROR function in front of the percentage calculation to show a blank or zero until accurate numbers are entered. In the sample workbook for this article on the Balance Sheet worksheet, we’ve applied all these techniques to Assets.

Income and Expense Statement

Another useful report is the Income Statement, also called the Income and Expense Statement, and Revenue Statement. Unlike the Balance Sheet, which is a snapshot in time about how we’re doing. An Income statement reports a specific period of time to show how we earned (or lost) what we earned (or lost). Simply stated: How much did it cost us to earn the revenue we made? Like the balance sheet, the composition of the income statement can vary depending upon industry or complexity of the operation. In our example, we’re using easy line items, such as:

• Revenue
• Cost of Goods Sold (COGS)
• Gross Profit
• Selling and General Administrative Expense
• Depreciation and Amortization
• Operating Income
• Interest Expense
• Other Expenses
• Income Before Taxes
• Income Taxes
• Net Income

Income statements may also include additional shareholder impact information. The calculations performed are:

• Revenue – COGS = Gross Profit
• Gross Profit - Selling and General Administrative Expense = Operating Income
• Operating Income - Other Expenses = Income Before Taxes
• Income Before Taxes – Income Taxes = Net Income

As you can see in a worksheet with these calculations, at first glance, it may not be easy to tell where the formulas are. Use the Show Formulas tool (Ctrl+`). To see where all the formulas are before beginning to edit to make sure you don’t delete any formulas.

Ratios

All of the items above when used in various combination and calculations yield rations that allow us to compare ourselves to others in our industry and determine how we’re doing in comparison. It is important to research and determine what is “normal” or “good” for our particular industry. It wouldn’t do any good to compare the ratios of the corner meat market with that of IBM!

By using named ranges to name each relevant line on the Balance Sheet and Income statement, we can just type in these terms and the relevant math operators to get the ratios. If you highlight both the name of the entry and the entry, use the Create from Selection button on the Formula tab in the Defined Names group to create the named ranges.