How to lookup Names with Spelling Errors (All kinds of spelling differences, Approximate Match)

Download the file by clicking on the link below if you would like to practice along with me.

Download Link:

Course: If you would like to learn in detail, how to calculate sales variances and the impact they have on sales $, profit $ and profit margin %, and how to explain performance vs budget and prior periods, click on the link for a detailed video course (at a special price). You will also learn how to analyse and present the results of the variances to management and will be able to download solved variance calculation Excel templates.

How to lookup and have an exact match between two columns where the spellings are different with all kinds of spelling mistakes, spaces and characters? In this video, I will show how you can use Excel fuzzy lookup tool to solve this time consuming problem.

Here is the Link to download the Excel Add-in called Fuzzy Lookup:

Often you find yourself comparing two reports or data sets where the names or text strings are similar but do not match exactly. As a human, you can tell that they are the same, but you cannot look up data using the exact match features of Vlookup or Xlookup. In this case, the Fuzzy lookup tool comes in handy. In this video, I show you how to install and use the Add-in to lookup Company names, or Customer Names from two separate data sources, so that you dont have to spend time manually finding the names and then copying and pasting the values from one table to the other.

I have used this method when trying to compare sales reports from two different sources which have the customer names, one report has the freight costs by customer and the other report has the sales by customer. When I try to bring the two together by using a vlookup or an Xlookup, I am unable to do that because the names do not exactly match. In addition, the differences in spelling do not follow a set pattern, so I cannot even use wildcard matches or be creative about using additional Excel formulae to help with the lookup. In this case technology becomes really useful. My recommendation is to use the Fuzzy lookup tool or add-in which can be downloaded from the Microsoft website and installed in Excel. This is such a time saver. I will show in the video the setup of the tables to use the fuzzy logic add-in as well as the use of the “Similarity threshold” to help lookup majority of the company names in the example. By the way, the sample data includes Revenues and Net Profits for 25 of US top Fortune 500 companies by revenue. You can also download the sample Excel from the link provided. Hope this video helps!



Hope you find the information in the video helpful. If you like to watch more videos in accounting, financial analysis and controller ship, videos that help you directly in doing your job, subscribe to my channel. If you liked the video, I would love if you could LIKE it and leave a comment. If you have any questions or feedback, again leave a comment. Lets stay connected at #learnaccountingfinance #lookupnames #spellingdifference

How to calculate Selling price Cost, Margin, Discount and Rebates (All Considered)


Download The Excel file here, if you would like to practice along with watching the video.

Do not forget to subscribe to my Youtube Channel.

Note: If you would like to learn in detail, how to calculate sales variances and the impact they have on sales $, profit $ and profit margin %, and how to explain performance vs budget and prior periods, click here for a detailed video course (at a special price for readers of this post) showing exactly how this is done. You will also learn how to analyse and present the results of the variances to management and will be able to download solved variance calculation Excel templates.

How do you calculate selling price of a product with cost and margin, while taking into account discount and rebates offered to the customer. If you have tried this calculation of a selling price of a product which requires not only the calculation of markup as you only have margin %, but also complicating the calculation are the discount and rebate factors, you probably ended up seeing a Circular Reference error in Excel. This is because the Selling price needs to be calculated, but discount and rebate / commission are also calculated based on the selling price. In this video, I will show you how computers and Excel can help.

Here is the Video …

Microsoft Excel comes with a built in Solver add-in that does the calculation for you. The Solver Add-in is an advanced version of the Goal Seek function and can even consider constraints to the calculation. All you need to do is to set up the product cost calculation template in a way that the Solver Add-in can do the calculation for you. If you had to do it on your own, you would have to use the trial and error method. But with the Excel Solver Add-in, you don’t have to worry, as it is very quick and easy to calculate selling price of a product so that you can achieve the target margin as well as consider discount and rebates and commissions (if applicable). So, if you are provided with, say discount of 1% and rebates of 3% that the customer would usually be able to claim, you can consider this in the product pricing decisions, and with the help of this calculation, you can calculate exactly the price you need to sell for, in order to still achieve the margin % that was originally required.

Connect with me:

Thank You!

Learn Accounting Finance

How to use Excel XLOOKUP Function – 7 tips for Reporting and Analysis using XLOOKUP


Click here to download the Excel sample data file so that you can practice along with me.

Course: If you would like to learn in detail, how to calculate sales variances and the impact they have on sales $, profit $ and profit margin %, and how to explain performance vs budget and prior periods, click on the link for a detailed video course (at a special price). You will also learn how to analyse and present the results of the variances to management and will be able to download solved variance calculation Excel templates.

Do not forget to subscribe to my Youtube Channel

Learn how to use Excel XLOOKUP and what this function can do for you. In this video, I will share 7 practical ways in which you can use the new XLOOKUP function which serves as a perfect replacement for both VLOOKUP and XLOOKUP.

We start with a real life work situation where you have to respond with data requests quickly. The first situation being where the General Manager is asking for Sales for specific customers within 5 minutes. Not only do you provide him the information requested within 5 minutes, but you go above and beyond to provide additional information that he may be interested in. In the second example, your Manager is asking you to complete and send the sales commission file based on annual sales and commission plan. You use XLOOKUP Match mode functionality to quickly respond to the Manager with the completed sales commission report.

In the third example, you have been asked by the Purchasing Manager to provide him help with pulling the most recent purchase price from a long list of materials and purchase history. You use the Search Mode argument of the XLOOKUP function to reverse the order of the data and provide by material SKU, the most recent purchase price and earn bragging rights.

In the fourth example, the external auditors have asked you to provide information related to customers, in a layout which is the opposite of how your sales data is set up. Knowing well that XLOOKUP can replace horizontal lookup or HLOOKUP, you quickly pull the information in the requested format and respond to auditor’s request.

Finally I share a tip that I have personally been using since the VLOOKUP days which would help you avoid XLOOKUP error, when by mistake the data ranges (lookup array and return array) are not aligned. This tip actually saves a lot of time as well and has been one of my favorite tips.

If you have more questions, or would like to learn about advanced ways of using XLOOKUP, please leave a comment. If you enjoy the information provided in the video, please do not forget to press Thumbs Up

Connect with me:

Thank You!

Learn Accounting Finance

How to Analyse and Present financial results – Avoid these four common mistakes!

present financial analysis

When I first started analyzing and presenting financial results to my organization’s leadership, I had no idea what I was doing wrong. I just felt something was not OK. I was not making a good impression. I would leave the meetings with this empty feeling. It was a little embarrassing, and painful at times. Can you relate?

This guilty feeling helped me, however, as I decided to learn and improve my financial analysis and presentation skills. That was a great decision, as things only got better from there. In this article, I will share with you four key mistakes that many beginner and often advanced finance professionals make when presenting financial results in management meetings. I will provide solutions as well, so that you can rectify these mistakes immediately, and become an indispensable and successful finance professional that you deserve to be.

#1: Presenting Numbers, Not telling Stories

The number 1 mistake that most beginner financial analysts, accountants, controllers and sometimes even CFOs make is present the results in the form of tables and numbers, year over variances and percentages. It is as if they are reading the results on the slides.

What is wrong with that, you ask? The problem with this approach is that it is much harder to comprehend. People loose focus quickly of what you are saying. They will not remember or even understand much of what you are saying. Even though, you may have provided some good information, your audience will not be able to absorb it. May be that is why, accountants and finance professionals are sometimes seen as boring. We do not leave an impression by sharing financial results in this format. Most of what we say during such meetings is forgotten before the meeting is over. The biggest problem with this approach is that the key message (if we had any) is lost in the details.

Note: If you would like to learn in detail, how to calculate sales variances and the impact they have on sales $, profit $ and profit margin %, and how to explain performance vs budget and prior periods, click here for a detailed video course (at a special price for readers of this post) showing exactly how this is done. You will also learn how to analyse and present the results of the variances to management and will be able to download solved variance calculation Excel templates.


Presenting results is your opportunity to shine. Each slide of your presentation should have a key message. That key message should also be the title of your slide. Your overall presentation should be a collection of key messages that can be narrated in the form of a story. If you structure your presentation this way, presenting it will be much easy. You will always know what to say and where you are going. It will be a coherent and thoughtful analysis with key takeaways for your audience. They will love what you have to say and remember it. Some may even get inspired!

#2: Too many Ideas in one Place

A lot depends upon the setting you are in. Is it a short, 15 minutes financial results update?, or a longer, more detailed business review? However, when we prepare our presentation slides, often we do not think carefully about what goes into each slide. Often, we lump multiple ideas or topics into one slide. This results in confusion, and our message is too broad. We seem to be jumping from one topic to another and then back quite frequently.


To avoid this situation, always create one slide for each topic. If more than one topic or issues are to be discussed, create separate slides for each one of them. Your audience finds it difficult to comprehend more than 5 objects on one slide any way. This includes a combination of pictures, tables and text. So, try to limit the number of objects to 5.

Do not include long textual paragraphs and definitions. Use minimal amount of animations, only to emphasize key points. For example, if your key message in a slide is mainly about Sales price increase and its impact on profit margin, you are better off not including numbers for Selling, general and admin expenses. If you are comparing prior year vs current year, you do not need to include budget or plan numbers in that slide.

If you have a lot to cover but not much time available, rather than skimming through each slide quickly, skip a few slides, focus on hammering home the key message on a lesser number of slides instead of going through all of your material.

#3: Data Issues and related Disclaimers:

One of the most common problem that we accountants and finance professionals face is the availability of  reliable data that can be used confidently to analyse and explain results. While your colleagues and leadership may appreciate that, the truth is they still expect you to come up with conclusive explanations regardless. I struggled with this one a lot initially. I thought that letting everyone know where the data has holes will help everyone understand that they cannot completely rely on the information. And therefore, should not make business decisions solely on the basis of the available analysis. The problem with this is that they still have to make decisions. They want you to provide them some conclusive guidance and you are not helping them by telling them that they cannot rely on the information you provided. It is like watching a movie without an ending. It can get pretty frustrating.


Although there is no easy fix for this problem, the first step is to acknowledge the challenge your audience is facing. The next step is to identify what elements of the financial information can be relied upon to a reasonable degree. You can always look at the data available from multiple angles and validate so that you can conclude to a reasonable level of certainty.

You have to take this as a challenge, and deal with the uncertainty. This is a key area where accounting and finance professionals can and should add value. Anybody can look at reports and summaries. Where we add value, is that despite problems with data, we can validate the information and make suggestions. This comes with time and experience, but you have to start by taking on this challenge. Trust me! you will discover that you have not challenged yourself enough in the past. Even the incomplete and crappy data gives you some valuable insights that you can share with your leadership. You only get better as you practice this skill.

#4: Most credible source of financial results and analysis

This represents the other side of the balance for finance professionals, and is also a key job requirement. If you are in the accounting or finance profession, specially if you are involved in presenting financial results, this is an absolute must for you. When companies need new products or improvements in existing products, they look to engineers. When they need talented people to hire, they look to HR. Similarly, when they need accurate financial information and advice, they look to finance professionals like You and I to guide them and solve their problems.  Do you provide accurate reports, analysis and commentary?

Your organization’s IT system may be generating automated reports for managers. Do the managers ask you if they have doubts on numbers in a system generated report? If it is not you, then may be, you have not established the credibility that is an absolute must.


If you are not there yet, do not worry. You really need to upgrade your skills such as financial analysis, variance analysis, data analysis as well as Microsoft Excel. You need to develop a thorough and clear understanding of cost and management accounting. Once you get better with these skills, your quality of work improves. Quality reports and analysis combined with your presentation skills such as those discussed in points #1 and #2 will make you an exceptional Finance professional who becomes the Go To person for management.

By the way, if you are serious with honing all of these skills, I recommend that you connect with me. I will be sharing with you, much of what I learnt about detailed financial analysis, presenting financial results, profitability and variance analysis (sales and cost of sales) and lots of advanced Excel. In addition, I will also be sharing my experience on how to find and successfully interview for the right job, how to get promoted quickly and become indispensable finance resource for any organization you work for. Make sure to sign up by clicking here for my email list and receive career transforming tips and information.


When done correctly,  analyzing financial results and business performance, and then presenting it can be an extremely rewarding experience. Especially, when you know you are adding value and making a difference. Your suggestions will be well respected. You will influence small and large business decisions. As you get better with this skill, you will find colleagues and business leaders from other functions relying more and more on you. You will be invited to more meetings and formal and informal discussions. Your advice will be sought. Even, your boss will value you more. Trust me! this is a great feeling.

If you do not already, you will begin to love your work. The fact that you become part of cross functional meetings and informal discussions will help you understand the business even better. You can gain operational insights which will generate more ideas in your head to analyse information differently and present new insights. It just keeps getting better. Your job will be secure and discussions around increment and promotion will become much easier, as everyone from HR to Sales, Operations and Finance functions will be seeing your contributions. No matter who the decision makers are, they will have only good things to say about you. You need to consider the advise given above, however, and start taking action on it immediately. Focus on development of your key skills such as variance analysis, accounting, Excel and presentation. I have seen a significant change in my income and accomplishments since making these improvements. I think I can help you with this. Make to sure to connect with me by signing up with your email address. Lets connect! Click here to subscribe to my email list.

Explaining the impact of Sales Price, Volume, Mix and Quantity Variances on Profit Margin (Current year vs Last Year)

How to explain the impact of Sales Variances on Profitability or Profit Margin of a business? In this article, I am going to explain with the help of an example, how to calculate sales variances, and how to understand the impact of these variances on the profitability of your business. Note that we are calculating the impact of Sales Variances on Profit. This is different from explaining sales variances on Sales $.

Note: If you would like to learn in detail, how to calculate sales variances and the impact they have on sales $, profit $ and profit margin %, and how to explain performance vs budget and prior periods, click here for a detailed video course (at a special price for readers of this post) showing exactly how this is done. You will also learn how to analyse and present the results of the variances to management and will be able to download solved variance calculation Excel templates.

By the time, you are finished with the article, you will be able to understand clearly how to calculate these variances. I will try to be concise, so I assume you are already aware of terms like Sales, margin, profits and variance etc. If you are not fully aware, click on Commonly used financial terms every new Financial Analyst and Accountant should know! where I explain these and other commonly used terms. Also, start following our blog and YouTube channel LearnAccountingFinance, so that you can stay up to date with practical information and training (knowledge you can use immediately at your work).

What you will learn?

We will start with data in the following example. The example uses data for 2017 and 2018 (current year vs last year) to calculate the variances. However, if you are trying to calculate variances versus budget, simply replace last year (2017) with Budget data and the calculation will work just fine.

In this example, we are selling three products which are 1) Apples, 2) Bananas and 3) Oranges. We have data for Sales, Cost of Sales and Profit margins. We also have the quantity, or number of units sold. See Tables below

sales Variance Table 1

sales Variance Table 2

As this article is about calculating the impact of Sales variances on Profit margins, we have deliberately kept the cost per unit as same over the two periods to avoid confusion. However, when calculated correctly, it does not matter if cost per unit has changed. As you will see in the calculations, sales variance calculations do not take into account change in costs. The only thing to consider in that case would be that the profit margin change would have an element of variances from costs as well which needs to be calculated separately (cost variances). In our example however, the profit margin increased by $268 and all of it is resulting from Sales related variances. After performing all variance calculations, you will see the split of variances as follows:

sales-variance-table-12Types of Sales Variances

Lets look at types of sales variances quickly. Broadly, there are only two types of Sales variances.

  1. Price Variance (Change in Selling Price)
  2. Volume Variance (Change in Volume)

The Volume variance is further sub-divided into Quantity and Mix Variances. Do you like acronyms. Here is a good one to remember. Its PVTM

Accounting Explained in 100 Pages or Less:

Sales Variance

Sales Variance Equation

where ‘P’ is for Price Variance, and ‘V’ is for Volume Variance. ‘T’ for Quantity and ‘M’ is for Mix.

If we calculate our variances correctly, the sum of Price and Volume variances should be equal to the total change in Profit Margin (excluding the impact of cost variances). Similarly the sum of Quantity and Mix variances should equal Volume variance. Its time to calculate each of these variances individually.

Selling Price Variance

Lets deal with Price variance first. Any change in price directly impacts Profit margin. From the data available, you can easily calculate the selling price per unit of each fruit (Amount of Sales ($) for each fruit sold divided by the number of units sold). So, for example for Apples, the selling price for 2018 is $11 ($660 Sales / 60 units sold). Similarly, the selling price of apples in 2017 was $10. Below is the table of selling prices per unit.

sales Variance Table 3

Looking at the table above, we can clearly see that the Selling price for apples and oranges have increased in 2018 compared to previous year, while that of bananas has decreased. This means, if we look at selling prices alone, we should see a favorable impact, or favorable variance from apples and oranges and unfavorable impact from bananas. Now, Selling Price variance will be calculated as follows:

(2018 Selling price – 2017 Selling price) x Units sold in 2018.

For apples, this can be calculated as:

($11 – $10) x 60 units = $60 Fav.

Why did we use 2018 number of units sold, and not 2017 units? The answer is that we are trying to determine the impact of change in Selling price. In other words, we are trying to see if the 60 apples sold in 2018 were sold at 2017 price, how would this compare with 2018 price. Therefore, the variance could also be calculated as follows:

Apples sold at 2018 Price – Apples sold at 2017 Price

which is …

($11 x 60) – ($10 x 60) = $60

Apply the same logic to bananas and oranges

Bananas – Sales Price variance = ($1.5 – $2) x 95 = -$48  Unfav. (numbers are rounded)

Oranges – Sales Price variance = ($10 – $8) x 50 = $100 Fav

Here is the summary of Selling price variances,

sales Variance Table 4

So, we can say out of total change in profit margin of $268, Price variance represents $113 (rounded), and we can also see that oranges are the largest contributors to the fav. price variance.

sales Variance Table 5

Volume Variance:

This leads to the calculation of our second variance; Sales Volume variance. Sales variances comprise of Price and Volume only. Since we have calculated Price variance already, we can already calculate the total volume variance which would be…

Sales Volume variance = Total Sales Variance – Sales Price Variance

$268 – $113 = $155

However, we need to still calculate it, as well as the two sub Volume variances, which are Quantity and Mix.

Lets start with Volume variance.

Sales Volume Variance =

(2018 Units Sold – 2017 Units Sold) x 2017 Profit Margin per Unit

Yes, I know you have some questions here.

  1. Why did we use Profit Margin per unit, and not Selling Price?
  2. OK, even if we use Profit Margin, why 2017 and not 2018.

See .. I can read your mind 🙂

Answer to Question 1. Remember we are trying to explain the impact of Sales variances on profit margin, not total Sales $. If we had taken Selling price instead of Profit margin, we would be explaining Sales $ variance (change in Sales $ from 2017 to 2018), but we are calculating the impact on Profit margin. For each increase or decrease in unit sold vs last year, the profit margin will be impacted only by the amount of profit margin per unit and not the total Sales value. Understanding this is important. Note that in the calculation of two sub Volume variances (Mix and Quantity) as well, we will use profit margin per unit and not Selling price per unit.

If you have understood answer to Q1, then you can also understand that when we calculated price variance, we took into account the change in profit margin per unit in 2018 (change in selling price directly impacts the profit margin). Now we are calculating the impact of change in volume (or number of units) and should exclude the impact of change in Profit margin in 2018. This is why we use 2017 Profit Margin. Think about it for a little while, internalize it and if you still do not understand, leave a comment and I will try to explain further.

Time to do the Math:

sales Variance Table 6

At this point, we have understood the impact of Sale price and volume on the $268 change in Profit Margin in 2018 vs 2017.

sales Variance Table 7

However, our analysis is not finished, and we need to understand the impact of Mix and Quantity.

Sales Mix Variance:

Sales Mix refers to the share of each product in total Sales, in terms of percentage. If you look at the number of units sold, you will see that in 2017, 50 apples were sold which is 28% of total sales of 180 units (50/180).

sales Variance Table 8

Sales Mix variance can be calculated as …

(2018 Mix % – 2017 Mix %) x Total units sold in 2018 x 2017 Profit Margin

So, our Sales Mix variance for each fruit will be as follows:

sales Variance Table 9

The share of apples in the overall product mix increased to 29% in 2018 (60/205). This change in mix of 1% multiplied by the total number of units sold in 2018 (205) will give us the number of apples sold that resulted in the increase in Mix %. In this case it is 3 apples (1% x 205 = 3). We know that the total number of apples increased by 10 (50 in 2017 and 60 in 2018). So out of the total Volume change of 10 apples, 3 apples represent Mix change and the remaining 7 represent Quantity change. We can see from the variances above that a drop in mix % of bananas by -9% has impacted the profit margin unfavorably by -$19 but this has been more than compensated by the increase in Mix % of Oranges by 8% (which has a higher Profit margin per Unit compared to bananas).

Calculating Mix variance separately in this way is important because each product has a different profit margin. Assuming the overall volume increased from 180 to 205 (just as in our example) but the mix remained the same as last year, then the change in total profit margin of the business would have been different, although we see the same quantity increase. This calculation of impact of increase in quantity while maintaining the same mix as last year is really our next variance, the Quantity Variance. Calculating Mix variance also helps when trying to explain Profit Margin % changes over the years, or vs budget because Quantity variance has neutral impact on % Profit Margin.

Sales Quantity Variance

As mentioned above, Sales Quantity variance measures the impact of increase in volume, or quantity while maintaining previous year’s mix.

Sales Quantity Variance

= (2018 Units sold @ 2017 Mix – 2017 Units Sold) x 2017 Profit Margin per unit

In our example fruit sales increased from 180 to 205. If the sale had increased maintaining the same product mix as 2017, our unit sales for 2018 would be as follows:

sales Variance Table 10

And the Sales Quantity Variance can be calculated as follows:

sales Variance Table 11


We have calculated all the variances now. The overall increase of $268 in Profit margin can be clearly explained with Price increase resulting in fav. variance of $113 and Volume increase resulting in fav. variance of $155. The volume increase includes $79 due to change in Product Mix.


Note: If you would like to learn in detail, how to calculate sales variances and the impact they have on sales $, profit $ and profit margin %, and how to explain performance vs budget and prior periods, click here for a detailed video course (at a special price for readers of this post) showing exactly how this is done. You will also learn how to analyse and present the results of the variances to management and will be able to download solved variance calculation Excel templates.

If you are also interested in learning how to calculate purchase price variance and the accounting entries involved in recording purchase price variance, click on the link How to calculate Purchase Price Variance (PPV) and track PPV accounting entries in SAP

Are you an accounting and finance professional looking to improve your financial analysis skills? Make sure you connect with me by subscribing to my email list. I will be sharing practical tips and advice that will help you transform you career this year. Click here to subscribe to my email list.

I have a YouTube channel with helpful accounting and finance, Excel and career related videos. You can find my channel by clicking on the link LearnAccountingFinance. Leave a comment if you found this information helpful or if you have any questions!