How to create a sales inventory in Microsoft Excel

How to create a sales inventory in Microsoft Excel
How to create a sales inventory in Microsoft Excel
Anonim

Excel spreadsheet software is part of the Microsoft Office office suite. It is possible to create different types of tables, indicators, etc. To follow the management of a business, this is the right tool. You can set up a table that gives the inventory of retail operations, for example. All you need to do is create a table with the columns that are useful for tracking sales and integrate the appropriate formulas.

Steps

Part 1 of 3: create the table and name the different columns

Create an Inventory List in Excel Step 1

Step 1. Size the columns

To set column "B" to the correct width, place the cursor on the vertical line between column "B" and "C", at the level of the headers, then click when the cursor changes shape and drag to the right, you can double the size for example. Repeat for columns "C" to "N". Another approach with the same result, once the width of column "B" has been made, right-click on the header of column "B", then select To copy. Then, select the headers of your columns "C" to "N", then right click and do To stick on. Another solution, select your columns "B" to "N", then right click, select Column width. Then enter the desired width and make OK.

Create an Inventory List in Excel Step 2

Step 2. Insert the titles

Go to the cell B2, then enter “Stock”. Select cells B2 To F2, then merge the cells by clicking Merge and Center. Go on G2 and write "Sale". Select cells G2 To L2 and click on Merge and Center. Finally, in M2 write "Forecast", select the cells M2 and N2, then press Merge and Center. Finally, select your cells B2 To N2 and press the button Fat in category Police tab Home.

  • Be aware that if your titles are too long and you want to keep as many columns as possible in your Excel table, you can use the function Orientation for these last ones. You can use this function to orient your title which is horizontal, diagonal, or even vertical. The icon is located in the tab Home, section Alignment. This is the icon with the letters "a" and "b" on an upward pointing arrow.
Create an Inventory List in Excel Step 3

Step 3. Give the column titles

Go to the cell B3, then enter "Product". Go to cell C3 and write "Stock". In the cell D3, type "In production". In the next one E3, enter "Unit price". In F3, type "Stock value". Write "Items sold" in the cell G3. Then put "Amount of sales" in the cell H3, "% VAT" in the cell I3 and "VAT sales" in the cell D3. For the cell K3, enter "Total sales" in the cell L3, write down "% total sold", in cell M3 type "New production". Finally, in the cell N3 write "Products ordered".

Create an Inventory List in Excel Step 4

Step 4. Size the cells

Select cells B3 To N3, then click Line wrap automatically, then on Align Center for vertical alignment and Center for horizontal alignment. Finally, press the button Fat in category Police tab Home.

Part 2 of 3: put the colors and borders

Create an Inventory List in Excel Step 5

Step 1. Choose the background color for the titles

Select cells B2 To N2, and then click the small arrow pointing downward on the icon Fill color in category Police tab Home. Choose color White, Background 1, darker 15%. You have a gray background for your title cells.

Create an Inventory List in Excel Step 6

Step 2. Color the background of the column titles

Select cells B3 To N3, then click the small arrow pointing downward on the icon Fill color in category Police tab Home. Choose color White, Background 1, darker 25%. You have a darker gray background for your column heading cells.

Create an Inventory List in Excel Step 7

Step 3. Apply yellow

Select cells G4 To L7, if you are monitoring the sales of four items monthly, for example, then click on the small arrow pointing downwards on the icon Fill color in category Police tab Home. Choose from the standard colors section, the color Yellow. You have put in yellow your column cells in the "Sale" part.

Create an Inventory List in Excel Step 8

Step 4. Grid your table

Select cells B2 To N7, then click the small arrow pointing downward on the icon Borders in category Police tab Home. Choose option All borders. You have grid your cells in your table.

Part 3 of 3: write the formulas

Create an Inventory List in Excel Step 9

Step 1. Enter the formulas

In order to complete the monitoring table, all you have to do is type the formulas so that your table is dynamic.

  • In the cell F4, write the formula = C4 * E4. This will give you the “Stock Value”. Then select the cell F4, then click on the little square at the bottom right and drag to the cell F7.
  • In the cell H4, enter the formula = E4 * G4. You will thus have the “Amount of sales”. Then, click the cell H4 and then, tap the small square at the bottom right and stretch to the cell H7.
  • In the cell D4, type the formula = (H4 / 100) * 5. This will give you the “sales VAT”. Then go to the cell D4, then click on the little square at the bottom right and drag to the cell D7. Note that in this formula the VAT is 5%, you are free to put the value that suits you.
  • In the cell K4, enter the formula = H4 + J4 and thus, you will get the “Total Sales”. Then, select the cell K4 and then click on the small square at the bottom right and drag to the cell K7.
  • Go to cell K8, then click the icon in the ribbon under the heading Editing tab Home. This is the icon that allows you to automatically sum the cells located above it. The formula will look like this = SUM (K4: K7). Now in K8, you have the sale total of all the items sold.
  • In the cell L4, enter the formula = (K4 * 100) / $ K $ 8. This will allow you to have the "% of total sales". Then, select the cell L4 and click on the small square at the bottom right and drag to the cell L7.
  • Go to cell L8, then click the icon in the ribbon under the heading Editing tab Home. The formula will appear as = SUM (L4: L7). The result should always be 100, this will inform you that there is no error in the percentage distribution of the total sales of each product.
  • In the cell M4, enter the formula = IF (C4-N4 <= 20; "Yes"; "No"). This will inform you in the column "New production", if you need to start the production of new products with regard to your Stock and Ordered products. Note that the value "20" means that when you have 20 products or less left, you must produce new ones. Then select the cell M4, then click on the little square at the bottom right and drag to the cell M7.

Advice

  • You can synthesize activity by day, week, etc. To do it quickly, you can start from this spreadsheet. You just have to right click on the worksheet tab (at the bottom of the page), then in the context menu select Move or copy, Check the box Create a copy, then press OK. Then all you have to do is modify according to your need.

Popular by topic