ASSIGNMENT 7

Executive Summary Your goal is to build a DSS that can be used for calculating costs and revenues associated with daily bus dispatching for Saint Bernard Bus Lines (see Case 8, p. 185). After you build the DSS, you will use Excel Solver to maximize gross profit for the company. Please make sure you include revenues from additional cargo in your calculations. Also, please don’t forget to include constraints for your DSS model when you run the Solver. Deliverables 1. Please submit an Excel work sheet with the following tabs: a. Tab 1 named “Bus Dispatching”. This tab should contain the DSS with an optimal combination of buses for each route so that gross profit is minimized. In addition to that, the tab needs to have a short interpretation of results. Make sure the results contain specific insights and/or recommendations for management. b. Tab 2 named “Answer Report”. You can generate this tab automatically to contain details about the Solver’s solution to your optimization problem. 11:1Y PI 61° 16. i 1121 12 <GI p. 189 • • • • o Income Statement Section The income Statement section (see Figure 8-4) is actually a projection of daily gross profits and is based on the number of buses that will be assigned either manually or by Solver. An explanation of the line items follows the figure. A 28 29 Income Statement Section: 30 Passenger Revenues: Cargo Reven u es: 32 Additional Cargo 33 Total Revenues: 34 less Operating Costs: 35 Daily Gross Profit: I Source: Microsoft product screenshots used with permission from Microsoft Corporation FIGURE 8-4 Income Statement section • Passenger Revenues—This value is calculated by multiplying the passenger tickets booked for each destination (cells 821 through 826) by their respective average ticket prices (cells C12 through C17), and then totaling the ticket revenu. for the six destinations. • Cargo Revenues—This value is calculated by multiplying the daily cargo shipments for each destination (cells E21 through E26) by their respective cargo prices (cells D12 through D17), and then totaling the cargo revenues for the six destinations. • Additional Cargo—This value is the additional revenue from cell 027, which will be used later in the assignment. • Total Revenues--This value is the total of Passenger Revenues, Cargo Revenues, and Additional Cargo revenues. • Less Operating Costs—This value is the Total Cost from cell N27. • Daily Gross Profit—This value is the Total Revenues minus the operating cos.. This cell will be used as the optimization cell for Assignment ID. If your formulas are correct, the initial Income Statement section will appear as shown in Figure 8-5. 28 29 30 32 33 35 Income Statement Section: Passenger Revenues: $ 296,850.00 Cargo Revenues: 13,480.00 Additional Cargo Total Revenues: $ 310,330.00 less Operating Costs: 3 24,393.75 Daily Gross Profit: I $ 285,936.25 Source: MMrosoft product screenshots used with permission from Microsoft Corporation FIGURE 8-5 Initial income statement The initial income statement correctly reflects the revenues expected from the passenger and cargo bookings, but the operating costs are not correct because the buses required to transport the passengers and cargo have not been completely assigned yet. Attempting a Manual Solution Attempt to assign your bus fleet manually in the spreadsheet. You have several good reasons for doing this. First, you can make sure your model is working correctly before you set up Solver to run. Second, assigning the bus fleet manually will demonstrate which constraints you must meet in solving the problem. For instance, if a passenger or cargo utilization rate is over 100%, you have not assigned enough buses to carry all