In: Statistics and Probability
The Kogod Entreprenurial Fund has decided to award money to four projects this year. The four projects will be labeled as A, B, C, and D. The outcome of each project is binary (successful or unsuccessful). They have a total of $150,000 available to distribute, and would like to maximize the expected value of the number of successful projects. If we denote the amounts of money awarded to the projects as XA, XB, XC, and XD, then the probabilities of success for the four projects are:
Project A: 0.000006*XA
Project B: 0.000005*XB
Project C: 0.000008*XC
Project D: 0.000006*XD
(Be sure to enter the correct number of zeroes when entering these
formulas into Excel. HINT: If you are getting an optimal expected #
of successful projects greater than 4, then there is a mistake
somewhere!)
They face the following additional constraints as well:
-The scope of Project A is small; they have requested only $20,000, and cannot be given more than that.
-Each project must receive at least $10,000.
-Projects C and D require some of the same analysts and lab space; the total amount awarded to these two projects cannot exceed $90,000.
They would like to figure out how much money to allocate to the four projects.
a) Set this problem up as a linear program in Excel, and show a screenshot of it. (Do NOT use Solver yet.) Your screenshot does not need to show the optimal award amounts or the optimal expected value of number of successful projects. However, it must be clear from looking at the spreadsheet what the decision variables, objective, and constraints are.
b) Enter the problem into Solver, and run it to find the optimal solution. How much money should be awarded to each project, and what is the resulting expected value of the number of successful projects?
c) Generate the sensitivity report. Explain the meaning of the shadow price on the constraint requiring no more than $20,000 to be awarded to Project A.
Kindly find attached the details of the solution of the problem as per the details provided .
I attach the screenshot of the working and the functions in the excel .
The output obtained onrunning the solver is
Sensitivity report
Answer Report
Limit Report