463,153 Members | 653 Online
Need help? Post your question and get tips & solutions from a community of 463,153 IT Pros & Developers. It's quick & easy.

# Price simulation

P: 5
I have level 0 products (finished product), level 1 products (component) and level 2 products (sub-component).

My subcomponents have all the price calculations, which are inherited by the component and then the finished product.

At the end, based on the finished product group, I want to add a % margin. This becomes then my sales price.

That's my calculation. Then (in a temp table?) I want to have a full price simulation where I can change whatever (margin, VAT, VAT refund, subcomponent prices, add different subcomponent) and see what happens. Then to give that out as a report and choose to either save or discard these changes.
If saved, they should have a reason on why something was changed.

I don't know if my model can represent that.. at all.

Attached Images
 TzVIN.jpg (94.9 KB, 11 views)
Sep 23 '20 #1
9 Replies

 P: 5 Dear twinnyfo, Thank you! I have attached the picture to my OP and apologise. Your latter assumption is what I'm going for, I'm afraid. Let me describe a basic calculation. Expand|Select|Wrap|Line Numbers Level 2 A: Basic price 5 USD + VAT - Specific rebate only for A - VAT refund = 4.5 USD   Level 2 B: Basic price 5 USD + VAT - Specific rebate only for B - VAT refund = 3.5 USD   = Level 1 C: 8 USD   Level 1 D: 10 USD   Level 1 E: 15 USD   = Level 0 F 33 USD   -----------------------------   Purchase price 33 USD   Of that VAT: ...   Of that VAT refund: ...   Of that rebate: ...   + Margin 10% (based on group that level 0 is in)   -----------------------------   Sales price 36 USD   I want to be able to modify the margin group (change from the 10% group to 15% group, for example)for my level 0. I need to be able to change basic prices for each level 2, as well as each of those specific rebates. VAT and its refund is unlikely to change often. The model (attached to my OP) shows my early data model. However I feel I'm getting lost here. Sep 23 '20 #3

 Expert Mod 2.5K+ P: 3,542 Unfortunately, I can't tell from your relationships how you determine your level 0, 1 and 2 components. I assumed you would have a table of Products with a subordinate table for components and a table subordinate to components for each individual part. I'm not sure your tables initially make much sense to me, but I am willing to keep learning about your set up to try to guide you to a better solution.... Sep 23 '20 #4

 P: 5 I was hoping to have all level 0, level 1 and level 2 in tArticle and via tBOM define their relationships to each other. If each entry has their assigned supplier, surcharges and price, I'd be able to calculate the purchase price. The surcharge on my level 0 would then give me the sales price. I'm not sure if that works. What I'm also unsure about is how to catch changes, e.g. give a reason for change. Add a field to all tables with change reason? Some kind of audit trail? It's been..a while..since I used Access. 4 Weeks Ago #6

 Expert Mod 2.5K+ P: 3,542 I think you are confusing me more than anything else. You may need to provide more concrete examples of actual pricing (without divulging any proprietary information) so that I can understand what you are "trying" to do. Remember, I'm a database guy, not a supplier or accountant. Talk to me as if I know nothing about what your company does or how it does it. Business rules are often complex--but those engrossed in the business take it for granted that others understand those business rules rather easily. I've been working in the same office doing more or less the same thing for 13 years, and I am still exploring some of the nuances of our business rules.... Thanks! 4 Weeks Ago #7

P: 5
Think of the following situation. You need to negotiate prices with a supplier who provides you with articles (they are my level 2). The following scenarios are possible:

- he gives you a discount for a specific article
- he gives you a discount for a group of articles
- he gives you a global discount (unlikely, so let's ignore that here. I never experienced this ever.)

So in that moment you will want to change the price of 1 or N articles and see what happens. Which level 1 is affected? Which level 0 is subsequently affected? And, evenly important: how much is the impact?

Regarding the BoM I have attached an example that shows my level 0, level 1 and level 2 very well. I don't go deeper than that. Each of those parts could come from a different company (supplier) at a difference price. The additional costs might also be different - shipping from China to the US costs more than shipping from Canada to the US. Those are my surcharges.

A basic calculation is this, referring to the example picture.

Rim (level 2), comes from Microsoft in the USA
Price 2 * 5 USD = 10 USD
VAT 10 USD * 10% = 1 USD
Shipping 2 USD
= Sum 13 USD

Tire (level 2), comes from Apple in Canada
Price 2 * 20 USD = 40 USD
VAT 40 USD * 20% = 8 USD
Shipping 10 USD
= Sum 58 USD

Spokes (level 2), comes from Apple in China
Price 72 * 1 USD = 72 USD
VAT 72 USD * 10% = 7.2 USD
Shipping 11 USD
= Sum 90.2 USD

Wheel (level 1), comes from Apple in Canada
Consumes the price of rim, tire and spokes.
13 + 58+ 90.2 USD
Shipping 5 USD
= Sum 95.2 USD

Frame (level 1), comes from Microsoft in the USA
Price 100 USD
Shipping 2 USD
= Sum 102 USD

Bicycle (level 0), comes from my company
Consumes price of wheel and frame.
95.2 USD + 102 USD
= 197.2 USD (PURCHASE PRICE)
+ My profit 10%
= 216.92 USD (SALES PRICE)

I hope I am more clear here. Sorry for any confusion.
Attached Images
 bom.jpg (94.2 KB, 6 views)
4 Weeks Ago #8

 Expert Mod 10K+ P: 12,439 To track changes you'll need to use the slowly changing dimensions paradigm. That is, you'll need to add start and end date fields on those tables so you can track the changes and when they changed. As far as the simulation, you can avoid dummy tables by creating one form with multiple subforms to track everything, but turn off autocommit, save or cancel the changes only when the user desires to do so. 4 Weeks Ago #9

P: 5
Thank you for the advice. As you previously assumed, I ran into problems when determining the level (0, 1, 2) with my previous model. I followed your advice and split into 3 tables.

However I'm not sure how to add suppliers (my vendors I buy from), surcharges and prices to all 3 levels. Do I simply connect by adding ArtNum, CompNum and SubComNum to tArtSup, tArtSur and tPrice?

I'm not finished with change reasons / dates that you mentioned either, will add soon.

Thank you for your help, my previous model was not a good fit, I realized that.
Attached Images
 TzVINT.png (20.6 KB, 8 views)
4 Weeks Ago #10