Sara’s Copy Shop – Break even analysis and what-if modeling in Excel [Videos]

Posted on September 13th, 2016 in 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.

breakeven-analysis-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.

  1. Build a model to estimate profit per given number of copiers & demand values
  2. 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:

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.

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

2 Responses to “Sara’s Copy Shop – Break even analysis and what-if modeling in Excel [Videos]”

  1. Joe says:

    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.

  2. […] 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 […]

Leave a Reply