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


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

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

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

How To Print Multiple Inventory Labels Based On Inventory Levels

Sep 10, 2012

How do I print multiple inventory labels based on our inventory levels?

For example:

Item No 1000
Descr Window
Customer Taylor
PO 9001
Quantity 10

Item No 1010
Descr Door
Customer Jones
PO 9011
Quantity 35

I want to print 10 labels with the info from Item No 1000 and 35 labels from Item No 1010????

View 8 Replies View Related

FIFO Inventory- COGS & Inventory Valuation

Aug 18, 2009

I had been trying really hard to get some solution on COGS valuation & inventory valuation on FIFO basis. my daily transactions are typical sales & purchases.

Attached is the inventory in/out movement from Quick Books. in the column "Num" type bill is the entry from purchase bill whihc always has a reference as P/O####. This is how i will capture the landed cost against a PO., another type "Inv Adj" is inter warehouse transfer. Name is cusotmer, Inventory is my item number. in some cases it is like "2000", in some cases it is 10000:10121, and in some cases it is 10000:10200:10201 that is why they fall in different columns when i export them.

What I would lilke to do: 1) Run a report by month, by customer showing cost of goods sold on FIFO basis, I can capture sales amount by running another report.

View 11 Replies View Related

Eliminating Sold Inventory From The Inventory List

Aug 8, 2006

I am not an excel whiz, so I will need layman's terms if possible. I may have found a solution to my problem under another thread, but I couldn't understand it. I have only briefly worked with macros, a very long time ago. Here goes...

I have an inventory list that has not been updated for sales of the inventory, only for purchases. I have sales lists, generally by month, in separate spreadsheets. I am looking for a simple, efficient way of either eliminating or at least matching up the data in the sales list to the inventory list in order to remove sold items from inventory (on paper). I am hoping the result will be a fairly accurate inventory list, and related value, so that a physical inventory count won't be necessary at this time. Obviously I could copy all of the sales info into one spreadsheet, sort by inventory number, then manually delete all sold items from the inventory listing. I am REALLY hoping there is a better option.

View 6 Replies View Related

Second Tab Is Inventory Numbers And The First Tab Has My Inventory Items

Feb 27, 2007

I have a workbook. The second tab is inventory numbers and the first tab has my inventory items. I scan in the inventory number (unique) and it adds it to my inventory sheet. As I use inventory I scan the barcode and it inputs the serial number into my used inventory tab. What I need to do it when I scan the serial number for used inventory into the used inventory sheet to have it go to the inventory sheet and delete the line for that serial number.

View 9 Replies View Related

Precentage Profit Formula

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

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

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

How To Split Profit Margin Into 5 Slices

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

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

How To Make Profit And Savings Equal

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

Totalling A Win Loss Column

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

How Do I Write Formula To Show A Profit Margin

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

Formula Needed For Calculating Profit On Sales

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

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

Tracking Weight Loss In Excel

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

Pivot: Gain/loss Over Time

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

Adding Icon Of Gain Or Loss

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

Formatting Dates To Won-loss Values

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

Formula For Calculating Win/loss Streaks....

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

Setting Sell Price To Specific Profit Margin?

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

Hedging Simulation, Solver To Obtain A Maximum Profit

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

Find In The Win, Loss, Draw Columns When There Is No Entry

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

Unable To Create Simple Win / Loss Chart

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







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