# Design for Material Amortization

1. Competent Performer
Windows 7 64bit Access 2010 32bit
Join Date
Jun 2014
Posts
262

## Design for Material Amortization

I apologize in advance if this is too complicated, but I just need to know if this can be done either with simple design methods or VBA/SQL.

I currently have a database set up to record material information based on part numbers and price breaks. Right now, my table design is as follows:

Now I want to set up something to include amortized parts. Parts can be amortized in many ways, but the most common is by quantity. So if Part1 is \$6.92 per unit, and it has a amortize amount of \$50.00 over the quantity, which means take the amortize amount (\$50.00), and divide it by the quantity, for example 10 pieces, which is \$5.00, and add that to the unit price, which comes out to \$11.92 per unit for 10 units. The amortize amount is determined by the user because of special circumstances, and so an input box would be useful here. A part in my Qtys table can have infinite amounts of amortizations depending on the vendor and any other special circumstances.

What I want to do in the form of an algorithm: Have a boolean or something which labels each part amortized or not, have an input box for the amortize amount, and the range of quantities to calculate. Calculate amortized unit prices for every unit quantity between a range, say 11 to 100. If the amortized unit price matches (by two or three decimal places) for any range and group them together with a price break. For example, if the amortized price for 95 pieces is the same for 96, 97, 98, 99, and 100 pieces, I want my price break range to group these together and store them under one record that reads (pretend its a table):

P/N PriceBreakBegin PriceBreakEnd AmortizedUnitPrice

Part1 95 100 \$4.50

Now I already have a table set up to look like this. And I'm currently working on getting a 1-infinity relationship between Qtys and AmortizationTable.

Is this design possible? I can provide more examples if needed.

Thanks.

2. VIP
Windows XP Access 2003
Join Date
Jul 2011
Posts
5,443
When are you figuring the amoritization? when the item is received into inventory? Or is this just used in figuring costs to sell items to your customers?

Is there an upper bound to your item amoritization (i.e. it doesn't change after 100 pieces)? does that upper bound vary by part number? (i.e. 100 for part A, 1000 for Part B)

I'm a little concerned by your use of 'infinity' I am assuming you mean 1 to many relationships and that you do have both upper and lower bounds to your calculations (values at which your amoritization does not affect the price of the item any further).

Here's an example database:

Gaker10.zip

On the form Select a part
Amoritize Amt is the total amount you want to amoritize
Amoritize Min and Max are the starting and ending value of item counts you want to use
Group by Amt is the range of values that are acceptable for a group (i.e. .01\$ or .001\$)

Didn't do a whole lot of error checking

3. Competent Performer
Windows 7 64bit Access 2010 32bit
Join Date
Jun 2014
Posts
262
The latter, rpeare. Figuring costs to sell items.

The upper bound varies by part number. Really there is no upper bound to buying material of course. But, I would like Access to tell the user when the change in unit cost is negligible, if possible. I'm no procurement agent, I just work with them, so I can't particularly tell when a material's unit cost will plateau, especially if there's variation between material prices, as there often is.

I tried your database, and I'm not sure I understand your logic. The Amortize Amount is a cost that is amortized across a given quantity. So, the amounts are not quite right in the result. The cost for a price break of 11 pieces should be \$13.32 for PartB because the amortize amount is \$100 (for example), the min is 1, and the max is 999. \$100/11 = \$9.09 + \$4.23 = \$13.32. Maybe I did not explain amortizing over a quantity very clearly. It is a little new to me but I understand how the numbers work. Otherwise this is exactly what I need How can I adjust this this to calculate the amortization as stated above?

4. Competent Performer
Windows 7 64bit Access 2010 32bit
Join Date
Jun 2014
Posts
262
UPDATE Post #3: Ok so I found your code and I'm going through it and I see:

Code:
```iPartID = cboPart
cBaseCost = BaseCost
cAmorAmt = AmorAmt
iAmorMin = AmorMin
iAmorMax = AmorMax
cGroupAmt = AmorDiff

iMax = iAmorMax
cCostMatch = (cAmorAmt / iAmorMax) + cBaseCost + cGroupAmt

db.Execute ("DELETE * FROM tblAmoritize WHERE Item_ID = " & iPartID)

Do While iAmorMax >= iAmorMin
cAmorCost = (cAmorAmt / iAmorMax) + cBaseCost
'Debug.Print iAmorMax & "  " & Format(cAmorCost, "\$#,##0.0000")
If cAmorCost <= cCostMatch Then

Else
iMin = iAmorMax
If iAmorMax <> iAmorMin Then
db.Execute ("INSERT INTO tblAmoritize (Item_ID, Item_LB, Item_UB, Item_Amor_Amt) VALUES (" & iPartID & ", " & iMin & ", " & iMax & ", " & cCostMatch & ")")
'Debug.Print "              " & iAmorMax & "  " & Format(cAmorCost, "\$#,##0.0000") & "  " & Format(cCostMatch, "\$#,##0.0000") & "   " & iMin & "   " & iMax
Else
GoTo EXITLOOP
End If```
So your calculations take the amortize amount over the maximum, when I need it to take the amount over the current quantity. Do I need to create another variable to keep track of the current quantity count?

5. Competent Performer
Windows 7 64bit Access 2010 32bit
Join Date
Jun 2014
Posts
262
Or maybe I should track it with the AmorMin variable and reverse the Do While conditions?

6. VIP
Windows XP Access 2003
Join Date
Jul 2011
Posts
5,443
Change the SQL statement to be cAmorCost instead of cCostMatch if you want the highest possible value for the group

7. Competent Performer
Windows 7 64bit Access 2010 32bit
Join Date
Jun 2014
Posts
262
Code:
`cCostMatch = (cAmorAmt / iAmorMax) + cBaseCost + cGroupAmt`
This statement?

What do you mean by "highest possible value"?

8. VIP
Windows XP Access 2003
Join Date
Jul 2011
Posts
5,443
no... the SQL statement, where the records are being appended to the amoritization table.

9. Competent Performer
Windows 7 64bit Access 2010 32bit
Join Date
Jun 2014
Posts
262
Got it! Thank you so much!

Is there any way to incorporate this into a Yes/No field? I have a table of parts and one of the fields is "Amortization?", which is a Yes/No. If it's a yes, I'd like to link your amortize database to it somehow. Like a disable/enable Amortize button feature or something.

10. VIP
Windows XP Access 2003
Join Date
Jul 2011
Posts
5,443
I don't think I'd bother. I don't know what your business is but if you're not amoritizing a cost across a number of units then you just have a unit cost and don't need the amoritization table at all. You could limit what gets added to your amoritization table though by limiting the part selection combo box to only those parts that should be amoritized.

11. Competent Performer
Windows 7 64bit Access 2010 32bit
Join Date
Jun 2014
Posts
262
That's a good idea. Thank you

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Other Forums: Microsoft Office Forums