The ultimate goal of our company’s operation is generating profit for the company’s owners. Profit is calculated as revenues minus expenditures. In other words, when we sell a Material we not only need to know if there is enough Material left, we also need to know how much we bought it for. The same question arises when we set the Material price. We need to know how much did it cost us, in order to determine how much we can afford selling it for.
We can buy the same Material many times. Every time the unit price can be different. Then we want to sell it for a bit of profit. To do so we need to know the lower limit of the selling price.
Let’s see what we can do to figure it out. This is a table of the Extension cords left in all our Warehouses.
When we sell one Extension cord we, basically, pull out a random item from the pile. So it’s a matter of chance which one of them we really sell - the one we bought Sep. 01 for $1.1 or the one we bought Sep. 13 for $1.21. All cords are identical from the client’s perspective, but they might be not identical for us in terms of the profit we generated. If we sell the cord for $1.5, our profit can be $0.4, $0.45 or $0.29 depending on the lot we take it from.
Things get even more complicated when it comes to selling a big amount at once. For now we have 55 extension cords left. What if we need to sell 52 of them? How do we calculate our overall profit in this case?
The answer is that usually we do not need to know the exact buying price for every piece of Material we sold. When we were solving the “how much Materials left” problem we considered all Materials being piled up in heaps (one heap for each Material in each Warehouse) regardless of the source of every particular piece.
What if we do the same for the Materials cost? We can just sum up the quantities and total prices for all extension cords we have left, and it gives us the data to calculate the average unit price (total price divided by quantity).
So when we sell extension cord, we presume it costs us $1.16 per item. Of course, this average unit cost will be changing over time as new GoodsPurchase and Services documents with different costs are registered.
Is this a fair approach? Do we get the actual profit calculating it from average cost? Well, usually it is. $63.55 - is exactly how much we spend for all the cords we have left for now. If we sell them for anything more than $1.16 per item we will generate overall profit in spite of the fact that we have bought some of the cords for $1.21.
That said, there are some cases when you might need to bind every Material sold to specific purchase document, but for the sake of our current task this is unnecessary.
Now. How do we store this Materials cost data?
MaterialsCosts accumulation register
We already have the BalanceOfMaterials accumulation register. Maybe we could use this register just adding more resources to it?
It could work but, in this case, we will store the same Material cost separately for different Warehouses. This solution is obviously redundant so we’d better create another register with the only dimension we need - Material.
What resource (or resources) will this register have? On the one hand, the only thing we really need at the end of the day is an average item cost. On the other hand, we need to recalculate this average every time we buy more of the same Material. To do so we need to know the overall cost and quantity. We don’t really need to store the item cost explicitly for we can calculate it on the fly dividing the Cost by the Quantity.
So, the new register has the following structure.
Now let’s make it visible somewhere in the user interface. I suggest we create a new Subsystem called “Money” and include the MaterialsCost register into it.
Next thing we need to do is posting the GoodsReceipt documents in this register. You can build the source code we need using the Register Record Wizard or write it by analogy with BalanceOfMaterials register posting code.
This is the resulting Posting event handler we need.
Procedure Posting(Cancel, Mode) RegisterRecords.BalanceOfMaterials.Write = True; RegisterRecords.MaterialsCost.Write = True; For Each CurRowMaterials In Materials Do // register BalanceOfMaterials Receipt Record = RegisterRecords.BalanceOfMaterials.Add(); Record.RecordType = AccumulationRecordType.Receipt; Record.Period = Date; Record.Material = CurRowMaterials.Material; Record.Warehouse = Warehouse; Record.Quantity = CurRowMaterials.Quantity; // register MaterialsCost Receipt Record = RegisterRecords.MaterialsCost.Add(); Record.RecordType = AccumulationRecordType.Receipt; Record.Period = Date; Record.Material = CurRowMaterials.Material; Record.Quantity = CurRowMaterials.Quantity; Record.Cost = CurRowMaterials.Price * CurRowMaterials.Quantity; EndDo; EndProcedure
Then we need to repost all GoodsReceipt documents to fill out the register with current data. When it’s done, the register balance table contains the following fields (as it can be seen in the Query Builder).
QuantityBalance is an overall quantity of this Material left, and CostBalance is an overall price we paid for it. We can divide CostBalance by QuantityBalance and get the current item price. Note, that we can get the item price for any point in time by querying the MaterialsCost.Balance virtual table using its first parameter like this.
SELECT Cost.CostBalance / Cost.QuantityBalance AS ItemCost FROM AccumulationRegister.MaterialsCost.Balance(&Date, ) AS Cost
Now, when we have item cost calculated, we can advise a user when he is repricing Materials or selling them.
Lesson 5-2 | Course description | Lesson 5-4