I did a check of my database and found over 50K zero dollar pieces of equipment. I was tasked with leading the efforts to help resolve this issue. Using the equipment export, I exported our entire equipment database for adds in the last 10 years in MS Excel. I concatenated the manufacture description column and the model number column. Using this, I created a pivot table on the Man+Model and took the average purchase cost for each (I filtered all zero dollars out as well). I then created a spreadsheet of all zero dollar devices and used a Vlookup formula to input the average cost into the purchase cost field. I imported the spreadsheet back into AIMS and it filled in over 75% of our zero dollar items. Fixing the rest was more labor intensive, but our team came together and fixed it in 2 days. To round it out to completion, Phoenix Data Systems is helping with a SQL statement to check the Estimated Acquisition Cost box. By doing this, it helped give a much more accurate COSR for our service! The C-Suite likes our new numbers!
