Please Help I have already completed excel part and have my date filled out. Im just struggling with how to write analysis for data I have provided.

Bay Area Limos operates transportation services to Bay City airport. The price of service is fixed at a flat rate for each trip and most costs of providing the service are fixed for each trip. Betty Smith, the owner, forecasts income by estimating two factors that fluctuate with the economy: the fuel cost associated with the trip and the number of customers who would take trips. Looking at next year, Betty develops the following estimates of contribution margin (price less variable costs, including fuel) for the estimated number of customers. For simplicity, she assumes that the fuel costs (therefore the contribution margin per ride) and the number of customers are independent.

Contribution Margin

Scenario per ride Number of Customers

Excellent.................................$50............................................7,200 Fair...........................................30.............................................4,500 Poor..........................................20.............................................3,000

In addition to the costs of a ride, Betty estimates that other service costs are $45,000 plus $5 for each customer (ride) in excess of 4,500 rides. Annual administrative and marketing costs are estimated to be $20,000 plus 10 percent of the contribution margin.

Required:

1) Use a spreadsheet (Excel) to prepare an analysis of the possible operating income for Bay Area Limos similar to that of Exhibit 13.15 on page 523 in the textbook.

2) Review all possible scenarios that you projected in part (1). Write a a analysis report discussing possible factors that could affect your decision to choose one scenario over another when preparing budgeted financial statements for next year. Determine which scenario (select only one) that you think is most likely and should be used in the budget. All students may select a different scenario and still be correct. You will be assessed on how well your assumptions/factors support your decision. You will have to make several assumptions in order to support your answer. Feel free to use whatever sources you see fit to support your claims.

Atelier The Home D . Page Layout Cost Accounting Project Seved Formulas Data Review Insert View Help BIUL. Paste Clipboard -A- SE E Merge Center - Condo - I X Bay Area Limos Sensitivity Analysis HIK MNOG 38 38 389 SIE. E o Type here to search PSE Atelier The Home D . Page Layout Cost Accounting Project Seved Formulas Data Review Insert View Help BIUL. Paste Clipboard -A- SE E Merge Center - Condo - I X Bay Area Limos Sensitivity Analysis HIK MNOG 38 38 389 SIE. E o Type here to search PSE