Probability statistics data analysis excel

Question

A hospital is trying to decide how many new rooms to add to a new wing they are building. Each new room will contain one bed. There are currently 100 rooms. Each new room will cost $80,000 for construction and equipment. They are considering adding anywhere from 100 to 300 additional rooms, in increments of 100 rooms, giving 3 possible investment possibilities so that the hospital can remain at 100 rooms, or move to a 200, 300, or 400 room hospital. In addition to the per room construction costs, there is an additional investment cost of $3,000,000 if any additional rooms are added, no matter the number of rooms.

The decision to build is based on comparing 1/10th of this investment to some annual Contribution to investment. For instance, if 200 rooms are added, then you can think about the $19,000,000 investment as a $1.9 million annual "Cost". Suppose this was compared to an expected annual Contribution of $2.1 million, then there would be an annual surplus of $200,000. The hospital wants to make their decision based on the highest expected annual surplus, with a decision not to build anything having a $0 Contribution.

If the hospital decides to build 100 rooms, it has determined that there are two possibly future outcomes, a "Low" case with probability 0.25, and a "Medium" case with probability 0.75. (there would be no "High" case for this alternative). If the hospital decides to build either 200 or 300 rooms, it has determined that there are three possibly future outcomes, a "Low" case with probability 0.20, a "Medium" case with probability 0.65, and a "High" case for the remaining probability.

If they build 100 rooms, the Contribution used for analysis is $1.4 million for the "Medium" case and $1.3 million for the "Low" case. If they build 200 rooms, the Contribution used for analysis is $2.5 million for the "High" case, $2.3 million for the "Medium" case, and $1.0 million for the "Low" case. Finally, If they build 300 rooms, the Contribution used for analysis is $3.0 million for the "High" case, $2.8 million for the "Medium" case, and $700,000 for the "Low" case.

What is the expected annual surplus if they add 200 rooms?

Answer 1

I answered a previous question you had related to this.

If you build a table in Excel like this, you can easily determine the answer to your question:

(All in Row 1:)

Column A: "Current # of Rooms"

Column B: "Added Rooms"

Column C: "Reference: Total Number of Rooms After Addition"

Column D: "Cost - Added Room Cost"

Column E: "Cost - Additional Capital Investment"

Column F: "Total Cost"

Column G: "Per Year Cost (1/10th Total Cost)"

Column H: "Contribution Probability - Low"

Column I: "Contribution Probability - Medium"

Column J: "Contribution Probability - High"

Column K: "Estimated Contribution - Low"

Column L: "Estimated Contribution - Medium"

Column M: "Estimated Contribution - High"

Column O: "Estimated Surplus"

Row 2 would represent the case where 100 beds were added

Row 3 would represent the case where 200 beds were added

Row 4 would represent the case where 300 beds were added

So cell A-2, A-3, and A-4 are all 100 (the number of existing beds)

And cell B-2, B-3, and B-4 are 100, 200, and 300 respectively (the number of added beds)

Column C adds column A and B so cell C-2 is "=A2+B2" or 200, etc

Column D multiplies $80,000 and the number of added beds so cell D-2 is "B2*80000" or $8,000,000

Column E contains the $3,000,000 of additional cost so E-2, E-3, and E-4 are all 3000000

Column F adds column D and E together so cell F-2 is "=D2+E2" or 11,000,000, etc

Column G divides column F by 10 so cell G-2 is "=F2/10", etc

Columns H, I, and J contain the probabilities:

0.25 0.75

0.20 0.65 0.15

0.20 0.65 0.15

Columns K, L, and M contain the estimated contributions:

$1,300,000 $1,400,000

$1,000,000 $2,300,000 $2,500,000

$700,000 $2,800,000 $3,000,000

Column N multiplies and sums the probabilities and their respective contributions so cell N-2 is:

"=H2*K2+I2*L2+J2*M2", etc yielding for cell N-2 an expected value of $1,375,000, etc

Column O subtracts the cost from the expected contribution so cell O-2 is:

"=N2-G2" or yielding an expected surplus value for the case where 100 beds are added of $275,000. etc.

The answer to this question would be in cell O3