SENIOR LIVING AND HEALTHCARE REAL ESTATE - BLANK MODEL SET: THE BOOK'S OWN INPUTS

Type these back into the blank files and the book's results come back. Cell references are sheet!cell.

Care_Home_Model_Blank.xlsx

   Inputs!B7       Registered beds                                                 70
   Inputs!B8       Admissions a month                                              2.79
   Inputs!B9       Admissions a month when beds are empty                          5.2
   Inputs!B10      Leaving hazard — first month                                    0.115
   Inputs!B11      Leaving hazard — months two and three                           0.055
   Inputs!B12      Leaving hazard — thereafter                                     0.04
   Inputs!B15      Self-funded weekly fee                                          1638
   Inputs!B16      Local authority weekly rate                                     1365
   Inputs!B17      Third-party top-up                                              182
   Inputs!B18      Share of council residents with a top-up                        0.38
   Inputs!B19      Self-funded share of ADMISSIONS                                 0.55
   Inputs!B20      Upper capital limit                                             23250
   Inputs!B21      Resident's own weekly income                                    356
   Inputs!B24      Care hours per resident per day                                 4.55
   Inputs!B25      Of which registered nurse                                       0.72
   Inputs!B26      Care assistant hourly rate                                      13.35
   Inputs!B27      Registered nurse hourly rate                                    24.1
   Inputs!B28      Contracted hours a week                                         37.5
   Inputs!B29      Annual leave, weeks                                             5.6
   Inputs!B30      Sickness and training absence, weeks                            1.4
   Inputs!B31      Heads per full-time equivalent                                  1.45
   Inputs!B32      Employer national insurance rate                                0.15
   Inputs!B33      Employer national insurance threshold                           5000
   Inputs!B34      Employment Allowance                                            10500
   Inputs!B35      Pension rate                                                    0.03
   Inputs!B36      Pension band starts at                                          6240
   Inputs!B37      Pension enrolment trigger                                       10000
   Inputs!B38      Share who opt out                                               0.1
   Inputs!B39      Agency share of care hours                                      0.0315
   Inputs!B40      Agency premium                                                  1.55
   Inputs!B41      Recruitment and training, share of gross pay                    0.026
   Inputs!B42      Non-care staff, gross pay                                       567500
   Inputs!B43      Non-care staff, full-time equivalents                           19.6
   Inputs!B46      Property, utilities, rates, insurance                           4427
   Inputs!B47      Catering                                                        2230
   Inputs!B48      Consumables, supplies, systems, professional                    2650
   Inputs!B51      Management charge, share of revenue                             0.05
   Inputs!B52      Required rent cover                                             1.8
   Inputs!B53      Trade multiple on EBITDARM                                      11
   Inputs!B54      Investment yield on the rent                                    0.06
   Inputs!B55      Lease term, years                                               25
   Inputs!B58      Growth: self-funded fee                                         0.045
   Inputs!B59      Growth: local authority rate                                    0.033
   Inputs!B60      Growth: third-party top-up                                      0.028
   Inputs!B61      Growth: wages                                                   0.042
   Inputs!B62      Growth: other costs                                             0.03
   Inputs!B63      Growth: rent, after the collar                                  0.028
   Inputs!B64      Growth: capital residents arrive with                           0.035
   Inputs!B65      Growth: resident's own income                                   0.033
   Inputs!B68      Months of embargo                                               6
   Inputs!B69      Admissions during recovery, share of normal                     0.7
   Inputs!B70      Months of slowed recovery                                       9
   Inputs!B71      Remediation cost                                                185000
   Inputs!B81      Lease covenant: cover below which the lease is breached         1.5
   Inputs!B82      Maximum loan to value                                           0.6
   Inputs!B83      Required interest cover                                         1.75
   Inputs!B84      Debt rate                                                       0.072
   Inputs!B85      Amortisation, a year                                            0.02
   Inputs!B86      Land per bed                                                    26000
   Inputs!B87      Construction per bed                                            137000
   Inputs!B88      Furniture, fittings and equipment per bed                       9000
   Inputs!B89      Professional fees, share of construction                        0.14
   Inputs!B90      Planning and statutory contributions per bed                    4500
   Inputs!B91      Irrecoverable VAT on fees, equipment and contributions          0.2
   Inputs!B92      Stamp duty on the land, flat rate                               0.045
   Inputs!B93      Contingency                                                     0.05
   Inputs!B94      Build programme, months                                         24
   Inputs!B95      Development finance, share of cost                              0.6
   Inputs!B96      Development finance rate                                        0.08
   Inputs!B97      South East: land per bed                                        52000
   Inputs!B98      South East: construction per bed                                168000
   Payor!B7        Decile 1, capital on admission                                  48000
   Payor!B8        Decile 2, capital on admission                                  76000
   Payor!B9        Decile 3, capital on admission                                  105000
   Payor!B10       Decile 4, capital on admission                                  138000
   Payor!B11       Decile 5, capital on admission                                  172000
   Payor!B12       Decile 6, capital on admission                                  210000
   Payor!B13       Decile 7, capital on admission                                  255000
   Payor!B14       Decile 8, capital on admission                                  312000
   Payor!B15       Decile 9, capital on admission                                  400000
   Payor!B16       Decile 10, capital on admission                                 620000

   Result: occupancy 89.5 per cent, EBITDARM 1,528,790, rent 849,328, cover at expiry 1.96x, six months of embargo 1,410,050, full development cost 17,282,862.

Payor_Mix_Tracker_Blank.xlsx

   Inputs!B5       Self-funded weekly fee                                          1638
   Inputs!B6       Local authority weekly rate                                     1365
   Inputs!B7       Third-party top-up                                              182
   Inputs!B8       Share of council residents with a top-up                        0.38
   Inputs!B9       Self-funded share of admissions                                 0.55
   Inputs!B10      Resident's own weekly income                                    356
   Inputs!B11      Upper capital limit                                             23250
   Inputs!B12      Leaving hazard — first month                                    0.115
   Inputs!B13      Leaving hazard — months two and three                           0.055
   Inputs!B14      Leaving hazard — thereafter                                     0.04
   Inputs!B16      Fee increase to test                                            0.05
   Capital!B8      Decile 1, capital on admission                                  48000
   Capital!B9      Decile 2, capital on admission                                  76000
   Capital!B10     Decile 3, capital on admission                                  105000
   Capital!B11     Decile 4, capital on admission                                  138000
   Capital!B12     Decile 5, capital on admission                                  172000
   Capital!B13     Decile 6, capital on admission                                  210000
   Capital!B14     Decile 7, capital on admission                                  255000
   Capital!B15     Decile 8, capital on admission                                  312000
   Capital!B16     Decile 9, capital on admission                                  400000
   Capital!B17     Decile 10, capital on admission                                 620000

   Result: self-funded share of beds 36.4 per cent, presentation premium about 38 a week, 8 per cent of a fee rise given back.

Rota_And_Employer_Cost_Blank.xlsx

   Inputs!B5       Occupied beds                                                   62.67929865857707
   Inputs!B6       Care hours per resident per day                                 4.55
   Inputs!B7       Of which registered nurse                                       0.72
   Inputs!B8       Care assistant hourly rate                                      13.35
   Inputs!B9       Registered nurse hourly rate                                    24.1
   Inputs!B10      Contracted hours a week                                         37.5
   Inputs!B11      Annual leave, weeks                                             5.6
   Inputs!B12      Sickness and training absence, weeks                            1.4
   Inputs!B13      Heads per full-time equivalent                                  1.45
   Inputs!B14      Agency share of care hours                                      0.0315
   Inputs!B15      Agency premium                                                  1.55
   Inputs!B16      Non-care staff gross pay                                        567500
   Inputs!B17      Non-care staff full-time equivalents                            19.6
   Inputs!B18      Recruitment and training, share of gross pay                    0.026
   Inputs!B21      Current regime — rate                                           0.15
   Inputs!B22      Current regime — threshold                                      5000
   Inputs!B23      Current regime — Employment Allowance                           10500
   Inputs!B24      Previous regime — rate                                          0.138
   Inputs!B25      Previous regime — threshold                                     9100
   Inputs!B26      Previous regime — Employment Allowance                          5000
   Inputs!B27      Pension rate                                                    0.03
   Inputs!B28      Pension band starts at                                          6240
   Inputs!B29      Pension enrolment trigger                                       10000
   Inputs!B30      Share who opt out                                               0.1

   Result: 1,694 hours on the floor, 61.5 care FTE, employer costs 294,943, FTE error 32,713 and 50,728.

Bid_Sheet_Blank.xlsx

   Bid!B5          Registered beds                                                 70
   Bid!B6          Asking price per bed                                            285000
   Bid!B8          EBITDARM, your view                                             1528790.099796036
   Bid!B9          Trade multiple                                                  11
   Bid!B14         Required rent cover                                             1.8
   Bid!B15         Investment yield on the rent                                    0.06

   Result: asking price 19,950,000, value on the trade 16,816,691, gap 3,133,309, implied cover 1.52x.

