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

Commonly used financial terms every new Financial Analyst and Accountant should know!

In this article, we will describe and explain some of the terms most commonly used during discussions of financial performance and business meetings. Understanding what these terms means is essential if you are a new or potential Corporate Finance professional such as a Financial Analyst in the FP&A department, or even a new Accounting and Finance professional. It helps significantly in the job interview process as well if you are interviewing for a job in such Finance roles as you are expected to know what they mean and demonstrate experience of their use.

The key financial and business terms we will discuss in this article are as follows:

  1. MTD, QTD, YTD
  2. Plan, Budget, Forecast, LE
  3. Gross Profit, Net Profit, EBIT and EBITDA
  4. Variance, Favorable and unfavorable variance
  5. YoY, vs LY, vs Bud

MTD – represents Month to Date. The ‘To date’ captions are usually used to represent financial performance or activities for a period of time. MTD will always be followed by the name of a month e.g. “MTD August” means results or performance from the start of August (i.e. 1st August) to the current date of the month of August. However, MTD is more commonly used to refer to the entire month, and can also be used to refer to a month that has already finished. For example, if you are currently in the month of August, then ‘MTD April’ will represent the financial performance for the entire month of April.

QTD – represents Quarter to Date. QTD represents the performance from the start of the Quarter to the current date. E.g. if the company’s financial or fiscal year runs from January to December, and you are looking at the performance of Q4 (ie October to December), QTD would represent performance for the period starting from October 1st to the current date. If the current date is December 15th, then QTD will represent the period from Oct 1st to Dec 15th (roughly 2.5 months).

YTD – represents Year to Date. This term refers to the financial performance, KPIs or activities from the the start of the year to the current date. Again, most commonly, this term is used to refer to the period from the start of year to the end of the most recent month. Similar to MTD, YTD is also followed by month. So, for example, “YTD March” refers to the results related to the period starting from the 1st of January and finishing on March 31st. It is important to remember that the fiscal year or reporting year of some companies may not be the standard January to December period. For example, for a company, the financial reporting year may start from July and end in June of the next year. In this case, the YTD period will start from July, and in this case, YTD September will only reflect the performance for the three months period from July to September.

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.

Plan or Budget – Most companies have an annual budget cycle, where well before the start of the year, an extensive and thorough exercise is conducted to plan and quantify the expected (or desired) financial results of the next year. The final outcome of the exercise is called “Budget” or “Plan”, and is represented in the form of budgeted income statement, budgeted balance sheet, and often a budgeted cash flow statement. However, a lot of detail may be available as back up to the numbers reflected in these budgeted financial statements. For budgeted income statement, it is common to start with YTD actual performance of the current year, and then add forecast for the rest of year. The full year forecast (including YTD actual numbers) for the current year then becomes the basis for the next year budget. Budgeted balance sheet and budgeted cash flow statements follow the budgeted income statement with additional assumptions for the budget year.

Forecast – The budget or plan is prepared usually once a year. However, things change quickly, therefore many organizations have forecasts in place as well. A forecast is an estimate of the financial performance, but is typically less extensive and thorough when compared with the budget exercise. The frequency of forecasting also varies. Some companies revise forecasts every month, while others may revise them every quarter or even every six months. It is important to note that while forecasts are revised frequently, the budget remains the same. Therefore, when comparing actual results, often the comparison is done both against the Plan (Budget) as well as the current forecast. This is because budget is not rendered completely irrelevant as a result of the availability of a Forecast. Some bonuses and commissions might still be linked to the original budget, and therefore keeping an eye on the performance vs budget is important.

LE – represents Latest Estimate. This term is used to define the most recently communicated or approved estimate of financial performance, specially related to sales. It is similar to a “forecast”, but different in that a forecast is usually submitted at the start of a quarter or a month, but latest estimate can be provided in the middle of a month or quarter as well. A typical example would be, for example at the start of the month of January, a sales forecast is submitted, lets say of $100,000 for the month. However, every Monday, the forecast is reviewed, and then based on new information, the forecast for the month is revised. Lets say, on the 15th of January, based on actual sales so far and information provided from Sales team, it now appears that sales for the month of January by the end of the month will be $120,000. This will be presented in the form of Latest Estimate (LE). So the forecast is still $100,000, but the latest estimate is now $120,000. Usually, a separate column is used to reflect latest estimate next to budget, forecast or prior year actual numbers.

Note: Not all organizations use Latest estimate, and often the term Forecast is used interchangeably with latest estimate.

Gross Profit – Gross Profit can be calculated with the following formula:

Gross Profit = Revenue – Cost of goods/services sold

where revenue represents the proceeds from the sale of products or services, and Cost of goods/services sold represents the cost of producing or procuring the goods, or in the case service, the cost of rendering the service related to the revenue earned.

What is important is that for the calculation of gross profit, other expenses required to operate the business (also known as Operating expenses) are not deducted from revenue. Gross profit only looks at the profit when considering costs of product or service, and not the operating costs of business. Here is a video I created explaining Gross profit, and the difference between Gross profit and Net profit in more detail…

Net Profit – Net Profit considers all the costs including cost of operating business such as selling, general and admin costs including taxes and interests etc. So, Net profit can be calculated with the formula below:

Net Profit = Gross Profit – Operating costs – interest and taxes

or Net Profit = Revenue – Cost of goods/services sold – Operating costsinterest and taxes

Net profit is also referred to as the bottom line, as this is literally found at the bottom of the income statement, and also reflects the overall net profitability of the business.

FYI: Revenue is often referred to as “Top line” as most income statements start with Revenue at the top.

EBIT – represents Earnings Before Interest and Tax, and is a very commonly used measure of the financial performance EBIT reflects the net profit or net income of a business excluding a) interest and b) tax expense, and can be represented with the formula:

EBIT = Net Profit plus (Interest and Tax expense)

What is the importance or use of EBIT? EBIT simply shows you the operational performance of a business before considering interest and tax expense. Think of it this way … if you are an investor looking to invest in a company, you are interested in knowing the EBIT from operations of a company because the interest and tax expenses may not remain the same when you buy the business. You may have extra cash available and might not need the same level of borrowing as the existing business, or the taxation rules that apply to you may be completely different. By looking at EBIT, you can tell exactly what a business can make from its operations on its own before factors such as interest and tax are taken into account.

EBITDA – represents Earnings Before Interest, Tax, Depreciation and Amortization. In calculating EBITDA, we remove depreciation and amortization expenses in addition to interest and tax expenses. Depreciation and amortization are often referred to as ‘non-cash’ expenses. This is because, the actual outlay of cash has often already taken place in the past. Depreciation is a systematic allocation of the cost of fixed assets over the useful life of the asset. So, for example, if a building is purchased at the cost of $1 million, and the useful life of the building is determined to be 25 years. Although the total cost of purchase ($1 million) may have been paid in year 1, a portion of the cost will be recorded in the income statement every year till the completion of the 25 years. Similarly, amortization is the allocation of cost of intangible periods over a pre-defined period of time.

Why is EBITDA important? When looking at the net profitability of a business, depreciation and amortization create two problems; 1) timing difference between the actual cash flow and the recording of expense in the income statement (as in the example above), and 2) different companies my use different methods or assumptions when calculating depreciation and amortization. As a result of these problems, it is often a good idea to take a look at EBITDA, specially when comparing two or more companies for their operational performance. EBITDA helps you compare the performance of companies by excluding the impact of financial, accounting and taxation decisions.

YOY – represents Year over Year. This usually represents a comparison of prior year to current year. You will hear the phrase ‘Year over year growth’ or ‘year over year decline’. A YoY growth of 2% in sales e.g, represents that sales have increased by 2% vs last year. The formula for this will be:

YoY Sales Growth = (Current year Sale Prior year sale ) / Prior Year Sale

However, the calculation does not need to be for the entire 12 months period. You can also have YoY growth or decline for a period of three months, six months or any number of months or days. For example, you may compare the sales of January to March period of last year with the same period (January to March) of the current year. The key is to compare the same number of months, when doing this comparison.

Variance – Variance simply represents difference. It can represent difference from target, difference of previous performance, difference from estimate or expectation and difference from budget.

Unfavorable Variance – For example, if the target for a sales representative for a month was to make a 100 sales of a given product. but the actual sales he or she made in that month turned out to be 90, the variance in this case is -10. It is denoted by a minus or negative sign because it is an unfavorable variance. This is because the more a sales representative can sell, the better it is for the company as well as the sales representative. Therefore, selling less than target is an unfavorable variance and is a negative situation denoted by a negative sign.

Favorable Variance – If, however the actual sales were 105 units, this would be a favorable situation and the variance would be represented by a positive sign, being +5 units.

Note that the higher the sales or income vs target, the more favorable the variance is. However, the higher the expenses are vs target the more unfavorable the variance, as expenses have an unfavorable impact on the profitability of a company.

vs LY and vs Bud – vs LY represents Versus Last Year (often also referred to as versus prior year), and vs Bud represents Versus Budget. Both of these terms are used to compare against actual current year performance. So, for example, vs LY would represent the difference between actual results this year, and the results for the same period last year. Similarly, vs Bud will represent the difference between actual results this year, and the amount budgeted for the current year. As an example, if actual revenue for current year was $10,000, budgeted revenue for current year was $12,000 and revenue from last year was $7,000. Then, in this case variance vs last year is +$3,000 (because actual sales in the current year are higher) and variance vs budget is -$2,000 (because actual sales in the current year are below the budgeted amount).

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

We also have a YouTube channel (called LearnAccountingFinance) with helpful accounting and finance, Microsoft Excel and Finance career related videos. You can find our channel by clicking on the link LearnAccountingFinance.

Make these 4 Changes to Your Resume before you apply for your next Job

Your resume or CV is your gateway for landing jobs. In most cases, you apply by submitting your resume. If you do not have a job, second to networking with the right people, updating your resume is the most important thing you can do. It is worth spending a lot more time than you already have. But what is wrong with your resume? Why does nobody contact you after you have applied.

The Mindset of Hiring Managers

When I did not know what goes on behind the scenes, I was so upset with not getting any responses from the employers that I started believing that most of the job postings are fake. But that is not true. Unfortunately, in the field of Accounting and Finance, despite many open vacancies, the number of applicants for each job is usually very high. As a result the only option left with the hiring managers, whether from Finance or HR function, is to quickly skim through the resumes and shortlist candidates. Do you know what this means? This means they are looking to eliminate candidates first before they start really going through the resumes in more detail to see if the candidate is the right fit. This is why tailoring your resume to each job posted is so important. Here are my 3 tips on tailoring your resume that will make it stick, and lead to an interview call.

1) Be Selective

Let me suggest to you here that despite your keenness on landing any and every job interview, you need to be selective as well. Just like the hiring managers, you should be able to eliminate jobs postings that do not seem relevant to your experience. Guard your energy level when applying for jobs. From the available list of job postings you should be able to short list jobs that you think are at least a 75% match with your experience and skills. I am not saying that you do not apply on job postings that are under 75% match, they can be part of your second round, once you are caught up with all the 1st grade matches. In the world of abundant applicants, quality and uniqueness is the key.

2) First things First

Once you have identified the jobs, read the description slowly and try to create a mental picture of what the role looks like. What are the challenges that the current employers are facing? What would be there ideal candidate for this role? Then start listing the experience, skills and achievements that match the requirement. Make sure to list the most important skills for the job first. The first few seconds of reading your resume are the most crucial so that you do not become part of the elimination list. If they read the first few lines of your resume, chance that they will read the rest of it increases dramatically, and so do the chances of you being called for interview.

3) Add examples, stats and numbers

Once you have the mental picture of the job requirements, you know exactly which of your experiences, skills and examples you need to highlight early in your resume so that it lights up the tired eyes of your recruiters or hiring managers. For example, if the role is related to the manufacturing industry, and you have worked for a manufacturing company as well as a retail and and a service firm, you know they will be most interested in your manufacturing experience. I can even predict that if you get an interview call, they will be asking questions mostly about your time and experience in the manufacturing company.

So, you need to highlight the same experience. Your resume will be heavy on your manufacturing experience. Not only that, even your experience details for retail and service industry should highlight skills that are similar to, or transferable to the manufacturing industry. Add numbers and details. If you helped the company save costs. Don’t just say that! Mention how you did that and how much cost you saved. For example you can state on your resume; “helped save $300,000 by identifying vendor billing errors over a period of three months” (true story by the way!). It does not really matter if the amount is small. It shows that you care and are always looking to add value to your organization which makes you stand out from the crowd. If you got promoted quickly, mention that. If you received a special award or recognition for your performance, mention that. Whatever is true and can make you stand out without sounding overly arrogant, you should include in your resume with examples and numbers.

4) Always include a carefully written cover letter

I have never submitted a resume without a cover letter. Even if the job posting clearly asks for a two page resume, my two page resume will always be followed by a cover letter. Ideally, the cover letter should be the first page the hiring managers or recruiters should see. A well written cover letter can make all the difference. Generally, the resume is very rigid and restricted in the quantity and type of information you can include in it.

A cover letter can take the form of a conversation though and that is where lies its power. I do not recommend the cover letter being longer than a single page. But you can summarize your most important strengths and skills, directly related to the job in the cover letter. The cover letter should be written in such a way that if someone does not even read your resume, they know what you bring to the table. This does not mean you summarize all your skills, experience and qualifications on the cover letter. The cover letter is all about exactly what you have determined to be the most important skills and experience required based on reading the job posting. Use formatting such as bold and italics to guide the eye of the recruiter to the most important and interesting facts about you.

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 discounted price for readers of this post) showing exactly how this is done. You also learn how to analyse and present the results of the variances to management.

In order to make the cover letter stand out, I have often included remarks, or comments received during my previous employments. For example, once while I was a Senior Financial Analyst, the CFO of the organization remarked, “you have raised the bar in the FP&A department”. Guess what? this phrase is a permanent fixture, typed in bold format, in all of my cover letters. By doing this, the reader gets intrigued and likes to read more on the cover letter. Once you have there attention for 15 seconds, you are probably off of their elimination list and the likelihood of them calling you for interview also increases significantly. You can also include similar statements or remarks in your cover letter, as long as they are true.

I hope these 4 tips that I have shared with you will help you significantly in getting your dream job quickly, as they helped me when I first learnt and applied them. Leave a comment and let me know about your experience.

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.

If you would like to be notified of my new articles and video posts, make sure you join my email list by clicking here and subscribing to my email list. Lets connect! All the best!

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!