Purchasing Recommendation Report

[metsales]

We are a distribution company that orders based on usage (average sales of an item) for 3, 6, and 12 month periods. Currently these calculations have to be run manually using the item history data from item maintenance. We would like to have a window that shows item monthly sales and then the usage for the 3, 6, and 12 month periods. This window would show the quantity of the item on hand, on order, allocated, and on back order. Additionally, we would like to be able to put in a factor for each item for stock levels that would be based on this usage (say 1.5 months). Finally we would like to run a smartlist or other report that would show the usage values, adjusted inventory quantity (On hand + On Order – Allocated – Backordered) and the quantity to be ordered to get to the desired quantity on hand ((Usage * factor) - Adjusted inventory Quantity).
Does anyone have any idea how to do this in dynamics or if there is a third party software package that could provide this type of purchasing quantity recommendation report?

 

[btamulis]

I would recommend using either Crystal Reports, Anyview Creator (standard or IDS).
You may also want to consider creating an SQL view and then linking view via smartlist builder.
I know smartlist has limited abilities to link tables from different series and you will need to link sales and inventory tables. The item factor (recommended months of usage on hand) could be a user-defined field in item maintenance.
Good luck