Sales Variance and Profitability Analysis (Price, Mix, Vol)
4.2 (17 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
71 students enrolled

Sales Variance and Profitability Analysis (Price, Mix, Vol)

Calculate in Excel and explain variances in Gross profit and Sales versus Budget and Prior periods with recommendations
Bestseller
4.2 (17 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
71 students enrolled
Created by Umair Bhatti
Last updated 5/2020
English
English [Auto-generated]
Current price: $139.99 Original price: $199.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 2.5 hours on-demand video
  • 2 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Assignments
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • What are sales related variances and how do they impact profit of a business? How do these variances help explain business performance?
  • How to explain variances resulting from sales price, quantity and product mix differences.
  • How to completely explain variance vs budget and previous year in terms of dollar amounts and profit percentage (%).
  • How to automate variance calculation in Microsoft Excel so that files can be updated every time a new calculation is required.
  • How to summarize variance calculation results in the form of automated graphs/charts.
  • How to present the results of variance analysis to management using Microsoft Powerpoint.
  • How to analyse and provide recommendations to management based on results of analysis.
  • Complete solved Excel files
Requirements
  • A general understanding of sales, cost and profit (Not mandatory).
Description

Do you ever find yourself, in business review meetings, trying to explain the business performance versus budget, or prior period, but not having all the answers? Then, this course is just for you. 

Being able to analyse and present the profitability of a business through variance analysis is a key business partnering skill. It can help you identify key business issues and establish your position as a trusted finance business partner to the senior management. This is a must have skill for all finance and business professionals, specially Sales Analysts, Financial Analysts, Accountants, Controllers, CFOs, General Managers and CEOs.

In this course, we take a detailed look at calculating and analyzing variances in sales and gross profit (budget variance analysis and prior period variance analysis), driven by changes in sales prices, sales quantities and product mix. With the help of these variances we will be able to explain precisely the performance gap, both in terms of dollar amounts and profit margin percentages, vs budget and prior period. We will calculate the variances by developing automated Microsoft Excel files. Once all the variances in price, volume, quantity and mix are calculated, we will convert them into charts for presentation, and then analyze in detail each variance. We will also explain performance versus budget and prior year, and then make precise recommendations to improve profitability and business performance.

Here is what you will learn in this course?

- You will be able to explain precisely the variance in amounts for Sales, Cost of Sales and Profit vs budget and vs previous period.

- You will also learn how to explain the variance in Profit margin percentage (%) vs budget and vs previous period, and what impacts the variance in margin points.

- You will learn how to summarize the variance results and present it to management in the form of easy to follow graphs/charts (visualization).

- You will learn how to analyse the results of the variance calculations, and provide recommendations to management (using Microsoft PowerPoint).

- You will start with current period actual sales, cost of sales and gross profit of a company, compared with budget and prior year results.

- You will learn about the hierarchy of sales variances; Sales Price, Volume, Quantity and Mix.

- You will learn how to create Excel templates to calculate all of these variances that are automated and update as soon as new data replaces existing data (this is great for monthly and weekly, or even daily updates).

- You will learn about when to use Selling price, and when to use Profit as a base for calculation.

- There will be quizzes and assignments to test and reinforce your knowledge.

- You will also get downloadable solved variance analysis Excel files, that we prepare during the course lectures. 

In summary, by the end of this course, you will be able to explain financial performance vs budget and previous year as a result of changes from sales price, quantities and product mix. You will be able to successfully explain results, and empower decision makers to make informed business decisions based on your recommendations. This will save you a lot of time and effort, and is likely to have a significantly positive impact on your confidence and career growth.

You can achieve this mastery of explaining variances in sales and profitability as soon as tomorrow, or your next meeting, if you take this course now.

So, don't wait and start learning by enrolling for the course, right now.

Hope to see you inside the course!

Who this course is for:
  • Anyone interested in explaining financial performance vs target or previous period driven by sales variances
  • Accountants
  • Financial Analysts
  • Controllers
  • Sales Managers and Sales staff
  • Sales Directors
  • General Managers and Managers of Operations
  • CFOs and CEOs
Course content
Expand all 27 lectures 02:21:02
+ Introduction
2 lectures 12:13

Topics covered and related sections.

Preview 02:24

If you are not familiar with the income statement, this lecture will serve as an additional resource to understand what an income statement is and what the major line items on an income statement represent, including net sales, cost of sales, gross profit and expenses.

Intro to Income statement, Net sales, Gross profit
09:49
+ Sales Variances - What you need to Know?
4 lectures 08:00

In this lecture, you will learn the different types of sales variances and how they are related (the hierarchy).

Preview 02:15

An important questions when calculating sales variances is what base to use for calculating the variance. Should it be Selling price or Profit. This lecture will explain precisely when and why you use each of the two bases for calculation.

Preview 01:39

You will learn about the different possible scenarios for variance calculation that are used commonly used in businesses.

Preview 02:12

In this lecture, you will learn about the impact changes in costs have on the calculation of Sales variances, and why changes in cost per unit are excluded from the course

Impact of changes in costs on sales variances
01:54
The Basics
3 questions
+ Calculate impact of Sales Variances on Profit of a business using Excel
4 lectures 28:27

Calculate sales price variance in Excel

Calculating Sales Price Variance
09:04

Calculate sales volume variance in Excel

Calculating sales volume variance
08:24

Calculate sales quantity variance in Excel

Calculating sales quantity variance
06:41

Calculate sales mix variance in Excel

Calculating sales mix variance
04:18
The best way to learn is to apply your knowledge immediately. Now its your turn. Use the attached Excel file to calculate all the variances you have learnt about in the lectures in this section.
Calculate Profit $ variances from Sales price, volume, quantity and mix
1 question
Calculating Sales variances
3 questions
+ Calculate impact of Variance on Profit margin (%) using Excel
4 lectures 15:08

Learn what is change in Profit margin or %

Preview 00:55

Lets look at an example to see what drives a changes in the Profit % for a business, and what changes result in no impact on the profit %. 

Change in Profit % - An example
03:40

How to calculate the variance in profit % as a result of changes in price

Calculating Profit % variance due to Price
04:16

How to calculate the variance in profit % as a result of changes in product mix.

Calculating Profit % variance due to Mix
06:17
Now its time to check your ability to calculate variances in profit margin %
Calculate Profit margin % variances
1 question
Quiz 3
2 questions
+ Automated Excel modeling for Sales, Cost and prior period variances
3 lectures 21:24

In this course, you will learn how to calculate variance in Sales $ (all variances including price, quantity and mix) by making a small change in your existing calculation.

Calculate Sales $ variances
07:45

In this course, you will learn how to calculate variance in Cost $ by making a small change in your existing calculation.

Calculate Cost $ variances
05:28

In this course, you will learn how to calculate all the variances including profit and sales $ and margin $ vs prior period, by making small changes to the existing file.

Calculate variance vs prior period
08:11
+ Summarize the results of all variances
6 lectures 35:05

An introduction to summarising variance results.

Preview 02:07

Learn how to summarise the results of variances in the form of a table that can also be used to create charts and graphs later.

Preview 03:21

Now, we learn a great way to present the total variances results in the form of one the most popular charts, the waterfall chart in Excel versions 2016 or later (where the waterfall chart is available as an option in Excel already).

Creating a Waterfall chart to present variances - Total variances
09:19

In the previous lecture, we learnt how to create a waterfall chart for total sales variances. In this lecture, we learn how to create waterfall chart for each individual product, and how this is slightly different from the total variances chart.

Creating a Waterfall chart for each product
04:41

In the 2016 version of Excel, Microsoft introduced the waterfall chart. However, in many companies, still, older versions of Excel are being used. If you are working with one of the older versions of Excel too, then in this lecture, we learn how to manually create a waterfall chart in the older versions which looks exactly similar to the auto-generated waterfall chart in newer Excel versions.

Manual Waterfall Chart - Older versions of Excel
11:39

Now that we have created waterfall charts for our variances, lets learn how we can embed these charts into Microsoft Powerpoint so that the powerpoint slides automatically reflect changes in data and charts every time new information is available.

Linking your Chart to Powerpoint for presentation
03:58
Use the attached Excel file showing summary of all variances vs budget to create a waterfall chart of your own.
Create a Waterfall Chart
1 question
+ Analyze, present and make recommendations!
3 lectures 19:27

You have learnt all about the variances and how they can be calculated. However, if you cannot tell what they mean, or what actions business needs to take, all this information is of no use. In this lecture, we anlayse all the variances we calculated deeply and summarise them for management. In addition, we ask insightful questions and make recommendations so that management can take decisive action to improve results and profitability. This lecture will truly enhance your business partnership side, which is the most valuable and most sought after skill for the Accounting and Finance professional.

Analysing variances and making recommendations - Part 1
10:08

Once you have learnt how to calculate variances and what they mean, light bulbs will go off and you will have many questions to ask; many ways in which you can slice, dice and then present information. In this lecture, we discuss, some of the additional factors that impact a business's sales and profitability. Keeping these factors in mind and performing additional analysis on them will help you go a long way in providing insightful analysis for your business.

Analysing variances and making recommendations - Part 2
04:35

Answering a question I receive a lot about what if we sold a new product (that was not budgeted or sold in prior year), or discontinued selling a product which we sold in prior year or was included in the budget. In this lecture I share my recommendation to deal with such a situation in terms of profit $ and % calculation.

Q&A: What if we sold a new product that did not exist in PY or budget
04:44
+ Wrap Up
1 lecture 01:18

Congratulations on completing the course and a big thank you.

Congratulations and Thank you!
01:18