Sheet1 Data for Individual Assignment 1 User benefitDistribution of benefits (per $1 spent)UrbanSuburbanRural Highway$1.2520%40%40% Rapid Rail$1.5030%50%20% Bus$1.1040%40%20%...

1 answer below »
See files attached.


Sheet1 Data for Individual Assignment 1 User benefitDistribution of benefits (per $1 spent)UrbanSuburbanRural Highway$1.2520%40%40% Rapid Rail$1.5030%50%20% Bus$1.1040%40%20% Innovative Urban$0.9070%30%-- MBAD6224 –Decision Making and Data Analysis Spring 2022 Prof. Philippe Delquié Page 1 of 2 Individual Assignment 1 General Instructions You are to work strictly individually on this assignment. You should not receive or give any kind of help in working on this assignment, and you should not discuss or exchange any information (electronic or other) about it with anyone. Read the assignment entirely, including the instructions at the end, before starting to work. Explain your reasoning and answers. Due date: The assignment is due on Sunday 30 January, 11:59pm EDT. What to turn in: Submit a single Excel file containing all your work, as explained in “Instructions” below. At the top of the first sheet in your file, type or ink the following statement: “I, [your full name], attest that I did not receive or provide help of any kind in working on this assignment.” How to turn in your work: Submit your work on Blackboard by clicking on “Submit Assignment” at the bottom of the left pane menu. If you submit your work multiple times, only your last submission will be considered. Transportation Funding A Department of Transportation has asked you to suggest the allocation of the DOT’s $500 million budget between highway, rapid rail, bus, and innovative urban mobility systems. Transportation economics studies show that a $1M investment yields user benefits of ▪ $1.25M for $1M spent on highways ▪ $1.50M for $1M spent on rapid rail ▪ $1.10M for $1M spent on bus ▪ $0.90M for $1M spent on innovative urban systems Political considerations require that urban populations receive at least $250M in benefits, suburbs at least $200M, and rural areas at least $100M. Research also indicates that the distribution of benefits for each transportation mode is as follows: Distribution of benefits among users Urban Suburban Rural Highway 20% 40% 40% Rapid Rail 30% 50% 20% Bus 40% 40% 20% Innovative Urban 70% 30% -- MBAD6224 –Decision Making and Data Analysis Spring 2022 Prof. Philippe Delquié Page 2 of 2 a) Formulate an optimization model to maximize total user benefits, subject to the DOT’s constraints. Describe the decision variables, objective function, and constraints of your model. Indicate all your Solver settings. b) What is your recommended allocation? The number of good sites available for rapid rail development is limited. If more than $250M is spent on rapid rail, additional rapid rail funds are spent at inferior sites, producing only $0.8M benefits for a $1M investment. c) Modify your original model from Part (a) to reflect this consideration. For this, you can duplicate your worksheet from (a) and update the model as needed. You should be able to do this with a linear model formulation. Explain all the modifications you made. d) What is your new recommendation based on the updated model? Instructions. Prepare an Excel file with two worksheets, one for Parts (a) and (b), the other for Parts c) and (d). Describe or explain key elements of your model. Inspect your file before submission to ensure everything is in order and visible. Remember to include the honor code statement at the beginning of your file. ---------- END OF INDIVIDUAL ASSIGNMENT ----------
Answered Same DayFeb 05, 2022

Answer To: Sheet1 Data for Individual Assignment 1 User benefitDistribution of benefits (per $1...

Mohd answered on Feb 06 2022
109 Votes
Answer Report 1
    Microsoft Excel 16.0 Answer Report
    Worksheet: [data-for-individual-assignment-1-spring-2022-lgznhrro.xlsx]Sheet1
    Report Created: 2/6/2022 8:37:32 AM
    Result: Solv
er found a solution. All Constraints and optimality conditions are satisfied.
    Solver Engine
        Engine: GRG Nonlinear
        Solution Time: 0.25 Seconds.
        Iterations: 7 Subproblems: 0
    Solver Options
        Max Time Unlimited, Iterations Unlimited, Precision 0.000001
         Convergence 0.0001, Population Size 100, Random Seed 0, Derivatives Central
        Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative
    Objective Cell (Max)
        Cell    Name    Original Value    Final Value
        $H$9        $4.75    $666.67
    Variable Cells
        Cell    Name    Original Value    Final Value    Integer
        $D$5    Highway Total spend $(Millions)    $1.00    $0.00    Contin
        $D$6    Rapid Rail Total spend $(Millions)    $1.00    $361.11    Contin
        $D$7    Bus Total spend $(Millions)    $1.00    $0.00    Contin
        $D$8    Innovative Urban Total spend $(Millions)    $1.00    $138.89    Contin
    Constraints
        Cell    Name    Cell Value    Formula    Status    Slack
        $D$9    Total spend $(Millions)    $500.00    $D$9<=500    Binding    0
        $E$9    Urban    $250.00    $E$9>=250    Binding    $0.00
        $F$9    Suburban    $308.33    $F$9>=200    Not Binding    $108.33
        $G$9    Rural    $108.33    $G$9>=100    Not Binding    $8.33
Sensitivity Report 1
    Microsoft Excel 16.0 Sensitivity Report
    Worksheet: [data-for-individual-assignment-1-spring-2022-lgznhrro.xlsx]Sheet1
    Report Created: 2/6/2022 8:37:32 AM
    Variable Cells
                Final    Reduced
        Cell    Name    Value    Gradient
        $D$5    Highway Total spend $(Millions)    0    -0.9166666269
        $D$6    Rapid Rail Total spend $(Millions)    361.1111    0
        $D$7    Bus Total spend $(Millions)    0    -0.4333333969
        $D$8    Innovative Urban Total spend $(Millions)    138.8889    0
    Constraints
                Final    Lagrange
        Cell    Name    Value    Multiplier
        $D$9    Total spend...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here