DOWNLOAD LINK (EXCEL TEMPLATE)
Download The Excel file here, if you would like to practice along with watching the video. https://learnaccountingfinance.files.wordpress.com/2021/01/calculate-selling-price-with-cost-and-margin-discount-and-rebates-shared.xlsx
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:
https://www.instagram.com/learnaccountingfinance
https://www.learnaccountingfinance.com
Thank You!
Learn Accounting Finance
Practical accounting and finance training to get the job, keep the job and get promoted quickly! 