1. Angela and Bob Master have decided to start a company, Copymax, that will place self-service copy machines onto customer premises in libraries, universities, high schools, shopping malls, etc. They plan to keep capital costs to a minimum by leasing heavy-duty copiers that have a self-service coin and smart card reader device attached. In addition to the lease cost and other expenses of the copier, Copymax would pay a fee to the customer organization providing the space for the copier. This fee would consist of a fixed monthly space rent plus, optionally, some incentive payments. As part of their business plan,
Angela and Bob have made the following assumptions:
Number of Copiers Leased 40
Copies per Month per Copier variable (Assume Demand = Production)
Price Charged per Copy $.05
Variable Cost per Copy $.03
Monthly Copier Space Rental Rate (per copier) $150
Other Monthly Expenses:
Lease Cost of Each Copier $250
Coin Collection Labor per Copier $35
Miscellaneous Fixed costs per Copier $50
Call the original plan, option A. The Masters are considering two other alternative arrangements for copier space rental payments.
In Option B, Masters would pay a space rent of only $50 per month per copier but would add a commission of one-half cent per copy. (They would be paying that on top of the fixed rent)
In Option C, CopyMax would pay a space rent of $75 per month per copier. A commission of one cent per copy would be paid only for that portion of the monthly volume (copies/month/copier) that exceeds a predetermined cutoff of 20,000 copies. (IF function will be required here)
a. Develop (three separate) spreadsheet models that can be used to determine the profit for each of the three options given the unknown variable of copy Demand.
b. Determine the break-even number of COPIES PER MONTH for each option
c. Create a (single) graph that will show how Profit for the three options responds to changes in Volume(copies per month per copier.)
d. At what Volume would the original option have the same Cost as the second option (read approximately off your graph)?
1.Set up complete models to calculate profit for each option
2.Make Excel do all of the work, all calculations.
3.Use the Excel Goal Seek, IF where applicable.
4.Report results and answers to questions