Introduction to Management Science

please need specific answer. The spreadsheet model (process) & algebraic model

resources used.- for algebraic model. (Modelling shortest path as Minimum cost flow)

Develop a spreadsheet optimization model in Excel to solve the BRIDGES TO SOMEWHERE Problem, which is described below. In addition to solving the problem correctly, the model should be clear and well-presented. Also type the corresponding algebraic model. BRIDGES TO SOMEWHERE The five residents of Hometown live in houses represented by the letters 'A' through 'E' as shown on the left side of the Figure. The offices where they will be working are represented by their matching letters on the island of Worktown. Because a river lies between Hometown and Worktown, the residents are unable to get to work. They have in their town budget enough funds to build two bridges that would connect Hometown to Worktown. The locations wh bridges could be built are indicated by the brown 1x3 hashed tiles. The two bridges can only be built in these approved areas. Once the bridges are built, the residents would then be able to commute t will always take the shortest path from home to work and can only travel in up, down, left or right directions (no diagonals) Each tile represents a 1km by 1km distance. As an example, if bridge four were built, resident 'E' would have to travel 10 km to reach his workplace. The problem is to decide which two bridges should be built in order to minimize the total commuting distance of all residents. these THREE o work. A commuter HometoWm Workrown