IMPORTANT EXCEL FORMULAS FOR SUPPLY CHAIN PROFESSIONALS AND STRATEGISTS

-


 1. SUM formula: The SUM formula is used to add up the values in a range of cells. In procurement analytics, you can use it to calculate the total spend on a specific category, department, or vendor

SUM formula: =SUM(range)

2. AVERAGE formula: The AVERAGE formula calculates the average of a range of values. This can be used in procurement analytics to determine the average cost of a product or service, the average lead time for a vendor, or the average quality rating for a supplier.
AVERAGE formula: =AVERAGE(range)


3. INDEX-MATCH formula: The INDEX-MATCH formula is used to search for a value in a table and return a corresponding value from another column. In procurement analytics, you can use it to match specific criteria, such as finding the price of a product based on its unique identifier or locating vendor details based on specific attributes.
3. INDEX-MATCH formula: =INDEX(column_to_return, MATCH(lookup_value, lookup_column, 0))

4. COUNTIF/COUNTIFS formula: The COUNTIF formula counts the number of cells in a range that meet a specific condition. The COUNTIFS formula allows you to count cells based on multiple criteria. These formulas can be used in procurement analytics to count the number of occurrences or instances that meet certain conditions, such as the number of orders placed with a specific vendor or the number of times a product was returned due to quality issues.
4. COUNTIF/COUNTIFS formula: =COUNTIF(range, criteria) or =COUNTIFS(range1, criteria1, range2, criteria2)

5. VLOOKUP formula: The VLOOKUP formula allows you to search for a value in the left-most column of a table and return a corresponding value from another column. In procurement analytics, you can use it to look up supplier information based on their unique identification number, retrieve pricing details based on a product code, or fetch contract terms and conditions based on the supplier's name.
5. VLOOKUP formula: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

6. IF statement: The IF statement allows you to perform conditional operations based on a specified criteria. In procurement analytics, you can use the IF statement to evaluate certain conditions and perform different calculations accordingly. For example, you can use it to determine if a supplier meets specific compliance requirements or to calculate the discount applied based on the volume of an order.
6. IF statement: =IF(condition, value_if_true, value_if_false)

7. Pivot tables: Pivot tables are a powerful feature in Excel that allows you to summarize and analyze large datasets. In procurement analytics, you can use pivot tables to summarize spending by category, department, or vendor, analyze trends and patterns in purchasing data, or identify cost-saving opportunities.
Pivot tables: Create pivot table by selecting ‘Insert’ -> ‘Pivot table’ from Excel ribbon.

Comments

Post a Comment

Popular posts from this blog

Strategic Management Process

-
Image
STRATEGIC MANAGEMENT PROCESS   Strategic management process consist of four basic elements i.e. situation analysis, strategy formulation, strategy implementation and strategy evaluation. All of these four elements are essential to execute a management plan in contemplation for required changes and improvements in a plan.  Situation Analysis Situation analysis is a comprehensive assessment of market presence in a company that rely on internal and external parts. It helps to analyze the capabilities and effect of business climate on the company. It inspects the recent and potential consumer of business and how they counter with products and services  Strategy formulation  Strategy formulation is a process that includes establishing and planning the policies of companies. It is segmented into three organizational forms named operational, competitive and corporate. Operational policies are connected with several sectors like human resources, marketing, finance and produc...
Read more

Traditional Procurement VS Modern Procurement

-
Image
Procurement is also known as the process of buying which companies adopt to buy their firm raw material, components and other resources to perform their operation. The traditional methods of purchasing were only restricted to focus on three basic criteria’s that is delivery , quality and cost. Later the researchers identified the importance of issues related to environment and its concern. Keeping environmental challenges in view the concept of green procurement which is even known as environmental purchasing was added as an essential in the firm supply management decisions. Over the previous year green procurement has been gaining a considering attention in both industry and academics, the prime reason is the further issue of global warming, changes in climate and also to prevent the natural resources from being depleted. Therefore, our research primarily focuses on green procurement and its contribution towards enhancing the performance of the firm.
Read more

STEPS OF PROCUREMENT AND BID MANAGEMENT

-
Image
  1. Bid Planning: This stage involves defining the objectives scope and requirements of the procurement bid. It also includes developing a bid timeline identifying key stakeholders and establishing evaluation criteria. 2. Bid Document Preparation: During this phase the procurement team prepares the necessary bid documents such as the RFP bid instructions terms and conditions and technical specifications. These documents outline the procurement requirements and serve as the basis for vendor submissions. 3. Bid Advertising and Outreach: In order to attract a wide range of qualified vendors it is important to advertise the bid publicly. This can be done through various channels such as online bid platforms industry publications and direct communication with potential vendors. 4. Bid Evaluation: Once the bid submission period closes the procurement team evaluates the received bids. This entails reviewing vendor proposals assessing compliance with the requirements and conducting a tech...
Read more