I'm trying to build a solution that will track costs for reloading ammunition, but am just not figuring out how to get the calculations down. The costs involved are fixed costs for all batches, fixed costs for a subset of batches, consumables costs, and information about each batch.
For those who don't know anything about reloading, fixed costs would include the cost of a press. There are also fixed costs that apply only to some calibers that are loaded, such as a set of dies. There are also consumable costs that would apply to a subset of calibers, such as bullets, primers, powder, and cases. I know what data I'd like to enter, but where I'm having problems getting my head around this is in tracking the costs over time. The costs for the non-consumable items are fixed, and will be a smaller and smaller portion of the overall cost over time as more and more batches of ammo are reloaded. Consumables will need to be tracked by quantity, as not all of any given consumable will used in one given batch of ammo.
Here's a small example of what I'm talking about:
reloading press: $300
dies for caliber #1: $30
dies for caliber #2: $40
500 bullets for caliber #1: $50
500 bullets for caliber #2: $75
35000 grains of powder, used by both caliber #1 and caliber #2: $100
1000 primers used by both calibers: $30
500 cases for caliber #1: $25
500 cases for caliber #2: $25
Let's say I load one 250 round batch of caliber #1. For that batch, I'll need 250 of the appropriate bullets, 250 primers, 250 cases, and we'll say 1500 grains of powder. The consumables cost for this batch would be $49.29 (half of bullet cost, half of case cost, one quarter of primer cost, and 1500/35000 of the powder cost. This yields a consumables-only cost of $0.197 per round. However, the press itself cost $300 and the appropriate dies cost another $30, which at the time of this batch (being the first batch reloaded), would add another $1.32 per round ($0.12 for the dies, and $1.20 for the press). All said, as of the moment this batch is completed, the per-round cost is $1.487.
Let's say I load another batch of the same caliber, but I change the powder load. Now I use 250 bullets, 250 cases, 250 primers, but now I use 1625 grains of powder. My consumables-only batch cost is $49.64. However, since I've doubled the number of rounds loaded, I've halved my fixed costs that apply to the individual batch, and the fixed costs are only $0.66 per round now ($0.06 for the dies, and $0.60 for the press). Now, the cost of this second batch is $0.859 per round, and the cost of my first batch has dropped to $0.857 per round.
Now I decide to run a third batch, which is for caliber #2. I use 500 bullets, 500 cases, 500 primers, and 3500 grains of powder. My consumables cost is $125, making my per-round consumables cost $0.25. I've finally started using the dies for caliber #2, and since this is the only batch for that caliber, the entire $30 cost of the dies applies. For the press, however, I've just doubled the number of rounds that the press has been used for, thereby halving the per-round cost of the press. So now my fixed costs for this batch are $0.38 ($0.08 for the dies, $0.30 for the press).
The cost of this batch is $0.63 per round, and the costs of batches #1 and #2 have dropped to $0.557 and $0.559, respectively.
So, it's easy enough to track the consumables cost for any given batch, and I would assume that this calculation would be stored in a "batch" table. Where the difficulty lays is in calculating what the fixed costs are for any given batch, since those costs will (theoretically, at least) drop with every batch reloaded. I'd also like to be able to track costs on a caliber basis- how many rounds loaded and what the average cost per round is. Again, this will change over time as the fixed costs are amortized over every batch of that particular caliber.
So far, I know I need separate tables for tracking fixed costs, consumables, and batches. I'm not sure if I need a caliber table or not, though I'm leaning towards not.
While the solution I'm trying to come up with is fairly specific, similar cost tracking for processes that have both fixed and consumables costs has had to have been done before- the theory applies to any manufacturer, repair shop, etc.
Anyone have any pointers that they could share for how to track these kinds of costs over time?