473,320 Members | 1,976 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.

How To: Using Desired Gross Profit Percentage to calculate Selling Price

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
====================
Nov 13 '05 #1
1 18513
Don,

I am in business to provide customers with a resource for help with
Microsoft Access, Excel and Word
applications. You can view my website at www.pcdatasheet.com. If you ever
need outside help, please contact me at my email address below.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Don Leverton" <ia*@telus.net> wrote in message
news:0Jpwd.82593$6f6.60276@edtnps89...
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
====================

Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
by: Colin | last post by:
I have a query that calculates the selling price of products on customer orders. Selling prices are calculated based on the average cost of the items when purchased. As I make new purchases, the...
1
by: Ladislau S. | last post by:
Dear reader, I am an occasional user of MS Access 2000 running on Windows 98. My hobby is ship model building so I made a database for things that I want to buy. After two strokes I bin unable...
4
by: uspensky | last post by:
I have a table (cars) with 3 fields: VIN, Class, sell_price 101, sports, 10000 102, sports, 11000 103, luxury, 9000 104, sports, 11000 105, sports, 11000 106, luxury, 5000 107, sports, 11000
2
by: chetchet007 | last post by:
I have a stock table with the cost price and the selling price, then i have an invoice that is produced. i need to produce a profit/loss for either daily, weekly or monthly i want to do this by drop...
7
by: jamesnkk | last post by:
Hi, Although this question may not directly link to Access, but I think it a common question to those developer.so hope you could suggest a solution. How do I get the average cost for Item sold,...
2
by: scentedstars | last post by:
I had this funtion working by using the onsumbit, but now I have to change it so that it will display the total price after changing the quanity amount. Simple I know, but right now i can not get...
6
by: damola | last post by:
I designed a small database to manage some products. Please how can i add more than one selling price for a product in ms access 2007 database i.e "unit price, pack price, retail & wholesale price....
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: 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)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.