MERGERS AND ACQUISITIONS - BLANK MODEL SET: THE BOOK'S OWN INPUTS

The four blank workbooks share one Inputs sheet. Type these sixty figures into it (in any of the four) and the book's results come back.
Switches: 1 is yes, 0 is no. Columns C, D and E of rows 60 to 63: on the balance sheet, bears interest after, in the covenant.

   Inputs!B7     Revenue                                               184,000,000
   Inputs!B8     EBITDA                                                27,600,000
   Inputs!B9     Depreciation and amortisation                         6,400,000
   Inputs!B10    Capital expenditure                                   7,600,000
   Inputs!B11    Increase in working capital                           800,000
   Inputs!B12    Net debt, assumed rather than refinanced              38,500,000
   Inputs!B13    Book equity                                           46,300,000
   Inputs!B17    Revenue                                               620,000,000
   Inputs!B18    EBITDA                                                96,000,000
   Inputs!B19    Depreciation and amortisation                         21,000,000
   Inputs!B20    Existing debt                                         186,700,000
   Inputs!B21    Shares in issue                                       118,000,000
   Inputs!B22    Share price at announcement                           8.3
   Inputs!B26    Announced multiple of EBITDA                          9
   Inputs!B27    Tax rate, both companies                              0.25
   Inputs!B28    Transaction costs, expensed in year one               6,900,000
   Inputs!B29    Debt share of the funding                             0.6
   Inputs!B30    Discount on the issue price                           0.045
   Inputs!B31    Base rate, before the lender's margin                 0.04
   Inputs!B35    Maximum                                               25,000,000
   Inputs!B36    Probability                                           0.45
   Inputs!B37    Year of settlement                                    3
   Inputs!B41    Run rate                                              7,200,000
   Inputs!B42    Share of the run rate delivered in year 1             0.3
   Inputs!B43    Share of the run rate delivered in year 2             0.7
   Inputs!B44    Share of the run rate delivered in year 3             1
   Inputs!B45    Cost to achieve                                       9,400,000
   Inputs!B46    Share of the cost spent in year 1                     0.55
   Inputs!B47    Share of the cost spent in year 2                     0.3
   Inputs!B48    Share of the cost spent in year 3                     0.15
   Inputs!B52    Weighted average cost of capital                      0.0925
   Inputs!B53    Perpetual growth                                      0.0175
   Inputs!B54    Identified intangibles, share of the excess           0.38
   Inputs!B55    Amortisation life, years                              9
   Inputs!B60    Unfunded pension deficit                              7,200,000
   Inputs!C60    Unfunded pension deficit                              0
   Inputs!D60    Unfunded pension deficit                              1
   Inputs!E60    Unfunded pension deficit                              1
   Inputs!B61    Provided tax litigation                               2,450,000
   Inputs!C61    Provided tax litigation                               1
   Inputs!D61    Provided tax litigation                               0
   Inputs!E61    Provided tax litigation                               1
   Inputs!B62    Declared unpaid dividend                              1,800,000
   Inputs!C62    Declared unpaid dividend                              1
   Inputs!D62    Declared unpaid dividend                              0
   Inputs!E62    Declared unpaid dividend                              1
   Inputs!B63    Uncapitalised leases                                  4,150,000
   Inputs!C63    Uncapitalised leases                                  0
   Inputs!D63    Uncapitalised leases                                  0
   Inputs!E63    Uncapitalised leases                                  0
   Inputs!B69    below 2.50                                            0
   Inputs!C69    below 2.50                                            0.01
   Inputs!B70    2.50 to 3.00                                          2.5
   Inputs!C70    2.50 to 3.00                                          0.0125
   Inputs!B71    3.00 to 3.50                                          3
   Inputs!C71    3.00 to 3.50                                          0.02
   Inputs!B72    3.50 to 4.00                                          3.5
   Inputs!C72    3.50 to 4.00                                          0.0325
   Inputs!B73    4.00 and above                                        4
   Inputs!C73    4.00 and above                                        0.05

   Result: rate 5.25 per cent at leverage 2.9694; year-one accretion -15.7134 per cent statutory, 11.2171 adjusted cash;
   value created -17,530,855 against the board's 13,436,667; break-even synergy 9,142,663; band with no solution 47,952,283 to 48,460,708.

