HOW TO USE EXCEL TO MAKE AN INVENTORY TABLE THAT CAN REFLECT THE NAME, TIME, AND QUANTITY OF THE INB

I'm just right for you to use I submitted it to you, please pay attention to check.

Excel material management system to achieve inbound and outbound management, inventory management, first-in-first-out, cargo location management, etc.

The tool uses excel database engine technology, designed and developed according to software engineering ideas, and runs stably, simple and practical. In addition to the content of product attributes, the public version does not make any restrictions on functions. Feel free to use it, but it may not be transferred for commercial use or **.

1. Warehousing management, including warehousing materials**, name, quantity, production date, warehousing date, and location.

a. After entering the warehousing quantity, the system will automatically update the inventory quantity of the inventory module.

b. Support modification function, if the inbound quantity is wrong, you can modify it, and the inventory will be automatically updated after modification.

c. The first-in-first-out function is managed by the warehousing date and location.

d. The picking information column is used to show when the warehousing record is out of the warehouse.

2. Outbound management, including outbound materials**, name, quantity, outbound date and other information.

a. After entering the outbound quantity, the system will directly follow the first-in-first-out principle of the production date to find the goods suitable for the location.

b. After finding the corresponding location, subtract the number of outbound goods from the location, and subtract the number of inventory tables at the same time; If the quantity is not enough, then move on to find another location.

c. Mark the picking information (from which location, how much quantity).

d. If the final inventory is not enough to ship, the corresponding record of the outbound table will show the number of out-of-stock.

e. The public version does not support the function of modifying the outbound quantity.

3. The following functions are the official version functions and are provided according to specific needs.

a. Warehousing and warehousing ledger.

b. Nissin report.

c. Single query (query the inbound and outbound information of the specified commodity).

d. Printing of inbound and outbound library instruction sheets.

e. Expense management: automatic calculation of inbound and outbound fees, storage fees, venue fees and other expenses.

f. Expansion of basic information of goods: specifications, weight, volume, shelf life, and safety stock.

g. Shelf life alarm, **alarm automatic** function.

Ha, I used it and made it myself.

It's simpler. Let me tell you about it.

Hope you can see it.

Date: Cargo 1 Cargo 2 Cargo 3 Cargo 4 .

10-11 into 1 2 in. Enter.

Enter. Enter. Enter. 10-12 out -1 -1 out. Go out.

Go out. Go out. Go out. If you want to check the quantity of goods, you can see the number in the column where the name of the goods is located. The dates can be viewed in the left column.

It is recommended to leave the corresponding manual order for correspondence. Out and in are separated. If the middle is not empty enough, you can insert a line.

Of course, it can also be another way.

Goods. Time in and out.

In this way, when you want to check how many are left, you can choose two columns.

Hope it meets your needs. Giving more points is 0 0

Negative numbers are used for outbound and positive numbers for warehousing, and the inventory balance is directly added to come out.

According to what you wrote, it is an inventory table, of course, you can make 3 sheet tables, one reflects the storage, one reflects the library, and one uses the link to make the balance.

Two sets of templates have been sent to you.

Please pay attention to check, you can refer to the application.

Industrial enterprise material consumption management report system.

Commodity purchase, sales, inventory (raw material purchase, consumption, storage) management report (system).

You can create the corresponding category entries in Excel** first, and then enter the corresponding inbound and outbound numbers according to the actual situation. Finally, set the corresponding logical calculation formula to automatically calculate the inventory, and the specific setting method is as follows:

1. Create an excel file, first enter the header of the file and the corresponding category, such as the previous period carry-over number, inventory, date and other information.

2. Then enter the relevant information in the corresponding **, such as the product name, the previous period carryover and other numbers.

3. Then enter the calculation formula =(d5+f5+h5+j5)-(e5+g5+i5+k5)+b5 in the inventory cell. The meaning of the expression is the sum of all the inbound numbers minus the sum of all the outbound figures, plus the carry-over figures of the previous period, and the remaining inventory of the day.

4. At this time, you can see the actual changes in inventory by entering the corresponding warehousing and warehousing numbers on February 1.

5. When the date is February 2, enter the corresponding number again, and you can see the result of automatic inventory calculation, and enter it according to the actual situation in turn. This includes the daily warehousing and warehousing details.

1. First of all, open an excel sheet (WPS or Office can be) and enter some outbound and inbound data in it, as shown in the following figure:

2. Then enter =B2-A1 in C2 next to it, and then press the Enter key The excel sheet will be automatically calculated, and then the mouse will stay in the lower right corner of C2 A cross symbol will appear, as shown in the following figure:

3. Then press and hold the small cross to pull down, it will automatically fill the quantity of inbound-outbound storage, C3 represents the number of B3-A3, and so on, as shown in the following figure:

4. Of course, the above method is suitable for what to do when there is less data than when there is more data, here you need to use a pivot table, first insert a pivot table in the insert, as shown in the following figure:

5. Put the outbound and inbound data in the list of rows and values in the lower rows respectively, and then change the name of the inbound data in the pivot table to inbound, as shown in the following figure:

6. Then place the mouse cursor anywhere in the pivot table and select the analysis in the menu bar, and find the fields and items in the analysis, as shown in the following figure

7. Click on the calculation field in the field, and then enter the name inventory, and use the inbound 1 (the most original data source) in the data in the formula - outbound, as shown in the following figure:

8. Finally, click OK to see that the inventory column is automatically displayed in the data table and the data has been calculated, this method is still very simple, even if the data volume is very large, it can be easily calculated, as shown in the following figure:

Summary. Hello, first make a **, reflecting the required data and content, the specific operation method is as follows:

1. Open excel, make **, and set the ** framework first.

2. Enter the date, product name, warehousing data, outbound data, and remaining inventory.

3. The remaining inventory quantity can be set with the formula, =c4+d4, press the enter key, enter the warehousing data and outbound data, and the remaining inventory data will be automatically calculated.

4. The bottom row can set a total, and the purchase, sale and inventory of this month can be calculated as a whole.

Use excrl** to count the purchase, sale and inventory, convenient and clear, the operation is relatively simple, you can do it several times, and you will do it more and more smoothly after mastering the skills, and you can also use it to count some other data, which is very simple.

How to use excel to make inventory and warehousing in the same **, you can clearly see the balance quantity and daily warehousing details?

Hello, it is a pleasure to serve you, I have received your question, I am sorting out the answer for you, it takes time to type, please be patient for a while, and answer it for you immediately!

Hello, first make a **, reflect the required data and content, the specific operation method is as follows: 1. Open Excel, make **, first set the framework of **. 2. Enter the date, product name, warehousing data, outbound data, and remaining inventory.

3. The remaining inventory quantity can be set with the formula, =c4+d4, press the enter key, enter the warehousing data and outbound data, and the remaining inventory data will be automatically calculated. 4. The bottom row can set a total, and the purchase, sale and inventory of this month can be calculated as a whole. Use excrl** to count the purchase, sale and inventory, convenient and clear, the operation is relatively simple, you can do it several times, and you will do it more and more smoothly after mastering the skills, and you can also use it to count some other data, which is very simple.

1.First, as shown in the image below, three tables were created in the workbook, named Inventory (Figure 1), (Figure 2), and Outbound (Figure 3). Please note that the date of the inbound and outbound tables is June 30th.

2. Then put in the warehousing formula, the first formula, that is, cell g3 write = sum(h3:bo3), and then fill it down, the second formula, that is, cell a3 write = inventory! a4。

Then all the way to f3 and then all the way to the bottom. Select all the columns in the table that are marked as billnumbers and format them as text.

3.Write down the formula for the next delivery. In the first formula, write =sum(i3:) on cell h3

bp3) and then fill it up. In the second formula, write the inventory on the =A3 cell. a4。

Then all the way to f3 and then all the way to the bottom.

The third formula is on cell g3 and it is equal to the inventory! Select all the columns in the table that are labeled as output individual numbers and format them as text.

4.Here's the inventory formula. There are four formulas.

The first h4 writes =if(l4>g4,"no","yes"). The second j4 write = storage! g3 third k4 write = output!

h3 4 l4 is equal to i4 + j4-k4.

5.Safety stock in the inventory table refers to the minimum inventory standard for the item. For example, if you consume 40 apples a day, it takes 3 days to buy apples. Opening inventory refers to the inventory of this material at the end of the previous month.

6.The formula is set and see what happens. Enter any materials in the inventory list. Then put 5 into the memory and 1 into the memory.

The common functions of warehouse inventory management in Excel are: summation function, subtraction function, and you can also use the filter function.

How to use: 1. First of all, list the purchase and sales data displayed according to the specific needs in excel.

2. Then summate the cells below the purchase quantity and monthly sales data to calculate the total purchase data and the total monthly sales data.

3. Then enter the subtraction formula according to the different purchase names in the position of the inventory.

4. Click Enter and drop down the formula to complete the calculation of the balance of the inventory.

5. You can also select the header row of the first row and click the filter button in "Sort and Filter".

6. After adding the filter button, you can click to select the name that needs to be displayed, so that you can only view the inventory corresponding to the selected name.

In Excel, warehouse inventory management involves a lot of commonly used functions, and the following describes several commonly used functions and their usages:

sum function.

The sum function is used to calculate the sum of a set of values and can be used to calculate the total amount of the current inventory. For example, if you enter the inventory quantity in column A, you can use the Suma function to calculate the total inventory quantity using the formula: =sum(A2:A100).

if function. The if function is used to make judgments based on the specified condition, and if the condition is true, it returns a value, otherwise it returns another value. In inventory management, you can use the if function to determine whether the inventory is sufficient.

For example, if you enter a fixed inventory quantity in column b, you can use the if function to determine whether the current inventory is sufficient, the formula is: =if(a2>=b2,"Adequate","Inadequate")

vlookup function.

The vlookup function is used to find a specified value in a ** and return another value corresponding to that value. In Inventory Management, you can use the vlookup function to find the inventory quantity of a specified item. For example, if you enter the item name in column C, the corresponding inventory quantity in column D, and the name of the item you want to find in column E, you can use the vlookup function to find the corresponding inventory quantity

vlookup(e2,c2:d100,2,false)

min and max functions.

The min and max functions are used to calculate the minimum and maximum values of a set of values, respectively. In inventory management, you can use these two functions to calculate the fewest items and the most items in your inventory. For example, enter "Least Inventory" in F1 and "Most Inventory" in G1, and the following formula in F2 and G2:

min(a2:a100) and =max(a2:a100).

These functions are just some of the functions that are commonly used in inventory management, and there are other functions that can also be used for inventory management. Hope it helps.

The advantage is that it is clear at a glance and not easy to make mistakes, and the disadvantage is that it is difficult to analyze data and ** is too large for printing, which is suitable for warehouse.

The advantage is that the company's production, sales and procurement can be intuitively understood through the later sorting, filtering, functions, pivot tables and charts, etc., and the disadvantage is that the pre-production takes a long time. The more data is processed, the more complex the formulas are written. It is difficult for people with 0 basic skills to actually operate.

For finance departments.