Price Lever for Customers

[DaniBoy]

Hello everyone,
I am in the process of building a database for a turf selling company. One thing that they asked me to do was the following.

They want to identify the price of the product that they will give the customer depending on the price level that customer has. Example: I Toms Landscape Inc. has a price level #1 then he would get that special for all the products the have under level #1. So when they make an invoice and pick that customer, once they select the product it will give them the price for that level the customer has. I am kind of lost on how to do this, any ideas?

Thanks

 

[Parker]

Ok Here’s how we do it:

All the items in the product table have a fixed price.

All these items are marked as to if they are discountable or not and the maximum level of discount available on each item.

All clients are then marked with a level of discount.

Each client can reach a level of discount against each item but their discount can never exceed the maximum level set against each product item.

Also this is set so that the selling price of a product can never be lower than the cost of sale, (cost of sale = purchase price plus any necessary costs to supply to client (i.e. overheads)).

The discount levels and profit levels are set by % and stored in a separate table.

By doing it this way then a client can 'earn' a discount level; product prices can be updated globally and each products discount can be individually set to allow for changing profit margins.

For example you may by gadgets in March @ $1 from your supplier. You need to make minimum 40% profit to cover your overheads, but you prefer to make 60% so the standard resale price is set @ $1.60 to be in line with your competition. You can allow up to a max of 12.5% discount on this item.

Fred Blogs has earned a discount level of 10% so he would get to purchase a gadget @ $1.44.

Jo Soap has a discount level of 15% so he would be able to purchase a gadget @ $1.36 but because gadgets are capped @ 12.5% his buy price is capped @ $1.40.

Now in April you buy the same gadgets from the same supplier @ $1.45. Your competition is still selling @ $1.60 (they probably bought more stock than you). You cannot afford to put your price up because you will loose business so you decide to keep your standard selling price the same by adjusting your % mark up. But, if you allow the same level of discounts then you will end up selling to some customers at a loss.

I have controls on my forms that are password controlled so that only certain people can alter or override them. These controls allow me to set the discount levels for each product and each client.

It also means that each product price, the discount applied and the sale price to the client can be clearly seen on screen at the time an order is placed so a live quote can be given to the client.

This is then reproduced at invoice.

One word of warning. If you decide to use this method, don’t forget to apply all math before tax.

Hope this makes sense and helps.

 

[DaniBoy]

Thank you!

Is there anyway I can get an example of your db to study the way you did it? It would realy help me out.

Thanks

Daniel

Ok Here’s how we do it:

All the items in the product table have a fixed price.

All these items are marked as to if they are discountable or not and the maximum level of discount available on each item.

All clients are then marked with a level of discount.

Each client can reach a level of discount against each item but their discount can never exceed the maximum level set against each product item.

Also this is set so that the selling price of a product can never be lower than the cost of sale, (cost of sale = purchase price plus any necessary costs to supply to client (i.e. overheads)).

The discount levels and profit levels are set by % and stored in a separate table.

By doing it this way then a client can 'earn' a discount level; product prices can be updated globally and each products discount can be individually set to allow for changing profit margins.

For example you may by gadgets in March @ $1 from your supplier. You need to make minimum 40% profit to cover your overheads, but you prefer to make 60% so the standard resale price is set @ $1.60 to be in line with your competition. You can allow up to a max of 12.5% discount on this item.

Fred Blogs has earned a discount level of 10% so he would get to purchase a gadget @ $1.44.

Jo Soap has a discount level of 15% so he would be able to purchase a gadget @ $1.36 but because gadgets are capped @ 12.5% his buy price is capped @ $1.40.

Now in April you buy the same gadgets from the same supplier @ $1.45. Your competition is still selling @ $1.60 (they probably bought more stock than you). You cannot afford to put your price up because you will loose business so you decide to keep your standard selling price the same by adjusting your % mark up. But, if you allow the same level of discounts then you will end up selling to some customers at a loss.

I have controls on my forms that are password controlled so that only certain people can alter or override them. These controls allow me to set the discount levels for each product and each client.

It also means that each product price, the discount applied and the sale price to the client can be clearly seen on screen at the time an order is placed so a live quote can be given to the client.

This is then reproduced at invoice.

One word of warning. If you decide to use this method, don’t forget to apply all math before tax.

Hope this makes sense and helps.

 

[Parker]

Is there anyway I can get an example of your db to study the way you did it? It would realy help me out.

Thanks

Daniel

I can’t post a sample of that db for a number of reasons. It would be a nightmare to try and separate the necessary and it is heavily populated. Plus I’ve never got around to commenting the code yet so it is really confusing if you don’t know what you are looking at.

But the theory is real easy.

This concept basically runs on calculation queries.

You need 2 extra tables to hold the discount and mark up information.

I’ve knocked up a quick example to indicate how the basic queries work. (It was very quick so ‘scuse the crudeness)

Its A97

From this you should be able to work out how to adapt it for your needs. And add the other necessary functions like price capping etc.

The security side is taken care of in the code samples on this forum. Look for password controlling a command button, and use this to allow resets within your forms.

HTH