Microsoft Dynamics GP with Excel Refreshable Reports

Excel Refresh-able reports are fairly new in GP. They are not “normal” Excel files, but are attached to a, pre-defined, data connection directly to the GP database – thus refresh as data is added to Microsoft Dynamics GP.

Our customer imports and exports large quantities of chemicals all over the world. They control their costs by comparing the purchase cost of the material (including accessory costs like: customs, ocean freight, demurrage, inland freight, insurance, and so on) with the sales price of the line item. Landed Cost in Microsoft Dynamics GP works very accurately to add a pro-rata cost to the inventory item based upon the multiple landed costs. GP even updates the cost of goods value of item to account for variances in vendor final invoices. Assigning unique lot numbers to each item as it is received, and selling those items by item+lot, allows for true computation of the gross profit on each lot. We are not talking about FIFO or LIFO or Average Cost or Standard Cost. We are talking about true cost of that exact material versus its sale price.

To that end we produced 5 SQL queries extracting the information this customer required:

• Sales Invoices showing Gross Profit by lot

• Purchases by Line Item with Original and Landed Cost columns

• Inventory Stock Status by Item/Lot, showing remaining value

• Landed Cost Purchase Receipts

• Adjustments/Variance to Landed Costs

We put all 5 queries into one Dynamics GP Excel Refresh-able Report, with a different data connection for each of the 5 tabs. We made this report available right from the Excel Reports section in Inventory within Microsoft Dynamics GP.

Customers love working with Excel. When the data changes within Microsoft Dynamics GP, then the Excel reports refresh automatically. We’ll improve the reports with some date parameters or Excel Filter columns, but the numbers have been tested and are true.