Formula for calculating Gross Profit with muktiple discounts #2

List Price less Disc1 less Disc2 equals Net Price. Sell price  less net 
price equal profit divided by sell price equal gross margin percentage.

$10 less 50% less 10% =$4.50. Sell for $12.50. $6.00 less
$4.50 =$1.50. $1.50/6.00 = 25% GP.

All of these figures are in cells of their own.  Cannot get this to 
calculate correcly.

Please help!  Urgent
0
carm (16)
9/25/2005 12:03:13 PM
excel 39879 articles. 2 followers. Follow

1 Replies
602 Views

Similar Articles

[PageSpeed] 27

CARM Wrote:
> List Price less Disc1 less Disc2 equals Net Price. Sell price  less net
> price equal profit divided by sell price equal gross margi
> percentage.
> 
> $10 less 50% less 10% =$4.50. Sell for $12.50. $6.00 less
> $4.50 =$1.50. $1.50/6.00 = 25% GP.
> 
> All of these figures are in cells of their own.  Cannot get this to
> calculate correcly.
> 
> Please help!  Urgent

Following the data that you have, I am just wondering how you arrive
at $6 --
which you defined as "Sell Price less net price." Based on you
calculations above,

Sell Price = $ 12.50 (given)
Net Price = $   4.50 (calculated as $10 less 50% less 10%)

Hence, *the difference is $8* ($ 12.50 - $ 4.50) and not $6 as yo
indicated. Unless you can explain how you got $6, I will simpl
consider this a typo and proceed with deriving the formulas required.

Assume the following entries are in the following cells:

List Price is in Cell A1
Disc1 is in Cell B1
Disc2 is in Cell C1
Net Price is in Cell D1
Sell Price is in Cell E1
Profit  ( Sell Price - Net Price ) is in Cell F1
GP percentage is in Cell G1


Your formulas are as follows:

In Cell D1 :   =(1-C1)*A1*B1

NOTE: Format Cells C1 and B1 as % and Cell D1 as $

In Cell F1 (formatted as $), =E1-D1

In Cell G1 (formatted as %), =(F1-D1)/F1

The formulas that I have derived are based on the conditions that yo
stated in your problem. PERSONALLY, I just cannot understand you
definition of your GP percentage. 

You have the GP percentage calculated as

(Sell Price- Net Price)/Sell Price

but shouldn't you have

(Sell Price- Net Price)/Net Price

as your GP percentage formula

--
BenjieLo

-----------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1101
View this thread: http://www.excelforum.com/showthread.php?threadid=47055

0
9/25/2005 1:54:34 PM
Reply:

Similar Artilces:

Microsoft ISATAP Adapter #(2,3,4,18,21,23)?
Those are the Devices on my Dell Laptop that aren't working and are causing me to not be able to accesss the internet and the error message on Device Manager says that it can not run because Microsoft cant load the needed drivers. Can anyone tell me what these Adapters are & how they work, and how do i fix the Drivers problem? -- TheronJR Posted via http://www.vistaheads.com "TheronJR" <TheronJR.43nr03@no-mx.forums.vistaheads.com> wrote in message news:TheronJR.43nr03@no-mx.forums.vistaheads.com... > > Those are the Devices on my Dell Lapto...

Opinions , info ... #2
Hello, I would like to ask the following question. Now, we have several company's with there own e-mail suffix for example test@company1.be , test@company2.pl , test@company3.fr , and there own local provider and IT infrastruture. What we would like is that all these company's get an e-mail address like @company.com. What is the easiest way to do this ? Can we use a pop3 server ? The company with .pl use there own exchange now but aren't connected to the main facility. Thx for the info ...

Formula in Chart Title
I want a chart title to say: FORCAST THROUGH 26 SEP 05. Where the date is located in cell J1. What would the formula be? Thanks, Phil In another cell, e.g. J2, enter the formula: ="FORECAST THROUGH " & UPPER(TEXT(J1,"dd mmm yy")) Select the chart Type an equal sign, then click on the cell that contains the formula Press the Enter key Move and format the text box that contains the title text. Phil Hageman wrote: > I want a chart title to say: FORCAST THROUGH 26 SEP 05. Where the date is > located in cell J1. What would the formula be? > Thanks, Ph...

how can I use my e-mail addresses on excel xp with microsoft outlo #2
I need to use my addresses on Excel xp with Microsoft outlook. I want to use my excel file as a database that I can change and improve everyday. I need to send mails to more than 1 contact, so creating an e.mail link is not enough for me: in fact, if I click on it, I send my mail only to 1 address... Txs ...

Possible to lock a range from calculating formulas?
I have a sheet with two ranges, nearly identical in format and formulas, where one is a constant update of budgets, forecasts, variances etc. The other is a revised budget. It has the same fields but I need to lock it in time and not have calculations done until it is allowd to do so at some point in the future. When the User checks a box on a form it signifies that the revised budget has been approved and I would like to stop any more calculations until such time that the checkbox is cleared and then re-checked. Is this clear? Is it doable? Thanks. rick It is diffi...

2 Bars and 2 Y Axis Chart
When I try to create a chart with 2 bars with two different Y axis, Excel stacks the bars. I just want two seperate bars. I change one to a line it works fine. Please Help ... am I missing something? Thanks DG This article might help: http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ DG wrote: > When I try to create a chart with 2 bars with two different Y axis, Excel > stacks the bars. I just want two seperate bars. I change o...

Formatting the result of a VLookup / Custom Formatting #2
Hi, I am having difficulty formatting a cell which contains both text and a VLOOKUP formulae. I have an example of the cell below: ="Here lies the text before Vlookup " &VLOOKUP($M$20,sheet1!$B$10:$L$1445,8,FALSE)&" Here lies follow-up text" The VLookup should return a date but instead is returning a string of numbers. I'm sure there is a way of setting the format within the formulae using dd/mm/yy somewhere. Could anyone tell me how to do this? ...

Backing Up Outlook Express 6 #2
Can it be done (address book / messages), and if so, how please ? Thanks. This newsgroup is for support of Outlook 97, 98, 2000, 2002 & 2003 from the Office family for Intel PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x microsoft.public.windows.inetexplorer.ie5.outlookexpress for OE 5.x http://support.microsoft.com/newsgroups/default.aspx?ICP=GSS3&NewsGroup=microsoft.public.windows.inetexplorer.ie5.outlookexpress&SLCID=US&sd=GN&id=fh;en-us;newsgroups microsoft.public.windows.inetex...

how do I set up a moving average in Excel #2
I am trying to set up a 7 day moving average and cannot figure out how to set it up? Any help would be great. Assume you have numbers to average in row 1, beginning at column 1 and going across the sheet. At G2, enter this formula: =AVERAGE(A1:G1) and simply extend the formula to the right, it will automatically give the average of the value above it and in the 6 cells to the right of that cell, sliding as it goes. Hope this helps. "p-nut" wrote: > I am trying to set up a 7 day moving average and cannot figure out how to set > it up? Any help would be great. ...

A macro for pasting this formula
I have this formula in Cell C1 "='SHEET1'!C8*'SHEET2'!S5" In C2 I want the formula "='SHEET1'!C9*'SHEET2'!S5" In C3 I want "='SHEET1'!C10*'SHEET2'!S5" In C4 I want "='SHEET1'!C11*'SHEET2'!S5"...... to cell C20 However in D1 I want "='SHEET1'!D8*'SHEET2'!T5" In D2 I want "='SHEET1'!D9*'SHEET2'!T5" In D3 I want "='SHEET1'!D10*'SHEET2'!T5" In D4 I want "='SHEET1'!D11*'SHEET2'!T5"..... to ...

time calculate
I have trying to make up a timesheet to calculate times and wage due 1. 06:00 to 20:00 = amount of hrs worked, I would like 14.00 instead of 14:00 hrs 2. To be able to calculate each day to total hourly week 3. Calculate to hours by �7.60 per hour cheers XL stores times as fractional days, so to get 14.00, multiply the time by 24: A1: 06:00 A2: 20:00 A3: =(A2-A1)*24 <=== 14.00 when formatted as a number Note that if your times span midnight, the "later" time (say, 3:00 am = 0.125) will be smaller than the "earlier" time (say, 9:0...

Multiple Formulas #3
Im creating a database for my school house and before i convert over to Access..Im trying to figure out some ways to calculate some numbers. For example, my hardest thing yet, if someone was to run an 18min:00sec 3mile run...he'll get a max score of 100. But if he ran it in 20min:34sec he'll get a score of 84. Being the longest run of 28min:00sec (failing). How do i create a formula to calculate the mins and sec to result in the point score. Set up your timing ranges on another sheet and then use a VLOOKUP with the last parameter set to TRUE. for example, if you table is a two...

how do i create a macro that would check the contents of cells #2
Hi, I am stcuk on Excel VB. I am trying to create a macro that when run would check to see if there is an 'X' in 6 specific cells on a row(the cells are next to each other), there is an X in all of them it would then display a msg saying 'not possible', else it would not doing anything. I hope i did pass the problem across in a sensible manner. Thanks for helping. You wouldn't need to use VB for this. You could just use Data Validation. Select your 6 cells, then select "Data" from the menu bar, then select "Validation..." In the ALLOW box, sele...

simple formula #2
Cell A4 = A1 + B3 Cell B4 = D1 + E3 C4 = G1 + H3 And so on. How can I write the formulat in A4 so that I can just drag that cell to cells B4, C4, D4 etc. so that it'll use the correct intervals of the other two rows? Can anyone help please thanks Hi enter the following in A4 =OFFSET($A$1,0,(ROW()-4)*3)+OFFSET($B$3,0,(ROW()-4)*3) and copy down -- Regards Frank Kabel Frankfurt, Germany Ash T. wrote: > Cell A4 = A1 + B3 > Cell B4 = D1 + E3 > C4 = G1 + H3 > And so on. How can I write the formulat in A4 so that I > can just drag that cell to cells B4, C4,...

PO Planner
After much pain in trying to get the PO Planner working properly (running scripts and installing service packs to overcome bugs in the software) we are now struggling with the simple calculations the PO Planner uses to generate line items. What is the actual calculatin used to generate PO Quantities? Also, can anyone tell whether an item is added to the PO if the Quantity Available is 1. equal or less than the Reorder Point OR 2. Less than the Reorder Point. The problem we are having is it seems only some items whose Qty Available = Reorder Point are being added but not all items are...

RMS 2.0 SP2 "does not enable remote access"
In the Microsoft article "How Microsoft Dynamics RMS can help with PCI compliance", there is a single mention of "Microsoft Dynamics RMS does not enable remote access". Unfortunately, the article doesn't explain exactly what this means. Does this mean after installing RMS SP2 that I won't be able to administer our Server remotely? Or is RMS itself somehow restricted? Or the POS's? I'm sorry Microsoft--this sentence raises more questions that it explains. thanks, randy This is a multi-part message in MIME format. ------=_NextPart_000_027A_01C8D...

Incorrect formula
Would appreciate help with my formula. I only want the 1 to be added to L28 and L29 if they have a value greater than 0. this formula produces 2 even if all the cells have nothing in them, but I need there to be nothing in the cell if no values are in any cells. If the value of every cell in the formula was 1 then my answer should be 14 but if cell L28 is 0 then the answer should be 12, and if neither L28 or L29 has a value then the answer should be 10, but because of the =1 in my formula I get an answer that is 12.(or 2 greater than I want if values are in other cells) Help appre...

Pivot table refresh #2
Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh End Sub I am using the above macro to refresh two pivot tables on the same worksheet. the problem is when I change a value on the worksheet and the macro runs, it seems to run as an endless loop. Please help. Private Sub Worksheet_Change(ByVal Target As Range) application.enableevents = false ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh ActiveSheet.PivotTabl...

office:mac #2
I just purchased office:mac 2004 and was wondering what the seal is supposed to look like. Mine looks like someone left off a small piece of the seal in the middle with a silver line through it and red writing on the silver line. Does that make any since? The guy at Staples said it is supposed to look like that. Help.....thanks! On 17/10/07 11:14 AM, in article 1192583675.602242.208900@z24g2000prh.googlegroups.com, "sstewart@mcleodusa.net" <sstewart@mcleodusa.net> wrote: > I just purchased office:mac 2004 and was wondering what the seal is > supposed to look like. ...

Description of booket Printed when 2 A4 sheets are printed on A3 p
I want to print a booklet. the information tells me to use 11.5 x 17 paper which will print 2 sheets of 11.5 x 8 and hopefully this will turn out a booklet in proper form? The way it works, select A3 in the printer setup first, in page setup select booklet. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Policy Printing" <Policy Printing@discussions.microsoft.com> wrote in message news:6A1D794B-79FD-468B-ABF7-FBA5C1BC1BC9@microsoft.com... >I want to print a booklet. the information tells me to use 11.5 x 17 pa...

Calculation in Orders Management Database
I downloaded the Orders Management Database Template from the Microsoft Website. When I entered in the price, it didn't add up the total cost of the item price times the number of items being ordered minus the discount. How would I get it to calculate the total for each item? I've not seen the template in question, but I'd imagine it works similarly to the sample Northwind database. In the order details subform in that the ExtendedPrice control is based on a computed column in the underlying query using the following expression: CCur([Order Details].UnitPrice*[Quantity]...

Formula to estimate value
I have a chart on a worksheet, that is created by data in another worksheet. Basically the chart will be an area chart with readings that will decrease in value after easch test date. eg. A B C D E F G 1/1/2010 | 6.25 | 6.24 | 6.24 | 6.25 | 6.25 | 6.26 | -------------------------------------------------------------------------------------------------------------- 1/2/2010 | ...

Discount % up to a certain price ($100) but no more
Any ideas on how to do this? I've searched customersourceand this newsgroup, no luck. Any answer on ths M$? "Derek" wrote: > Any ideas on how to do this? I've searched customersourceand this newsgroup, > no luck. ...

outlook 2002 rules wizard #2
In Outlook 2002, I went to create a rule in the rules wizard. I created a rule based on content, which asked me to specify a word found in the heading and subject. I created rules to recognize the specific word "[spam]" in the heading and subject with an additional rule to place these e-mail in the "specific folder" I created called Spam. After I hit okay, I get a message that says, "a value in the rule description box has not been set. To set a value click on the underlined words in bottom of the rules wizard." When I click on the underlined word a...

Stopping NDR's #2
How does one go about configuring Exchange to NOT generate NDR's for incoming mail, but still let my internal users know that our server failed to deliver an email? Example - an internal network user sends an email to a good address, but for whatever reason exchange does not deliver it within the specified time frame I set in exchange. An NDR should be generated and delivered to my user. However, if an email comes INTO my network with a bad email address for a network user, I want NO NDR to be generated, so that spammers cannot hammer my server and learn correct email addresses. ...