Rental/Billing Rates

[tyaubin]

We bill customers for equipment that is being used at different sites. If certain items are returned in good working order we credit them a certain percent of the items original cost. If they are completely used up or lost we bill them in full. Internally we call this rental and sale rate. I have been trying to figure out a way to accomplish this with custom fields but have been unsuccessful so far. I would be fine having it export and have the calculations done in a custom excel sheet but can't wrap my head around the order of operations that would be necessary. I have added a custom field called return rate that can either be 50% or 100%. This is added to each asset so assets we charge in full for get marked as 100 but assets that can get returned used are 50. These assets that are marked as 50 get set as abandoned for asset status if they are returned broken or not returned at all. I guess I could have an equation in excel that would look for the 50 or 100 and my rates would be set that way. The only issue I have is that we don't credit a customer for an asset until it has been checked back in. So if it is at a location for 5 months and we have billed them in full for that asset for 5 months then it gets checked back in in the middle of the 6th month before we do billing how can I have that credit be applied based on time checked out. Along with that if an asset gets checked in in the middle of the month how can I bill that based on location if it is no longer at that location when we run billing.