Classic Limo, Inc. provides limousine service to Tri-Cities airport. The price of the service is fixed at a flat rate for each trip and most costs of providing the service are stable for each trip. Marc Pence, the owner, budgets income by estimating two factors that fluctuate with the economy: the fuel cost associated with each trip and the number of customers who will take trips. Looking at next year, Marc develops the following estimates of contribution margin (price less variable cost of the trip, including fuel) and for the estimated number of customers. Although Marc understands that it is not strictly true, he assumes that the cost of fuel and the number of customers are independent.
Contribution Margin Per
Scenario Ride (Price - Variable cost) Number of Customers
Excellent $40 10,500
Fair $25 6,000
Poor $15 4,500
In addition to the costs of a ride, Marc estimates that other service costs are $50,000 plus $5 for each customer (ride) in excess of 6,000 rides. Annual administrative and marketing costs are estimated to be $25,000 plus 10% of the contribution margin.
Required:
1) Using the above information, construct an Excel spreadsheet to prepare an analysis of the possible operating income for Classic Limo, Inc.
2) 2) If you were manager of Classic Limo, Inc. and had to choose only one budget scenario to use for planning for the year, which one of the nine scenarios would you choose?