Office Accounting Simplifies Physical Inventory

By David H. Ringstrom, CPA

Physical inventory is one of the most onerous tasks assigned to accountants. We’d all much prefer to do something other than tally the number of beans in a bin. However, physical inventory is a necessary business practice, so in this article I’ll discuss ways that Microsoft Office Accounting makes process faster and easier.

 

Inventory Basics

Keep in mind that Office Accounting Express — a free download for anyone to use — only tracks service and non-stock items. Your clients that stock inventory items will need to either purchase a retail copy of Office Accounting Professional or follow the steps within OA Express to upgrade. OA Professional offers many powerful features not included in the free Express version, so encourage your clients to download the free 60-day trial version . While they’re at this site, they can also download free trials of every version and component of Office 2007.

Office Accounting groups inventory items in with other customer lists. You can access the item list several ways:

• Choose Customers, Customer Lists, and then Items, or
• Press Ctrl-Shift-U, or
• Click Items in the Find section of the Customer Home page.

Instant Add:
You can add items on the fly from the Quote, Sales Order, Invoice or Cash Receipts screen by choosing Add a New Item from the item list drop down.
You’ll want to review the item list before you start the inventory process. In particular I recommend that you can set the status of any discontinued items to Inactive. Doing so will eliminate noise from the Physical Inventory Worksheet. Follow these steps to make the process easier:

1. After you use one of the preceding steps to access the item list, ensure that the Current View, shown in the upper right-hand corner, is set to Active.
2. Click the On Hand field heading until the item list is sorted in ascending order. Doing so brings all of the items with a zero quantity on hand to the top of the list.
3. To make inactive, first double-click on the item. Then, clear the Active checkbox in the upper right-hand corner of the Item screen, as shown in Figure 1. Click Save and Close.
   
   

   Figure 1: Mark discontinued items as inactive.

 

Print Physical Inventory Worksheet

The next step is to print the Physical Inventory Worksheet:

1. Choose Reports, Inventory, and then Physical Item Worksheet. Alternatively you can choose Vendors, and then Physical Inventory Worksheet. The report is shown in Figure 2.
   
   

   Figure 2: The Physical Inventory Worksheet gives you a starting point for counting.

   
   
2. The report defaults to Active items only as of today, but you can easily display all or only inactive items for any date:
1. Click the Filter Options button.
2. Choose Active Status, and then select All, Active, or Inactive.
3. Choose As Of, and then choose a report date.
4. Click OK to apply your filter changes.
3. Office Accounting provides an all or nothing approach to displaying inventory items, so you either get all items, all active items, or all inactive items. However, with the report onscreen you can choose Actions, and then Export to Excel. Once the report is in Excel, you can use the Sort or Filter commands in Excel 2007 (known as AutoFilter in Excel 2003 and earlier) to arrange the list in the order that you wish. You may also want to erase the Quantity On Hand field, so that you ensure a true count for the items on hand, rather than at-a-glance “oh, that looks right” responses.

Limitations: Office Accounting doesn’t provide a field for storing location IDs for individual items. You can create a user-defined field to do so, but none of the reports currently provide access to this information on a mass basis. The Office Accounting Analysis Tools provide access to more fields than the built-in reports, but still no access to user-defined fields.

Adjust Inventory Quantities
Once you’ve completed your physical inventory, Office Accounting makes it easy to reconcile the count with your books:

1. Choose Vendors, Inventory Adjustment, and then Adjust Quantity.
2. As shown in Figure 3, the Adjust Inventory Quantity screen allows you to quickly enter corrected quantity amounts all at once. To do so, follow these steps:
1. Choose an adjustment account from the top of the screen.
2. Complete the New Qty. field for all items in question.
3. Enter a memo, such as “Adjusted amounts per physical inventory performed on 12/31/07”.
4. Click Save and Close to record the updated quantities.



Figure 3: You can adjust all incorrect quantities in one fell swoop.

 

Write Down Inventory

The previous steps allow you to adjust differences in quantity due to shrinkage or other issues. In other cases you may want to adjust the current value of the inventory due to damage or obsolescence. To do so, follow these steps:

1. Choose Vendors, Inventory Adjustment, and then Adjust Quantity and Value.
2. Choose an adjustment account from the top of the screen, as shown in Figure 4.

Figure 4: The Adjust Quantity and Value window enables you to write down items.



1. Complete the New Qty. and/or the New Value columns for the items in question.
2. Enter a memo such as “Record diminished value due to obsolescence”.
3. Click Save and Close to record the updated quantities and/or changes in value.

Alternate Access: The Item list window includes an Adjust Inventory button that provides access to the Adjust Quantity and Adjust Quantity and Value features.

 

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.