Hi Folks,
I *can* calculate the Gross Profit Percentage where both the Cost and
SellPrice are known, using the formula:
(SellPrice - Cost) / SellPrice = GPP
eg ($24.92 - $14.95) / $24.92 = 40%
But I wanted to do the reverse of that....to calculate the SellPrice, where
only Cost and the desired GPP are supplied.
I was struggling with my high-school math, and having a heck of a time with
this :)
I did try a Google Search on this newsgroup, and came up empty.
Then I found this link which does a good job of explaining the theory and
math: http://www.mygrossmargin.com/tips.html?nid=1386
Using this info as a guide, I created this function ... which I am posting
here in case anyone is interested.
Public Function fGetSalePrice(vCost As Double, vDGPP As Double) As Variant
'*******************************************
'Name: fGetSalePrice (Function)
'Purpose:
'Author: Don Leverton
'Date: December 15, 2004, 04:16:26 PM
'Called by:
'Calls:
'Inputs:
'Output:
'*******************************************
On Error GoTo ErrHandler
'(Quote from http://www.mygrossmargin.com/tips.html?nid=1386)
' By dividing cost by the inverse of the gross margin
' you wish to achieve, you will arrive at the sell price
' necessary to achieve the desired gross margin.
Dim vPrice As Double
vPrice = (vCost / (100 - vDGPP)) * 100
fGetSalePrice = Format(vPrice, "Currency")
ExitHere:
Exit Function
ErrHandler:
Dim strErrString As String
strErrString = "Error Information..." & vbCrLf
strErrString = strErrString & "Error#: " & Err.Number
strErrString = strErrString & "Description: " & Err.Description
MsgBox strErrString, vbCritical + vbOKOnly, "Function: fGetSalePrice"
Resume ExitHere
End Function
--
Don Leverton,
Store Manager
NAPA / Bellzer Boyz
Store # 1529
6320 Imperial Way
Olds, Alberta T4H-1M5
====================