Inventory Valuation by Branch

If you're interested in extracting data from HD on your on a great place to begin is a workbook to display your inventory valuation by Branch.  If you need help with creating an ODBC connection or selecting files you can go to the early April posts using the links below.

ODBC Setup For HD Files

Extracting Data from the Production Database

Select the Branch Item Master (IVPMSBR) and the Item Master (IVPMSTR) files from HD1100PD.  Join the files by the Item Number (IVNO07).

Select the following fields for your workbook:

Branch Number (IVPMSBR/IVNO10)

Product Number (IVPMSTR/IVNO04)

Product Description (IVPMSTR/IVDN01)

Quantity on Hand (IVPMSBR/IVQY01)

Weighted Average Cost (IVPMSBR/IVAMW6)

Extended WAC (IVQY01*IVAMW6)

To limit the number of records that will be returned to the worksheet we'll select a single Branch and only items that have a Quantity on Hand that is not equal to zero.  This criteria can be added by either clicking Criteria > Add Criteria and using the displayed dialog box or by clicking View > Criteria to display the Criteria field and typing your criteria directly in the Criteria Fields and Values boxes.  Either way you choose you'll need to enter IVNO10 equal a specific Branch (I used Branch 9 from our test database) and IVQY01 not equal to zero as below.

Click the button with the exclamation ( ! ) to run the query to display the data.  If the data appears correct then either click File > Return Data to Microsoft Office Excel or click the fourth button from the left; either will return your data to the worksheet.  If you're using Excel 2007 the window below will display.  Click OK to load the data beginning in the default location (cell A1) or change to another location if desired.

In Excel 2007 the data will return as a table as below:

Now, it's simply a matter of formatting your "Inventory Valuation by Branch" report the way you want.

In a future post we'll add the ability to change the query results by changing the Branch Number by changing the value in a cell on the workbook.

As always, if you have any questions related to this post or anything related to Excel leave a comment here or email me at the office.