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

Sep 12, 2013I 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

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

ADVERTISEMENT

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 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 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 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 RelatedI 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 Replies View RelatedI have the margin and I have the sale price. How do I figure out the cost?

View 12 Replies View RelatedI 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.

I am looking for something to calculate values based on the past three months. I already have written a macro to retrieve all of the data and just need this as an add-on.

for example, lets say it is 5/31/2012. I have a row with dates going like

10/31/2010 11/31/2010 .........

I want the formula to calculate SUM(march+april+may row 68)/SUM(march+april+may row 59)

I have an input tab where I can select the month, say 5, and get the periods.

3/31/2012, 4/31/2012, 5/31/2012

if I changed the 5 to an 8 it would get 6/31/2012 7/31/2012 8/31/2012

I don't know if i need VBA for this but I am trying to have something to calculate this depending on the period selected in the input tab.

I am using excel 2010. I am thinking something with an offset function and maybe a loop? but still not sure.

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:

=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.

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.

View 4 Replies View RelatedI use Windows Vista and Excel 2007.

So to cut to the chase :

On my spreadsheet, I have 6 main groups of columns, each one comprising 4 columns with their own automated calculations across the range.

Group 1: Cell H5, Cell I5, Cell J5, Cell K5

Group 2: Cell L5, Cell M5, Cell N5, Cell O5

Group 3: Cell P5, Cell Q5, Cell R5, Cell S5

Group 4: Cell T5, Cell U5, Cell V5, Cell W5

Group 5: Cell X5, Cell Y5, Cell Z5, Cell AA5

Group 6: Cell AB5, Cell AC5, Cell Ad5, Cell AE5

Then I have two columns following those: Col AG, Col AI

In each one of those two columns, I am trying to sum up a total value using The IF Function.

In Cell AG5 : the sum of the values of cells K5+O5+S5+W5+AA5+AE5,

ONLY IF the values of Cells H5, L5, P5, T5,X5, AB5 is equal to AG3 (which is a symbol (wingding 3 for capital P), this occurs 3 times out of 6, sometimes twice only, and at random, so I need the sum of 3 cells (or 2) out of 6, based on the above criteria.

Same in Cell AI5: sum of the values of cells K5+O5+S5+W5+AA5+AE5

ONLY IF the values of Cells H5, L5, P5, T5,X5, AB5 is equal to AI3 (which is a symbol (wingding 3 for capital L), this again occurs 3 times out of 6, sometimes twice and again at random, so again I need the sum of 3 cells (or 2) out of 6, based on the above criteria.

Using the formula: =IF(T5=AI3,(W5),0) , I retrieved the value of cell W5 into cell AI5; and when I tried the formula :

=IF(T5=AG3,(W5),0) , in cell AG5, it returned the value 0 which is correct again, as T5 was equal to AI3 but NOT to AG3.

But I cannot work out how to add all of the three values relating to each symbol, independently in each column, as they each occur randomly.

This is the way it looks in the spreadsheet:

Cell H5 Cell I5 Cell J5 Cell K5/ Cell L5 Cell M5 Cell N5 Cell O5/

Symbol (for P) Text Text Value /Symbol (for L) Text Text Value

And so on 4 more times across.

I could of course add it all manually for each line but I would rather have a formula to do it for me as all the other calculations are automated and/or conditionally formatted.

I have 3 different costs for an item, 15 different items. The costs very depending on the delivery state. Is it possible to write a formula that will retrun the right cost for an item, depending on the delivery state?

if ap10 = "CA" then look up in range AH2 to AH18

if ap10 = "GA" then look up in range AI2 to AI18

if ap10 = "ME" then look up in range AI2 to AI18

if ap10 = "PR" then look up in range AI2 to AI18

if ap10 = any other state then look up in range AG2 to AG18

cell AP10 being the cell that the state is listed in

Cell U10 being the cell that item is in (a drop down menu)

This is the vlookup formula that will get one price.

=IF(ISNA(VLOOKUP($U10,TABLE!$AF$2:TABLE!$AI$18,2,FALSE)),"",VLOOKUP($U10,TABLE!$AF$2:TABLE!$AI$18,2, FALSE))

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 am trying to create a simple formula to extract cost from a total that includes both cost and and a percentage for maintanance. Assume $100, 10% of which is maintenance the remainder is cost. If I just subtract 10% from $100 I get $90, however 10% of $90 is $9 which equals $99.

View 8 Replies View RelatedI need a macro to calculate the cost basis of the inventory. ( Field F3 and F4 in Sheet1)

Please refer to attached sheet....

use the “INDEX & MATCH” formula and I thank him very much for that. Now, I have another problem which I think the “IF & ELSE” statement may come into play. I am trying to calculate the cost of a cushion that is governed by it’s thickness.

Example:

ABCDEFGHI

1CushionB/FrontS/SideBorderQuantitySeat

2Feather121251£8.25

3Feather121241£7.32

4

B2 represents 12 inches

C2 represents 12 inches

D2 represents 5 inches

144 represents square foot

The calculation is,

F2=B2*C2/144*D2*£1.65*E2..............

I am trying to keep a running total of cost, here is what I am doing

A B C D E F G H I

Date Books Pencils tax Month Books Pencils Tax Total

1/2/2014 11.50 2.50 .76 January 35.85 13.50 3.44 52.79

1/5/2014 14.50 3.85 .83 February 10.95 1.50 .68 13.13

1/6/2014 9.85 7.15 1.85

2/6/2014 10.95 1.50 .68

I only used Jan and Feb as an example. So as you see on the right side I will have all 12 months for the year and I just want all the totals for each month to auto calculate. I will have between 15 and 30 entries each month.

I am attempting to create a time chart that keep track how long a particular person works on an individual project to finally calculate profit & loss. Attached is the spreadsheet.

I am using invoice #'s to differentiate the jobs but have no clue how to make it calculate how much time was spent per job, per person then calculate the total cost per job per person?

sheets.time chart.xlsx

2014 tab- grey column is installer and white is shop employees

profit loss tab- all calculations done here

payroll tab- all cost data here

I am trying to put together a spreadhseet that will calculate the future cost of education fee assuming a given inflation rate. I would like the reuslt to be highlihted in a list or a table. The user would only need input current fee, inflation rate and years where fees are payable. The spreadsheet would do the rest. I have laid this our best I can attached.

View 5 Replies View RelatedI have built a darts scoreboard on Excel 2007, to track scores but also record statistics such as 3 dart average and -checkout success. As I don't know how to write macro's I've created it using formulae only - it's not fancy but does the job!

I have a number of .WAV files that call out the main scores - 60, 100, 140 and 180 and I'd like the scoreboard to play the relevant file when someone scores that amount. I've trawled through lots of forums but have been able to find one that does exactly what I need it to, and I don't know how to adjust the ones I've found.

This is how the scoreboard works:

There are 25 different sheets, one for each leg of the match that could be played. The sheets are named Leg1, Leg2, Leg3 etc.On each sheet, Player 1's scores are entered in cells J15 to J47, and Player 2's scores are entered into cells O15 to O47Once Player 1 or Player 2 wins the leg, I move on to the next sheet to play the next leg. All the scores entered remain in each sheet until the match has ended

This is what I need the macro to do:

When a score of anything other than 60, 100, 140 or 180 is entered, do nothingWhen a score of 60, 100, 140 or 180 is entered, play the relevant .WAV fileIf, for example, Player 1 scores 60 the '60' .WAV file plays. 60 will be left in J15, then Player 2 may score 100, which will be entered into O15 and should trigger the '100' .WAV file.Note that either Player 1 or Player 2 can start the leg, so the order scores are entered between column J and column O can differ.

I wish to select all the cells which contain the word " Available " in column A at once . But i didn't find any option to do so using excel's Go TO Special feature.

I am using Excel 2010.

If my file is like so

A1 B1 C1 D1 E1

A2 B2 C2 D2 E2

A3 B3 C3 D3 E3

i NEED THE OUTPUT IN NOTEPAD

A1B1 C1 D2 E1

A2 B2 C2 D3E2

The spacing varies from row to row

if i am saveing as notepad file....in macro how can i give the spacing properties give an example macro and i will customize it according to my need

I have two sections to this spreadsheet: 1) the entity section; 2) the order section.

I have created a macro to insert rows in both these sections. The macro appears to work fine for the first section but not for the second section. The problem I cannot solve is in respect of cells D9:D10. The macro is as follows:

I hold some data in the Excel 2007 file. It's about 400 rows and about 10 columns. On occasion I have to print some values from this file. But it has to be in defined position on the paper. It is like I already have the prepared paper with fields, and I just have to fill some fields on the paper with data from the columns. Is it possible to make a program that will take each row that I label for printing, and print just some columns but in a defined position on the paper ?

View 5 Replies View Related
Copyrights 2005-15 www.BigResource.com, All rights reserved