Accounting / Business Excel Problem and Solutions

84 ? Test Item File Chapter Five ? 85 Excel Problems 1. The following network describes the hourly volume of traffic that can flow between various communities in Dover County. Assume traffic can flow in both directions between each community at the same rate. What is the maximum flow of cars between Communities 1 and 6 in one hour? Formulate this problem as a linear programming problem. Answers: Maximize: X61 Subject to: Balance of flow constraints: Node 1: X61-X12-X13 = 0 Node 2: X12-X24-X25 = 0 Node 3: X13-X34-X35 = 0 Node 4: X24+X34-X46 = 0 Node 5: X25+X35-X56 = 0 Node 6: X46+X56-X61 = 0 Arc capacity constraints: 0 ? X12 ? 500 0 ? X13 ? 400 0 ? X24 ? 600 0 ? X34 ? 400 0 ? X25 ? 200 0 ? X35 ? 600 0 ? X46 ? 100 0 ? X56 ? 500 0 ? X16 ? 100,000 2. Refer to problem 1 and its associated Excel solution shown below. What values would you enter in the Solver Parameter dialog box for the Excel spreadsheet model? Set Target Cell: By Changing Cells: Subject to the Constraints: Answers: Set Target Cell: B21 By Changing Cells: B4:G9 Subject to the Constraints: B25:B30 = D25:D30; B4:G9 ? B14:G19 3. Refer to problem 2 and its associated Excel spreadsheet model. a. What equation should be entered in cell H4? b. What equation should be entered in cell B10? c. What equation should be entered in cell B25? Answers: a. =SUM(B4:G4) b. =SUM(B4:B9) c. =B10 – H4 4. Bob Jenkins needs to drive from City 1 to City 7 and would like to find the shortest route between the two. The road system with the distance in miles between cities is shown in the network below. What cities should he travel through to minimize his distance? Refer to the following Excel spreadsheet model. What values would you enter in the Solver Parameter dialog box for the Excel spreadsheet model? Set Target Cell: By Changing Cells: Subject to the Constraints: Answers: Set Target Cell: B23 By Changing Cells: B4:H10 Subject to the Constraints: B27:B33 = D27:D33 5. Refer to problem 4 and its associated Excel spreadsheet model. a. What equation should be entered in cell B23? b. What equation should be entered in cell B27? c. What equation should be entered in cell I4? Answers: a. = SUMPRODUCT(B4:H10,B15,H21) b. = B11 – I4 c. = SUM(B4:H4)