Excel Exercise
hotgirl42
ExcelExibit.xlsxSheet1
| Chapter 17 | |||||||||||||||
| Exhibit 17-9 | |||||||||||||||
| Census Projection Worksheet | |||||||||||||||
| For 2019 budget | |||||||||||||||
| Reimbursement rates | Oct 2018 | Jan 2019 | Oct 2019 | ||||||||||||
| Private pay | $192 | $0.00 | |||||||||||||
| Private insurance | $176 | $0.00 | |||||||||||||
| Medicaid | $107 | $0.00 | |||||||||||||
| Medicare | $125 | $0.00 | |||||||||||||
| Veterans contract | $180 | ||||||||||||||
| Exhibit 17-9 | |||||||||||||||
| 2019 Average daily census projections | |||||||||||||||
| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Year | |||
| Private pay | 26.0 | 26.0 | 28.0 | 29.0 | 30.0 | 30.0 | 31.0 | 31.0 | 31.0 | 31.0 | 30.0 | 30.0 | 29.4 | ||
| Private insurance | 2.6 | 2.6 | 2.6 | 2.6 | 2.6 | 2.6 | 2.6 | 2.6 | 2.6 | 2.6 | 2.6 | 2.6 | 2.6 | ||
| Medicaid | 58.0 | 58.0 | 60.0 | 60.0 | 62.0 | 62.0 | 62.0 | 62.0 | 62.0 | 61.0 | 59.0 | 59.0 | 60.4 | ||
| Medicare | 17.0 | 18.0 | 18.0 | 19.0 | 19.0 | 19.0 | 20.0 | 20.0 | 20.0 | 20.0 | 20.0 | 20.0 | 19.2 | ||
| Veterans contract | 2.0 | 2.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.3 | ||
| Total | 105.6 | 106.6 | 108.6 | 110.6 | 113.6 | 113.6 | 115.6 | 115.6 | 115.6 | 114.6 | 111.6 | 111.6 | 111.9 | ||
| Occupancy rate | 83.8% | 84.6% | 86.2% | 87.8% | 90.2% | 90.2% | 91.7% | 91.7% | 91.7% | 91.0% | 88.6% | 88.6% | 88.8% | ||
| Note: For revenue projections the average daily census should be first used to calculate patient days. | |||||||||||||||
| When using a budgeting software program, only census needs to input. The software is programmed to calculate the patient days. | |||||||||||||||
| 2019 Patient days | |||||||||||||||
| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Year | |||
| Private pay | |||||||||||||||
| Private insurance | |||||||||||||||
| Medicaid | |||||||||||||||
| Medicare | |||||||||||||||
| Veterans contract | |||||||||||||||
| Total | |||||||||||||||
| 2019 Revenue projections | |||||||||||||||
| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Year | |||
| Private pay | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
| Private insurance | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
| Medicaid | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
| Medicare | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
| Veterans contract | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
| Total | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
| Note: The revenue figures are rounded. Only whole dollars appear in the budget as they do in the financial statements. | |||||||||||||||
| Housekeeping chemicals expense | |||||||||||||||
| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Year | |||
| 0.58 PPD | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | ||
| Total expenses if the operating margin is to be 8% | |||||||||||||||
| Total revenue | $0 | ||||||||||||||
| Operating income | $0 | ||||||||||||||
| Total expenses | $0 |
© 2010 Jones and Bartlett Publishers, LLC
