Free Microsoft Excel 2013 Quick Reference

Inventory: FIFO, LIFO and Average Cost

.
.

Hello,

I need to compare and calculate the Unit Cost Price of my Inventory based on the 3 methods of inventory valuation: FIFO (First In, First Out), LIFO (Last In, First Out) and Average Cost.

Next, I enclose 3 snapshots of each method with the results required (columns color yellow).

Thanks in advance for your help.

regards,
Pedro
.
.


hi,
I have an inventory (attached) and I would like to know how to attach a fill-in-the-blank form to buy/sell/use items from the inventory. this form will also change the quantity in the data inventory worksheet along with the average unit cost, and total unit cost. Can you great people help me. I am reading the VBA info but it is going to take alot of time. I would like training/tutorial in near future.

I've written a custom function to calculate the weighted average cost of an inventoried item. The function works as expected when I have an inventory sheet selected. When I select a non-inventory sheet, the function in the same cell that was just working returns #VALUE!. In the sample attached, select the "Items" [green] tab and hit calculate. Next select the "Inventory" tab and you'll see my function highlighted in red reflecting #VALUE!. Hit calculate and the function works correctly. I believe this is a VBA error, but I'm not a programmer. I'm using a UDF because the sumif formula was mega in size.


	VB:
	
 Single 
     'STR1 identifies the specific item       [a refrigerator]
     'RNG1 identifies the Description column  [refrigerators, stoves, supply lines, everything]
     'RNG2 identifies the Transaction column  [Purchase, TransferIN, TransferOUT, Installed, Sell / Dispose]
     'RNG3 identifies the IndexB column       [this is a calculated field reflecting total cost (QTY * Price)]
     'RNG4 identifies the QTY column          [this is an input field reflecting the quantity of each transaction]
     'RNG5 identifies the current row - could be pulled from STR1 if dimensioned as range  [identifies current row]
     'iSheets identifies the current sheet index - used "with / end with block"  [identifies current sheet]
    Application.Volatile 
    Dim iRNG1Col As Integer, iRNG2Col As Integer, iRNG3Col As Integer, iRNG4Col As Integer 
    Dim iStart As Integer, iStop As Integer, iSum As Single, iCnt As Integer 
    Dim rCell As Range 
    With Sheets(iSheets) 
        iRNG1Col = RNG1.Column 
        iRNG2Col = RNG2.Column 
        iRNG3Col = RNG3.Column 
        iRNG4Col = RNG4.Column 
        iSum = 0 
        iCnt = 0 
        iStart = Range("DetailBeg").Row 
        iStop = RNG5.Row 
        For Each rCell In RNG1 
            If rCell.Value = STR1 Then 
                Select Case rCell.Offset(0, iRNG2Col - iRNG1Col).Value 
                Case "TransferIN", "Purchase" 
                    iSum = iSum + rCell.Offset(0, iRNG3Col - iRNG1Col).Value 
                    iCnt = iCnt + rCell.Offset(0, iRNG4Col - iRNG1Col).Value 
                Case "TransferOUT", "Installed", "Sell / Dispose" 
                    iSum = iSum - rCell.Offset(0, iRNG3Col - iRNG1Col).Value 
                    iCnt = iCnt - rCell.Offset(0, iRNG4Col - iRNG1Col).Value 
                Case Else 
                    iSum = "#N/A" 
                End Select 
            End If 
            iStart = iStart + 1 
            If iStart >= iStop Then 
                Exit For 
            End If 
        Next 
    End With 
    PRICESUM = iSum / iCnt 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


In the attached Sheet how can I work out how much of each item is purchased per month and what is the average cost per purchase and unit. I know I can do this using sort and/or filter, but I want it automatically done on a seperate Worksheet for each month. For example - we bought chicken 5 times at a total cost of R1820 which means an average cost of R364 per purchase or R88.706 per unit (kg). If I use sort, once the data is sorted I cannot revert it to the original state.

I hope some clever soul can help ....

The organisation I wish to report on has many cost centres which each contain many people. These people are on many different grades, and each are on differing salaries (even those in the same grade).

One “reporting group” has many cost centres.

There are several reporting groups.

I need to report on average salary per grade / per reporting group.

I have attached some dummy data. The “rep group” tab displays the reporting groups and the cost centre mappings (ie reporting group England contains cost centres 1, 2, 3, 4, 5, 6 and 7). Note in reality the cost centres are not this simple, they are 6 digits and varying ranges.

In the “salaries” sheet each individual is listed along with their cost centre (in column c) and their grade (column d). Their salary is shown in E.

I hope this is clear, thanks for looking!!

Hi there, i'm looking for a little help on writing a formula for a work spreadsheet. I work in transportation deal with a a lot of financial information thats categorized by week number. I'll attach a mock spreadsheet as an example of what I'm working with.

I'm not even sure what formula to begin with, whether it's =if, some form of =vlookup, i have no idea. So any help would be very appreciated!

What i need to do is have it so cell B4 in the Reference Tab (Week 1 for 2011), looks at the Weight tab, and finds both 2011 and Week 1 in the same row for everything that applies (in this case, i need it to look at cells A172:F175), add everything that applies from the F column, and divides it by everything that applies in the E column.

So I need a formula in B4 in the Reference tab that basically says:
"Find everything that applies to the week on my left, for the year above in the Weight Tab, and average weight for that month and week"
I'm not sure how to word that better, but if i can explain things better or answer any questions, i'll happily do what i can.

The second formula i need (also in the Reference tab) would go into i4, i'd imagine this is damn near the same formula, if any differences at all. Similar to the one above, i need it to look at what week and year it applies to, then looks at the "Costs" tab (in this case, A172:F175). Sums "Expense", and divides it by the sum of "Total Profit" for that specific Week and Year.

I'm looking to find a way to do this as automated as possible as this is part of a very thorough daily running report, but i'm looking to avoid VBA and Macros as much as possible since it's a large corporation where using either of these have been disabled on 90% of computers other than about 4 people in my office. Any help is more than appreciated, thank you!

I have a file,in a sheet,there are 36 models with category wise cost.Say, model-SPRFDRKCC and category wise cost-CKD,LOCAL,E.Oil etc.Now,I want the average cost of all the model with each category.For an example,I want to know the average cost of CKD of all the model in the month of Nov,11.Can it be solve from an excel formula?The average cost should be come as I have mentioned in my file on cell C366.

Please refer attached file for more clarification.

Regards

Pradeep Kumar Gupta

Hello Everyone

I think this is a simple question, but still I am messing up the formula. Hope you guys can help me out. I will truly appreciate for your help.

I have column A with a given list of City Pairs, and the costs associated are listed in Column C> NOTE that Column A can have multiple same City Pairs, for example in Column A, Row 3 to Row 9 have same city pair ATL to DFW (listed as ATLDFW). and now coming to the main problem, I have in column E desired set of city pairs that I want the average of costs among the given set of pairs (Column A).

in order for it make sense I have attached a copy of the excel spreadsheet that i have been working on.

Please help me with this issue guys.Help 2.xlsx

*EDIT* Ok, I figured i would try to better articulate what it is im looking for.

Refer to the attached sample workbook,
(Keep in mind that there is an error in the sample workbook; Im not sure how to remove and re-attach the sample workbook as this is my first day on this forum.)

Cell F19 should contain =D19*B19

Column 1- These values will not change and represent price/cost of each UNIT
Column 2- "UNITS" the quantity or number of items at column1 value
Column 3- "$ AMT" the total cost of UNITS at that price/cost, i.e. Column1*Column2

Im looking for a way populate the values in Column2 when, "NUMBER OF UNITS" AND "AVERAGE COST PER UNIT" are changed

Ideally speaking, As few units in one row as possible to allow for a good mix.
For example: What i don't want is $9000*86 for 774k.

If it helps: "AVERAGE COST PER UNIT" will always be a whole number rounded up to the nearest 1000.

I believe a Most Even distribution of units will work fine.

After the distribution/population of fields in column2;

D29 must equal F3
F29 must equal F7

I'm looking for something that will populate the values in D12:D27 while adhering to the two conditions above. Even when the Input at F3 and F4 are changed.

The values in "UNITS" represent the number of a specific cost item in inventory when added together do not exceed the "TOTAL COST OF INVENTORY". Depending on store location, the "AVERAGE COST PER UNIT" and/or the "NUMBER OF UNITS" will change. It will be used to help forecast how many of a specific cost item should be purchased based on the overall average unit cost without exceeding the budget or inventory space.

Hello All,

I have a spreadsheet with lots of batches, but the example I am attaching has only 2 batches.

Now my problem is, I have a function that determines when the GRIND TIME starts and ends in this batch. So, what I am looking for is capturing the average temperature of a chemical during Grind Time.

I am having trouble telling Excel to average a group of number between two times.

For Example, in the worksheet, you will notice that

Batch "B0401" has a Prod Time of 7:12 (sheet3) and a Grind Time of 7:52
Batch "B0402" has a Prod Time of 9:51 (sheet3) and a Grind Time of 10:51

I need to find these times, based on the batches (Lot ID) in sheet4 and average the "mixer 2 water flow" during this time only. This average should go into the "chill water flow ave" column in sheet3

I have a large number of batches for each month in many worksheets, so a function that I can drop and drag or a vb code would be great.

Clear as mud? Thanx for trying!

mctld

Hello, thanks in advance to anyone who reads this and attempts to help.

I have a very large data set in which specific cells need to be averaged. The cells that should be averaged are in a predictable locations relative to one another.

For instance, Cells A3 and A14 need to be averaged, and then cells A25 and A36, and then A47 and A58 and so up to A400 or so. My current formula will average A3,A14, but then averages A14 with A25 which is not what I want.

Do any formulas come to mind to solve this? Perhaps averaging the original cell and 11 after it, skip 11 cells and average that cell and 11 after, etc...

I am not sure how to accomplish this. Anything pointing me in the right direction would be much appreciated! Please let me know if the original .xlsx file is need.

Thanks again.

Hello all,

I have a mechanism in which I enter the details of a trade (whether I have bought or sold a security) into cells D5:D14 in the 'Trade Blotter' worksheet and when I press 'Add to Portfolio' (in cells A1:B2), each trade is subsequently moved to a list starting in row 25. The aim of this is to keep a historical track record of every security I have ever bought or sold.

What I am looking to do is to organise these individual trades by security on 'Sheet 3', so that whenever I add another trade into the 'trade blotter' tab, it automatically updates the information on 'Sheet 3'. So for example, if there have been 2 trades with the security ticker BER historically on the 'trade blotter' sheet (which can become thousands of entries), it will show the aggregate values of these 2 trades (i.e. how much they cost total, how many shares were bought total, etc) and display these details in 'Sheet 3'.

The information I am looking to aggregate for each security is in the 'Trade Blotter' worksheet by individual trade. The information I need to aggregate is the total shares owned (formula shown in cell B2 'Sheet 3'), total cost (formula shown in cell C2 'Sheet 3') and average cost per unit (formula in cell D2 'Sheet 3').

There will be thousands of trades over time and many different securities, so the goal is to have 'Sheet 3' become a summary sheet that can tell me at any moment, how many shares are owned of each security, the total cost and the average cost. As the securities are not known in advance, I will not have every single security listed in column A of Row 3, thus it is important that where it is the first occurence of a security in 'Trade Blotter', that security is automatically inputted in column A of 'Sheet 3' (this is where I believe VBA would be required).

I have spent the past week at about 4 hours a day doing this and cannot figure out how to achieve this, so I appreciate any help that can be given.

Hi

I'm looking to use a command button which upon a click, ask the user which years from the 15 years of columns I have to sum and average.

So I imagine it would be like click the comm button,

1st question, which years would you like to sum (the years could be seperated by a comma.. because of the 15 years where each column represents each year, a user might want year 1 to 3, than 5 to 7).

I imagine would have to use the special tool, like when you chart a number of rows, than seperate with comma... where you highlight a section or range, than seperate by comma and highlight another section of a range of rows..?

Or without highlighting physically the range, we enter the years into the input box, with a comma...

or another way is when a commnand button is clicked, a userform with 15 radio box is shown, each radio box signifcying each year of the columns, and when you check it it signifies the years to add and average.

problem is also, i have like say 15 rows of line items, don't want to create a userform for each one, so the userform would or should have a dropdown box that reads the actual rows to designate

for example

Line Items (col 1) Year 1 (col2) Year 2 (col3) ... Year 15 (col16) Sum/aver (col17)

Revenue
Expenses
COGS

From the above, once we select the years would sum the chosen years and divide by the no. of years chosen and dump this into col 17 for each of the respecdtive line items.

any help appreciated.

Hi guys,

This is too complicated for me, so i am asking anybody's help to formulate Excel formulas to obtain the average buy price and average sell price for me to do this futures trading. Thanks a lot. I downloaded the Htmlmaker to post the spreadsheet here to show the manual way to calcualte the average buy price and average sell price but when it is on html form, i clicked on the 'Please click this button to send the source into clipboard' button & then i paste into this thread. Is the way to make my spreadsheet appear here correct cause it cannot work. Please help.

I am logging weather data in 15 min intervals. However, other people on the team want data in hourly intervals. I have found a script that inserts a new row after every fourth row and averages the data in column A. I would like to average the data in columns A through H.

Sub HourlyDataSum()
'
' HourlyData Macro
'
'
Const xlShiftDown = -4121

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("C:Scripts4-9 to 4-17 Weather Data-1")
Set objWorksheet = objWorkbook.Worksheets(1)

i = 1
j = 1

Do Until objWorksheet.Cells(i, 1) = ""
If j = 5 Then
Set objRange = objWorksheet.Cells(i, 1).EntireRow
objRange.Insert (xlShiftDown)

strFormula = "=average(A" & i - 1 & ":A" & i - 4 & ")"
objWorksheet.Cells(i, 1) = strFormula
objWorksheet.Cells(i, 1).Interior.ColorIndex = 44

j = 0
End If
j = j + 1
i = i + 1
Loop

If j 0 Then
Set objRange = objWorksheet.Cells(i, 1).EntireRow
objRange.Insert (xlShiftDown)

strFormula = "=average(A" & i - 1 & ":A" & i - j + 1 & ")"
objWorksheet.Cells(i, 1) = strFormula
objWorksheet.Cells(i, 1).Interior.ColorIndex = 44
End If

End Sub

Any help would be greatly appreciated.

A scoresheet in excel returns 5 separate scores. I need to ignore the
highest and the lowest figures and average the other three to get a result.
How do I do this. I have tried
=IF(H71B74,B75,B76,B77,"0","H71")
h71 IS THE CELL CONTAINING THE SCORE WHICH SHOWS IN B73. THE OTHER FOUR
SCORES ARE ALL SHOWN IN B74, B75,B76 AND B77. i ONLY WANT THE MIDDLE THREE
SCORES TO BE AVERAGED AND PRODUCE A RESULT. HELP WOULD BE MOST APPRECIATED.

I'm using COUNTIF and AVERAGE. How do I count or average cells of only
unhidded rows

Can you calcualte "weighted average cost of capital" On Excel? If so, how?

I have a column of start days and times, 12/8/08 15:00, and end days and
times, 12/18/08, 20:00. I subtract the two columns to get the number of days
and hours between the two times, which is formatted with a "d:h" custom
format to show the rounded off number of days and hours, 10 D 5 H. This
column of time differences needs to be added and averaged. The sum function
does not show a correct total nor dos the average function show a correct
average. Is it a function issue or a formatting issue? The total for the
hours and the average of the time differences should be in a day hours
format, d:h, or some format that will show the total or average days and
hours. I have experimented with a varity of formats and tried different
functions but have been so far unsuccessful. Any assistance would be
appreciated. Thank you.

Hey all! I'm new to this forum, and pretty much a n00b when it comes to excel, but I've been trying to make my spreadsheet a little more efficient as of late.

Basically I'm trying to sum and average values based on what month and year they are, with any date.

I've been trying to do something like this:


	VB:
	
=SUMIF(A3:A81,"="&DATE(2009,10,"*"),B3:B81) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but it doesn't seem to work...

also, same question, but with an averageif twist?

Hi,

I'm very new to excel VBA programming..can any body please suggest a
easy solution for the following:

I need to use both sum and average in subtotal function on different
columns at a time..

eg: Column A : Name
Column E: Rate
Column F: net value

I want to do sum on Column F and average on Column E for every change
in column A...

Any help will be greatly appreciated..

Thanks..

Please help... I have a series of "problem" records in an Excel spreadsheet. Each record contains the date the record was entered, the date work started on the problem, and the date the problem was fixed, i.e., title/description, date created, date started, and date resolved. I need to count how many records were enter each month for the past 12 months, the average time it took to start work (date started minus date created) for all records in a given month, and average time it took to complete work (date resolved minus date started) for all records in a given month. Thanks. I've been beating myself silly trying to find answer in textbooks and Internet. talbotl@netzero.net

Hi folks,
I have a spreadsheet where I have employees create ticket open and close data. They have their own sheets so I copy paste their info into a template. Then, I have to delete the two rows of data (not the actual rows themselves) under what I just copy/pasted, then enter a count and average label down 1 +2 rows and over 4 columns from the bottom of my selection. Then I need counta and average down 1 + 2 rows and over 3 columns from the bottom of my selection. I'm selecting data from the right most set of data. I've pasted the record macro so you can see what I'm trying to do.

autoall Macro
' Macro recorded 11/1/2010 by IBM_USER
'
' Keyboard Shortcut: Ctrl+o
'
Rows("10:11").Select
Selection.ClearContents
Range("H10").Select
ActiveCell.FormulaR1C1 = "Count"
Range("H11").Select
ActiveCell.FormulaR1C1 = "Average"
Range("I10").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[-7]C[2]:R[-1]C[2])"
Range("I11").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-8]C[2]:R[-2]C[2])"
Range("H10:I11").Select
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
End Sub

Hey all, hope you peeps can help me out once more.

I have a simple little sumproduct formula that is supposed to calculate and average, however it doesn't work they way I need it to the reason is BLANK cells

=((SUMPRODUCT(--(tableno=$A3),D$66:D$5485)))/SUMPRODUCT(--(tableno=$A3)).

Below is a small extract, so A3=21, the first part of the sumpoduct gives me the correct answer of 14,156, the second part also works and give me the answer 5, but what it's doing is working the average of 14,156 / 5 = 2,831.20 which is not what I want, I need it to ignore where 21 = Blank and give me the true average of: 14,156 / 3 = 4,718.66.

tableno. D$
21 4,974
21 4,390
21 4,792
21
21

Because that tableno is used for many other calculations in the same spreadsheet I can't just simply delete the 21 from the blank cells. I need some sort of way to say if(D$="" - also do not count tableno...

Does that make any sense?

Thanks

Can you calcualte "weighted average cost of capital" On Excel? If so, how?