Use Excel to help with IRS audits

During the course of a tax audit, an Internal Revenue Service (IRS) agent may request additional data to assist in the examination of your tax records. Make your job easier by using Microsoft Office Excel 2003 to provide the requested information. The Excel spreadsheet program helps make number-crunching easy and critical numbers simple to find.

By using an Excel worksheet, you can respond to audit requests in a number of tax-related areas. Depreciation expenses is a great example. You probably already know how useful Excel worksheets can be to calculate annual depreciation expenses and accumulated depreciation by asset.

Read on to learn how a manufacturing company benefited from using Excel to communicate depreciation-related data and quickly respond to required changes to a tax return under audit.

Case study

During an audit of the 2007 tax return for Contoso, Ltd., an IRS auditor requests additional information supporting Contoso's tax depreciation expenses. Contoso uses an Excel worksheet to track tax depreciation expenses and accumulated depreciation for its assets by year.

Hide or print selected Excel data

Contoso's controller could print this entire worksheet and give the printout to the auditor. But the audit covers only the company's 2007 tax return. It is not only unnecessary but also ill-advised to provide more information to the IRS than the auditor requests, so the controller does not want to provide depreciation data from any years that are not under review.

Here are the steps that the controller follows to select and print only the 2007 portion of the company's depreciation worksheet to deliver to the auditor:

  1. Select the Excel worksheet containing the company depreciation data.

Excel spreadsheet containing Contoso tax depreciation data

  1. Select the columns that contain depreciation data from the years not under audit. In this example, the columns that are selected contain data from 2005, 2006, and 2008.
  2. On the Format menu, click Column and then click Hide.
  3. To print only the 2007 depreciation data, select the area in the worksheet that includes:
    • Rows in the 2007 column (include both the Depr Exp and Accum Depr columns) through the Total row.
    • Columns for the asset name, acquisition date, acquisition cost, depreciation method, and useful life in years for the year under audit.
  4. On the File menu, click Print Area and then click Set Print Area.
  5. On the File menu, click Print to produce a hard copy of the depreciation expenses for the year 2007.Excel spreadsheet showing depreciation detail for 2007, the year under audit.
  6. To display the hidden columns on the depreciation worksheet, select the entire worksheet. On the Format menu, click Column and then click Unhide.

Track adjustments to the depreciation worksheet

During the auditor's examination of the company's tax return, the auditor discovers some errors in the company's 2007 tax depreciation schedule. These errors are included in the auditor's final report.

The Contoso controller could revise only the incorrect values on his depreciation worksheet. But he realizes that it may be important to track the depreciation detail as originally filed, as well as the revised detail after the audit, in case there are subsequent audits for the years before or after 2007.

Here's how the controller tracked these changes on the company's depreciation worksheet.

Add columns to track adjustments

The controller adds new columns to the 2007 tax depreciation schedule to record the audit corrections by following these steps:

  1. Select the Excel worksheet containing the depreciation data.
  2. Place the insertion point in the first column for the year 2008 (the year after the audited year). On the Insert menu, click Columns to add a new column to the left of the 2008 column. Repeat this step to create a second new column.
  3. Select the following column headings: 2007 (audited year), Depr Exp, and Accum Depr. Copy these headings to the left column of the two columns created in step 2.
  4. Modify the headings in the new columns as follows:
    • Change 2007 to 2007 Audit.
    • Change Depr Exp to Depr Exp Adj (Depreciation Expense Adjustment)
    • Change Accum Depr to Accum Depr after Adj (Accumulated Depreciation after Adjustments)

Excel spreadsheet showing depreciation schedule with new columns added to record audit corrections,

  1. To copy the Excel formula from the 2007 Accum Depr column to the 2007 Audit Accum Depr after Adj column, copy all cells between the 2007 Accum Depr column heading and the Total row. Select the row beneath the 2007 Audit Accum Depr after Adj column heading, and paste the contents of copied cells.

Audit adjustment 1: Add a new asset

The first error that the auditor discovers is a worksheet entry for a computer that was purchased on June 1, 2007. This computer should have been depreciated, but it was inadvertently recorded as office supplies. The controller adds the new asset to his Excel worksheet by following these steps:

  1. To add a new row to the bottom of the worksheet, place the insertion point in the last row of the worksheet. On the Insert menu, click Rows.
  2. Enter the following data in the new row:
    • Asset name: Computer #4
    • Acquisition date: 06/01/07
    • Acquisition cost: $1,100
    • Depreciation method: 150% DDB
    • Useful life (years): 5
  3. Enter 0 in the 2007 Depr Exp column, because no depreciation was claimed for this computer on the 2007 tax return.
  4. In the 2007 Audit Depr Exp Adj column, type 350, which is the 2007 depreciation expense for this computer from the auditor's report.
  5. To calculate the accumulated depreciation after audit adjustments for Computer #4, copy the cell in the Accum Depr after Adj column for another asset. Select the cell in the Accum Depr after Adj column for Computer #4, and paste the contents of the copied cell.

Excel spreadsheet showing new asset added to depreciation schedule.

Audit adjustment 2: Remove an asset

The second error that the auditor discovers is a worksheet entry for a 2007 inventory purchase recorded inadvertently as office equipment and depreciated. The depreciation reported is not allowable.

The controller removes this asset from his depreciation worksheet by following these steps:

  1. Select the 2007 Audit Depr Exp Adj cell for the asset named Office Equipment.
  2. Type -4500, which is the depreciation expense that was improperly added in the 2007 tax depreciation schedule. The 2007 Audit Accum Depr after Adj column calculates automatically.

Excel spreadsheet showing Office Equipment depreciation expense removed from depreciation schedule.

Audit adjustment 3: Change existing asset

The third error that the auditor discovers is a worksheet entry for a copier that was treated as seven-year property but should have been treated as five-year property. Contoso claimed $2,143 of depreciation for this copier in 2007. The auditor's report states that the company should have claimed $3,000. The controller must make an $857 positive adjustment to the 2007 tax depreciation schedule.

  1. To preserve the amount claimed on the 2007 return, select the 2007 Depr Exp cell for the copier and enter the revised depreciation value. In this example, the controller enters $2,143.
  2. Select the 2007 Accum Depr cell for the copier, and enter the revised value. In this example, the controller enters $2,143.
  3. Select the 2007 Audit Depr Exp Adj cell for the copier. Enter 857, which is the depreciation adjustment from the auditor's report. The Accum Depr after Adj column calculates automatically.

Excel spreadsheet showing revised depreciation expense for copier

Adjusting future depreciation after an audit

After the audit report is finalized and the tax results are updated for 2007, the controller must turn his attention to the books for 2008, which are still open.

As a result of the addition of the 2007 audit adjustment columns, the controller changes the 2008 depreciation columns on his Excel worksheet by following these steps:

  1. To adjust the Excel formulas for the 2008 Accum Depr column, copy the cells starting from below the 2007 Accum Depr column heading to above the Total row.
  2. Select the cell immediately below the 2008 Accum Depr column heading, and paste the contents of the copied cells.
  3. To calculate 2008 depreciation for the new computer (Computer #4), copy the 2008 Depr Exp cell for another asset that uses 150% DDB as its depreciation method (for instance, a different computer). Select the 2008 Depr Exp cell for Computer #4 (the new asset), and paste the contents of the copied cell.
  4. To remove 2008 depreciation for the Office Equipment asset, select the 2008 Depr Exp cell for Office Equipment, and type 0. Select the 2008 Accum Depr cell for Office Equipment, and type 0.
  5. To adjust the life of the copier for future depreciation calculations, select the Useful Life (Years) cell for Copier, and type 5.

Excel spreadsheet showing depreciation adjustments make to 2008 as a result of audit revisions.

Audit completed with minimal effort

A tax audit can be a time-consuming project for a controller. The IRS auditor may request additional details for numerous items on the company's tax return, including depreciation expense. However, an organization that uses an Excel worksheet can quickly and easily provide an auditor with the requested details, without the organization having to create new documents.

Adjusting to depreciation expense errors found during an audit can be a daunting task. But the controller at Contoso used Excel to track the company's tax depreciation. He preserved a trail that included the original depreciation claimed and the audit adjustments. He also used Excel to easily adjust the calculations for future years' depreciation to reflect the audit changes.

 
 
Applies to:
Excel 2003