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.
View 2 RepliesI 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.
View 3 Replies View RelatedThis 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,-
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.
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.
View 7 Replies View RelatedI 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
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?
i made a claculator in vb.net, and now i have to make one in vba, what i did was as follows, first ill post the code from last year, then my vba attempt;
View 14 Replies View RelatedI have a sheet with 22,000 rows 6 columns of sales data from my Amazon sales each order has between 3-6 rows. When the data comes in from Amazon some of those rows wont have my sku in the sku cell. Since i use a pivot table to summarize all my sales I need a way to pull the sku from the order number that has it. I have attached a picture of my problem.
View 6 Replies View Relatedi sell a bunch of items on ebay and I would like a formula so that I can have make the same profit on all items
ok here is an example
lets say i purchase something for A$10
it costs me B$3 to ship
i pay an ebay fee of C10% only on A ($10)
i pay a paypal fee of (D30 cents plus 2.9%) on A and B ($13)
I want to charge my consumer E$4 for shipping
Lastly I want to make a profit of $1.50
I want to do this on multiple items so is there a way that I can just put a number in A and make the same profit of $1.50 on all of them?
[url]
eBay uses this URL to view bids and if the item is my item, I will be able to see my bidders email address - though I have to login first.
Can excel:
1) Login my eBay ID
2) Automatically go to all my items listed to download the bidders emails?
or if it can't,
can it,
1) Login to my eBay ID
2) Go to the item numbers that I have inputted into excel to download the bidders email everytime I click on a button in excel spreadsheet?
I would like the following code to be run for all those sheet/s that has a name = "single" (Not case sensitive neither an exact match) of my active workbook.
Code:
Sub UIUIUI()
Dim LR As Long, i As Long
LR = Range("I" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With Range("I" & i)
If .Offset(, -1).Value = 1 Then .Value = .Value & "-"
End With
Next i
End Sub
I am trying to create a calculator which will show start date and end date based on the specific date provided.
EX: column A has January 1, 2013, column B has Wednesday, Column C should have a start date which supposed to be 4 days ago (December 28).
I am trying to automate something I've been doing manually. Let me explain.
1.) I am givien information about fees associated with a mutual fund product. A sample is reproduced in F4:F8.
2.) Next, this information is entered into the table. See A4:D12.
3.) Finally, from this table, I must calculate the fees associated with various account sizes.
4.) I did this manually in cells G15:G16 for 2 different account sizes.
See the attached spreadsheet and the comment boxes inserted containing my questions.
View 10 Replies View RelatedI am an office worker in a (very) small business, and so we use excel as a simple database, which covers our needs easily. In particular, we use a sheet as a register of fees. The question I have involves the creation of a macro to find over-due fees.
The format of the sheet is:
A.Invoice date | B.Reference code | C.Client name | D.$$ cost | E.Due date (always 14 days from invoice date) | F.Payment received date | G.$$ Received
At current, when a fee is overdue, we manually check the list for unpaid fees past the due date and highlight them (generally yellow) and then copy and paste these to a seperate sheet (same book). I believe this could easily be accomplished with a macro, but I am not at all fluent in the coding.
What I believe the macro would require to do is sort through the list (first invoice in row 4), Check ifblank for column B (to make sure a fee exists), then check current date against due date. If 'overdue', check that G=D (received = cost). If not, highlight that row and copy to row 1 of new sheet. then return to fee sheet, move down a row, rinse and repeat untill out of fee's.
If column B is blank, then it might be the 2 row gap left between each month, so it would need to check ifblank for 3 consecutive rows before ending the macro.
If due date is blank (which happens sometimes because we enter drafts into the sheet, and just leave the dates blank until we send them out, but still has a ref code) then it should count as 'not overdue' and move on.
I think it'd be better as an on-click macro rather then an automatic macro, since it only needs to be done at certain times, not every time we fiddle with the sheet a bit. Being on-click, it could even use the same sheet to copy all the overdue's to (rather then create a new sheet every time), as long as it 'select all - delete's it before copying the new things.
Thanks for any help. I signed up just to get this help, so I deeply appologize if I have broken some sort of policy or rule.
I am using Excel 2007. I want to create a graduating schedule to figure fees. For example, $10 per thousand for the first $500,000. $13 for the next $2,000,000, etc. I have tried to understand the IF function but am not sure if this is the correct one to use.
View 9 Replies View RelatedI am attempting to calculate commission (J2) based on the data entered in cell D2
1. The total fees (H2) is a part of the calculation. It represents a value from .5% to a maximum of 3%.
2. If the Loan Description is specifically ‘80/20’ then 80% of the Loan amount is used in calculating the commission. (note: ‘80/20*’ is also a valid entry). Otherwise the total loan amount is used.
D E F G H I J
Loan Dsc. Int. DSCNT% Y-S-P% Fees Loan Amount Commission
80/20 7.52.0 1.0 3.0 $137,403 $4,122.09
I attempted this formula and obviously it’s incorrect:
Calculating Commission (J2)
=IF(H2=" "," ",=IF(D2="80/20",(I2*0.80)*(H2*0.01),I2 * (H2*0.01))
1. If H2 is blank then TRUE enter a blank
2. FALSE: H2 contains a fee rate then calculate the commission Commission Calculation: If the Loan Description is 80/20 then take 80% of the loan amount and multiply it by the rate fee amount (as a percentage) to get the commission. If the Loan Description is NOT 80/20 then use the whole loan amount in the calculation.
I am trying to build a spreadsheet to calculate referral fees paid to our agents. These are sliding scale fees and I need the formula to consider both the high and low range of the project value. For example we pay a certain percentages for sales in the following ranges:
Up to $5,000,000 (I got this one to work)
$5,000,001 to $7,500,000
$7,500,001 to $10,000,000
$10,000,000+
I assume the best way to do this is with an IF command but I only know how to use it for a single value, not a range. Also, if the statement is not true, then return $0.
I am looking to setup a formula to calculate a Fee that is based on the number of users of a service in a month, with the rate varying by the number of users. The first million users in a month will bring in $0.40 per user, for users 1-4M it will bring in $0.30 per user, for users 4M-7M it will bring in .20 per user, and for users beyond 7 million it will be .10 per user. So for example, if a user count was 9 million in a month, the calculation would be ($0.40 x 1 million) + ($0.30 x 3 million) + ($0.20 x 3 million) + ($0.10 x 2 million). I know the answer to the problem is obviously $2,100,000 but I can't build the formula that solves that and can handle instances where the user count is capped in one of the individual brackets. (IE if there are 3.5M users)
I've attached an example spreadsheet : Variable Fee Schedule.xlsx‎
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.
View 9 Replies View RelatedUsing 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"?
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
I have the margin and I have the sale price. How do I figure out the cost?
View 12 Replies View RelatedI'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%
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
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.
In cell "C4" how would I write the formula to calcuate my precentage profit based on the given cost and sell pricing.
View 3 Replies View RelatedI 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))
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.