473,320 Members | 1,722 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Price simulation

5 Nibble
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.
Attached Images
File Type: jpg TzVIN.jpg (94.9 KB, 52 views)
Sep 23 '20 #1
9 1589
twinnyfo
3,653 Expert Mod 2GB
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.
Sep 23 '20 #2
Zelur90
5 Nibble
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
  1. Level 2 A:
  2. Basic price 5 USD
  3. + VAT
  4. - Specific rebate only for A
  5. - VAT refund
  6. = 4.5 USD
  7.  
  8. Level 2 B:
  9. Basic price 5 USD
  10. + VAT
  11. - Specific rebate only for B
  12. - VAT refund
  13. = 3.5 USD
  14.  
  15. = Level 1 C: 8 USD
  16.  
  17. Level 1 D: 10 USD
  18.  
  19. Level 1 E: 15 USD
  20.  
  21. = Level 0 F 33 USD
  22.  
  23. -----------------------------
  24.  
  25. Purchase price 33 USD
  26.  
  27. Of that VAT: ...
  28.  
  29. Of that VAT refund: ...
  30.  
  31. Of that rebate: ...
  32.  
  33. + Margin 10% (based on group that level 0 is in)
  34.  
  35. -----------------------------
  36.  
  37. Sales price 36 USD
  38.  
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
twinnyfo
3,653 Expert Mod 2GB
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
SwissProgrammer
220 128KB
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.
Sep 23 '20 #5
Zelur90
5 Nibble
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.
Sep 24 '20 #6
twinnyfo
3,653 Expert Mod 2GB
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!
Sep 24 '20 #7
Zelur90
5 Nibble
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
File Type: jpg bom.jpg (94.2 KB, 39 views)
Sep 24 '20 #8
Rabbit
12,516 Expert Mod 8TB
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.
Sep 24 '20 #9
Zelur90
5 Nibble
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
File Type: png TzVINT.png (20.6 KB, 42 views)
Sep 25 '20 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Steaming Balturd | last post by:
are there any php based economic simulations out there - not trading games per se, but more like simulating an economy, be it running a business or running a government? All i've been able to...
0
by: Constandinos Mavromoustakis | last post by:
Dear all, first we apologize if you receive multiple copies of this announcement. please see below if you are interested. Thank you in advance....
0
by: Constandinos Mavromoustakis | last post by:
http://agent.csd.auth.gr/~cmavrom -------------------------------------------------- ============================================================================ = 37th Annual Simulation...
0
by: Gus | last post by:
---------------------------------------------------------------------------- ------------------------------------ Call for Papers: 38th Annual Simulation Symposium Part of the 2005 Spring...
19
by: Nicolas Pernetty | last post by:
Hello, I'm looking for any work/paper/ressource about continuous system simulation using Python or any similar object oriented languages (or even UML theory !). I'm aware of SimPy for...
0
by: Karatza Helen | last post by:
Our apologies if you have received multiple copies -------------------------------------------------- Call for Papers: 38th Annual Simulation Symposium Part of the 2005 Spring Simulation...
1
by: Tim Silva | last post by:
SDX Modeling, Simulation and Numerical Computing Environment for science and engineering has now been released. Major features include: * A unified simulation environment for modeling virtually...
4
by: Richard Blackwood | last post by:
Hello all. I have a few questions about simulation programming. One, do all programmers know to how to code a simulation? By simulation I mean a model of real world relationships (i.e. like...
1
by: CarlaLee | last post by:
Hi guys.. I'm a newbie in java and I try to understand the logic of this simulation I want to control the arrival value of the customer and server. There is no error but the result is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.