Recalculating Inventory Sales History

[DC505]

Hi,

I have a large problem and hopefully someone might have some suggestions. When we were first setup with Mas 90 our reseller neglected to tell us that inventory items sold is calculated by when the batch is updated. She told us to wait and not update the batch until we had some more accurate inventory numbers. Well we did not update the batch till several months later and low and behold all of the inventory sales numbers were off because they only showed the amount sold in the month that the batch was updated. I need this to be accurate as right now I have a custom Crystal report that adds up all of the items for each invoice/sales order and gives the correct sales in that month but in the system it is all screwy. Does anyone have any suggestions on how to recalculate this and to fix this problem? And as a future prevention any thought as to change this rather surprising behavior of how the program calculates inventory sold? Thank you in advance!

-Adam

 

[BigLouie]

And as a future prevention any thought as to change this rather surprising behavior of how the program calculates inventory sold?" It is SOP for ANY accounting package that inventory is not updated until the invoice is posted. GAAP rules and all that. You should update your invoices at least daily.

As for your problem you need to import into the two inventory summary tables, not sure but I think they are IM9 and IM_13. Export to Excel, change figures, re-import and overwrite with correct figures.

 

[DC505]

Thank you BigLouie, I've come to a similar conclusion although I think I might be able to use Crystal Reports to create a report that recalculates it automatically using the data and then export it into csv format for reimporting into those tables. Editing 15,000 some lines of customer data does not sound like fun to me. We are updating the invoices daily now but it was just unfortunate that it was not explained to us earlier and also we did not have an accountant on staff who knew these things at the time, now I wish we did. The thing I was surprised about was that when I went to view what was sold during the year it was based on the a file that just had the inventory added to it per batch and did not calculate the inventory sold based on the ship date as a real-time calculation. That's all.

-Adam

 

[DC505]

Hello,

I finished two Crystal Reports that should help those that need to recalculate inventory for both the IM_09 & IM_13 files that hold both the warehouse and customer history.

1) Run report and export into Excel (Data Only)
2) Open resulting excel file and save as csv file in Excel
3) Create an Import of Inventory files in Mas 90 leaving out period 13 on quantity, dollar sold, and cost of goods sold.

I left out the quantity returned since I don't use it but it should be very easy to add by looking at how I structured the report.

For some reason I could not figure out why the csv export in Crystal Reports did not correctly export the file, maybe it will for others. In addition if someone could tell me what the 13th period in the file is for, I just don't know as much about that. Hopefully these will help, they have helped me immensely.

http://www.listech.net/files/Inventory Recalculation - Customer.rpt

http://www.listech.net/files/Inventory Recalculation - Warehouse.rpt

These are hosted off of my personal domain and if someone has problems please let me know and I can e-mail them to you.

-Adam

 

[BigLouie]

Well one thing I am sure of, you're not an accountant. Period 13 is used in accounting, don't want to go into a long detail but if you were a CPA you would know when and why it is used. You will be fine if you just use the normal 12 periods.

Can't open your file as I am on 8.5 and I suspect that is 10

 

[DC505]

Correct, I am most definitely not an accountant. I am merely the computer guy tasked with figuring out how to make this work for everyone. Those files are saved in Crystal 10.0 as I created them for 4.20 since I am moving to that anyways.

-Adam