The Complete Sales and Profit Variance Analysis Course (Price, Volume, Mix impact on Profitability)
ฝัง
- เผยแพร่เมื่อ 14 ต.ค. 2024
- 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?
NEW Course Alert (A newer, more detailed course is now available)
Here is the link for updated income statement & EBITDA analysis (New Course) ebitda.thinkif...
Sales variance Full Course: bit.ly/3xjMR8t
Email questions to: learnaf@outlook.com
FP&A playlist: • FP&A - Financial Plann...
In The Complete Sales Variance Analysis course in Excel, you will learn how to calculate and analyze sales price, volume and mix variances in Microsoft Excel, and then convert them into Powerpoint presentations providing management recommendations.
Subscribe: www.youtube.com...
Are you a First Time or New Manager? Check this out: amzn.to/35qWzLc
/ learnaccountingfinance
www.learnaccou...
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.
Here is the link again: ebitda.thinkif...
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.
Hope to see you inside the course!
![โรงเรียนดัดสันดาน นักเรียนเถื่อน..!! [โรงเรียนไดเม่]](http://i.ytimg.com/vi/vRKPeXn4Fl4/mqdefault.jpg)
New course (FULL EBITDA analysis): ebitda.thinkific.com/courses/learn
This helped me SO much on my grad project. Thank you!
U explain it better than the books and my instructor.
Thank you T T ❤
Thank you so much for sharing in such a beautiful way
I am very grateful to you for this video. It helps me to better understand price-volume variance. Thank you.
Thanks L RM.
@@LearnAccountingFinance In the video, you computed the profit variance of $6,115. However, I want to compute the sales variance of $8,905. I computed the price and volume variance for sales, but I need to further compute the quantity and mix variance for sales. What is the formula for the quantity and mix variance for sales? I sincerely appreciate your help.
Amazing video.... Plz add discount, new prod discontinued, return expiry breakage impact also,
Thanks for the good explanation, can you please upload the excel sheet that you used in the video. Thanks
This is great ! How would you do this for changing cost ?
Many Thanks that was very helpful , Respect from Saudi Arabia
Thank you very much. I have spent some great time of my life in Saudi Arabia. Great memories.
@@LearnAccountingFinance I hope u will make video for sales variance Analysis by cost and GP ( gross profit)
This is amazing, you break it down so clearly, Thank you and please carry on with the excellent work,
Thank you very much Almas.
How you explain is flawless. Learning quite a lot, thank you.
Thank you Julien. Appreciate the feedback.
Greetings from Brazil!!
Is still this course available for buying, and if it is, how could I obtain it from Brazil?
Man you rocked!
Thank you. Yes this course is still available at varianceanalysis.thinkific.com
Thanks a lot for the great video. I signed up for the course. But I could not find the solved excel templates you worked on in the video in order for practising.
Thank you amir. You will have to purchase the course to get access to the files.
Good explain
Combined this with what if analysis for budged plan and I just done my assignment, thank you so much
Excellent!
@@LearnAccountingFinance quick question , is there a function I can use for example if the price is 10£ for 900 units sold and if I increase the price by 10 p the units drop to 880 but is there something I can use if I increase by 20 p the units sold to drop by 40 and so on ?
You can create a formula. In this case -20 units per 10p = -2 units per p. So, multiply the desired change in pennies with -2. So change of 20p in price = 20 x -2 = -40units. Just use cell references in place of numbers.
@@LearnAccountingFinance I think I got it, but what would the formula would look like ? And do you have a donation link ? Because you deserve it for the help
Thanks a lot Tom. You are a kind soul. I would appreciate if you donate the amount you intended to someone in real need near you. You can do this on my behalf if you like. :).
Thank you for the video, very clear explanation for the mix effect among products which I was personally struggling with, Is the excel file you used for available as an attachment ?
Also I have a question; If I have a new product being sold that didn't exist in the budget were should it be allocated to ?
Thanks! All of this is available at ebitda.thinkific.com/courses/learn
Hi, thanks for the video; very well explained. I am performing a similar exercise for my organization; The concepts are very clear for me nevertheless, when I make the calculation on product line (Apples/ Bananas ...) and add them up I get 1 result; But I I apply the same principles on the totals the numbers don't add up; I understand why they don't add up for the mix effect , but the number don't match even for the Price effect; I would be very grateful if you could examplain why this is the case;
Very helpful
Dear, really appreciate the way you explain things. However, you label is misleading. Most of them should Profit Volume variance, Profit Quantity Variance, you labeled them Sales instead.
I am mistaken?
Valid point! I guess i started with sales not intending to add profit variance but then added profit variances anyway. Thanks for the suggestion.
Hi,
Thanks for this video, explanation is very clear.
I am trying to perfrom similar analysis for Transport emission where I have Emission generated, volume transported, Km, Mode of transport, Payload, route optimization of last year & this year. I am bit confused about how to get the output of Volume variance, Payload variance, average distance.
Thank you Ashima. I am not sure I fully understand but generally speaking, you can split the amounts based on quantity and rate (amount per chosen quantity). Then you can apply the information in the video to calculate rate or price, quantity and mix variance. Mix comes into play only when you have multiple customers or products or geography etc. Again different rates per quantity.
@@LearnAccountingFinance
In my data there is no field of amount & qty.
Terrific video, thanks a lot!
Thanks Justin. Glad it helped.
@@LearnAccountingFinance How would you approach a scenario where you've sold each product at multiple rates?
Justin. I would use average price for the period under analysis.
@@LearnAccountingFinance Cheers .
Really good explanation. Can you please share the excel worksheet?
well explained
Thank you!
thanks for the video, regarding quantity effect, for example using current year vs prior year, as long as the amount is negative then does it just mean from PY to CY, total absolute volume has dropped? and for mix effect, if the final amount is negative, it just means from PY to CY, we are selling less portion of higher margin items ? thats why the effect is unfavorable ?
Correct
@@LearnAccountingFinance thanks, so as a take away, conclusion is if volume drops from LY 100 to CY 90 units then volume effect is definitely negative, but mix effect can be positive/negative, depending on what the 90 units consist of
@@stoneage8810 thats right.
Dear sir, why we take budgeted profit in volume variance, why we do not take actual profit to calculate variance. Plz plz elaborate it to us pl, i am unable to understand it. Request you to pls make us understand. Reason and science behind taking budgeted profit / budgeted price / previous year figure.
Sir pls help
Dear Mohsin. The reason you take budget profit when calculating volume variance is that you are only trying to isolate the impact of change in volume ignoring all other changes. Actual profit would already include all other factors such as price, cost, volume and mix and you will get a misleading result if you take actual profit. The same logic applies for using prior year numbers when calculating variance vs prior year. Think of it like this. Every variance you calculate should reflect the change in result if only that single type of change happened. E.g in the case of volume variance, what would be the variance if only volume had changed. Everything else like price, cost and mix remained the same. This way when you add up all individual variances, the total will match the total variance. Here is a much more detailed course on this topic. www.ebitda.thinkific.com
How would you do this if you were only presented with Sales and GM for the five fruits, that is no quantities are provided?
Hi, When i change per unit cost of any item in your example check zeros do not come zeros. Therefore cost is affecting the calculation
KIndly advise
i could not find excel file for mentioned data in video, could you please share the same
How do you account for a product that has sales but was not budgeted for? Doesn't that throw off the variances for all of our products?
👍🏻
Looking for personalized help on this or similar topics? Lets discuss!
calendly.com/learnaf
You can also connect on Facebook or Instagram. Look for @learnaccountingfinance
Your explanation is great and can follow. However, I disagree with the Volume and Mix calculation.
In your example, you showed Act Qty and Bud Qty.............if you make Act Qty TOTAL = Bud Qty TOTAL which is 26645, then using your example of Act Qty @ Bud Mix minus Bud Qty multiple with Bud Profit/Selling Price, this will give you ZERO Impact under Volume. And once you apply the the MIX formula thereafter, the working you have will essentially make the VOLUME impact 100% on mix.
In my thinking, I think the MIX impact has to also take into consideration of the Selling price of the product itself and compare it against the total average selling price of all products. For example if Apple is selling at $1.00, and total average selling price of all products is at $1.50, then selling more mix of the apple will produce a worse mix impact than selling a Banana at $2.00...............make sense?
why actual margin / profit is quoted as difference between actual price and standard cost (AP - SC) why not ACtual cost (AP - AC )????????????????? IN case if u want to use this formula only (AP- SC) why dont u call it as standard margin and why do u call it as actual margin?????????
no sound
the volume is too low 🤨