Question

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 the 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. 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 percent of the contribution margin.

Required:
Using the above information, construct an Excel spreadsheet to prepare an analysis of the possible operating income for Classic Limo, Inc., similar to that in Exhibit 13.15 on page 558 of the textbook. You must submit this part of the assignment as an Excel file and utilize formulas for all calculations. You will be graded on the answers provided, formulas/calculations, and presentation. If you would like feedback on this part of the assignment before submitting, please email your spreadsheet to me at least 48 hours prior to the due date.

Answer 1

Poor $(14,250)

Fair $26,250

Excellent $87,000

Poor $6,000

Fair $60,000

Excellent $141,000

Poor $44,250

Fair $138,750

Excellent $280,500

Step-by-step explanation:

Prepare of an analysis of the possible operating income for Classic Limo, Inc.,

Contribution MarginA Numbers of CustomersB Total Contribution MarginC=A*B Service costsD Marketing & AdminE=25,000+(C*10%) Operating Profit / (Loss)F=C-D-E

Poor $15* 4,500= $67,500- $50,000- $31,750 =$(14,250)

Fair $25 *4,500= $112,500 -$50,000 -$36,250 =$26,250

Excellent $40* 4,500=$180,000-$50,000-$43,000=$87,000

Poor $15*6,000=$90,000-$50,000-$34,000=$6,000

Fair $25*6,000=$150,000-$50,000-$40,000=$60,000

Excellent $40*6,000=$240,000-$50,000-$49,000=$141,000

Poor $15*10,500=$157,500-$72,500-$40,750=$44,250

Fair $25*10,500=$262,500-$72,500-$51,250=$138,750

Excellent $40*10,500=$420,000-$72,500-$67,000=$280,500

CALCULATION FOR SERVICE COST

Service costs=$50,000+($10,500-$6,000)*5

Service costs=$50,000+($4,500*5)

Service costs=$50,000+$22,500

Service costs=$72,500

CALCULATION FOR Marketing & AdminE=25,000+(C*10%)

$25,000+(Total Contribution margin *10%)

Poor $25,000+($67,500*10%)=$31,750

Fair $25,000+($112,500*10%)=$36,250

Excellent$25,000+($180,000*10%)=$43,000

Poor $25,000+($90,000*10%)=$34,000

Fair $25,000+($150,000*10%)=$40,000

Excellent $25,000+($240,000*10%)=$49,000

Poor $25,000+($157,500*10%)=$40,750

Fair $25,000+($262,500+10%)=$51,250

Excellent $25,000+($420,000*10%)=$67,000

Therefore the possible operating income for Classic Limo, Inc.,are

Poor $(14,250)

Fair $26,250

Excellent $87,000

Poor $6,000

Fair $60,000

Excellent $141,000

Poor $44,250

Fair $138,750

Excellent $280,500