How to Create an Inventory Control Spreadsheet in Excel with Material Inputs and Outputs
🟢LEARN EXCEL WITH ME IN THE COMPLETE EXCEL COURSE WITH CERTIFICATE: http://excelentejoao.com.br/sejaexcel...
🔵DOWNLOAD THE FREE SPREADSHEET: https://excelentejoao.com.br/wp-conte...
In this free Excel video lesson, we'll learn how to create an Inventory Control Spreadsheet in Excel with Material Inputs and Outputs.
The purpose of an Excel inventory control spreadsheet is to track all incoming and outgoing products, materials, and inventory items.
We can record purchases and sales, that is, inputs and outputs, and the spreadsheet automatically records and calculates the current balance for each registered product.
We'll have two lessons: a registration and inventory tab and a transactions tab.
The first tab, Inventory Record, will help us record all the products we have in our inventory. Using a sum formula, the table will automatically calculate the total inputs or outputs—the final balance for each item.
Conditional formatting is an Excel tool that allows us to automatically color rows based on a cell value. The inventory will automatically be highlighted in green, yellow, or red depending on the quantity.
The sumifs formula will help us calculate the balance of each item. This Excel function adds with criteria, adding only what we need, for example, adding by month, adding by item, adding by type, adding by category, etc.
In the Transactions tab, we will record all inputs and outputs for each product in the inventory. We will use information such as date, type (input or output), product name, quantity, and notes.
The drop-down list is another tool we will use to automatically select the item and the type of transaction.
The options box is, in short, a small list that appears when you click on a cell and allows you to select an option from the list. Many people also call it a picklist, data validation list, option box, name box, checkbox, Excel list, etc.
#ExcelenteJoão #Excel #Dashboard