I have spent so long looking at this I have become blinded. I don't even know how to properly describe this problem.
I am trying to determine a selling price that includes a fee that is dependent on the selling price. I need to calculate the selling price including the fee by leaving a margin on the cost price.
Perhaps this will help to explain
CREATE TABLE TEST (Cost [decimal](18,3),NettMargin [decimal](18,3),Nett [decimal](18,3), FeeMargin [decimal](18,3),Fee [decimal](18,3),Sell [decimal](18,3))
INSERT INTO [dbo].[TEST]([Cost],[NettMargin],[Nett],[FeeMargin],[Fee],[Sell])
VALUES (100,0.023,2.3,0.07,7.7,110)
Select Sell * FeeMargin as Fee from TEST
Select Sell - Cost - Fee as Nett from TEST
Select Nett / Cost as NettMargin from TEST
Set Sell = ?? where NettMargin = 0.05
October 1, 2020 at 1:08 pm
Your TEST table appears to include all of the items which you are trying to calculate, making this a little difficult to fathom.
What are the inputs to the problem and what are the outputs?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 1, 2020 at 1:44 pm
Phil
I am struggling to explain this, hence a valid result in the table.
I need to calculate the sell price by changing the nettmargin
The cost and FeeMargin will persist. The fee however is dependent on the sell price.
October 1, 2020 at 1:55 pm
Phil
I am struggling to explain this, hence a valid result in the table.
I need to calculate the sell price by changing the nettmargin
The cost and FeeMargin will persist. The fee however is dependent on the sell price.
You appear to have more variables than equations!
As Cost and FeeMargin are the only inputs, if I told you that Cost = 500 and FeeMargin = 10%, could you calculate the other results?
If so, please show us how.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 1, 2020 at 2:29 pm
Assuming Cost = 500, FeeMargin = 10% and NettMargin = 5%
Sell - Cost - (Sell * FeeMargin) / Cost = NettMargin
Sell -500 - (Sell * 0.10) / 500 = 0.05
The only way I can solve that is by brute force ad sell would be aproximately 583.34
October 1, 2020 at 2:34 pm
You start with
Select Sell * FeeMargin as Fee from TEST
Select Sell - Cost - Fee as Nett from TEST
Select Nett / Cost as NettMargin from TEST
If you expand them out:
Select Sell * FeeMargin as Fee from TEST
Select Sell - Cost - (Sell * FeeMargin) as Nett from TEST
Select (Sell - Cost - (Sell * FeeMargin)) / Cost as NettMargin from TEST
Which would lead to:
Set Sell = ?? where ((Sell-Cost-(Sell * FeeMargin))/Cost) = 0.05
October 1, 2020 at 2:54 pm
Yep but how do we calculate Sell knowing Cost , FeeMargin and Margin
October 1, 2020 at 3:04 pm
Assuming Cost = 500, FeeMargin = 10% and NettMargin = 5%
Sell - Cost - (Sell * FeeMargin) / Cost = NettMargin
Sell -500 - (Sell * 0.10) / 500 = 0.05
The only way I can solve that is by brute force ad sell would be aproximately 583.34
Based on this formula, I calculate Sell = 500.15 and no brute force is required.
Why is there no mention of Fee here?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Using basic math substitution .....
-- Fee = Sell * FeeMargin
-- Nett = Sell - Cost - Fee
-- NettMargin = Nett / Cost
----------------------------------------------------------------------------------
-- NettMargin = ( Sell - Cost - Fee ) / Cost
-- NettMargin = ( Sell - Cost - ( Sell * FeeMargin ) ) / Cost
-- NettMargin * Cost = Sell - Cost - ( Sell * FeeMargin )
-- Cost + ( NettMargin * Cost ) = Sell - ( Sell * FeeMargin )
-- Cost * ( 1 + NettMargin ) = Sell * ( 1 - FeeMargin )
-- ( Cost * ( 1 + NettMargin ) ) /( 1 - FeeMargin ) = Sell
So the SQL is ...
SELECT t.Sell
, CalcSell = ( @Cost * ( 1 + @NettMargin) ) / ( 1 - @FeeMargin )
FROM dbo.TEST AS t
October 1, 2020 at 4:07 pm
Here's how to calc the new selling price for any new net margin. (I always did love algebra.)
Edit: I didn't see Des's until after I had posted mine.
DECLARE @NewNettMargin decimal(18, 3)
SET @NewNettMargin = 0.05 --<<-- adjust as needed
SELECT
ROUND((NewNett + Cost) / (1 - feemargin) + .0009, 3, 1) as NewSell
FROM TEST
CROSS APPLY (
SELECT @NewNettMargin * Cost AS NewNett
) AS calc1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 1, 2020 at 4:28 pm
45 years since I did algebra at college.
Thanks so much for the refresher guys.
Don't you just hate it when you cant see the wood for the trees.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply