I think it depends on how many tapes you have, how often it is backed up and how many tapes are used per backup iteration. Ideally, you would want three iterations (current, recent and fail-safe) at all times as this should give you the best shot at having a valid backup
If, for simplicity, you use 1 backup tape per weekly backup and you've done 10 backups (A-J), you would want to order back A B C D E F G and keep H I J at the offsite storage. This would reduce the amount of times you need to order back from off-site (and most likely reduce the cost of ordering back). Once you use up A-G again, you order back H-D and rinse & repeat
I think an excel spreadsheet would be the best case scenario for this simple of an example. Unfortunately, life is usually not this simple. You definitely need a naming convention (internally) for which tape is which and a listing of the dates backed up. If you're doing full backups each iteration, this will be fine. You then determine how many iterations you want to keep (via cost analysis or used tapes to total tapes ratio). If you have a very large system you'll need a TMS, but then we're talking dollars spent.
If you're doing partial backups, then you might need a naming convention for each type of backup tape (Audio A-D, Video E-G, Data H-K) and track the iterations separately, with the same process.
You might also want to talk to your off-site storage facility and see what method they use to track and if they have a web portal or anything for which you can do inventory.
Ultimately, this is an inventory management system. Depending on your budget, you can look at some of the TMS systems in the wiki link, or you can look for a basic inventory management system, but ultimately excel will be able to do everything you need. You may need some VBA for reminders of when to order back tapes, but the functionality is all there and the support for learning how to do it is all here.