Price simulation

[Zelur90]

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.

Link to my model: model

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

Please advise.

[twinnyfo]

Zelur90,

First, Welcome to Bytes!

Second, please upload the actual photo to this thread, as some of us are unable/unwilling to access linked images.

Third, I am sure what you are requesting is possible. But, I think what you are asking is a bit beyond the initial scope of a site/forum like this. We are certainly glad to assist you with particular problem areas and work those those particulars, but it appears you have nothing started at this point.

That's OK--in one sense--because I think you are asking where to start. One of the keys will be having a good table structure, such that your level 0, 1 and 2 product tables are appropriately related. When this is the case, much else usually falls (more easily) into place.

However, your description seems to be that you want to simulate various scenarios for pricing and profit margins. In theory, this is straightforward--or could be. If, for example, you have a cost for all your level 2 components and your standard markup is 10% and you want to see how that affects overall cost when applied to all your level 2 components, then that may be quite easy. But, if you want to have different markups for different types of components, then this could very easily become tricky and extremely complicated.

Again, this is not to say that it can't be done. Just quite involved.

We are standing by for further assistance if you have detailed specifics of what we can hepp with.

[Zelur90]

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.

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.

[twinnyfo]

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....

[SwissProgrammer]

Do the math first. Then work on the code.

Visit your local accountant, or an accountant of your choice, or your local college's accounting class instructor (at the college), or a college math instructor, and ask them how to do this without calculus.

Then if you cannot get that to work in code, visit a college computer science instructor and ask them for help.

Do not visit them at home! Go to the local college and request help there.

Fine tune your process, and if you have questions, post them here.

If twinnyfo, whom I consider far more capable with this type of stuff than myself, does not get what you are asking then get accountant or college professor help just setting up what you are doing. An accountant might help you for a small fee. A college will probably help you for totally free.

[Zelur90]

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.

[twinnyfo]

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!

[Zelur90]

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.

[Rabbit]

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.

[Zelur90]

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.