Hi, I'm Scott Aden, your engineering and systems expert! With a passion for innovation, I specialize in designing and optimizing complex systems for efficiency and performance.
Almost all business transactions that involve the purchase/procurement of goods or services begin with a Purchase Order on the part of the purchaser, sent to the supplier. The Purchase Order can be a simple phone call between the purchaser and the supplier. In most cases the PO is an elaborate document that acts as an official record of the intent to buy a product or service sent to the supplier by the purchaser.
Because most businesses use some form of Enterprise Resource Planning (ERP) software, a Purchase Order is usually integrated into the system. It forms part of the standard procurement process document flow of Quotation → Purchase Order(PO) →Goods Receipt Note(GRN) →A/P Invoice →A/P Payment.
Figure 1 Procurement Document Flow
However, for some, a Purchase Order is a handwritten document or as mentioned earlier, a simple phone call to place an order, okay, but could be better. That is why in this step-by-step guide, you will learn how to create your own Purchase Order Excel Workbook that has database features to store your orders and automated data entry using Excel VBA/Excel Macros. You will learn how to create Excel Tables, create Named Ranges, and use the powerful XLOOKUP function to automate data entry in the PO form.
The skills you will learn here will help you kickstart your business and make a good ‘professional’ impression if you are just starting out. Or perhaps you already have some business mileage but don’t have the resources to invest in a fully integrated ERP software. Or better yet, you are fed up with the manual document processing at your organization and you want to automate the process by making it more efficient and simple (because you are creative) and stand out in your department. Whether you are a beginner at Excel or you have some significant Excel Hours on your belt (well done you), this will give you some useful insights that you can also apply to other areas of your business and daily life.
First, let’s have a quick overview of the template design.
Procurement Template Overview
A Purchase Order Form
Picture 1 Purchase Order Form
This worksheet will have the Purchase Order Form which you can fill out your supplier details and items you want to procure. It will be designed to enable printing and exporting to PDF. Notice it has a ‘SAVE’ button that we will assign a Macro that automatically saves the form data to a database table. This ensures you keep all your PO data for future reference or analytics.
Database Tables
As standard, you need at least three tables to store the most important information. An items database that has all the items or services your organization procures. A suppliers database that has all the information about your suppliers and a PO database table to save your POs after they are raised. The information you will add in the Items and Suppliers tables will be used to automate data entry in the Purchase Order Form.
Picture 2 Items Database Table
Picture 3 Suppliers Database Table
Picture 4 Purchase Orders Database Table
Picture 4 Purchase Orders Database Table
Here is the video tutorial describing the template overview.
Video Link 1 Template Overview
Looking for project-driven supply chain management software?
Current SCM is the first of its kind – supply chain management software purpose-built to support the most complex procurement and materials management projects. With materials management and vendor document requirements uniquely integrated into the order, Current SCM provides a unified, collaborative platform to streamline the end-to-end process of project-driven procurement and materials management.
If you are engaged in any direct procurement, technical procurement, project procurement or third-party procurement, Current SCM will improve your procurement and materials management workflow. If you are engaged in all four, Current SCM will revolutionize the way you do business.
Contact our sales professionals at Current SCM today!
Data validation is a feature that allows you to control what type of data can be entered into a particular cell. It allows you to ensure that users input only valid data. In our PO form, we want to ensure the user inputs only registered items and suppliers. This helps in maintaining data integrity and accuracy in your spreadsheet. It also acts as an easier way of entering data in a form since it allows you to select values from a dropdown list.
Excel has several types of validation criteria that you can apply to cells including; whole numbers, decimal numbers, lists, dates, time, text length, and custom formulas. We will use the list option in this tutorial.
Select the cell next to the “Supplier Name:” field.
On the Excel ribbon, navigate to the Data tab and in the Data Tools group, select Data Validation.
Choose the type of validation criteria (in this case list). Click inside the Source: field and press F3 on your keyboard to bring up the named ranges. Select the SupplierName and click okay.
Picture 10 How To Apply Data Validation
Highlight the entire range of cells under the Item Description column that will be used as the PO List of items. Repeat steps 2 and 3 above and choose the ItemList named range.
Once done, we are going to use XLOOKUP to get the rest of the supplier information and item information based on our selection.
How to Use the XLOOKUP Function to Get Data from Tables
The XLOOKUP Function is a powerful function that allows you to search a range or an array and returns an item that corresponds to the first match found. That is, if you have a table of 3 columns, you can search for a certain value in the first column and return it’s corresponding value in the second or third column. In our Supplier’s Table, the XLOOKUP will look for a supplier name in the Supplier Name column and return their address, phone and email address. Similarly, we will look for an item description and return its corresponding UoM, Unit Cost, and Item ID.
The XLOOKUP takes six arguments. Three arguments are required for the function to work, while the other three are optional, that is, even if you don’t include them, the function will still work.
Argument
Description
lookup_value
(required)
This is the value you want to search for
lookup_array
(required)
The array/column/range to search
return_array
(required)
The array/column/range from which the corresponding value will be returned.
[if_not_found]
(optional)
Specifies what value to return if the match is not found. If omitted, Excel returns #N/A error.
[match_mode]
(optional)
Here you define whether to perform an exact match or an appropriate match. 0 – Exact Match. If none is found, return #N/A. This is the default. -1 – Exact Match. If none is found, return the next smaller item. 1 – Exact match. If none is found, return the next larger item. 2 – A wildcard match *,?, and ~ which have special meaning.
[search_mode]
(optional)
Specify the search mode to use: 1 – Perform a search starting at the first item. This is the default. -1 – Perform a reverse search starting at the last item. 2 – Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned. -2 – Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.
For example, in order to get the address of the supplier selected in your PO template, provided you have already registered them in your SupplierDB table, here is how the XLOOKUP would look like.
Picture 11 XLOOKUP Syntax
The first argument, lookup_value, is represented by whatever will be in range C12.
The second argument, lookup_array, is represented by, “SupplierDB[Supplier Name].” This is the syntax for referencing a table’s column inside any function/formula. The first part, “SupplierDB”, is the table name and the second part in square brackets “[Supplier Name]” is the column name.
The third argument, return_array, is represented by “SupplierDB[Address]”. It is the same table column reference syntax as described above.
Lastly, we tell the function to return an empty string, represented by two double quotes (“”), if a match is not found.
To get the other field values like email, phone, and ID, all we need to do is change the third argument(return_array) to reference the required column.
SupplierDB[Email] for the Email: field
SupplierDB[Phone] for the Phone Number: field
SuppierDB[Supplier ID] for Supplier ID: field
The same steps used to get the Supplier information will be used to get the items information. Check out these videos for a deeper dive into XLOOKUP.
Video Link 6 Cell Validation & XLOOKUP Part1Video Link 6 Cell Validation & XLOOKUP Part2
Looking for project-driven supply chain management software?
Current SCM is the first of its kind – supply chain management software purpose-built to support the most complex procurement and materials management projects. With materials management and vendor document requirements uniquely integrated into the order, Current SCM provides a unified, collaborative platform to streamline the end-to-end process of project-driven procurement and materials management.
If you are engaged in any direct procurement, technical procurement, project procurement or third-party procurement, Current SCM will improve your procurement and materials management workflow. If you are engaged in all four, Current SCM will revolutionize the way you do business.
Contact our sales professionals at Current SCM today!
The clean, renewable energy age is here to stay and hydrogen is at the forefront of this revolution. The year 2024 is shaping up to be a landmark year for clean energy such as hydrogen, with significant investments, technological developments, and strategic actions driving the sector forward.
Let’s take a closer look at what’s driving this transformation.
A Surge in Investment is Fueling Growth
One of the most notable trends in the hydrogen industry is the increased collaboration among North American countries. The United States, Canada, and Mexico are joining forces to share knowledge, resources, and best practices, driving progress and accelerating the development of hydrogen technologies.
In fact, Jennifer Granholm, the United States Secretary of Energy, is confident that “Clean hydrogen is a game changer. It will help decarbonize high-polluting heavy-duty and industrial sectors while delivering good-paying clean energy jobs and realizing a net-zero economy by 2050.”
1. Policy Tailwinds
Across North America, governments are strategically implementing favorable policies to speed up the hydrogen sector’s development. These policies act as catalysts and provide a dynamic environment for investment and innovation in clean energy.
2023 witnessed a landmark shift in the U.S. clean energy landscape with the Inflation Reduction Act (IRA). This game-changer has become a global talking point, especially for its significant impact on clean hydrogen production. The IRA throws a financial lifeline to clean hydrogen projects through tax credits.
Hydrogen fuel and extracted clean energy are also gaining momentum outside of the United States. Canada has taken a leadership role with its Clean Hydrogen Production Tax Credit. This fiscal measure offers tax breaks to companies that invest in clean hydrogen projects.
Tax incentives range from 15% to 40% for eligible project expenditures. Projects that produce hydrogen with the lowest carbon intensity receive the most assistance. This makes clean hydrogen more economically attractive and underscores the government’s commitment to a sustainable energy future.
These collaborative efforts by North American governments pave the way for a robust hydrogen economy, fostering clean energy development and contributing to a more sustainable future.
2. The Rise of Hydrogen Hubs
A new concept, hydrogen hubs, is also gaining traction. Think of them as a one-stop shop for all your clean hydrogen needs.
These strategically located hubs consolidate critical infrastructure elements under one roof. Production facilities using renewable energy like solar or wind power generate clean hydrogen. High-pressure tanks or cryogenic storage facilities ensure safe and efficient hydrogen storage. Dedicated pipelines or innovative transportation methods like ammonia carriers facilitate long-distance transport to users.
Hydrogen hubs eliminate the need for complex, point-to-point logistics by integrating all the essential elements in one location. This reduces transportation costs, minimizes energy losses, and creates a more efficient hydrogen ecosystem.
Where Is Hydrogen Headed in North America in 2024?
The winds of change are blowing strong in the North American energy sector, and hydrogen is at the forefront of the storm. Here’s a glimpse into what’s on the horizon for hydrogen in North America in 2024:
Net-Zero Ambitions Propel Hydrogen Forward
Businesses increasingly know the need to adapt their operations to achieve net-zero emissions. Hydrogen, a clean-burning fuel, is emerging as a frontrunner in decarbonization efforts. With robust policies like the U.S. Inflation Reduction Act and potential follow-ups, North America is well-positioned to lead the global hydrogen race.
A Booming Industry on the Horizon
The North American hydrogen market is expected to be a game-changer, reaching a projected annual value of $140 billion or more by 2030. Current capacity is around 0.53 million tons per year (mtpa), but that number is set to climb significantly, reaching an estimated 1.7 mtpa by 2030.
Blue vs. Green Hydrogen
While North America dominates blue hydrogen production (using natural gas), the focus is shifting towards green hydrogen (produced from water using renewable energy). This shift is driven by the need for truly sustainable solutions and increasing global demand for clean hydrogen.
Trends in the Hydrogen-Fueled Aviation Industry in 2024
While Europe implements mandatory SAF usage in 2025, North America is also expected to see similar regulations emerge in the near future. 2024 will be a critical year for the aviation industry as it prepares for this shift towards sustainable fuel sources.
Cost Reduction and Demand Growth
Some challenges remain in the path to widespread adoption of clean hydrogen: reducing manufacturing costs and stimulating demand. Collaboration between industry, government, and academia is essential to overcome these hurdles. By working together, these groups can drive innovation in areas like hydrogen infrastructure and transportation applications, ultimately leading to a more robust market for clean hydrogen.
One of the biggest challenges facing the North American aviation industry is the need to increase production capacity to meet the rising demand for SAF. This requires partnerships and investment in new SAF production facilities.
Mexico: The Rising Star in the World of Clean Energy
While the U.S. and Canada have grabbed headlines in the North American hydrogen race, Mexico is quietly emerging as a potential game-changer. Mexico boasts some of the world’s most abundant solar and wind resources, particularly in northern states like Sonora and Tamaulipas. This natural resource provides a perfect foundation for clean hydrogen production through electrolysis powered by renewables.
Strategic locations along Mexico’s borders could become hubs for clean hydrogen production, attracting electrolysis, storage, and transportation businesses. These hubs could further strengthen North American energy security and collaboration.
Despite its promising potential, Mexico faces challenges in its quest to become a leader in clean hydrogen. For instance, building the necessary infrastructure for large-scale hydrogen production, transportation, and storage will require significant investment. Additionally, the region requires a clear and comprehensive regulatory framework for hydrogen production, transportation, and use is needed to attract investment and ensure safety and environmental responsibility.
The Road Ahead: Challenges and Opportunities
While the future looks bright for hydrogen, there are still challenges to tackle, like safety regulations and workforce development. However, initiatives like the COP28 declaration and the U.S. Department of Energy’s Hydrogen Shot program show a commitment to overcoming these hurdles.
In conclusion, the hydrogen sector in North America is undergoing a remarkable transformation in 2024. With significant investments, technological advancements, and a focus on clean energy, the region is leading towards a more sustainable future.
Looking for a partner for your new Technology Pilot Plant?
Vista Projects is an integrated engineering services firm able to assist with your hydrogen project. With offices in Calgary, Alberta, Houston, Texas, and Muscat, Oman, we help clients tailor engineering phases for the unique needs of their projects. Contact us today!
Looking for your next (last?) great job at a great EPC firm?
Vista Projects is hiring for multiple roles in engineering and system integration – apply today!
