HOW TO CREATE A WAREHOUSE MANAGEMENT TABLE IN EXCEL

First of all, I've been asking questions about excel inventory ** for a long time. What impressed me the most is that most of my friends want to display invoicing and other information in a single sheet page at the same time.

In fact, there is nothing wrong with this, first of all, it is clear at a glance and intuitive, and this form of report is the boss's favorite to see; Secondly, you don't have to do a lot of reports, and you can get all the information in one table, which feels like saving work time. But :::

The disadvantages of this ** are very big, why do you say that? Because: most of my friends have many types of products and models.

Someone said, use a formula, I said yes, but have you thought about it, if you have 30,000 rows of data, and each row has a formula, then okay, your ** is not far from paralysis. It can't be opened, or even when it is opened, it is garbled and the data is lost. Oh, I can feel your mood...

About summarizing inventory, you can make an inventory table, enter some summary formulas, and sometimes use VBA programming, if you don't know VBA, you can record macros, and then go to view**, copy-paste ** to where you use it. It's going to be better. In fact, everyone's actual situation is different, and to do this kind of thing, you must consider it according to the actual situation, so as to really improve your work efficiency, and you don't need to fill in line by line.

Well, let's not talk about it, I can't say it anymore...

Uh, see you say, if established. If you don't learn excel professionally, it is quite difficult for you to create your own reports.

So, I'll tell you about it.

You do warehouse management.

Do you know the model of the product in the warehouse?

How much do you enter every day, how much do you come out, do you operate with a voucher or do you watch people get them? If so-and-so comes and says you will give whatever you want, do you want to show you the receipt, so that you know how much you have taken, when and who will take it like this?

If that's the case, I'd recommend that you use software to manage it.

A report is a report. It's easy to use software.

I just searched for a free warehouse management software.

As you mentioned, all of these features are available. And I think you have learned to use soft year management, efficiency is not something you can compare with excel reports.

Hope it helps.

Personal experience.

Create a new worksheet in Excel.

In the appropriate position, enter the text "Warehouse Statistical Report, Product Name, Inbound Quantity, Outbound Quantity, Unit Price, Acceptance Amount, Total Amount".

Center the text. Headings are combined and centered into **. Pull the width of each column to the right place.

Click Format Cells and select the Borders option to add all borders.

Amount calculation: Click the cell under the amount to enter the formula =(b3-c3)*d3, that is, = inbound quantity - outbound quantity * unit price.

Use the sum formula in the total amount field below the amount to get the amount, or directly click on the sum symbol of the self-friend.

1. Create a new user information table: start from cell B2 and so on, enter the warehouse name respectively; Warehouse Leader; system operators; Accounting time; Start date; items such as the date of termination;

2. Create a new **quotient information table: start from cell A1 and so on, and enter **quotient respectively; **Name of the trade; Contact; mobile phone number; Contact**; Fax; QQ number; contact address and other items;

3. Create a new picking department table: enter the department ID in A1, enter the picking department in B1, enter the picker in C1, enter the picker's mobile phone in D1, and enter the picker's mobile phone in D1; From the second line, fill in the information about the picking office.

4. To create a new employee table, enter the employee's name from A1, B1 to enter the department, C1 to enter the position, D1 to enter the mobile phone number and other items from A1 onwards;

First of all, create an excel table named inventory management table, open it, and create four workbooks in the table, such as the warehousing table, the outbound table, the commodity table, and the inventory table, at this time, design the inventory table, as shown in Figure 01

<>3.Then select column A, that is, click A, and enter "commodity" in the name box above column A, as shown in Figure 03. If there are new products in the future, you can add them in turn, but the items in the inventory table must be the same as the names in the product list.

Now you need to make a drop-down menu of the product name, click on the B2 cell in the warehousing table, data--validity--setting, select "sequence" in "allow", tick in front of "allow null value" and "provide drop-down arrow", fill in "=commodity" in "**", OK, so that the warehousing table is linked to the commodity table, the B2 cell drop-down menu will appear in the commodity table, and what goods can be selected here, this method also supports dragging down. The same is true for outbound tables.

5.Back in Figure 01 of the inventory table, enter =sum(if(inbound table!) in cell E3. b2:

b8=a3, warehousing table! c2:c8,0)), and then press ctrl+shift+enter, this formula represents:

If the contents of cells B2 to B8 in the warehousing table are the same as those in cell A3, then add their quantities, otherwise it is 0, and the name in the inventory table must be exactly the same as the name of the product in the inventory table.

6.Enter =sum(if) in cell H3 (outbound table!). b2:b8=a3,outbound table! c2:c8,0)), the method is the same, but changed to the outbound table.

7.Enter =b3+e3-h3 in k3, which means inventory = opening + inbound - outbound.

Your errand problem, I'm not going to face it head-on.

First, your warehouse management software is likely to have the need for handheld devices, so it is difficult for Excel to meet the support of these multi-terminal devices.

Second, your management software, you may be in different places to view the warehouse data in real time, although you can be based on cloud storage and multi-person cooperation to complete the guess, but obviously this is not the best and optimal way.

Third, the warehouse management software is updated on demand in the later stage. And there aren't many of these people in the industry. Then the follow-up maintenance is not easy.

Fourth, you can borrow the existing excel warehouse management software, and you can meet your needs with a little modification.

Fifth, you can also use warehouse management software written in other programming languages, which is fully functional, rich in developers, and much more convenient for on-demand updates or daily maintenance in the later stage. And it basically supports multi-terminal and real-time synchronization.

Hope mine can help you.