Question

For this exercise assume you are a human resources specialist for a hospital and you are going to need to hire many nurses to staff a new hospital with 1200 beds. You realize that a good way to predict the number of nurses needed is to see how many beds a hospital has. So, you call around to a few of your HR colleagues in the area (actually 8) to see how many nurses and beds each hospital has. You figure this will give you an idea of how many nurses you will need. In an Excel spreadsheet (see attached file) you have the number of nurses and beds for each hospital you called. Based on your sample, how many nurses will you need to staff your 1200 bed hospital? Using Excel show Dr. Von the number of nurses you will need. In the cell with the number of nurses needed make sure you show Dr. Von the formula you used. Just putting a number will not count for much (if anything). Dr. Von wants to see the formula used to calculate the number of nurses needed. Be sure to spell out where this number is on the Excel spreadsheet that you submit so he can find it quickly.

By Convention the Xs are used to predict the Ys, ie bere used to predict the number of nurses needed

Sampe	beds = x	nurses = y
hospital A	200	240
hospital b	300	260
hospital c	400	470
hospital d	500	500
hospital e	600	620
hospital f	700	660
hospital g	800	820
hospital h	900	860

Answer 1

The following data can be used to deduce a trend between the number of beds that are there at a hospital and the number of nurses in that hospital.

We can draw a scatter plot between the number of beds and the number of nurses. This would provide us with an equation that we can use to find the number of nurses that would be needed for a 1200 bed hospital.

The trend shows that a linear equation best represents the relationship between the number of beds and the number of nurses in a hospital.

The equation is given by y = 0.9321x + 41.071

We can substitute x as 1200 in the above equation to find the value of the number of nurses required.?

From the above screenshots of calculations in Excel, we see that the number of nurses that would be required for a 1200 bed hospital is approximately 1160.

The calculation is in cell B18.