Inventory management for girls in Excel_Makeup/care products

I want to ask you a question first, not limited to men and women...

Inventory Management "How do you/your makeup/skin care products decide when to replenish?"

Expiration Control "Do you know the shelf life of each cosmetic/skin care product you buy?"

Budget Control "Every year, I have a budget to buy these supplies, but I don't know how to manage it?"

△△△

Or, do you/do you have other worries about managing makeup/skin care products?

It's right to read this article. The small book integrates these three types of issues "inventory, deadline, and budget" into a workbook (excel). You only need to record the running account in real time. There is no need to do any complicated function settings.

I have written a lot of excel operations, most of which are about single functions and functions. I feel that I can write a few articles on the use of excel in various life, and then write a disassembly according to the content of each setting in it. Wen, will this make it easier to understand?

It doesn't matter if you don't understand it well, there is an electronic file ready to be set up for you to download and use; please go to the end of the article and click on the link "Album Book: Makeup & Care Products Record".

In fact, sometimes there is no need to deliberately understand the meaning of each function, as long as the purpose can be achieved. When I first learned excel, I just copied and pasted it directly. I solved the problem at hand first, and then I came to study it.

△△△

There are three tabs in excel, namely "Charts, Records, Categories", and how to use them will be explained here.

1. Inventory management for girls_Pagination/Record

This page is very important. The sources of data all come from here. When will you come in and update it?

1. When you just bought a new cosmetic/care product, record the brand, capacity, price, expiration date, etc., and make relevant remarks.
2. When starting to use, record the date of opening the can.
3. When it is used up, record the use-up date.

Don't underestimate the small matter of recording. The more complete the information, the more you can understand the habits that you don't care about, such as when you will make big purchases at a fixed time, the progress of each item's use, the degree of indiscriminate spending...etc. Kind of like accounting.

 I remember watching a movie, the accountant in the movie said, "If you want to understand a person, you should start from the person's bills and expenses, and then you can understand the person's living patterns and habits."

2. Inventory Management for Girls_Pagination/Category

Here is the category classification. Both tables can be adjusted. As long as they are within the black line, they can add and update content. The categories here are mainly used to set drop-down menus and charts.

[Left] Size classification of makeup/care products

[Right] Classification of the validity period of makeup/skin care products

You don't need to update the content here at any time. You only need to make changes here if you can't find the corresponding category. This pagination is that as long as it is set at the beginning, there will be little changes later.

3. Inventory management for girls_page/chart

This page mainly allows you to view the current inventory status. There are three charts and a pivot analysis table, so you can quickly grasp the status of your "inventory, amount, budget, and validity period".

Three charts: No manual updates are required, the charts are automatically adjusted.

A hub: need to manually "grab range", "refresh" data.

 Chart 1: Inventory ratio_See your inventory status and inventory ratio.

 Chart 2: Amount Percentage_See the percentage of your own spending.

 Chart 3: 2020 Budget_Current Budget Status 

△△△

 Pivot Analysis Table: Validity Overview_Validity Status of Various Supplies 

How to manually "fetch range" and "refresh" data?

 "Crawling Scope" When there is a new or updated page/record side, it needs to re-fetch the scope.
"Refresh" suggests to update it every time you look at it; take a screenshot on the right side of Step4

Step1_Arouse the "Pivot Analysis Table Analysis" function; the following picture is a screenshot that has not been awakened

Step2_ Click anywhere in the "Pivot Analysis Table" in the cell, the function of "Pivot Analysis Table Analysis" will be invoked in the upper functional area, click it.

Step3_Click "Pivot Analysis Table Analysis" >> "Change Data Source" "Change Data Source (D)..." >> "Table/Range (T):" Just change the numbers in the last three codes

Step4_"Table/Range (T):" Suppose we add to 200 cells, directly modify 200 >> "OK" >> Click anywhere in the "Pivot Analysis Table" in the cell and right-click >> "Rearrange" (R)”

Finish

Excel Tips x New shortcut key: Ctrl+Q

Quickly call out the "Data Bar" , "Chart", "Total", "Table" and "Trend Chart". Today, only the function of the "Data Bar" will be demonstrated. For other functions, you can play and see for yourself.

 Quickly call out the "data bar"

Step1_Select cells E9:E12 Step2_Press "Ctrl+Q"
Step3_Select "Format (F)" and "Data Bar" to complete.

△△△

 Does anyone want to know the settings for each function in this flipbook?

The things covered are... data groups, function formulas, cell color changing (conditional formatting), two-level drop-down menus (data validation), definition names, pivot analysis tables, graphs (bar charts, radial rings) Figure)..etc.

Saying so many nouns that make people want to sleep, shouldn't they sleep to death? XD

If you want to know how to set it up, you can tell me in the comments, you can only share if you have to knock the bowl ^^ hehe

===> Excel Workbook Download: Makeup & Skincare Records <=== Using office365

Do you like this article?
Can clap or leave a message to let me know :D

this article? Can clap or leave a message to let me know :D