Your cousin Martin wants to open a copy shop. He has asked you to develop a profit
Fantastic news! We've Found the answer you've been seeking!
Question:
Your cousin Martin wants to open a copy shop. He has asked you to develop a profit model for him and to write a brief report on what your profit model indicates that he should do. Specifically, he wants to know:
- Under the assumptions he has given you (see below), what is his profit per year?
- Under the assumptions he has given you, what is his breakeven demand per day?
- He wonders what his profitability would look like if demand were lower or higher than his base case. He thinks that realistically, demand could be anywhere from 1000 copies a day to 2250 copies a day. He'd like to know his profitability at each level, in increments of 250 copies per day.
- The copy machine company representative provided him with a probability distribution of daily demand, based on other stores that are in areas similar to Martin's. Using those probability distributions, and the demand sensitivity analysis, what would be his expected annual profit?
- At what level of demand would it be profitable to lease a second copier? A second copier would not change the level of demand. It would be leased at the same rate as the first copier. The shop's fixed costs would stay the same.
- What would you advise him to do based on what you have learned from your model?
Upload your spreadsheet model and a brief report to Cousin Martin.
Here is the information he has provided:
- Annual rental for a big copy machine: $40,000
- Fixed costs for leasing and running his store, monthly: $500
- Variable costs per copy: $0.03
- Revenue per copy: $0.12
- He plans to open the shop 300 days/year. However, he could open more days if he needed to. He believes that opening more days would not affect the demand per day.
- He thinks a base case for demand will be 1,500 copies each day the store opens. However, he recognizes that this is a guess at this point.
- He thinks he should start with one copier. The copier has the capacity to produce 2250 copies per day. If demand is higher than this, he will have to either turn away business or lease a second copier.
- The copy company salesperson supplied him with the following probability distribution for the number of copies demanded based on stores in locations similar to his:
Copies per Day | probability |
1000 | 5% |
1250 | 10% |
1500 | 25% |
1750 | 35% |
2000 | 20% |
2250 | 5% |
Could you help walking me through what the proper steps are to reach answers to the questions provided in an excel spreadsheet
Posted Date: