PREVIEW

Saint Bernard Bus Lines provide passenger transportation services and small-parcel logistics from its headquarters in Philadelphia to major metropolitan areas in the mid-Atlantic region. The company uses a hub-and-spoke network with Philadelphia at its center to provide services to six other cities: Baltimore, Buffalo, Washington, DC, New York, Norfolk, and Pittsburgh. Considering the tremendous growth the company has seen in recent years, management has determined that the current manual system for allocating vehicles has outlived its usefulness. Furthermore, the company is interested in the possibility of selling unused cargo space to other freight companies.

You have been hired as a new MIS consultant to develop a DSS model for Saint Bernard Bus Lines. Your completed model will be used to assign the bus fleet to its six destinations while minimizing costs. You will also modify the model to calculate how taking an additional cargo will affect the company’s profitability and operating costs.

PREPARATION

• Review spreadsheet concepts discussed in the class and in your textbook.
• Complete any exercises that your instructor assigns.
• Complete any part of Tutorial D that your instructor assigns, or refer to it as necessary.
• Review the file-saving procedures for Windows programs in Tutorial C.
• Review Tutorial F as necessary.

BACKGROUND

You will use your excel skills to build decision model and determine how many of each type of bus should be assigned to six Bernard Bus Lines destinations. The model requires the following data, which the management team team has compiled for you:

• Data for four different types of buses in the fleet:
  • Passenger capacity
  • Cargo space
  • Operating cost per mile (includes fuel, labor, and overhead)
• Ticket pricing to each destination
• Cargo pricing to each destination
• Distance from Philadelphia to each destination

The marketing department has also given you information about the passenger and cargo demand for each city:

• Expected daily passenger load
• Expected daily cargo load

To satisfy passenger requirements and cargo demand, your Solver model will assign buses by number and type to each destination city. The model will also calculate daily revenues from both passenger service and cargo, as well as the total daily operating cost. You will use the results of these calculations to create a daily gross profit statement. You will run Solver first to minimize the total operating cost. Next, you will modify the model to examine the effect of accepting additional cargo on the total operating cost and profitability. Finally, you will run the modified model to maximize daily gross profits.

—Description of case truncated due space constraint, the solution file contains the complete solution though.—