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.
2 Responses
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.