Create an Inventory List in Excel

Microsoft Excel can be used to create a detailed inventory tracking worksheet that can be programmed to calculate factors like the cost of goods, percentage of total sales, the per unit cost and other pertinent information. This article provides detailed instructions on how to create an inventory list in Excel for use in managing the monthly inventory of a retail sales operation.

Steps

Format the Title, Column Headings and Text

  1. Adjust the column width. Place the mouse pointer over the line that separates the column headers for columns B and C. Click and drag the line between the B and C headers to the right to increase the width of column B by roughly double. Repeat this process to widen columns C through M.
  2. Enter the title, the column headings and the text. Click in cell B2 and type "Monthly Inventory January (Year)." Select cells B2 through D2 and press the Wrap Text button on the Alignment menu in the toolbar. Click the Merge and Center button, the Top Align button and the Align Text Left button.
    • Highlight the newly merged cell and select the format brush from the clipboard menu on the far left-side of the toolbar. Click and drag cells F through H to copy the title formatting. In the newly merged cell, type "Total Cost of Goods."
  3. Enter the column text. Type "Quantity" in cell B3, "Product" in cell C3, "Units" in cell D3, "Previous," in cell E3, "Build To" in cell F3, "Case Price" in cell G3, "Unit Price" in cell H3, " # Sold" in cell I3, "Sales Cost" in cell J3, "Rank" in cell K3, "% of Sales" in cell L3 and "Reorder #" in cell M3.
  4. Format the column headers. Select cells B3 through M3 and click the Wrap Text button, the Merge and Center Button, the Top Align button, the Align Text Left button, and the Bold button on the formatting toolbar.

Format the Borders and Shading

  1. Shade the title background. Select cells B2 to M2 and click the down-pointing arrow on the Fill Color button located on the tool bar in the Font menu. Choose "White, Background 1, Darker 15 %" from the Fill Color palette. The title background is shaded.
  2. Shade the column headings. Select cells B3 to M3 and click the down-pointing arrow on the Fill Color button located on the tool bar in the Font menu. Choose "White, Background 1, Darker 25 %" from the Fill Color palette. The column headings are shaded.
  3. Highlight the active columns in yellow. Select cells H4 to M35 and click the down-pointing arrow on the Fill Color button located on the tool bar in the Font menu. Choose "Yellow" from the Standard Colors palette. The active columns are shaded.
  4. Format the column borders. Select cells B3 to M36. Click the down-pointing arrow on the Borders button located on the tool bar in the Font menu. Select the "All borders" option. The column borders are formatted.

Enter the Formulas for the Inventory List Spreadsheet

  1. Enter the formulas for calculating the build to, unit price, cost of sales, sales rank, sales percentage and amount to reorder.
    • Type the following formula in cell H4: =G4/D4.
    • Type the following formula in cell I4: =F$4-B4.
    • Type the following formula in cell J4: =I4*H4.
    • Type the following formula in cell K4: =RANK.EQ(J4,J3:J6,1).
    • Type the following formula in cell L4: =J4/I$2.
    • Type the following formula in cell M4: =F4-B$4.
    • Click in cell J36 and press the Auto Sum button in the editing menu on the formatting toolbar.
    • Type the following formula in cell M4:=J36. All the formulas have been entered and the inventory list spreadsheet is complete.

Inventory List Template

Doc:Inventory List

Tips

  • To add new sheets in order to create weekly, monthly and quarterly inventory reports, click the Add Sheets button near the bottom of the application window, next to the sheet tabs. The Inventory worksheet can then be copied and pasted into the new sheet(s).

Related Articles

Sources and Citations

  • Go with Microsoft, Excel 2007-Comprehensive First Edition, Shelly Gaskin and Karen Jolly. Published in 2008, Pearson Education Inc., Upper Saddle River, NJ.
Retrieved from "https://kipkis.com/index.php?title=Create_an_Inventory_List_in_Excel&oldid=273478"