Estimating required hospital bed capacity requires a thorough analysis. There are a lot of ways of approaching a capacity requirement problem, but I think we can agree that a simple spreadsheet analysis just won't cut it.
The approach described in this post makes use of discrete-event simulation and, just to clarify, makes abstraction from a lot of variables which should be taken into consideration in a real-life analysis.
To explain the approach, the following case will be used:
An emergency department of a small regional hospital receives complains about its emergency admission capacity. After investigation of its admission data it becomes clear that their service level is not up to par with that of other hospitals. Therefore, plans for the redesign of the emergency department and an investment in its emergency bed capacity are presented. The plan proposes a new bed capacity of 12 beds (coming from a previous of 10 beds). The Chief of Medicine wants to know what the effect of this investment will be on their emergency admission service level.
The emergency department has recorded data on the interarrival times of patients that are admitted (or should be admitted) to an emergency bed. The following graph shows the interarrival distribution (triangular: mode=5, min=.1, max=12):