Calculate Profit And Loss Data Based Off Lots Of Account Criteria?

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


ADVERTISEMENT

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

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.

View 4 Replies View Related

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?

View 11 Replies View Related

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.

View 1 Replies View Related

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

View 14 Replies View Related

SUMIFS - Record Profit Or Loss On Daily Basis / Display Total For Last 7 Days

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

Complex If Statement To Calculate Date Range From Create Account To Stop Account

Sep 7, 2012

Creating a formula that basically has a big IF statement. I hope this makes sense, so here goes. I am looking to find the "age" or "elapsed days" of an ID from when they created an account to when they stopped. This might be very complex and if there's not a way to calculate the Elapsed days (even though that's what I prefer), perhaps there is some formula that can return a value like a "1" or "True" or something that lets me know the statement is true.

original formula from post 3261273
{=IF(D2>0,A2-INDEX(A$2:D$16,MATCH(B2&"1",B$2:B$16&C$2:C$16,0),1),"")}

New formulas needed or revised from the above:

Formula 1: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Create to Suspend but NO Stopped)

Formula 2: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped) OR Column 6 (Unsuspend), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Suspend Only, No stopped or Unsuspend)"

Formula 3: The ID in column 2 does not have to be the same, but if there is a value in column 3 only (Create) and there is no value in the Suspend, Stopped, or Unsuspend columns, then place a "1" in the cell. My manual calc is the "Create Only" column.

Event Date
ID
CREATE
Suspend
STOPPED
Unsuspend
Elapsed Days (Create to Suspend but NO Stopped)
Elapsed Days (Suspend Only, No stopped or Unsuspend)

[Code] .........

View 1 Replies View Related

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.

View 7 Replies View Related

Calculate Data Based On Laid Down Criteria?

Jul 26, 2013

I am trying to create a sheet which will automatically calculate data based on laid down criteria.The criteria are as follows:

Cab Type in cell () Indica

If the value of cell () is less than or equal to 40Km then cell (B3) will be 400.

For every extra km above 40 the cell value in cell (B2) will be extra km done multiplied by 8 in cell (D2)

If the value of cell () is greater than 40 but less than or equal to 80 then cell (B3) will be 800

For every extra km above 80 the cell value in cell () will be extra km done multiplied by 12 in cell (D2)
Cab Type in cell () Indego

If the value of cell () is less than or equal to 40Km then cell (B3) will be 600.

For every extra km above 40 the cell value in cell (B2) will be extra km done multiplied by 14 in cell (D2)
If the value of cell () is greater than 40 but less than or equal to 80 then cell (B2) will be 1200

For every extra km above 80 the cell value in cell (B2) will be extra km done multiplied by 16 in cell (D2)

Ther are about 5 different types of cabs in the sheet.

Cab Type Provided
Total KM run
Basic Amount
Extra km

What I want to achieve is as under:

1. If value of cell A1 = Indica and value of cell B2 is entered as 40

Then the values of cells (C2:e2) should populate automatically as 400, 0, 400

2. If value of cell A3 = Indica and value of cell B3 is entered as 40

Then the values of cells (C3:e3) should populate automatically as 400, 40, 440

View 8 Replies View Related

Mapping Account . How To Add Additional Data In To Account#

Aug 21, 2009

I have two sheets. First contains Department # 0002549, and the other one contains 5 more digits(#02001 0002549). I will need to find the exact match for the first sheet to contain all 12 digits. THey are all in different order, so I probably have to match them first then add whatever the first 5 digits number. How should I tackle this issue? I was thinking to use vlookup but how could you use vlook up if only last 7 digit matches and add addtional digits to exisiting number?

View 9 Replies View Related

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.

View 14 Replies View Related

Calculate A Multi-variable Equation Based Off Of Data In A Table With Different Criteria

Jan 8, 2010

I'm trying to calculate a multi-variable equation based off of data in a table with different criteria. If you reference the attached spreadsheet I am trying to calculate cost based on weight and zone. For the data entered in cells B1 and B2 944.01 would be the cost. If you look at the table in G2:N3 you'll notice that the values in G2 and G3 are the weight limits G2 is from 0-4999 and G3 is 5000+, this should impact where the value in B1 falls. B2 should select from H1 to N1.

View 3 Replies View Related

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

View 13 Replies View Related

Index Function - Data Identified Based On A List Of Account Numbers And Return This Data To A New Sheet

May 8, 2007

I have a multiple column spreadsheet (Call it- "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet.

In "Money" I have:

IE; columns B, C respectively have cust #A100 & 20.00
columns E, F respectively have cust #B100 & 40.00
columns G,H respectively have cust #C100 & 60.00
Etc.

(above for illustration-there are 100 lines of data in these columns with varied account numbers and respective dollars)

So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes.

I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....

View 17 Replies View Related

To Calculate That Loss Or Gain As A Percentage Of Their Original Weight

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

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

View 4 Replies View Related

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?

View 9 Replies View Related

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.

View 2 Replies View Related

Calculate Difference Based On Criteria

May 5, 2013

I have a number which consist of 12 digits such as 765432123345. The first two digits denotes the year. I need to create a formula in the next column to calculate the difference in years by comparing to the current year of 2013. In this scenario, the difference will be 37. Is there a way to do this?

View 5 Replies View Related

Calculate Increase Based On Criteria

Jan 17, 2009

We typically give out Free Rents (usually a few months) to our tenants, which is always up on the front. And then we would charge a rent increase ($.50 per year in the example) starting 12 month after the rent free rent ends and every year (12 months) afterwards. The rent will be flat between each increase. My questions have two parts:

1. Is this something array formula can handle in one formula?

2. Since I am not an expert on array formula, what I ended up doing was to create a line for "Rent Bump Date" which is defined as the Free Rent + 13 for the 1st rent increase (meaning if you dont get free rent you would start paying increase in month 13 and every 12 months after. And I put in what the rent would be for each rent bump date. My solution is if the rent for each month (1 - 240) would be determined based on the ranges of those bumps. For example,

Month 1-15 Rent $20
16-27 20.50
28-39 21
40-51 21.5
52-63 22
64-75 23
......
......
......
232-243 30......................

View 5 Replies View Related

Calculate Wages Based On Criteria

Jun 8, 2006

I am working on my companies tax reports for the first time. The wages for unemployement and FUTA do not calculate IF the total wages are over a certain amount.

Im trying to get the formula to see if the total wages are over $7000 then dont calculate. Ive tried several different things from the links, but still can't make it work.

total wages are $7800, so i only need to calculate it on $7000 and then not calculate it again for the next quarter.

View 3 Replies View Related

Calculate Percentage Based On Criteria

Jul 29, 2006

I have events in column 1,in my sheet there are about 800 events.

In col 2. each contestant has a label,blue(b),green(g) or red(r)

In column 3 I want to print the % of blue + green of the total for each event,

e.g first event in attached sheet would show 77.7% down to row 10 in col 3 etc.

View 9 Replies View Related

Formula To Calculate Based On Criteria

Apr 20, 2007

I have a spreadsheet that has staff id in one column and the work items number that they have done in a daily basis in another column.

The actual list is very long. I need to summarize in another column how many work items that they have completed in a daily basis.

I have attached a sample spreadsheet as an example. I would need to summarize in column H based on the staff ID. Some work items are shared by two staff but it will have to be counted as one work item completed for each staff. If work item B123466 is completed both by staff M56 and M54, then it will be counted as one for each.Currently, I am doing this manually with the filter function which is very tedious and often has mistakes. I would like to formularize this task.

View 6 Replies View Related

Calculate Rank Based On Criteria

May 20, 2007

I want to calculate the sales rank of the customer whose CUSTOMERID is equal to my ID? Note that sales rank is what position the salesperson is in terms of DOLLAR VALUE OF SALES.

Just a bit stuck on this. How do I calculate the rank? Is it a If statement or a Vlookup Question?

The spreadsheet can be found here: http://www.megaupload.com/?d=5D0YCHIG (It's too big to be uploaded to the forum hosting).

View 9 Replies View Related

Calculate Volume Based On Criteria

Dec 22, 2007

Need a way of getting one of 4 values in sheet 2 to perform a calculation with. The aim is to show the total volume of a given item. Because there are four data sources for the actual volume (with varying degrees of accuracy) I've incorporated all of them in one sheet but want to use the values in precedence i.e. the first figure is where I've had someone physically tell me how many fit to a pallet (cubic metre) and know that to be accurate. If the value there is 0 I want to use the next figure as certain other items were measured physically. Column three and four are from some legacy systems and therefore should only be considered if the figures are 0 in the first two columns. Rather than ramble on any more I've attached a sample sheet

View 4 Replies View Related

Calculate Formula Based On Cell Criteria

Aug 30, 2006

I only want the formula to operate when there are values to calculate and then ONLY refer back to the last calculation. If there are gaps between the calculations I would like those to remain blank. Is that possible?
I have attached an example of what I am trying to do.

View 3 Replies View Related

Rank A List Of Teams Based On Their Win/loss Record

Oct 11, 2009

I want to rank a list of teams based on their win/loss record. The list is something as follows.

Team A0-3
Team B0-2
Team C2-0
Team D1-2
Team E2-1
Team F2-1
Team G0-2
Team H3-0
Team I1-1
Team J1-1
Team K2-1

The column with the actual score contains the formula, =INDEX('2009'!$C$3:$BL$3,MATCH($A4,'2009'!$B$1:$BJ$1,0)). I would like to rank the list to something like,

Team H3-01
Team C2-02
Team E2-13
Team F2-13
Team K2-13
Team I1-16
Team J1-16
Team D1-27
Team B0-29
Team G0-210
Team A0-311

View 4 Replies View Related

Checking Two Lots Of Data For Any Differences

Jun 4, 2009

I need to make sure that the two sets of data i have highlighted in yellow and blue are completely the same. Please can some one write a formula that will flag a cell next to the blue column indicating that there is a difference between that of the blue and the yellow columns individual row.

View 3 Replies View Related

Calculate Median For Multiple Non-Sequential Columns Based On Criteria

Sep 27, 2012

I have long series of data in 8 columns (B, F, J, N, R, V, Z, AD) starting at row 5 down to row 86404. I want to find the total median for each row, where a condition is met for that row. The condition is if number>10 and the number is located in row 3 for each column (i.e., B3 for column B). So, if B3, J3, R3 are all >10, then I want to find the median for all the numbers in the columns B,J,R.

I assume there is an array formula that could do this? I've tried a few solutions with no luck.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved