Vending Machine Inventory Excel

May 12, 2020 How It Works. The industrial vending machines are intuitive and can store virtually any type of item for asset control in any industry. Employees first use the machine’s identification system for access via barcode scanners, proximity cards, biometrics, smart cards, mag-stripe IDs, or other method; you can configure the machines with whatever works for you. As Scotland's leading vending solutions supplier, we provide all of the services you will need, whether you have 10 or 500 employees. From desktop coffee machines to full-scale hot and cold food vending machines, Excel will have a solution for you. Vending machine inventory spreadsheet is a significant tool in the business for conducting the day-to-day operations of the vending machine business. It is very important to manage inventory of vending machines, and in the long run this can be very useful. Simply put, being able to keep a tab on inventory and sales of your machines is yet another important foundation to a smooth and successful vending machine venture. Doing it in an efficient and organized fashion is the goal, but difficult without the right resources. Instructions on how to create and manage vending machine templates in Toucan. Microsoft Excel Inventory (Simple) - Duration: 7:59. Train The Brain Videos Recommended for you.

Vending Machine Inventory Control
Vending Machine Inventory Template
Tool Vending Machines For Sale
Inventory Management Vending Machines
Vending Machine Inventory Excel Templates
Vending Machine Inventory Excel Template

Vending Machine Inventory Control

Electrolock is a premiere provider of insulation products and solutions for various manufacturing and repair industries. Some of their products include high voltage insulation products for power generation and electric coil manufacturers, custom insulation solutions, and thermal insulation for extreme environments such as large motor generators. They create electrical, battery, and thermal insulation, and bespoke product offerings to service their customers’ most demanding requests.

In this post we’ll look at how Electrolock partnered with a Microsoft Gold Partner, Confluent, to build an inventory management solution using Microsoft Power Apps, Power Automate, and Microsoft Dataverse.

Business scenario

Electrolock and their customers needed to perform several functions with easy and accuracy – track inventory used, create a purchase order, perform a simple reconciliation of inventory for invoicing, and finally, record revenue using Dynamics AX, their ERP system and single source of truth.

Electrolock was in the process of trying to win a customer away from a competitor who had an electronic point of sale system for their clients to track inventory used in electric motor repair. They realized that to win the bid, they would require a similar, easy-to-use digital solution.

Before Power Platform

Prior to the Power Platform solution, Electrolock and their customers were using Excel spreadsheets to communicate inventory levels and shipments back and forth over email. This was a cumbersome process, it was imperative for the business to find an online solution, and an opportunity for Electrolock to win bids against their competition.

Power Platform Solution – Inventory Management

While Electrolock was researching different options for creating their own solution for managing inventory online, they decided to hire Confluent, a Microsoft Gold Partner. Together they successfully rolled out a Power Platform solution used by Electrolock and their customers for Inventory Management.

Power Apps canvas app used for inventory management

Vending Machine Inventory Template

The Inventory App is a Power Apps canvas app with data stored in Microsoft Dataverse. The app is used by Electrolock’s customer, who requires parts to fix electric motors. The app is used on mobile tablets on the manufacturing floor. All parts are given on consignment to the customer and stay on Electrolock’s books until the customer creates a PO for a specific job or jobs. Electrolock invoices the customer based on the jobs attached to the PO. The customer uses the app to track all parts “picked” from inventory and used for a specific job, and to close jobs every two weeks so that Electrolock can invoice the customer. The size of parts varies from being as little as nuts and bolts to as big as a suitcase. The smaller parts are stored in vending machines and the bigger parts are stored in big bins on the manufacturing floor.

For a clearer idea of how the app works for a customer of Electrolock in the electric motor industry, consider the steps of a worker on the ground floor who starts a job and needs parts to fix an electric motor.

They use the app to:

Create a job and add job line items for each part.
Easily add line items: the app includes a field that allows the user to search for item parts using either the item number or item description.
Enter a “Lot” number that indicates which production batch a part belongs to and the quantity of the item taken. The Lot can be used later for pinpointing the manufacturing date of a part if it is faulty or causes problems.

The customer then goes to the floor and picks the parts from the bins or vending machines. They continue to add inventory in the app as they complete the job to fix the electric motor.

Power Apps canvas app screen for adding items to a job

After couple weeks, the customer performs a “reconciliation” process by validating the count of inventory used and returning any unused parts. The app allows the supplier to return all items that were not used and auto-calculates the total parts used in the job.

At this point, a manager from the customer’s team needs to check that the items used have been correctly entered and creates a Purchase Order (PO) for all the jobs in that time period. The manager uses a model-driven app to create a PO. They assign all the jobs to that PO, thus grouping them together to get a total dollar amount of the PO.

Model-driven app used to manages the active purchase orders

The last step in the model-driven app from the customer side is to run a Power Automate flow that transmits the PO. The flow sends an email with an attached CSV file that consolidates all the parts used on various jobs by item number, quantity used, and total amount per item.

Left: Flow that transmits PO from consolidating all the parts used by the job; Right: Email with an attached CSV file generated by the flow.

The CSV file is transmitted via Power Automate by invoking a FetchXML HTTP request to Microsoft Dataverse, and then converting that response into a CSV table. From the CSV, a file is created and stored in SharePoint, and the Dataverse record is updated to show the transmission of the PO data is complete.

The model-driven app is also used to manage inventory. “Transfers” represent the inventory sent from Electrolock to the client. They can use the Power Apps canvas app to receive inventory which has been created in the model-driven app. Electrolock uses Power Query to retrieve data from Dataverse to currently manage its inventory. Soon, an integration between Dynamics AX and Dataverse will be established to handle transfers.

Solution Architecture

The Inventory Management solution involved Power Apps and Power Automate. Both canvas and model-driven apps were used for their unique strengths. A flow completed the communication of purchase orders. All of the parts relied on Microsoft Dataverse as the underlying data source.

Impact and benefits

Some of the key benefits of the Power Platform solution are as follows:

Simple and automated system used by floor technicians to record inventory used on projects
Easy for technicians to enter parts used as they work with mobile devices on the floor
Timely, accurate and relevant data – reducing the amount of manual entry and rework
Seamless reconciling process – listing inventory, drafting PO, sending invoice
Easy for Electrolock to track the inventory that is sent to the customer with the model-driven app
Reporting – PO Report for Jobs and inventory used, On-hand Inventory so customer knows what is in the warehouse, and the replenishment report which shows which items need restocking.

About the partner

Confluent, a Microsoft Gold Partner, started their Power Platform journey as an early adopter in 2015 and to date have built more than 300 solutions across 100 midsize and enterprise customers. They focus on delivering cloud-based business application solutions on Dynamics 365 and the Power Platform. For them, the combination of Office 365, Power Apps, Power Automate and Power BI has allowed companies of all sizes the ability to afford a digital transformation initiative. They have leveraged these tools combined with Microsoft Dataverse to solve a myriad of problems, such as process automation, project management, and field service. It is through delivering an App in a Day workshop at a Microsoft Technology Center that Electrolock learned about Confluent.

You manage your most valuable assets: Your Employees. IDS takes care of the rest.

IDS specializes in equipping organizations across numerous industry verticals with innovative, controlled dispensing solutions in order to maintain efficiency and security 24/7/365.

Applications

Inventory control systems for today’s workplaces

Inventory control systems are our specialty. Regardless of industry, our solutions are designed to meet the needs of your workforce, ensure employee satisfaction and most ultimately, improve the bottom line. Our Intelligent vending machines can help track inventory without being physically at the machine with real time software. Simply login to the program and see what your inventory needs are for each machine. Inventory control systems with smart vending machines are a huge benefit to any business.

Take control of your medical supplies inventory while improving the level of care provided to your clients.

Provides customers with instant, self-service access to workout supplies and fitness-friendly snacks and beverages.

Tool Vending Machines For Sale

Provide secure, self-service access to office supplies and tech devices and accessories – anytime!

Reduce textile replacement costs by up to 75% while also decreasing laundering costs.

IDS’ automated micro-stores are as easy to use as scanning your barcode and retrieving your product.

Inventory Management Vending Machines

Maximize the efficiency of your supply operations to reduce inventory costs by up to 30%.