Use EXCEL to make an inventory table of different goods in the warehouse

Here's one, [Invoicing Company Edition] may be more suitable for you.

Search: Beijing Futongville Technology ******, the first article of the company's dynamics, there are many versions in it**,**Later, please spend 5 to 10 minutes carefully reading the operation help, which will be helpful to you. Including [Warehouse Enterprise Edition] [Invoicing Enterprise Edition] [Standard Free Edition].

The warehouse company version focuses on warehouse management, such as location management, first-in, first-out, etc.; The Invoicing Company Edition focuses on the cost of purchases and the profit from sales. Both editions come with reporting and query capabilities. If you just want to understand the principle, you can ** free version.

The invoicing and inventory management tool of small warehouse warehouse adopts Excel database engine technology, is designed and developed in accordance with software engineering ideas, and is stable in operation, simple and practical. No additional files are required, and there is no need to do any installation and setup on the system. It has complete warehouse and invoicing management functions, such as inbound and outbound management functions, inventory management functions, and location management functions.

It can realize first-in-first-out according to the production date, can calculate inventory costs, sales profits, statistics, weekly reports, monthly reports, and query the history of entry and exit. It is widely used in the warehouse management of small and medium-sized logistics enterprises, the inventory management of various production enterprises, and the purchase, sale and inventory management of various commercial enterprises.

I sent it to you casually, please check.

First in the same workbook: inventory summary, warehousing, warehousing three **.

Enter data in the inventory summary table.

Then select the Model column and set the Model column to Model Input Uniqueness (to avoid duplicate statistics):

1. Data validity - setting: custom - formula: =countif(b:b,b1)=1

2. Error warning - stop.

Set up the formula in the inventory summary table.

A3 cell: =if(b3="","",1)

A4 cell: =IF(B4="","",a3+1)

E3 cell: =if(b3="","",sum(d3+f3-g3))

F3 cell: =if(b3="","",sumif (warehousing!) C: C, B3, Warehousing! e:e))

G3 cell: =if(b3="","", sumif (out of the library!) C: C, B3, out of the warehouse! e:e))

Then copy the formula from the upper row to the following row.

Enter data in the inbound table and set the formula.

A3 cell: =if(b3="","",1)

A4 cell: =IF(B4="","",a3+1)

D3cell: =if(c3="","", vlookup (c3, inventory rollup!) b:c,2,0))

Then copy the formula from the upper row to the following row.

Enter data in the outbound table and set the formula, the same as in the inbound table.

Then the column with formula is protected by formula, and then you only need to enter the data you need to input in the cell without formula.

Statistical table of material receipt, dispatch and storage.

Material Category: Material Name Specification Model Unit of Measure Inbound Outbound Balance Quantity Unit Price Amount Quantity Unit Price Amount Quantity Unit Price Amount Material Receipt, Dispatch and Inventory Statistics Table.

Material Category: Material Name Specification Model Unit of Measurement Inbound Outbound Balance Quantity Unit Price Amount Quantity Unit Price Amount Quantity Unit Price Amount You send an email address, I will do it for you.

Statistical table of material receipt, dispatch and storage.

Material Category: Material Name Specification Model Unit of Measure Inbound Outbound Balance Quantity Unit Price Amount Quantity Unit Price Amount Quantity Unit Price Amount.

1. First of all, you need to build a **.

2. Enter the warehousing date, **, sales volume, original inventory, and existing inventory respectively.

3. Assuming that the warehousing date corresponds to B2, and the others are C2, D2, E2, F2, then the formula F2=E2-D2 can be used. When the sales volume changes, the inventory will change. The key problem is that I use a raw stock as a bridge.

Set it in a cell, as long as I enter a product name here, you can display the above information of this product in the following cells, and you can quickly view the product information and inventory. In a large number of records, in the form of rows, each cell after the name of each product is related to the product's warehousing date, **, sales volume, on-hand inventory, etc.

Small Business Business Purchase and Sales Table. XLS (Purchase Cost Statement, Sales Table, Gross Profit and Inventory Automatically Generated, with Instructions for Use).

Yes, the inventory table and the sales table are combined into one, and the four major items are divided according to the four major items of opening, warehousing, warehousing, and balance.

Each item is divided into quantity, unit price, and total price, the sales amount is equal to the total outbound price, the profit is equal to the total outbound price minus the total warehousing price, and the balance unit price is equal to the warehousing unit price.

Things are different in every company, simple purchase, sale and inventory, ++ will come out.

Refer to the attachment and pay attention to the timely update of the data every day.

Just use Excel a little bit.

The procedure is as follows.

1. Requirements and settings for excel software.

1. This ** is made with excel2003, please make sure that the office2003 software has been installed in the computer before use.

2. Enable macros in Excel, click "Tools-Macros-Security" in turn, select "Low" in the pop-up dialog box, and then confirm and restart Excel.

Second, set up the basic information of the warehouse for accounting.

1. Click "Commodity Information", "Business Information" and "Customer Information" in the main interface to improve the basic information in these three worksheets.

2. These are the basic information necessary for warehouse accounting, among which the ** in the goods information needs to ensure the guarantee of each product.

3. If the original goods have been booked with excel, the original data can be copied to the system, and attention should be paid to maintaining the structure of the goods data worksheet to avoid confusion in the formula.

3. Data entry of warehousing and warehousing orders.

The warehousing order in this set of warehouse accounting** is basically the same as the operation of the outbound order, and here is an example of the inbound order to illustrate the operation method.

1. Click "Warehousing Order" in the main interface to enter the operation worksheet, and the system has automatically numbered this document and automatically used the day as the warehousing date.

2. Select the supplier from the drop-down list, fill in the warehouse keeper, bookkeeper and other information.

3. Select the goods in each line**, and the product name, specification model, unit and other information will be automatically entered. Enter the purchase quantity and amount, and the unit price will be automatically calculated.

4. Enter all the records of the current warehousing list, check whether it is correct, and then click the "Save" button to complete the warehousing operation.

Novices do warehouse import and export **, and learn it immediately.

Hello, I can customize it for you.

The specific steps are as follows:

2.Use the formula for the balance = Quantity of storage + Number of storage + ...- Number of outbounds - Number of outbounds...., enter the formula and click the enter key. The following items can be generated by dragging down the lower left corner of the calculated balance cell.

Remarks: As long as the material name and warehousing information are clearly written, it can be clear at a glance, and the purpose of production has been achieved.

I'll provide you with a: excel warehouse management tools, to achieve warehousing and outbound management, automatic calculation of inventory, location management, production date batch number management, first-in-first-out, inventory ** monthly report statistics, inbound and outbound history inquiry, moving average method to calculate inventory costs, warehousing**, outbound**, calculate sales profits and other functions. You can go to My Space**.

Generally speaking, warehouse management tools include the following basic functions, warehousing, warehousing, inventory (automatically calculated according to inbound and outbound warehousing), cost (moving average method accounting), profit (sales amount minus cost price), statistics (**monthly report), query (inbound and outbound history). Other extensions such as product name, specification, weight, volume, unit, etc. are also available. It may also involve production date and batch number management, location management, first-in, first-out, etc.

The main difficulty is in the automatic inventory counting. The general practice is to use pivot tables, but if the amount of data is large, the speed will be severely affected. It is better to use VBA, and the speed is not affected.

Depending on the industry, the specific items may vary slightly.

I happen to have one or two under my hand, so I'll give you a copy.