# How To Split Profit Margin Into 5 Slices

Oct 17, 2013

This is the Case:

I Produce something with a €2000 cost price. this is variable.

I Sell it for lets say € 2500,- to someone this is also a variable.

That's makes a Profit of € 500,- on the sale. This is 125% revenue

Until here, no problem using a spreadsheet. but now the difficult part.

I have to split of the 25% margin over 2 persons. This has to be done as follow:

a. The first slice is 2,25 for Mr A
b. The second slice is 15,00 for MR B
c. The Third Slice is 2,75 for mr A

This divides 20 of the 25 % over 2 persons. Still 5% margin remains

The last 5 is devided as follow:
d. 85% for MR A
e. 15% for MR B

So in this case:
MR A receives: a:€45 + c:€55+ d:€85 = €185,-
MR B receives: b:€250 + e:€15 = €265,-

The tricky part is when the margin is less than 20%.
for example; If the margin is 14%. than only slice a. and a part of slice b. an be paid off. so in that case;
MR A receives: a:€45 + c:€0+ d:€0 = €45,-
MR B receives: b:€230 + e:€0 = €230,-

Any formula which calculates the revenue for MR A and B automatically based on only the cost and selling price.

A B
1 Cost price - €2000
2 Selling Price - €2500
3 MR A - €185,-
4 MR B -€265,-

## How Do I Write Formula To Show A Profit Margin

Dec 1, 2008

I need to work out the profit margin from goods i sell.

I have cost A2
Pack size A3
Net cost A4
Tax A5
Gross price A6

I need to work out the margin between A5 and A6.

## Data Validation To Calculate Profit Margin

Jan 25, 2005

I have a price sheet for my store in Excel. I want to make the spreadsheet easy for my sales people to use to calculate differing profit levels. I'd like to embed a drop down box at the top (I can do that part) that they can select, say, 10%...20%...30% markup. I know how to do that basic formula. I don't understand how to link that formula to a number selected in a drop down box. I don't know how to make the prices change based on the value selected in the drop down box. I can't do vlookup because it's about 1500 lines long. Also, it doesn't have to be drop down box based--that's just the idea in my head. I tried just making a macro that would run when they hit the button, but when the macro runs it switches the focus back to the top, very annoying if you were looking at prices on cell D811.

## Setting Sell Price To Specific Profit Margin?

Nov 29, 2012

I am looking for a formula which will calculate a sell price with 20% profit after taxes and costs. I need it to factor in a fixed shipping cost (eg £10), a 7.5% Amazon fee of the sell price, then 20% tax of the sell price. I currently have a formula which essentially does this but as I'm not good with Excel it requires me to input all the values every time which is not practical considering our large inventory.

## Ebay / Amazon Profit Calculator - Making Specific Margin After Fees?

Feb 3, 2014

I don't want to appear too expensive or give my products away. If I have a part that costs me £20.00 plus vat i.e £24.00. I have to include carriage £5.00 plus vat i.e £6.00. Ebay fees for car parts including PayPal is 10%. How much would I charge to retain 20% percent after all fees and charges and what formula would I use to make the calculator.

## Excel Macro Or VBA To Calculate Desired Profit Margin Depending On Cost

Sep 12, 2013

I need to develope a macro for Excel to calculate desired profit margin depending on our cost of each item. Here's the scenario.

A1: landing cost
If 0

## Calculate Proposed Selling Price By Entering Desired Profit Margin Percentage

Jun 23, 2010

Is there an excel formula that can calculate a proposed selling price by entering a desired profit margin percentage and knowing the cost of goods sold?

I know that gross profit is calcualted by subtracting the cost of goods sold from the selling price and that the gross profit margin is calculated by dividing gross profit by the selling price..but not coming up with a way to back out a selling price by just knowing the cost of goods sold and entering a desire gross profit margin?

## Rearrange Slices Of Pie Chart In Descending Order Without Sorting Data?

Aug 27, 2013

How to rearrange slices of pie chart in descending order without sorting the data?

## Margin Between 1st And 2nd

Aug 15, 2006

I have events in column1 and ratings in column 2. The ratings are not sorted.I column 3 I want print the difference between the highest rating in event 1 and the second highest e.g. in the attached sheet column 3,rows 2 to 7 will contain 13.9. Similiarly for event 2 etc down my full sheet of 1200 events.

## VBA - Align Image To Right Margin?

Jun 1, 2013

Using VBA, how would I move an existing shape object over to the righthand margin?

I suppose what I need is something like:

VB:
ActiveSheet.Shapes("Picture 1").Left = activesheet.margin.right - ActiveSheet.Shapes.("Picture 1").Width

But how do I find "activesheet.margins.right"?

## Add A Margin Calculation To A Formula?

Apr 20, 2014

I put my margin in G6 of the data sheet and the cost are the numbers on the Mail Rates sheet.

cost/(1-G6) and then round result up to next .25

=VLOOKUP(Calculator!E6,'Mail Rates'!A2:D32,Calculator!D6+1,FALSE)

I am using the vlookup above to bring the mail rates to a calculator. I want to add a margin to the mail rates before they reach the calculator.

The margin amount is in G6 of the "data" sheet and the margin formula is "mail rate"/(1-G6)

I would then like to round the result up to the next .25 dollar

## Margin Forumla :: How Do I Figure Out The Cost?

May 27, 2008

I have the margin and I have the sale price. How do I figure out the cost?

## Weighted Average Gross Margin

Mar 1, 2012

I'm trying to calculate a weighted average gross margin but am having difficulty because I've instances of positive cost of sales and negative revenue. On a line by line basis I've had no problems calculating the margin but when it comes to calculating the weighted average margin as a whole I come unstuck. Looking at the data below (A1:D14) you can see that the margin should be somewhere between 90-99%.

Total RevenueCost of SalesGross ProfitGross Profit %4,805(449)4,35690.6%4,992(330)4,66293.4%3,467(246)3,22192.9%4,974(317)4,65893.6%3,873(130)3,74396.6
%3,756(258)3,49893.1%3,955(128)3,82796.8%2,872(59)2,81397.9%1,401(1)1,40099.9
%(10,987)21,45810,47195.3%3,042(172)2,87094.3%2,748(40)2,70998.6%2,682(172)2,51093.6%

## How To Calculate The Sale And Margin Between Certain Hours

Sep 10, 2008

I have a table with daily sale entries and margins. I want a formula to calculate my sale and margin between certain hours.

The original data table is below.

Data table:
ABCDEF1DateHoursQuantityUNIT PRICESALEMARGIN201/07/0810:351-4,00-4,00-4,00301/07/0810:35122,0022,0010,05401/07/0810:5016,006,001,80501/07/0810:5016,006,001,80601/07/0811:0313,803,802,00701/07/0811:0316,806,801,67801/07/0811:0415,005,002,64901/07/0811:0733,339,993,901001/07/0811:0735,0015,008,071101/07/0811:1016,006,003,971201/07/0811:1115,605,602,971301/07/0811:1316,006,003,971401/07/0811:1412,602,601,831501/07/0811:1412,602,601,781601/07/0811:21111,6011,606,401701/07/0811:2226,0012,006,821801/07/0811:2413,503,501,471901/07/0811:28111,6011,603,952001/07/0811:4115,605,602,902101/07/0811:4115,605,602,902201/07/0811:42118,8018,806,722301/07/0811:4417,807,801,612401/07/0811:4523,507,002,942501/07/0811:4712,002,001,532601/07/0812:0113,203,201,84

The results I need:
ABCD1DATESALE BETWEENSALEMARGIN201/07/200810:00 TO 11:0030,009,65301/07/200811:00 TO 12:00148,8970,04

## Set Up Spreadsheet To Calculate Margin On Stock Positions

Nov 16, 2011

I am trying to set up a spreadsheet to calculate margin on stock positions.

There are 3 tiers, based on number of shares. My example:

400 shares in held in total.

1st 200 shs margin at 10%.

next 100 shs margin at 20%.

everything above 300 shs (ie 100) margin at 30%.

The 400 shs may be made up in any number of ways, (ie lots of smaller amounts) and i need a formula to give me the margin amount on each position, flexible enough for when positions are added / removed.

I've been looking at SUMPRODUCT but can only seem to make it work for the total, not each individual position.

## Precentage Profit Formula

Apr 21, 2009

In cell "C4" how would I write the formula to calcuate my precentage profit based on the given cost and sell pricing.

## Calculate The Profit And Loss

Jul 20, 2007

I have a trading system spreadsheet that I am at a loss as to how to calculate the profit and loss.

I was unable to attach a sample of the spreadsheet. I hope this explanation works. If not, I could email it.

Where:
Market position is either 1 or -1
Entry price is the price where the position is reversed.

For example:
H3=1 and I3=100.56, then
H4=-1 and I4=100.88, then
H5=1 and I5=100.53

I need to calculate the profit as follows:
I4 - I3 (because market position of 1 expects the price to go up) and then,
I4 - I5 (because market position of -1 expects the price to go down)

I want to calculate this as a running total to the bottom of the column.

This is the formula I was working with, but it doesn't work:

=IF(OR(\$J4="",ISERROR(MATCH(-\$J4,\$J4:\$J\$55,0)),),"",\$J4*(OFFSET(\$K4,MATCH (-\$J4,\$J4:\$J\$55,0)-1,)-\$K4))

## How To Calculate Max Profit Per Quarter

Nov 28, 2008

Formula to get the Quarter number for the highest profit per Product, like in B16:F16.

The excersiser instracted us that it must be with one formula with no Macro, not User Defined Function and not openning additional colums.

I know how to find the qtr. Number - like for A2: =INT((MONTH(A2)+2)/3) but I am lost in finding how to combine the calculation for the Qtr and the most profitable Qtr. probably with MAX on SUM.

## Inventory And Profit/Loss

Feb 11, 2007

I have a workbook that contains several worksheets. The 2 important sheets are Imports and Sales.

In reading the MS KB, the help screens and this forum, I found a way to create a pivot table from 2 sheets using Data>Pivot Table>Multiple Consolidation Ranges. While that PT will provide a basic inventory and profit picture, it only allows 4 selects.

When these products are imported there are several SKU's. What gums up the works is there are several vendors for the same SKU's. Each vendor has a different price. The selling price is constant so the profit made on each particular item varies.

I would like to be able to keep track of inventory by vendor as well as profit by vendor.

Using the Consolidation PT does not seem to work.

I also tried making one sheet to hold all the data. i.e. data showing imports and sales. However, each entry is driven by the date. So while item A was received on 1/1/07, it was not sold until 1/2/07. The necessary columns for imports are filled in while the columns for sales remain blank until it is their row entry indicating a particular sale. At which point all the columns regarding Imports are blank. I swear, looking at the data sheet with all those blanks (actually0's) is like looking at a mouth full of broken teeth. The resulting PT is also not attractive.

I can not imagine I am the first person to have this problem. How do others handle figuring inventory and profits when you have two sheets. Are Pivot Tables the wrong solution? What would be better?

## Formula To Decrease A Margin, In Connection With Increasing Basic Value

Oct 8, 2008

I'm trying to build a formula to form a price-list. I have some basic prices from a supplier and want to build my prices with a simple rule: the higher the basic price is (column A), the lower my profit margin (in %) should be (column B). Example:

Basic value is \$50, my price is \$75 (50% margin)
Basic value is \$100, my price is \$130 (30% margin)
Basic value is \$150, my price is \$172,5 (15% margin)
And so on...

I forgot most of what I've learned on Excel at my university (long time ago...), so I tried to do it by using simple thresholds, with "if" function:

## Auto Calculate Profit By Part #

May 21, 2007

I have attached a chart I'm working on. Each item is assigned a part number and it automatically takes away from the inventory each time an item is sold. I was wondering if the bottom half could work with the top to determine profit.

Example:

Customer purchases Part # 3 (C15) Quantity 1 (D15) for \$450 (E15). Is there any way to set it up so whatever part number they enter...it subtracts their sold price from the cost (C5 through C11) to show the profit in (F15).

## Calculating Price Based On Profit

Jun 26, 2012

I'm using a spread sheet to determine what I should charge customers. I want to make at least \$300 profit on every job. If my profit is not at least \$300 then I must up my price until profit reaches \$300. Currently I'm manually adding a percentage (Factor) to the original price until I reach \$300 profit. In the image below:

A is the original price
B is the factor by which I multiply the original price to increase it
C is the new figure which is reached after multiplying (1+B2)*A2
D is the costs related to that job (I enter these manually on a separate sheet within the workbook)
E if the profit (C-D)

I use a factor in B because when I increase the price, then I have to increase several parts of the job by the same percentage so I multiply each part by this factor to increase the price of each by the same percentage.

Now what I would like to do is if E2.

## How To Make Profit And Savings Equal

Dec 18, 2012

How do I come up with a price that will make my profit and my customers savings equal.

Example:
Retail price \$178.99
My cost \$113.46

If I charge \$142.23 then my profit is \$28.76

Customer pays \$142.23 plus \$8.00 shipping (\$150.23)
\$178.99 Retail minus \$150.23 = \$28.76 savings

## VBA To Calculate Profit & Loss Of Each Race Bet

Feb 1, 2009

I had a member make the MACRO in the attached spreadsheet some time ago, when i run it on the spreadsheet a runtime mismatch error occurs. I cant find the mismatch.

## Limit Data Validation Values To A 1 Point Margin In 3 Different Cells

Nov 17, 2009

I have a section of my program that asks for board scores (point value is 6-10 in increments of .5) I have set up a data validation so that users can only enter those specific values. Problem is, none of the values can be more than 1 point from each other. (the part I have yet to figure out)

Example, if the first value is a 6.5 the second is 7.0 and the third is a 9.0, I need the data validation to display an error because the 9.0 is beyond the one point spread.

Summary: Display an error message if any of the points are more than 1.0 point from another.

## Profit / Loss Payroll And Cost Of Supplies

Mar 6, 2013

I have multiple departments I need to keep track of multiple numbers with. For example, I need to see the cost of payroll for last fiscal year, vs this fiscal year and have excel show that as a gain (bad) or shrink (good).

I need to track sales this FY vs last FY, obviously, a gain is good and shrink is bad.

Finally, I need to track d&d goods this FY vs last FY.

Where I'm getting held up is, for example last year's payroll was 12000 and this year's is 12500 but next months is 11000 this FY vs 13000. How do I get excel to figure out that payroll decreasing is good and not just do simple subtraction or negative addition thereby allowing me to just plug the numbers in and excel figure out if what I'm throwing at the cell is bad or good comparing to last year?

Also, if I could figure out the if/then to show positive numbers green and negative numbers red that would be quite awesome too.

## Formula Needed For Calculating Profit On Sales

Dec 14, 2012

In my spreadsheet, I have data for each sale in each row. Column C is the buy price, and column D is the sell price. I need column C to be subtracted from column D to get the profit into column E so that I can AutoSUM any cells in column E for total profit over any time period. The problem I'm having is that in columns C and D, I have either a dollar sign in front, or the letter m following without a space (ie \$45.00 or 150m). Many cells in column C also contain another word following the buy price, and many cells in column D have a date following the sell price (ie \$32.00 12/11 or 42m buyout).

Examples of how my spreadsheet looks:
A1: 12/13/12 B1: Item #1 C1: 100m D1: 152m
A2: 12/14/12 B2: Item #2 C2: \$20.00 D2: \$35.00
A3: 12/15/12 B3: Item #3 C3: 90m bid D3: 120m 12/16

So looking at my example above, I would need cell E1 to automatically show "52m", cell E2 "\$15.00", and cell E3 "30m"

How I can get a formula for all of column E that will give me my profit for both \$ and "m" for each item (row). I have tried the SUM function but just keep getting the #VALUE! error, it does not ignore the text like many people say it should.

## Split 1st & Last Names & Split Addresses After 1st Comma

Mar 6, 2008

I'm using Excel 2000 and I have a spreadsheet with 4 columns (A-D) and many (500+) rows.

Part 1:
#########################################
Colums A & B both contain identical data - a first name and a last name in the format "John Doe".

I want the second word ("Doe") removed from all cells in Column A so that only the first name remains, and I want the first word ("John") to be removed from every cell in Column B so that only the last name remains.

So, where A1 & B1 both started with the data "John Doe" now A1 contains only "John" and B1 contains only "Doe".
#########################################

Part 2:
####################################################
Column C contains addresses in the format:
"#5 - 123 Fake Street, Some City, CA 90210"

There is ALWAYS a comma and a space after the street address, then the name of the city or town followed by more data which may include one or more commas.

I would like everything BEFORE the first comma to remain in column C, and everything AFTER the first comma & space to be moved into Column D of the same row. The first comma and space are not needed again.

So, where C1 started with "#5 - 123 Fake Street, Some City, CA 90210", it now only contains "#5 - 123 Fake Street" and D1 now contains "Some City, CA 90210".
####################################################

## Data Validation: Restrict The Value Entered On A Sales Sheet To Force The Value To Be Over 15% Margin

Mar 12, 2009

I want to restrict the value entered on a sales sheet to force the value to be over 15% margin. In column M you enter a value in column N it report the margin. I want to force the value in M to give a minimum 15% in column N or report an error.

## Averagif/averageifs :: To Compute The Realised Profit Or Loss

Oct 25, 2008

How to compute the realised Profit or Loss for each of the shares transacted for the year ended 31 December 2008 based on average cost of each share.

using AVERAGEIF, if appropriate. [Pls refer to the attachment.]