Sensitivity Analysis
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 |
|
|
per Ride |
|
Scenario |
(Price = Variable cost) |
Number of Customers |
Excellent |
$50 |
4,800 |
Fair |
30 |
3,000 |
Poor |
20 |
2,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 3,000 rides. Annual administrative and marketing costs are estimated to be $20,000 plus 10 percent of the contribution margin.
Required
Use a spreadsheet to prepare an analysis of the possible operating income for Bay Area Limos similar to that in Exhibit 13.5. What is the range of possible operating incomes?
Exhibit 13.5 Manufacturing Overhead Budget
|
A |
B |
C |
D |
E |
|
1 |
SANTIAGO PANTS |
|
||||
2 |
Schedule of Budgeted Manufacturing Overhead |
|
||||
3 |
For the Budget Year Ended December 31 |
|
||||
4 |
|
Variable |
For Total |
|
|
|
5 |
|
Overhead |
Production |
|
|
|
6 |
|
per Unit |
(Exhibit 13.2) |
|
|
|
7 |
Units to be produced (from the production budget Exhibit 13.2) |
|
170,000 |
units |
|
|
8 |
Variable overhead |
|
|
|
|
|
9 |
Indirect materials and supplies |
$ 0.30 |
$ |
51,000 |
|
|
10 |
Materials handling |
0.40 |
|
68,000 |
|
|
11 |
Other indirect labor |
0.10 |
$ |
17,000 |
$ 136,000 |
|
12 |
|
|
|
|
|
|
13 |
Fixed manufacturing overhead |
|
|
|
|
|
14 |
Supervisory labor |
|
$ 102,000 |
|
|
|
15 |
Maintenance and repairs |
|
50,000 |
|
|
|
16 |
Plant administration |
|
85,000 |
|
|
|
17 |
Utilities |
|
55,000 |
|
|
|
18 |
Depreciation |
|
140,000 |
|
|
|
19 |
Insurance |
|
30,000 |
|
|
|
20 |
Property taxes |
|
60,000 |
|
|
|
21 |
Other |
|
22,000 |
544,000 |
|
|
22 |
Total manufacturing overhead |
|
|
$ 680,000 |
|
|
23 |
|
|
|
|
|
|
|
|
|
|
|
|