Most businesses are heavily reliant on suppliers to keep their business running smoothly. This makes supply chain management a critical business function – one where MS Excel thrives.
Using Excel for supply chain management is ideal because it allows you to keep tabular data, which can be used as a database by using VBA code. Additionally, Excel has a very advanced filtering system that allows different data to be selected from a wide range of tabular data.
In this post, we’ll explore some of the ways to use Excel for supply chain management with examples and screenshots. There’s also a free Excel Supply Chain Management template to download at the bottom of this post.
Global Supply Chain Management Issues
Many businesses work on ‘just in time’ ordering, so when the unexpected happens with deliveries, this causes a disaster if not properly managed.
This is very apparent at the moment with major supply chain problems between the UK and Europe. Similarly, the worldwide chip shortage affecting vehicle production is another example of how a chink in your supply chain management can cause a significant risk to the business.
“The Association of Manufacturers of Domestic Appliances has reported a shipping cost increase of up to 300% since 2020, while the China-Britain Business Council (CBBC) say it’s as much as 350%.” – AAT
Setting Up Excel for Supply Chain Management
Excel is an incredibly powerful tool and has the capacity to store and number crunch vast amounts of data, making it ideal for keeping records of your supply chain.
A simple supply chain management spreadsheet would have a spreadsheet tab with the following columns:
- Supplier Name
- Address
- Phone
- Product
- Quantity
- Price
- Date Ordered
- Date Expected
- Date Delivered
This keeps track of orders being made to suppliers and allows the monitoring of prices between different suppliers. It also enables delivery dates to be checked and shows if deliveries from a particular supplier are on time.
You can add filters to your supply chain management spreadsheet by selecting the column headings and then click the Data tab on the ribbon. Click on the Filter icon in the Sort & Filter group of the ribbon, and you will see drop-down filters for each column.
The filters allow enormous flexibility in terms of selecting specific data. For example, you could look at ordered dates between the start and finish dates or filter out what has been delivered late. You can easily add a formula to compare the expected date to the delivered date and add a filter to the column.
You can easily see which suppliers are on time, and which ones are late with deliveries.
Learn global supply-chain strategies, planning and operational concepts with this insightful paperback from John Mangan.
Using MS Excel For Stock Control
Within your supply chain, you also need to monitor stock levels to ensure enough stock of particular products to satisfy demand. To add a stock control tab to your supply chain management spreadsheet, add an additional tab by pressing the ‘+’ icon in the bottom left-hand corner of the sheet and add the following columns:
- Product
- Quantity
- Price
- Date
- In / Out
The stock would be logged with an I flag in the In / Out column as the stock is received. The stock would be logged with an O flag in the In / Out column as the stock is issued. Where an issue of stock is made, quantities and prices are given a negative value, making it easy to use filters to see how much of the product is in stock.
By selecting a range of values or a column with your cursor, you can see the totals in the bottom right-hand corner of the Excel window.
You can use the filters to select ranges of dates or particular products. A product filter will show you how much of a particular product is in stock. If it is low, then a new order needs to be placed with the relevant supplier.
Expanding Your Supply Chain Spreadsheet
Additional tabs could be added to your supply chain spreadsheet to show more information on products and suppliers. For example, you could have a list of products in tabular form, with details of which supplier it can come from, cost from that supplier, whether it is mission-critical, and lead times for that supplier.
If the order of the product is not urgent, you can focus more on cost, and pick the supplier with the lowest price, provided that the lead time is reasonable. If the product order is urgent, you need to focus on the supplier with the shortest lead time and consider the cost a secondary consideration.
If it is a mission-critical product, then it becomes even more urgent. You could also have a list of suppliers in tabular form showing:
- Name
- Address
- Phone Numbers
- Email Address
- Credit Terms
- Rating
The rating could be a value of 1 to 5, dependent on how good the supplier is in your view in delivering on time and not making mistakes with order quantities or product names.
You could also have a tabular table of contacts at each supplier, giving their direct line phone numbers and emails. When deliveries need chasing, having someone that you know in an organisation is handy.
By using filters on the tabular data, you can extract the information necessary to make purchasing decisions and manage your supply chain.
Excel Project Management Toolbox: Top 25 Excel Tools
Supply Chain Working with a Database
Taking this idea one step further, you might make your Supply Chain Management spreadsheet more sophisticated by using VBA code and linking it back to a database, such as Microsoft Access.
A relational database can easily be created and easily by linking your Excel spreadsheet with VBA code. Excel forms can provide a professional-looking front end to allow users to update the data and enable users to query the database in the same way you would use the Excel filters.
You can quickly produce reports tailored to your requirements, e.g., the best-rated suppliers and what they supply. Who has the shortest lead times?
NEED HELP WITH AN EXCEL PROJECT, BIG OR SMALL?
The Excel Experts can help you or your business complete an unfinished project, integrate web applications, automate tasks, and much more.
Conclusion
By using tabular data in Excel, it is straightforward to monitor purchasing from suppliers and make the best purchasing decisions to get the best value for money and make sure that your organisation does not run out of an essential product.
You can also see fallback suppliers, so if one supplier cannot complete an order in the time demanded, you have other suppliers that may be able to fill the gap. Control of purchasing data in this way is essential to the smooth running of any business.
Free Spreadsheet Download
Complete our form to download a simple supply chain management template spreadsheet for your business.
