Inventory Analysis

[johncrane]

I am trying to setup a program which will do Sales Forcasting and Inventory Level calculations with Reorder Levels, Safety Stock and automatic ordering of replacement inventory and also Sales Forcasting vs Acutal sales for Trend Analysis of product sales.

I am in a Filemaker 5.5 Server environment with a G4 500mhz server. The client computer will be a 600mhz iMac.

I have an Inventory file that contains about 2500 product items. I have defined 36 fields that contain the sales quantity figures for the last 36 months. With all of these fields in one record I can accomplish the calculations that I need. The 36 fields are set up as YR1Mo1 = the Current Month. The Remaining fields are Yr1Mo2, Yr1Mo3....down to Yr3M12. The problem comes when it is time to update the current month field with the current month sales quantities. The first thing which I have to do in the Close Month Script is to shift the data for a field to the Next month Fields. Example would be to do a setfield YR3MO12 to YR3MO11 value, then YR3MO11 to YR3MO10 value, etc.. This results in 35 set fields on each record. Even with the Freeze Window command in effect it takes forever. About 6 records per minute. I may have to accomplish this in an Overnight Environment but that would still take about 7 hours.

I have considered leaving the 35 fields alone and just changing the month and year associated with each of the fields. This would allow me to just import and match the Monthly quantity sold in to the correct field. The fields would no be like JanYR1, FebYR1....DecYR3. By knowing the current month I could identify which one of the fields the current month data would go into.

This solution would allow me to update the file very quickly however some of my calculation would become difficult if not impossible. I would find it hard to display a Trend Line graph for a product with the data in this format.

I have also considered putting the data in a seperate file with one month data for each record. This would pose a problem in getting all of the data together at one time to compute a standard deviation of actual sales vs forcasted sales. Also the Trend Line computation would pose a problem.

I am looking for anyone that has been faced with a problem like I have described and for any suggestions anyone might have. Sorry about the long explanation but I could not describe it any other way.

Thanks in Advance for any guidance.

[LiveOak]

A lot of San Diego County posts today...

The secret to keeping track of all sorts of data is to break data up into small transactions. Don't create a file where each record is a month of sales, keep a file of individual sales. Don't have a timecard for a week or month, enter individual person/project/task/time in a file. It is MUCH easier to sum up small transactions than it is to break apart larger pieces of data. A clue that your structure is wrong is that you need to change the field definitions to accomodate changes in date. Use relationships to compare data in separate files for the same time period.

-bd