1/A
B
C
D
E
F
G
H
I
[Code]...
I am trying to calculate the inventory coverage. In C3, I have the inventory amount as of january-end, which is 47,6 M. I want to know how many days of inventory I am carrying, according to the later months cogs figures. In this example, I calculated it as 110 days of inventory. is there a single formula I can use, to calculate the inventory coverage for january, and copy it accross the all columns?
I am making an excel sheet which can track the hypothetical effect building an order has on current inventory. The user is to change the quantities in this hypothetical order whenever they want.
It is composed of two sheets, the inventory sheet and the model sheet. The inventory sheet has the costs of every part carried and how many are currently in stock. The model sheet has a breakdown of what parts are required to build the different models.
I am looking for a function or VBA which can take a hypothetical order (build xx of model one + build xx of model two + build xx of model three), see how many total parts are needed, then calculate and display the effect it has on current inventory. This is only to see the hypothetical effect on inventory, not to actually change the inventory in the inventory sheet.
I've attached what I have so far - most everything is completed except for this final calculation. I have tried using the VLOOKUP function in VBA to try and come up with a solution but I couldn't figure out the best way to go about this. I think the hardest part might be that a hypothetical order can be made up of all three models.
I need a macro to calculate the cost basis of the inventory. ( Field F3 and F4 in Sheet1)
Please refer to attached sheet....
I need help to calculate inventory value using FIFO method...
View 9 Replies View RelatedI am trying to calculate Inventory Months Supply.
I can calculate the months suppply as shown below in multiple cells, but I am looking for a formula to do in one cell..
The correct result in this example is: 2.26048
Calculate the weeks cover of current inventory based on a sales forecast. the forecast is in weekly buckets so I was thinking using some form of loop statement within the function which basically starts with the inventory figure then subtracts the forecast week by week until the inventory figure is less than the weekly forecast. I should then be able to divide the inventory by forecast and add back the number of loops to give me the weeks stock figure.
I have done this using if statements but it will only allow me to use 10 statements in the function and I need to do this for a full years worth of forecast.
Does anyone have a suggestion as This problem is becoming quite frustrating.
Here's an example of how the spreadsheet looks
Forecast103050205040Inventory 20019016011090400Weeks Cover6.05.04.03.02.01.0
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????
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.
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.
I would like to create a formula that would verify that specific work shifts have been covered each day. The spreadsheet has already been created by someone else, so I am hoping not to recreate the wheel.
The goal is to make sure that all desired shifts are covered with a result in the last cell of the column that would indicate "covered", "not covered" or even a true or false statement.
As an example, an 8am shift is needed. The choices are 8A or 8ALEAD and only one of these is needed for each day.
Can something be set up with conditional formatting from a master list of required shifts.
such as: 7OR, 730*(for just 730 and 730LEAD), 11A, etc.
This is just a snippet of what the schedule looks like: ...
I'm a basic user of excel 2003. I recorded a macro that copy a teacher absent schedule to a daily cover schedule as follow:
Sub ABS_M1()
'
' ABS_M1 Macro
' Absent teacher monday 8/29/2012 by Oscar
' Daily cover schedule
'
Range("A65:J67").Select
Selection.Copy
Sheets("Covers").Select
Range("B5").Select
ActiveSheet.Paste
End Sub
Using the same VBA: What function should I use to make the Sub ABS_M1 move down 4 rows to a new range on the cover sheet if the first Range ("B5") is already used and so on?
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 RelatedI have a number of houses & a number of water plants (as in water processing plants, not the green kind!) Each water plant can provide a certain % water coverage to all properties. 'All Properties' is classed as the number of houses + the number of water plants. The 'coverage' as a percentage is given by the formula:
View 5 Replies View RelatedFirstly i am running Excel 2003 (at work)
Now my problem is that i have been asked to make a spreadsheet about Printer and Fax Cartridges (Zzzzz) but i do not know how to do the sum.
this is how my Spreadsheet looks like this (cartidge amount etc all on top line of spreadsheet)
(Hp45 etc all down left hand side) ...
I AM TRYING TO SUM OF EACH DAILY INVENTORY ITEM. PREVIOUSLY I USED FORMULA SUGGESTED FROM TEETHLESSMAMA (=SUMPRODUCT(--($A$5:$J$13=A19),$B$5:$K$13)).
BUT THIS FORMULA NOT WORK FOR NEW FORMAT OF INVENTROY DATA. I tried to make some change in it to get the result, which is not working well.
I am doing inventory right now and need help sorting numbers that I have. Ex:
I have numbers like
10
1001
101
AB1394-10
AB2948-MPG
XD89-MPG KIT
F64 KIT
GSW294-HD10
I need something that will help me sort it by the numbers only (numerically by the first part only). I want the above numbers to look like the following after sorting:
10
F64 KIT
XD89-MPG KIT
101
GSW294-HD10
1001
AB1394-10
AB2948-MPG
Basically, I'm trying to get the computer to sort the numbers numerically by disregarding any letter before the number.
I have a worksheet that has multiple units listed on it. Each unit has a materials used list with quantities. I want to reference this column to another sheet and get a total materials needed list and also reference it to my inventory and subtract that number from my materials on hand. how can I do this?
View 6 Replies View RelatedI am trying to figure out a formula for doing my inventory. I have items with control numbers on them and I would like to determine how many items there are. Example Starting #12335A Ending #12445A, if you subtract 12445A from 12345A you get 10, but in the inventory you need to count the ending number as it still remains in your count, so the answer is actually 11. I have been using for example =Sum(A8,-A9) +1 to get the correct number. However, if I have additional lines on my report that do not have inventory in them, they will show an incorrect total of 1 because of the +1 in the formula. I am trying to figure out how to do this calculation without having to delete and add the formula each time inventory is added. I have people that will not go near the formulas, so I need it to total with out having to be changed.
View 8 Replies View RelatedI am trying to put together an inventory worksheet and I have a rec'd column and a usage column, followed by a total column. I want to be able to havea formula in the total column at all times, so whoever updates the inventory sheet just needs to put rec'd and used. What I am trying to do though, is have the total row for a particular month blank, unless someone enters a rec'd amount or a used amount.
View 9 Replies View RelatedSo far I was able to find solutions to all of my small problems with excel just by searching here but with this one I need help and ideas how can it be done differently.
We need to track inventory from when its taken, when it was installed and we need to know every month when inventory is counted how much do we have on hand in our truck....
I'm trying to set up an inventory spreadsheet for my work, but i'm running into a snag.
What I want to do is have three columns that interact with eachother.
1. Units in stock
2. Reorder Level
3. Reorder Tolerance
4. Status
What I want, is to have the status column react in one of three ways. Either "In Stock", "Reorder Needed" and "Out of Stock".
So if units in stock is at 110, with a reorder level of 100, and a tolerance of 10%, I want the Status to show "Reorder Needed" with a red cell color. But if stock is at 111 or more, I want it to be shown as "In Stock". Zero should be "Out of Stock".
I have inventory on an excel sheet with over 1500+ items. There are number of columns including: product id, product code, description, price, headline, path etc. I am uploading this to my online store I am creating. And, I have a lot of duplicate items so I can place them in different sections of the store by editing their path. However, these duplicate items in the excel sheet only have the product id, and not the description, price, and all the other cells. Thus, I need a forumla that will find matching product id's and fill in blank cells with their description, price, etc. so it will match its partner completely .......
View 14 Replies View RelatedI having trouble to sort out and summarize inventroy products for each week. I want to sumarize all weekly inventory in summarize table. Please see attached file. Pls keep the formate as i shown in attached spreasheet.
View 9 Replies View RelatedIf I wanted to use excel to track inventory in/out, is there any way I can do the following?
I have a barcode scanner and I wanted to be able to quickly add and subtract quantities.
So, I want to create three fields at the top of list, one with the function to Add 1 to quantity, one to Subtract 1, and one simply to find (well I guess I can use excels find feature, but that would be an extra step)
When I scan a barcode into the "Add 1 to quantity" field (which only appears at the top of the page), excel will automatically add 1 to the corresponding field next to the item that has that UPC number. The same for Subtracting 1.
We have 100+ clients. Each client has inventory in our warehouse. Every day we write down how many items went out to our client on a piece of paper and subtract that number to get a balance inventory. When we run low, we order more on a new FO number. What is the best approach for tracking this? I was thinking one way is to put our clients in a drop box at the top of the page and then the data will appear at the bottom, however I can't get that to work. I am trying filters to see, but I am unable to subtract the balance automatically from the last FO# and from the correct area.
Is there a way to choose a client and product at the top of the page and have the tracking below?
I'm trying to make a worksheet to track inventory, not for sales but for tracking how many supplies are on hand in storage rooms, closets, etc. The worksheet will be used by as many as 20 different users probably on a network.
Because it will be used by many people, I'm trying to see if there is a way that the worksheet can be updated after every save. In particular, the amount that are "on hand".
For example, say there are 10 of a particular item on hand to start. Joe takes 2 and goes to record it on the worksheet. He inputs the 2 he takes, which now show that there are 8 on hand remaining. He saves the worksheet. Then, Jerry comes and takes 2 and goes to record it on the worksheet.
I can't figure out how, or if it's possible, to have the worksheet show Jerry that there are 8 on hand to start, not 10. So that when he takes his 2, it should show that there are now 6 on hand remaining. I've attached an example worksheet.
On one sheet, amongst other information, I have a record of how many shirts, hats, and jackets(1 or 2 or 3), each rep has, along with what sizes they are (S, M, L, 1, 2, 3, 4, 5). There are two columns for each. On the other sheet I want to track our inventory. I came up with this to try to count small shirts
{=IF(OR(Hierarchy!$AB$3:$AB$260="S"),COUNT(Hierarchy!$AA$3:$AA$260),0)+IF(OR(Hierarchy!$AM$3:$AM$260 ="S"),COUNT(Hierarchy!$AL$3:$AL$260),0)}
But instead it counts all shirts. how I could modify it to count within the paramiters?
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?
I'm using excel as an inventory database but need to know how to change the number format according to what the user is keeping track of. For example drums, bags , cylinders ect...
How can i change the number format to display the unit of measurement added by user?
I am trying to calculate the number of days that items are were held in inventory as of a given date. See below for some sample data.
'As Of' - the date in question
# Holdings - Using sumproduct to find items bought on or after the date AND sold after the date
Held Days - ? Total number of days that the six items were sitting in inventory as of the date.
As Of #ItemsHeld Days
5/21/98 6
ItemBoughtSoldTotal Inventory Days
19/3/919/3/961827
21/2/921/2/971827
31/2/921/2/971827
43/2/928/21/951267
55/1/925/1/971826
65/1/925/1/971826