# 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))

View 14 Replies
ADVERTISEMENT
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.

View 4 Replies
View Related
Jun 15, 2014

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.

View 1 Replies
View Related
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?

View 11 Replies
View Related
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.

View 1 Replies
View Related
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.]

View 14 Replies
View Related
Dec 24, 2011

I have a spreadsheet to record profit or loss on a daily basis. The figure for each day can therefore be positive, negative or zero.

I want to add a column to display the total for the last 7 days (NOT the last 7 calendar days), in which either a profit or a loss was recorded (so excluding any cell that is zero).

I would prefer to add (insert), the column for each day as it comes and the range would obviously vary if the new day's figure was not zero.

View 9 Replies
View Related
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.

View 14 Replies
View Related
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).

View 13 Replies
View Related
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.

View 7 Replies
View Related
May 1, 2009

I run a slimming club and would like to use Excel to keep records of members weights, losses, gains etc. What I would like to do is enter their starting weight into a cell then each week when they are weighed enter a loss or gain in an adjacent cell. I would then like Excel to calculate that loss or gain as a percentage of their original weight. Is this possible?

View 11 Replies
View Related
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

View 4 Replies
View Related
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?

View 9 Replies
View Related
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.

View 3 Replies
View Related
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,-

View 1 Replies
View Related
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.

View 2 Replies
View Related
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

View 9 Replies
View Related
Sep 27, 2009

I have two columns for a Home and an Away win-loss records for a team over a few seasons. The win-loss stats in each column are in the form 24-13 (2-two digit numbers with a hyphen in between). How can I get a total at the bottom of the column for all the win-losses?

View 9 Replies
View Related
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.

View 10 Replies
View Related
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.

View 6 Replies
View Related
Aug 2, 2008

How do I track my total weight loss in excel? Here is an example of what I am trying to do.

8/1/2008 228lbs

8/2/2008 225lbs

8/3/2008 223lbs

Total 5lbs

I would like use a formula that would subtract my absolute weight loss total day by day in August. Basically, I want the total "cell" to equate to my total weight loss in August. For Example after 8/3/08 the "total cell" should equate to 5 lbs. What formula do I use to do this?

View 6 Replies
View Related
Feb 12, 2009

I have a excel 2000 spreadsheet that has beginning and ending dates for certain services. I'm trying to tease out what our customer base has been doing over time in aggregate- i.e. how many customers subscribing to X in a given month/year.

The only way I've been able to do this in the past has been generating pivot charts of start dates (with 'start mo' and 'start year' busted out) as a positive count (+1), stop dates (with 'stop mo' and 'stop year' busted out) with a negative count (-1), then copying these tables into yet another page where I mash them together to get the 'flow' of our customer base over time and generating charts from there. Needless to say, it seems a whole lot more clunky than it should be, and I'm having a hard time searching the innarwebs for similar examples (even though I'm sure it's been done before). Example:

View 2 Replies
View Related
Dec 4, 2012

I have the data in

A1=30 and B1=40

A2=60 and B2=20

And I also 2 small Pictures ie picture1 and picture 2 which i insert in same excel sheet. I wanted to lookup C1 cell , if A1 is greater than B1 then Picture1 or vice versa.

View 1 Replies
View Related
Jan 12, 2010

I currently copy/paste sports data from the web into MS Excel. One of the columns pasted contains "Won-Loss", which appears as '1-0' on the web. However, once the data is pasted, Excel identifies this field as a date and changes the value of '1-0' to 'Jan-'00. I would like to insert a new column and convert the field back to text (i.e. Jan-00 to 1-0). I have attempted several formulas, however no luck as of yet.

View 9 Replies
View Related
Jul 6, 2004

I have data from a tems current win/loss record. I want to calculate what the longest win streak is and the longest losing streak as well. What formula do I use?

View 9 Replies
View Related
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.

View 3 Replies
View Related
Apr 18, 2007

Im setting up a fixed hedging simulation using excel and want to use solver to obtain a maximum profit. I have taken historical stock prices and then predicted future prices using the random walk process. To create the hedge I am using european calls and puts in both long and short positions. The simulation has been set up so that the options are being brought/sold when the historical data ends and then sold/payed when the predicted data finishes.

I am letting solver change the values of the strike of each of the options and also the amount purchased/sold for each of the options too! My constraints are that all the strike prices have to be >= 0, all the amounts of each particular option has to be >= 0 and that profit has to be >= 0. It is worth noting that as well as the values already mentioned, d1 and d2 values are also being calculated on the sheet which are then used to calculate the value of the options being used (using Black-Scholes model), this value is then given a realistic spread value and then 3% is added on as the brokers fee. All of the values mentioned are being taken into consideration when caluclating profit.

View 6 Replies
View Related
Oct 16, 2009

I m trying it out I find in the Win, Loss, Draw columns when there is no entry the formula leaves a 1 when there should be a 0 in the Draw column.

View 4 Replies
View Related
Dec 4, 2012

I have a # of games played column (Column A), a # of games won column (Column B), and a number of games lost column (Column C).

I'm trying to make it so Column C automatically enters the number when I put in the number in Column B.

Example:

Games Played........Win........Loss

1.........................1...........0

2.........................1...........1

3.........................1...........2

4.........................2...........2

For the fifth game played, if I won it, how do i make the 2 to automatically enter into the loss column when I put in the number 3 in the win column?

View 2 Replies
View Related
May 15, 2014

Whenever I save and close the attached, after selecting the NO on line 23, I lose all the buttons associated with my macros. If I save with everything expanded, no rows hidden, my buttons stay put.

View 1 Replies
View Related