In this Excel video tutorial, we gonna see How to Make a Purchasing Control Template in Excel with Purchases Control, Suppliers Control and Products Control.

Let's learn how to create a Purchase Control Spreadsheet in Excel from scratch, step by step, to help us automate tasks and save time using Excel. The spreadsheet has three parts: Purchases, Suppliers, and Products. Additionally, the spreadsheet includes dropdown lists, item lists, status indicators that change color, important analyses, and several other automations. With this spreadsheet, we can manage purchases, record purchases, and maintain a history of all purchases made, including product cost, shipping cost, delivery date, and more.

We'll learn how to create dropdown lists in Excel, checkbox lists, option boxes, and item lists, as well as how to automatically update a list in Excel whenever a new value is added. We will use the INDIRECT function in Excel to help us with dropdown lists. The INDIRECT formula ensures that the list is always automatically updated.

We will also learn how to create tables in Excel, how to transform data into a table, or how to format data as a table. Tables are an excellent tool that helps keep our data organized, and whenever a new row is added, it maintains the formatting of the previous rows. This way, we can insert and delete rows in Excel without affecting or messing up the formulas and without losing the formatting.

The first part is the purchase registration and recording section, where we can manage everything we are buying, including the date, product name, quantity purchased, unit price, total value, supplier name, delivery date, status, shipping cost, material ID, and more.

In the status section, we can select from four options: Delivered, On Time, Late, To Purchase. The statuses change color automatically to make the spreadsheet more interactive and dynamic, improving visualization and identification of each status. The colors used are green, red, yellow, and purple.

The second part of the spreadsheet is the product registration section. Here, we have information such as the last update date, product name, ID code, EAN, SKU, brand, stock location, shelf, etc. The product registration table will help us keep the dropdown list always updated.

The third part of the spreadsheet is the supplier registration section, where we can register the update date, supplier name, phone number, email, address, as well as information on the quantity purchased from each supplier and the total amount spent with each supplier. The sum is automatically updated using the SUMIF function in Excel, which uses the supplier name as a criterion and the start and end date range.

#JopaExcel #Dashboard #Excel