Unit Selling Price calculation using Unit Weight*Rate per kg

vikash

hello everybody!
We are using Navision since 5 years but till now no solution has been reached regarding automatic selling price calculation. Please help.
Our firm produces Steel Welded Mesh for Construction Industry and we make Mesh of different sizes and weights. Each item weight is calculated using the wire sizes & length. E.g 10 bars of wire 6mm + 8 bars of wire 12mm.
Alle Tech designed a weight calculation module & incorporated it in navision which works satisfactorily to some extent but has limitations.
our main concern is how to calculate the selling price of each item using the weight determined by the weight calculation mudule & is shown in the weight field of the item card. The formula to calculate the selling price is as follows:

Selling price = Weight * Rate per Kg

Now each client has different rate per kg and one client may have various rates per kg depending on project size.
We would like to input the Rate per kg while creating the sale quote, the sales order and in the customer card. In which field can we input the rates and how do we modify the program to incorporate all this computaion.

Any urgent attention will oblige.
Thanks.
[/b]

themave

It depends on how many items you have, but if it is not too many it should not be real hard to set up if I understand your needs correctly.

On the customer card you can set up prices,
each item is listed, with a unit of measure, a minimum qty and and a unit price.

so for an item the we use

SPI-5-280x

I can set up sell prices of

Customer 3760 SPI-5-280X EA 1.00 25.00
Customer 3760 SPI-5-280X EA 2.00 20.00
Customer 3760 SPI-5-280X EA 3.00 15.00
So when I enter that item on an invoice, if the customer bought one his price is 25, if he buys 3 his price is 15 each.

I don't know if this is everything you want, but it should get you started.

vikash

vikash wrote:

hello everybody!
We are using Navision since 5 years but till now no solution has been reached regarding automatic selling price calculation. Please help.
Our firm produces Steel Welded Mesh for Construction Industry and we make Mesh of different sizes and weights. Each item weight is calculated using the wire sizes & length. E.g 10 bars of wire 6mm + 8 bars of wire 12mm.
Alle Tech designed a weight calculation module & incorporated it in navision which works satisfactorily to some extent but has limitations.
our main concern is how to calculate the selling price of each item using the weight determined by the weight calculation mudule & is shown in the weight field of the item card. The formula to calculate the selling price is as follows:

Selling price = Weight * Rate per Kg

Now each client has different rate per kg and one client may have various rates per kg depending on project size.
We would like to input the Rate per kg while creating the sale quote, the sales order and in the customer card. In which field can we input the rates and how do we modify the program to incorporate all this computaion.

Any urgent attention will oblige.
Thanks.
[/b]

sorry themave! but this won't work
actually we have more than 3500 items. each client may have +-200 items. Now part of the items are tailor made to clients specifications and part are stantard items needed by all.
our main concern is how to get navision calculate the selling price automatically using item weight * rate per kg. Weight varies considerably and is very difficult to establish a list of items per client.
since weight is already calculated and given on the item card, we only need to find out how to input the rate/kg for each client which will calculate the selling price.
thks anyway themave!

johnson_alonso

Hi Vikash,
if you want to use selling price = weight * rate / kg, do you want to use it instead of unit price ?

AlexWollenberg

Hi Vikash

Your problem is not with Navision but with cost- and selling price calculations in general.

The price of an item can be classified in different unit categories. (Possible unit categories: Number, Weight, length, measurements, M3, and so on.) Yours is Weight (probably for a certain range of products). You therefore have to find out what the minimum invoiced weight increase is, and make this the standard for all calculated products that have "weight" as ther calculating category. Otherwise getting it automated involves a lot of manual labour.

If it is "grams" your price for those items should be "per 1 gram". If the customers order involves 14,33 Kg of that article (Y) he gets an invoice for 14330 x "customer specific price for Y" or x "quantity related price for Y" which ever is lowest. In my own application i made a distinction to keep the customer specific pricing even if the quantity price would be lower.

The useage of the standard customer specific pricing would be able to get around this. Important is to determin wether the customer specific price is decreased stepwise based upon the base customer specific price if the quantity increases. If this is so you have a lot of work cut out to do unless you standardize this.

Example of that last sentence:
Regular customer | Customer 1 | Customer 2
price x 1= 100 | price x 1 = 98 | price x 1 = 97
price x 100 = 95 | price x 100 = 93,50 | price x 100 = 92
price x 1000 = 92 | price x 1000 = 91,50 | price x 1000 = 91

If the steps are different for each customer you have to manually put them into the customer cards.

Dealing with customer specific prices when having a lot of customers: looking at them percentagewise often tackle's this problem. e.g. Your customer pays a certain percentage of the unit price depending on several criterea.

It is very hard to automate a process where the price is not based upon the demand of a single product but on the size of an entire project or invoice since the prices have to be recalculated when all data is available. I believe SP1 for Navision 4 has a solution for this ( i don't know the English term for it but it recalculates the prices after mutations)

Not being able to determine the actual quantity used for a customer doesn't sound good. This is basic administration and not a problem solvable by software.

(We have made a tailor made costprice calculation tool based upon our needs since this is the base of any corperation, i cannot answer the " in wich field" question since i wonder if you can get your needs with standard navision or even with available modules)


gr.

Alex vd Wollenberg
Accountmanager e-business
McDOS International.

vikash

Sorry johnson alonso & AlexWollenberg!

I think everybody completely misunderstood the issue.
The problem is not in unit of measure. We sell all products in units e.g No. of Colums, beams, slabs, roofing steel, bridges, fencing, wires etc.

But the price is calculated as follows:

Items Unit Weight Rate/kg Selling Price(U.Wt*Rate

Column FC1 10.75 Kg Rs 22.50 Rs 241.88
Column FC2 15.68 Kg Rs 30.00 Rs 470.40
Column FC3 25.48 Kg RS 32.75 Rs 834.47
Beam FBM1 12.50 Kg Rs 28.00 Rs 350.00
etc etc. with more than 3,000 specific items in stock.

Now suppose one client takes FC1 for two different projects. The rate/kg may vary depending on volume ordered. So when issuing a sales quote or a sales order we should be able to input varying rates/kg.

As I pointed out previously, we have a module to calculate each item unit weight. What we need is a formula whereby selling price is automatically calculated using weight and a field where rate/kg can be input.

Normally our problem is more complex since if an item uses different types of steel size to be produce, the rate/kg for each size e.g 6mm/8mm/10mm/12mm is different and we have to sum up the number of bars for each size used, determine their weight, multiply each size by its own rate/kg, and sum up all to get the selling price.
But let us keep it simple for a start.

We just need a field to input the rate/kg. Items are sold by unit, not by kgs/grams/lb.

Savatage

Have you given any thought about using Item Sales Qty Disc Table.

Instead of having a ton of prices you could alter the item discount depending on the amount bought.

code - Minimum Qty - Discount%
Test - 2 - 1%
Test - 4 - 2%
Test - 6 - 3%
etc.

or call it
code - Minimum Qty(weight) - Discount%
Weight - 10kg - 1%
Weight - 50kg - 2%
Weight - 100kg - 3%
???

It can be set for each item differently OR not.
It can also be setup for all customers or just the one's you choose.

I don't know how your pricing structure is set-up but perhaps a similar type of solution could be used.

vikash

sorry but the original problem remain the same!

Just tell me to make Navision calculate the selling price using the weight from the item card and a rate per kg which will be inserted in a designated field.

themave

This will do it, make the list price in the item card the Rate per kiligram.
Normal Navision behavior

Sell Price = List price (item Card) * Qty (related to Selling unit of Measure) * Base unit of measure * Discount

You want

Sell Price = Weight from Item card * Qty * Rate per kg (to be inserted somewhere

so substitute Rate per kg for List price on the item card and then by setting up your base unit of measure, selling unit of measure, you get standard navision behaivor calculated your sell price with no modification.

Now for discounts, agian you can use a combination of Navision discounts to reach what you want. there are Item discount, customer category discount, and volumn discounts.

Now your are asking a very complicated question and expecting a simple answer of make this field here and everything will be fine. But your final solution is going to take into account a lit of Navision behavior with discounts and it is not a simple anwser. But this can certainly get you there, I believe.

vikash

Many thks for your reply themave.

We will check your solution which seems appropriate.

Do you think we need to modify any form or report?

What we undestand is that :
Base unit of measure=Kg
Selling Unit of measue=Item weight
Is that what you mean?

So where do we input the Rate per Kg and where will be the Selling Price?

We are sure you can get our problem solved.

Thanks for your exteemed attention themave!

vikash

Thank you very much themave, the solution you suggested worked fine,
By putting the Rate per Kg in the Item Card Invoicing Tab Unit Price, Kgs in the Base Unit of Measue field , Units in the Selling Unit of Measure and inputing the item weight in the Qty per Unit of Measure in the Unit of Measur table, the price shown in the Sales Quote & the Sales Invoice forms are exactly what we needed.
We even updated our purchse price similarly as we purchase from our factory department based on kg & rate per kg & unit being shown on the purchase invoice.
So full marks to you themave and thank you very much indeed. We really feel indebted to you.
this forum is a great success.

Savatage

vikash wrote:

We really feel indebted to you.
this forum is a great success.

Become a site supporter!
http://www.mibuso.com/forum/viewtopic.php?t=9861

Joanna_1990

I come from Poland. I recently came to the United States and was surprised by the different conversion rates than in Poland. How to convert price per kg to lb? After searching the Internet, I found the website https://howkgtolbs.com/cost-per-kilo-to-cost-per-pound. Very nice conversion factors. I absorbed them quickly and now I feel more "at home" when shopping.