Menu
Sara’s Copy Shop – Break even analysis and what-if modeling in Excel [Videos]
Analytics , Financial Modeling - 2 comments
Last week, I asked you to share an analysis problem that you couldn’t solve in Excel. We got quite a few very interesting problems in comments and email. In this post, let me explain how to solve Sara’s copy shop problem using Excel.
What is Sara’s copy shop problem?
Thanks to Caroline who posted this problem.
Sara wants to open a copy shop. Each copier costs $5,000 per year to lease. The rent & other fixed costs per month are $300. There is a $0.02 variable cost per copy. Each copier can print up to 100,000 copies per year. She plans to charge $0.11 per copy from her customers. Sara estimates that the demand can be any of the 4 values – 500, 1000, 1500 or 2000 copies per day.
- Build a model to estimate profit per given number of copiers & demand values
- Find the mix of copiers & demand values that can make maximum profit for Sara (copiers – 1 to 6, demand – 500 to 2000)
Building a break-even model & enhancing it with what-if engine
We can use simple formulas to build a break-even model for this problem. We can then enhance it by using 2-way data tables to calculate optimum mix of copier & demand values.
I created a video explaining the process. Check it out below. You can also watch this on our YouTube Channel.
Download Sara’s copy shop model workbook
Click here to download the workbook with break even & what-if analysis model. Play with various formulas to learn more.
Learn more about Analysis & Modeling using Excel
Check out below resources to learn more about various analysis and modeling techniques using Excel.
Resources:
Case studies:
- Cost benefit analysis – Which bulb is best?
- Building a retirement calculator using Excel
- Calculating CAGR using Excel
- Multiple scenarios using slicers
Courses:
Can you help Sara?
Do you think our model is adequate? If not, what else would you add to it? How would you enhance it? Please share your suggestions and implementations in the comments area. Shower suggestions on Sara so she succeeds. Go.
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
| « Stacked Bar and Indicator Arrow Chart – Tutorial | Formula Forensics No. 041 – Convert a Roman Numeral to a Number » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
2 Responses to “Sara’s Copy Shop – Break even analysis and what-if modeling in Excel [Videos]”
How would you leverage this same technique to do sensitivity analysis on commission payments for budgeting?
If I have 100 sales people with different commission rates and higher rates at higher attainment levels (100-110% gets a higher rate, 111-120% gets an even higher rate, etc.) and they will all sell different amounts, what would be our expected payout? If we hit our goal sales number and everyone does 100%, that's easy but if we have just a few sales people that do 200%, the cost will be much higher.
[…] Gegevens-tabellen in Excel. Een handig hulpmiddel bij scenario-analyses. Een artikel op de site van Chandoo gaf me de zet om dit ook daadwerkelijk te […]