You are thinking of opening a small copy shop. It costs $5000 to rent a copier for a year, and it costs $0.03 per copy to operate the copier. Other fixed costs of running the store will amount to $400 per month. You plan to charge a price of $0.10 per copy, and the store will be open 365 days per year. Each copier has a maximum capacity of 100,000 copies per year.

Assignment/Activity:

Part 1

Based on your demand forecast, you estimate that you will be able to sell an average of 1,500 copies per day. You have rented 3 copiers.Before you get started on the actual spreadsheet, think about each of the pieces of information that youve been given.

a) What is the decision variable for this company? What should the companys objective be? Are there any uncertain variables (any variables you are estimating without certainty)? What are the given inputs?Before you start part b.) think about your overall plan for organizing the spreadsheet. Where will you put each of the different pieces of information.

b) Design a spreadsheet model to calculate profit for the company based on the information given, the number of copiers provided, and the estimated demand.

c)Change the number of copiers to 4. What happens to profit? What about if you change the number of copiers to 2?

Part 2

In reality, the company has the ability to choose the number of copiers to rent, so in this part of the problem you will use your spreadsheet model to determine how many copiers to rent. Continue to assume that demand is 1,500 copies per day.

a) Create a one-way data table to evaluate profit for a range of possible copier options. Evaluate 0 to 8 copiers.

b) Use conditional formatting to highlight all possibilities that lead to positive profits in green and all possibilities that lead to negative profits in red.

c)Based on the demand information provided, how many copiers should you rent for your company?

Part 3

To extend the problem even further, now we will evaluate our decisions based on a variety of possible demands.

a) Create a two-way data table to evaluate profit for a range of values for the number of copiers and the estimated demand for copies. In the columns you should evaluate 0 up to 8 copiers. In the rows you should evaluate 0 up to 2000 copies for daily demand (counting by intervals of 250 copies).

b) Use conditional formatting to highlight positive profit values in green and negative profit values in red.

c)Based on your data table, what values for demand would lead you to the decision to never open the copy shop? For all values of demand above that level please provide the number of copiers that maximizes profit.

d) Finally, estimate the level of demand that will be required for the company to break even given each of the possible number of copiers rented.