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.
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 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 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.
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.
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.
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.
I'm trying to design a Profit and Loss template that calculates off an accounting software data dump. I will attempt to explain the problem here but I have included a summary in the linked workbook which would be easier.
DOWNLOAD WORKBOOK: [URL]
I have two problems:
The Formula: Basically, I need to calculate every cell in the Profit and Loss statement based on the below criteria: The Profit and Loss item accounts, e.g. Gross Income has 6 accounts. There is a reference table for these - so incorporate index match/vlookup? User selected data from 4 combo boxes.
I am thinking a SUMIFS formula (using the combo box values) and somehow combining a vlookup to pull the items accounts. I'm starting to think that VBA is the best method?
Tweaking: The attached workbook is a small example. The full data dump contains 60,000 rows.
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 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:
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.
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.
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).
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.
I'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.
I have 2 columns First Name & Surname. What I want to do is create a data validation list on the surname which results in the 2nd data validation list only showing the first names which link to one of the surnames.
i.e. If I selected Smith in the 1st validation list then I would only like to see 'Paul' as an option in the 2nd list
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.
I have attached a sheet that I am working on. I want cell G1 to be less than or equal to 165. That cell contains a formula. If the formula takes the number to over 165 the validation is allowing it.
I would like to know if it's possible to populate a data validation list based on what is selected from 4 validation lists?
for example: On sheet1: If 'Group1' is selected from data validation list1 then data validation list5 will show a list of all items from Group1. If 'Group2' is selected from data validation list2, then data validation list5 will display all the items in 'Group2'...
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%
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?
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:
