This is the simplification of the busy year-end inventory work!

Speaking of this time of year......

So it's the end of the semester. It's a salvation that it's not the end of the century. In the corporate sector of the world, it is a big job that comes once every six months, and it is time to take stock.
How does your company do inventory?
Rather often, it is a method of printing a list of equipment managed by a database or Excel, exporting data, handing over printed materials and files to each department, and asking them to "check the actual product".

In fact, Colorkrew used to do that before. However, there are many problems with this.
The most important thing is the sense of speed. Inventory is originally the job of the corporate department, and even if we ask for sales or development, it is often put on the back burner, and the results are sometimes not returned.
Also, since this is a work that is only done twice a year, how to carry it out is not thorough, and there may be omissions in the results of the actual confirmation that comes back, or the information that is slightly out of focus.
In that case, the person in charge will have to go on a business trip and re-investigate the strange data.
In fact, at that time, it took me more than a month to do it. At that time, desktop PCs were often installed under desks, and he said, "Emotional sys is a job that crawls."

The way to improve this is to "simplify inventory (physical confirmation) work". If the work is simple, it can be implemented immediately, and if the work is simplified, mistakes are less likely to occur.
Also, it takes a lot of attention to match the returned results with the original ledger data (if it's printed materials, it's manual input, manual input), but if it's systematic, this can also be improved.

"Simplification of inventory work", what should be done for this, our recommendation is, of course, Colorkrew Biz. "Inventory" function for equipment management.
Just scan the QR code of the asset sticker attached to the device with the app and click [Inventory] → [Confirm], and who confirmed the actual product and when will be recorded in the Biz database.

Of course, Colorkrew Biz is an internet service, so it doesn't matter if the person who checks it is in the office or working remotely from home.
By the way, our company also has an office in Brazil, and information about the actual product confirmation is flying in from there as well.
Brazil is on the other side of the earth, and it is also directly behind the central core. If you want to go to "Total Recall (2012)", go with "The Fall".

Once data can be easily aggregated, it is human nature to want to know the progress.
This time, I would like to explain how I manage my progress. I use Excel.
There will be quite a long functional expression, but thank you for your cooperation.

So let's go.

Preparation

First, create a file to check the progress. This is because the ledger is divided by different people who manage it depending on the type of asset, or because it cannot be messed up because there are or do not incur monthly expenses.
However, in the work of inventory, the treatment is the same in terms of "finding out who is using which equipment", so we collect the data in the ledger and create a file to check the progress each time.

Launch Excel and create a "blank workbook". There are two sheets to make first. The sheet name and role are as follows.

• "Items" …… Fill in the name of the sheet (described below) to take the history.
• "Equips" …… It will confirm the progress and consistency of the physical confirmation.

Creating an Equips Sheet

Start by creating an Equips structure. The first line is used to display history and progress, so put the following item names in the second line.

• Asset number
• Inventory date
• Inventory Conductor
• User
• User email address
• Scheduled date
• Consistent
• Additional Conditions and Information
  • The following is equipment information (device type, name, model number, introduction date, etc.)

There are two important points at this time.

Prepare information about the person using the device that can be matched with the user information of Colorkrew Biz.
The above refers to the user email address. Use this and the mail registered in Colorkrew Biz as a key to check whether the user himself or herself has taken inventory.

The other is the display format of the cell. For each item that needs to be displayed as a date (such as the date of the count), select a column and set the cell format to "Date". If you forget this, the date will be displayed as an Excel internal processing number and you will be in a state of "What is this?"

When you have finished setting up the items, you post each item related to the device from the ledger file or database.

Importing Biz Data for the First Time

After this, I fill in the functional expressions in the cells of the Equips sheet, but I can't debug without the data. So I will make the first data.
Create a new sheet and name the sheet with the date of data download (this is a sheet to take the history that was described as "described later" above).
What you should be aware of at this time.
It is NG to put a minus ( - ) or a slash ( / ) in the sheet name. The function expression will judge it as an operator and you will not be able to reference the data.
An 8-digit number is fine, but since the functional expression is difficult to read, the recommended boundary symbol for the year, month and day is the underbar ( _ ).

Once you have the Date sheet, connect to the Colorkrew Biz dashboard, open Fixtures → Reports, and select Equipment Inventory History.

Specify the period from "Inventory Start Date" to "Today" to download the file.
Open the saved CSV file in Excel. Select all of them and copy and paste them into the "Date" sheet you created earlier with "Value Only".

Once you have copied and pasted it, create data for reference.
Open the Items sheet and fill in the sheet name of the Date sheet in cell A1. Create a new "Date" sheet and copy and paste the inventory history csv in column A2, A3, A4...... I will continue to fill in the sheet name.

Filling out Functional Expressions

We will prepare to confirm that the progress and actual confirmation of the product have been properly carried out by the user. Fill in the cells (columns) of the Equips sheet with the following function expressions:

In addition, the number of items registered as equipment is written as $2000 in the cell range designation in the formula. In the case of Colorkrew, it is actually about 1800, but it is specified up to 2000 lines with a little margin. Please adjust accordingly to your company's situation.

"Date Selection" Cell

First, let's set the input restriction in cell A1 for easy reference of history.
In the Input Regulations dialog, select List and select rows 15~30 of column A in the item sheet. You can now easily access past progress data by selecting the drop-down list for cell A1.

Inventory Date Column

Fill in cell B3 with the following formula and copy and paste it to the end of the column (the row with the equipment information).

=LET( Date, XLOOKUP( A3, INDIRECT( $A$1&"!$C$2:$C$2000" ), INDIRECT( $A$1&"!$T$2:$T$2000"), 0 ), IF( Date=0, "", Date ) )

"Inventory Implementer" Column

Fill in the following formula in cell C3 and copy and paste it to the end of the column.

=LET( Asset Area, INDIRECT( $A$1&"!$C$2:$C$2000" ), Name, XLOOKUP( A3, Asset Area, INDIRECT( $A$1&"!$R$2:$R$2000" ), "" ) & " " & XLOOKUP( A3, Asset Area, INDIRECT( $A$1&"!$S$2:$S$2000" ), "" ), IF( Name=" ", "", Name ) )

"Consistent" column

Fill in the following formula in cell G3 and copy and paste it to the end of the column.

=LET( Aka, XLOOKUP( A3, INDIRECT( $A$1&"!$C$2:$C$2000" ), INDIRECT( $A$1&"!$P$2:$P$2000" ), "" ), IF( C3="", "", IF( Aka=E3, "○", "×" ) ) )

"Progress Display" Cell

Before filling in the function expression, change the display format of cell B1 to "Percentage". I think it would be nice to have about two significant digits below the decimal point. , fill in cell B1 with the following formula:

=COUNTIF( $C$3:$C$2000, "=?*" ) / COUNTA( $A$3:$A$2000 )

This is it. Don't forget to save your files.

How to Use a Progress File

Basically, all you have to do is download the inventory history data from the Biz management screen, copy and paste the contents to the sheet of that date, and add the sheet name of the "date" sheet to the list.
If the QR scan of the inventory (physical confirmation) has been performed, the person who performed it and the date will be displayed. However, if the person who took stock is a person registered as a user, a "○" will be added to the "match" column.

What's good about using Biz is that when someone asks, "Are you taking inventory?", you can update to the latest data in one minute and immediately answer the progress rate up to two decimal places.
Compared to the days of paper-based inventory, which was updated once a good day, it is a whopping 1440 times more efficient (w

Mochito Practical Techniques

Colorkrew conducts inventory by having QR scans performed in the first week of March/September (if inventory cannot be done during the period, please specify a date when it can be done and ask for QR scanning by that date).
The 1~2nd day after announcing the start is relatively smooth, but the progress slows down from around the 3rd day.

There are probably two reasons for this.

1. You are not motivated to take inventory in the first place.
2. You forgot to take inventory of some equipment.

(1) is out of the question, but in most cases, (2) is a pattern of forgetting to scan the QR of small items such as mobile phones and mobile routers, even though the inventory of the main equipment such as a PC has been completed.
In fact, the latter is more troublesome than the former, and since I believe that "I have finished the inventory", even if I post an announcement on the company's SNS or the same newsletter email, it will not stick to the person in question at all.

So, what should I do?
…… Leverage progress files and group chats.

Filter the Equips sheet by the Inventory Date and Count Performed By columns to create a list of unimplemented equipment. Generate a copy and paste of this as a separate file for presentation and sort by user. Create a Teams group chat with the listed users as members, and attach a list of unimplemented devices to announce the group to only the target audience.

By the way, to avoid duplicate names of unimplementers, launch PowerShell and use the following command. When you run it, it will remove duplicates from strings in the clipboard. You can also copy Excel columns.
The easiest way to use it is to paste the following command into a PowerShell window, copy the columns from Excel, and run the command in PowerShell.

Get-Clipboard | Sort-Object | Get-Unique | Set-Clipboard

If you write out the message in the sentence "This chat is sent to a member who has not finished taking inventory" , it is quite effective for people who think "I'm done~".
On top of that, unless you finish the inventory and leave the group chat, activities are added to Teams for each other member's "I did it!" reply message, which is extremely annoying.

Therefore, the inventory proceeds without additional reminders, which is a happy effect for the person in charge of implementation. It's like a combo attack in a card game that cuts down on the opponent's life points every turn.

**The life of those who have not yet implemented it has long been zero!" **

While muttering, the person in charge just needs to look at the group chat screen. Ah, nectar.

That's why

Asset management personnel nationwide (or even the world), why not realize 1440 times more efficient kaizen with Colorkrew Biz?