Multiple Criteria Lookup, Left Lookup -Excel Tips and Tricks

#multiplecriterialookup #leftlookup #indexmatch #Excel has various formula and functions, in this video we have talked about certain Excel tips and tricks. After watching this video you will know about, 1. #IndexFunction 2. #MatchFunction 3. Left Lookup 4. #DuplicateRemoval 5. How to create #dropdown 6. Multiple criteria #lookup 00:00 Intro 00:41 Showing Result 01:40 Index Function 02:33 Match Function 03:28 Left Lookup 06:55 Creating Drop down 09:40 Multiple Criteria Lookup 14:30 Conclusion We have already posted certain videos on #Exceltipsandtricks, it is available in #playlist - https://www.youtube.com/watch?v=6xjqhQXHGMg&list=PLIo8gVJ3LwqhkLGdoyZWW1UTKUT2jGXHV If you have any queries, recommendations, questions please post it in comment section.

Managing Uncertainty in Project - Project Management Tool

#uncertainty #projectmanagement #excel

A project manager has to manage various uncertainties like #Projectschedulevariation, #commoditypricefluctuation, #EstimationError and #Rejection / #Rework. Many professional has confusion over how to handle these uncertainties. In this video we are explaining how to access and #mitigaterisk associated to these uncertainties in a mathematical way.

We have already posted couple of videos related to handling uncertainty,

1. Monte Carlo analysis in Supply Chain using Excel - https://youtu.be/oXpgwp1YQjQ

2. Handling Uncertainty in Procurement using Excel - https://youtu.be/r6E_zXZiLgs

We had used #Yasai Excel add-in to do simulation in first video and two variable #whatifanalysis in second video. This video also uses Excel to do the simulation but it is without any Excel add-in.

00:00 Intro

00:55 Major risks in Project

02:11 Business Case

05:13 Modeling uncertainty associated with Risk in Excel

11:50 Simulation

16:02 Conclusion

If you have any questions please post it in comment section

Handling Uncertainty in Procurement using Excel

#handlinguncertainty #procurement #excel

A procurement professional has to handle many uncertainties like fluctuation in raw material price, variation in demand forecasting, currency fluctuations, etc. In our last video, we have did #MonteCarlo simulation of uncertainty with #Yasai Excel add in.

Last video link - https://youtu.be/oXpgwp1YQjQ

Now in this video, we will use #Whatifanalysis in excel to simulate uncertainty in Procurement

00:00 Intro about topic

00:56 Explaining about uncertainty in Procurement

01:09 Business Problem

03:29 Spend Calculation

05:50 What if Analysis

08:43 Validating result

09:52 Conclusion

If you have any queries, please post it in comment section

Monte Carlo Analysis in Supply Chain using Excel

Watch the video above...

#montecarloanalysis #supplychain #excel

Monte Carlo is used to simulate uncertainties, we just need to mathematically model the uncertainty and simulate it. This video shows us how to simulate the uncertainty in supply chain using excel.

Supply Chain experiences various #uncertainty everyday like currency fluctuation, variation in freight charges, supply & demand fluctuation. Business has to evaluate and validate these uncertainty to #mitigaterisk

After watching this video, you can easily understand on how to create simulation using excel and how to use the simulation to validate the system

We had talked about forecasting using excel in this video, if you want to know about it, please watch our video - https://youtu.be/1nXLyYd4fbI

Yasai Excel add in can be downloaded from - https://http://www.yasai.rutgers.edu/downloads.html

00:00 Intro on Monte Carlo

02:00 Excel add-in

04:10 Explaining Supply Chain Uncertainty - Business Problem

10:00 Simulation

13:56 Result Validation

19:08 Conclusion

If you have any queries, please post it in comments section...

Tricks to use in Vlookup - Excel tips and tricks 2021

#exceltipsandtricks #vlookup #eliminateerror

Watch the above video....

We have shown simple tricks which can be used to eliminate error and save time while using vlookup in #excel, this smart technique will help everyone in their professional life.

1. Multi-column Vlookup - We generally use vlookup multiple times if we have to get the lookup value in multiple columns. This video will show you simple technique 2. Error due to Space - If there is space added to lookup value then we get error. You can get rid of this error easily after watching this video 3. Vlookup output for lookup value contains specific characters in lookup table

00:00 Intro about Vlookup

00:40 Trick-1 : Multi-column Vlookup

01:41 Trick-2 : Eliminating error due to space

03:36 Trick-3 : Output for incomplete vlookup value

04:34 Conclusion

If you have any queries, please post it in comment section.

Excel Slicer - Dynamic Selection tool for Dashboard

#Excelslicer is one of the dynamic selection tool for #dashboard. Many people might have confusion on how visual charts were updated dynamically by selecting an option. This video will clarify that confusion. #Slicer is one of the primary tool in dashboards. Slicer is nothing but a visual filter used in dashboards, which dynamically filters the data and updates the visual charts in dashboards.  

00:00 Intro 00:21 Showing a slicer 00:56 Slicer creation 02:20 Conclusion If you have any questions or comments please post it in comment section

Import data from PDF to Excel - Excel Tips and Tricks 2021

#pdftoexcel is very simple after watching this video, this simple #exceltrick will help to extract table from pdf file to excel without any error. We have already posted a video on extracting data from website to excel, you can watch that video in link - https://youtu.be/1nXLyYd4fbI

Many people do copy and paste for importing data from pdf to excel which is time consuming as it requires formatting. #Excel has lot of hidden features to simplify our works, this video has been created to make #awareness on one such technique. This #exceltip will help many #youngprofessionals in their day to day professional activities.

If you have any queries please post it in comment section below.

Procurement Plan to Save Money - Negotiation Tool

#Procurementplan to #savemoney is one of the important aspects in #supplychain. #Budgeting, #negotiation, #SOB decision, #purchaseplanning and #spendreduction are the 5 key take away from this video. Procurement strategy is explained using a simple technique in excel which can be used as a tool for negotiation and share of business decision. Procurement manager can easily adopt this technique and deploy this in order planning over a period of time.  

Forecasting can be learnt through watching our video on forecasting - https://youtu.be/1nXLyYd4fbI If you are interested to watch next part then please do comment in comment section.

Blockchain Technology in Supply Chain & Challenges

    Blockchain is a technology based on internet, prized for its ability to publicly validate, record and distribute transactions in irrevocable way. It acts as a digital record / encrypted ledger with transparency. This technology was invented to support Bitcoin transactions, since then transactions are encrypted so it is believed to be more secure. 

     Today, Blockchain is a potential game changer in financial segment. In India, 15 banks jointly formed Indian Banks' Blockchain Infrastructure Co Pvt Ltd (IBBIC) to process letter of credit. Bankers believe that use of Blockchain will eliminate paperwork and reduces processing time also ensuring opening of LC against same invoice only once which means duplication is not possible. 

    Similarly, Blockchain is believed to have the potential to change Supply Chain operations by faster and more cost-efficient delivery of products, improving products’ traceability, enhancing coordination between partners, and aiding access to financing.

  

    In blockchain technology, components / assets of transactions like inventory, Bill of Lading, LC, orders, loans, etc. are given with unique identifiers which act as digital tokens. Similarly participants in blockchain are given with unique identifiers or digital signature which they use to add blocks to the block chain. Every transaction is recorded as a transfer of corresponding token from one participant to the other. No participant is allowed to rewrite past data. 

    Food Supply Chain will be the first industry to undergo with more implementation of blockchain technology. World Wildlife Fund in partnership with ConsenSys utilized blockchain technology to track Pacific Tuna (fish) from vessel to super market. There were reports that consumers were buying Illegal, unreported and unregulated Pacific Tuna (IUU) unknowingly, operators of those fishing were believed to use slaves for fishing. Now consumers are increasingly calling for fully-traceable seafood due to the application of blockchain technology. This eliminated Illegal, unreported, and unregulated (IUU) fishing in Pacific region. 

    Walmart , partnering with IBM developed a blockchain-based traceability solution that would be applied across the company’s food supply chain. The tested system allowed the retailer to track incoming food supplies from “farm to store” in near real-time. Aside from increased visibility, the company also explores how blockchain technology can be extended towards monitoring and controlling the spread of foodborne illnesses and help minimize costly recalls. Walmart further tested an application that traces pork in China and produce in the US, to authenticate transactions and the accuracy and efficiency of record keeping.

    IBM is working on with a number of gold and diamond industry leaders (Asahi Refining, Helzberg Diamonds, and others), helping them create blockchain backed solutions for tracking and authenticating their products throughout the supply chain. Everledger startup is attempting to do the same. Maersk and IBM are working on cross-border, cross-party transactions that use blockchain technology to help improve process efficiency.

Challenges:

• Blockchain provides transparency in process though it is considered as advantage, Data Privacy questions of companies need to answered and addressed
• Supply Chain transactions are complex, varies between segments, following same process will not be possible
• Infrastructures like Internet of Things (IoT), Radio-Frequency Identification (RFID) needs to implemented for better tracking using Blockchain technology, this requires investment and digitization of process
• Supply Chain blockchain process needs to be operated as private blockchain with authorized participants but who is going to authorize the participants as there is no central governance in blockchain?
• Business Entities may not entertain two different suppliers of same material to be added to same blockchain network as transparency of price may reduce the negotiation opportunity
• Blockchain requires capital investment, since many participants are going to be part of it, sharing investment capital by all participants may not be possible in all cases
• Currently Bitcoin operates at only 3,60,000 transactions per day through blockchain which is limitation so processing numerous transactions of supply chain will not be possible
• Even when a blockchain record is secure, there is still the danger that a contaminated or counterfeit product might be tagged and introduced into the supply chain, either in error or by a corrupt actor
• Inaccurate inventory data resulting from mistakes in scanning, tagging, and data entry
• Blockchain is immutable so transactions of past can't be revised if there is an error
• Many of the participants in current supply chain process don't have knowledge on Blockchain, educating all the required participants about blockchain will require time and capital investment
• Cost of developing and running cost chain is not clear

Sources:

1. https://www.mckinsey.com/business-functions/operations/our-insights/blockchain-technology-for-supply-chainsa-must-or-a-maybe#
2. https://hbr.org/2020/05/building-a-transparent-supply-chain
3. https://www.wwf.org.nz/what_we_do/marine/blockchain_tuna_project/
4. https://medium.com/@infopulseglobal_9037/blockchain-in-supply-chain-management-key-use-cases-and-benefits-6c6b7fd43094

Dynamic Gantt Chart using Excel - Project Management Tool

#Ganttchart is a #projectmanagementtool used by many #projectmanager across globe. Gantt chart is one of the primary visualization tool in #projectmanagement. Schedule of a project is easily visualized using Gantt chart.

#Excel is used by many project managers to create Gantt chart and they prepare #static charts which takes time to make changes to charts if there is any change in schedule but a #dynamicchart updates automatically when there is a change in schedule.

We are showing in this video on how to create dynamic gantt chart using Excel to save time.

If you have any queries you can post here.