Cost-Volume Profit Analysis / Breakeven analysis in Excel with multiple products by Chris Menard
HTML-код
- Опубликовано: 19 окт 2024
- If you need to find the breakeven point in Excel with one product, it is fairly easy. It gets tricky with multiple products. This is how to get the weighted average contribution margin and calculate the break-even in units with 2 or more products. This is used for Cost-Volume-Profit Analysis.
Cost-Volume-Profit Analysis with multiple products. The contribution margin is a company's sales less its variable expenses. Then, divide the company's fixed costs by the contribution margin. This will give you the company's break-even point in total dollars of sales.
To create a cost-volume-profit (CVP) Chart or Breakeven-Analysis Chart, see my video • Break-even analysis in...
I train the Executive MBA students from the University of Georgia in Excel. Feel free to visit my website or sign up for my free class on Excel Tables.
www.chrismenardtraining.com
chrismenardtrai...
And make sure you subscribe to my channel!
- EQUIPMENT USED --------------------------------
○ My camera - amzn.to/3vdgF5E
○ Microphone - amzn.to/3gphDXh
○ Camera tripod - amzn.to/3veN6Rg
○ Studio lights - amzn.to/3vaxyy5
○ Dual monitor mount stand - amzn.to/3vbZSjJ
○ Web camera - amzn.to/2Tg75Sn
○ Shock mount - amzn.to/3g96FGj
○ Boom Arm - amzn.to/3g8cNi6
- SOFTWARE USED --------------------------------
○ Screen recording - Camtasia - chrismenardtra...
○ Screenshots - Snagit - chrismenardtra...
○ RUclips keyword search - TubeBuddy - www.tubebuddy....
DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week!
Thanks Chris and great explanation!
Upvoted and subscribed!
Thanks for the sub!
@@ChrisMenardTraining Least I could do for the help youre providing sir
Best and most easy break down, I do thank you.
I appreciate the feedback. Thank you.
Thank you for this very clear explanation!
Thank you so much for your breakdown!
I appreciate it. Thank you, David.
Thank you Chris for a simple but very useful lesson/ I earned from this
Thank you. I'm glad you liked the video.
So simplified. Love it. What if one wants to know , " how much units does one have to sell in order to earn a targeted profit?
Take the fixed cost and add the target profit. Now divide by the contribution margin per unit. Example: if 6,000 is fixed cost and 2,000 is target profit and your cont. margin per unit is 20, your breakeven is 6,000/20=300. Your target profit is 2,000. So add 6,000+2,000=8,000. Divide 8,000 by 20 and you get 400 units. 300 units are breakeven and the additional 100 units x 20 is your profit of 2,000.
Many Thanks Sir
Most welcome
This video was 'Excel'lent! :D Thank you so much for sharing! I am an entrepreneur going into the tea business and this easy to follow and understand tutorial has helped me to complete my financial overview for an investor! Must subscribe to your channel ;)
Thank you. Good luck with your business.
Thanks! this is a great help...
I have a question about product mix percentage. Is it based on unit sales or dollar value sales? For example, in your video, does "product A" represent 20% of a dollar sales total, or 20% of total number of units sold?
David, that is a great question. The percentages in row 6 in the video are based on total sales. Here is a screenshot of the Sales or Product Mix. drive.google.com/file/d/1OcvWgQ58rBjOfFsCSdA32_zpX7Xf3MIM/view?usp=drivesdk
@@ChrisMenardTraining Thanks Chris. I have another question: How would you go about creating this spreadsheet when sales are a mix of retail and wholesale pricing? (ie the same product would sell for $100 retail or $50 wholesale) Would I have to do separate analyses on each?
Great video! How can you construct a break even chart with multiple products and a different VC for each product?
Hello. I'll look at it this weekend. I'm not sure it can be done, but I'll give it a try.
From India, can you provide where you calculate break even analysis for one service or more then one services provided by the startup in the service industry? If possible for you with editable Excel sheet along with graph as early as possible. Please
Sir, If I am not wrong.... in a breakeven analysis - the contribution margin should be equal to your fixed cost plus variable cost. in other words in order for a business to make no profit or no loss, the contribution margin should cover fixed cost and variable costs. hope for your input on this.
The contribution margin (CM) is your Sales - Variable expenses. You can look at your CM in two ways. 1) by unit and 2) by ratio. I did both in the image link. If you take your CM, $4,000 in my example in cell C4, and subtract your fixed cost, cell C5, that is the net profit or net loss, which I'm showing as zero. So I'm breaking even. So if you CM and Fixed cost are the same, you breakeven. If your CM is greater than fixed cost, that is your net profit.
To breakeven, your CM equals your fixed cost (see column C in my screenshot. I have 4,000 in fixed cost and exactly 4,000 in contribution margin.
You wrote, "the contribution margin should cover fixed cost and variable cost." In my example, my fixed costs are 4K, and variable costs are 6K. Add those together, and that is Sales. I hope this helps. drive.google.com/file/d/1ES0RyBZEU7Grtl33rAf_-oX4Nf-9gcuk/view?usp=drivesdk
Have you done cvp video ? I suppose it comes after break even analysis
Calculating the break-even is the same as cost-volume-profit analysis. To find a targeted income, you would add the fixed cost and targeted income, then divide by the contribution margin per unit. Example: Fixed cost is $240,000 and targeted income is $120,000. CM per unit is $400. You would add $240K + $120K. Take the $360K and divide by $400 to get 900 units to make $100,000 in net income.
Great Video Chris..Do you have any books available?
how do i make a line graph out of this data
Should the net income must equal to zero?
Yes. Net income of zero is break-even.
@@ChrisMenardTrainingDo you have a video that the price per unit varies for monthly and yearly? Just like in subscription? Do I have to separate the monthly and yearly or should I just use the monthly price?
Hello sir. How do u calculate B19?
Hello. I did B17 - B18 and pressed Enter.
how do you determined the product mix ratio?
Ali Akl you decide the product mix for yourself. They can come from industry numbers, or it could come from historical data if you had it.
How Can I create a Breakeven chart with Multiple products ???? OR should I do it for each product??
Each product. I don't' believe it is possible for a BE chart for multiple products. There would be lines all over the place in the chart. Each product is the way to go.