excel financial modelling
kok12
CHAPTERNINE_Growth_Capital.docxCHAPTER NINE CASE STUDY 4: Growth Capital
Let us take our model building one step further. In this chapter, we introduce our fourth case study, investing money in a growth business. This is often known as a growth equity, a growth capital, or a development capital story. A number of investors focus on investment opportunities of this type. In such an investment opportunity, the investor is looking for businesses that require additional development capital to fully realize their growth potential. The business might be at a stage where it is profitable, but further capital is needed in order to scale the business up.
Such businesses can be found in a variety of sectors. Our particular case study involves a textile manufacturing company.
Having built the business from scratch four years ago, the founder has taken it to a point where it is now profitable. Demand for the company’s textile products is very strong, and the company needs further capital to pay down a burdensome debt that was initially provided by a nonbank third party and was taken on to finance the early-stage development of the business. Further capital is also required to repay a bank overdraft facility and endow the business with much-needed cash in the short term.
The founder has invited your investment firm to consider financing the next phase of growth of the business and has provided key inputs concerning operational assumptions (revenue; earnings before interest, taxes, depreciation, and amortization [EBITDA]; working capital; and capital expenditures). You have been leading discussions with the founder on behalf of your firm, and the key terms of your proposed investment have been agreed upon. The inputs are summarized here, and you will need to build a five-year integrated financial model that includes the profit and loss statement, the balance sheet, and the cash flow statement. In addition to this, you’ll need to calculate the initial financial returns on your proposed investment to see whether the project makes financial sense for your firm.
9.1 KEY INPUTS
Historical Sales and EBITDA. For now, limited historical financial information has been provided, namely the most recent historical sales and EBITDA.
Image Sales growth %. Sales growth projections have been provided for the next five years, after which revenue is projected to grow with inflation.
Image EBITDA %. EBITDA margin is projected to improve over the next two years and stabilize thereafter at 18 percent.
Image Stock. This is projected to be 10 percent of sales
Image Trade debtors. This is projected to be 17 percent of sales.
Image Trade creditors. This is projected to be 20 percent of sales.
Image other creditors. This is projected to be 8 percent of sales.
Image Capex. This is projected to be 5 percent of sales.
Image Corporation tax. This is projected to be 28 percent of profit before tax.
Image Depreciation. This is assumed to be $1.5 million annually.
Image Term loan A. There is an existing term loan balance (see sources and uses inputs in Table 9.2) that will be paid down using part of the proceeds from the new investment. Assuming that the balance remaining after paying down the term loan is $5 million, then the proposed amortization schedule is $1 million per year for the next five years (that is, the loan is projected to be fully repaid in five years).
Uses of funds. The proposed investment is estimated at $33 million and would be used to:
Provide cash of $10 million to the business.
Pay down $22 million of the existing debt of $27 million (see balance sheet inputs in Figure 9.1).
FIGURE 9.1 POSTDEAL BALANCE SHEET
Pay fees totaling $1 million related to processing the transaction to the investor.
Sources of funds. The proposed investment would come from the following sources of funds:
Ordinary equity investment of $15 million (see the later discussion of the postmoney equity valuation).
Loan stock of $18 million (see the later discussion of the terms of the instruments).
Based on the predeal balance sheet and the sources and uses of funds, it is possible to determine the postdeal balance sheet. The predeal balance sheet is the balance sheet immediately prior to the transaction, and the postdeal balance sheet is the balance sheet immediately after the transaction. The postdeal balance sheet is the starting balance sheet for the financial projections. In particular, the projected balance sheet at the end of Year 1 depends on the postdeal balance sheet. Likewise, the Year 2 balance sheet will in turn depend on the Year 1 balance sheet. And so on and so forth.
The predeal balance sheet is presented on the left-hand side of Figure 9.1. Manual adjustments to the predeal balance sheet are made to reflect the sources and uses of funds. Then the postdeal balance sheet is calculated for each line of the predeal balance sheet by adding the adjustments to the predeal number. The adjustments are made as follows.
Uses of funds. The proposed investment is estimated at $33 million and would be used to:
Provide cash of $10 million to the business. An amount of $10 million is included in the cash balance line. This amount will be added to the predeal cash balance of –$2.4 million to result in a postdeal cash balance of $7.6 million.
Pay down $22 million of the existing debt of $27 million. An amount of $22 million is included in the term loan A line. This amount will be deducted from the predeal term loan A balance of –$27 million to result in a postdeal term loan A balance of –$5 million.
Pay fees totaling $1 million related to processing the transaction to the investor. The transaction fees are capitalized (that is, they are added to the long-term assets of the company). An amount of $1 million is included in the transaction costs line. This amount will be added to the predeal transaction costs balance of 0 to result in a postdeal transaction costs balance of $1 million.
Sources of funds. The proposed investment would come from the following sources of funds:
Ordinary equity investment of $15 million. An amount of $15 million is included in the ordinary shares line. This amount will be added to the predeal ordinary shares balance of $9 million to result in a postdeal ordinary shares balance of $24 million.
Loan stock of $18 million. An amount of $18 million is included in the loan stock line. This amount will be added to the predeal loan stock balance of 0 to result in a postdeal loan stock balance of –$18 million. Note that the negative sign reflects the chosen convention of showing liabilities in the balance sheet as negative numbers.
The resulting postdeal balance sheet is presented on the right-hand side of Figure 9.1. The postdeal balance sheet shows that, as a result of the transaction, the company’s cash position has improved substantially, from a negative $2.4 million to a positive $7.6 million. The postdeal balance sheet also shows an increased equity capital base, up by $15 million to reach $24 million. The company’s long-term liabilities have also changed to include loan stock of $18 million and also a reduction in the existing debt, which is down by $22 million to reach $5 million.
Postmoney Valuation
The proposed equity transaction is based on a share price of $290. As presented in Figure 9.2, this means that the investor would acquire 51,724 shares (that is, 15,000,000 divided by 290). Based on a total number of outstanding shares of 60,000 before the investment, the investor would acquire 46 percent of the company’s equity (that is, 51,724 divided by [60,000 plus 51,724]). Acquiring 46 percent of the equity for $15 million means that 100 percent of the equity would be valued at $32.4 million (that is, $15 million divided by 46 percent). With the equity value at $32.4 million, the postdeal debt at $23 million (that is, $18 million loan stock plus $5 million term loan A), and the postdeal cash of $7.6 million, the enterprise value of the company is $47.8 million (that is, $32.4 million plus $23 million minus $7.6 million), giving an enterprise-value-to-EBITDA multiple for Year 0 of 5 times (that is, 47.8 divided by 9.5).
Investor Exit Assumptions
Exit assumptions are provided in Figure 9.3. The proposed investor exit time frame is five years from the date of the transaction. The exit enterprise-value-to-EBITDA multiple is assumed to be 8 times. This is an improvement from the entry enterprise-value-to-EBITDA multiple of 5 times, reflecting the view that the business will command a higher valuation in five years’ time.
FIGURE 9.3 INVESTOR EXIT ASSUMPTIONS
The transaction includes warrants giving the investor the option to increase its equity stake at the time of exit by purchasing an additional 4,000 shares at $385.7 per share (or a total of $1.5 million). The warrants are effectively a sweetener to the loan stock, enabling the company to reduce the interest it would otherwise be paying on the loan stock.
Furthermore, management has been given an incentive to perform well through management options giving the management team the opportunity to acquire 5,000 shares at $385.7 per share (or a total of $1.9 million) at the time of the investor’s exit.
If both the investor warrants and the management options are exercised, the total number of shares will be 120,724 (that is, 60,000 + 51,724 + 4,000 + 5,000) on a fully diluted basis. The investor’s stake on a fully diluted basis will be 55,724 shares, or 46.2 percent (that is, 55,724 divided by 120,724).
Debt Assumptions
Term loan A has a five-year maturity and bears an interest rate of 12 percent per annum. Figure 9.4 shows new loans drawn down as part of the deal. For the term loan A there is 0 amount drawn down. In other words, there is no new drawdown of the term loan A. The amortization schedule was provided earlier and is $1 million per annum over five years.
FIGURE 9.4 DEBT ASSUMPTIONS
The loan stock is a bullet loan with a nine-year maturity, bearing an interest rate of 20 percent per annum.
Other Assumptions
Interest is paid in the year in which it is incurred.
Tax is paid a year after it has been incurred.
Exit occurs at year-end.
9.1.1 Creating the Operational and Sources and Uses Inputs Sheets
Let’s create the Operational Inputs sheet in which we capture these inputs starting from, say, row 7, as illustrated in Figure 9.5. The timeline is also shown on row 1 in Figure 9.5.
FIGURE 9.5
We can also create the Sources and Uses Inputs sheet, which captures all the key inputs described earlier. An illustration is shown in Figures 9.6 to 9.9.
9.2 WRITING MODEL SPECIFICATIONS
Essentially, here we have two parts: the integrated financial model part (profit and loss statement, balance sheet, and cash flow statement) and the exit schedule. In Chapter 8, we learned to build an integrated financial model, and so for the first part of the model here, we can refer to the previous chapter. Once we have an integrated financial model, we can then build the exit schedule using a combination of the profit and loss statement, the balance sheet, and the cash flow statement.
9.2.1 Building the Integrated Financial Model
Profit and Loss Module
Sales
Year 1 sales is given.
Year-over-year sales growth is given.
For each period of time t, calculate sales_t as sales_t–1 times (1 + sales growth_t).
EBITDA
For each period of time t, calculate EBITDA_t as EBITDA margin_t times sales_t.
Depreciation
This is given.
EBIT (Earnings Before Interest and Tax)
For each period of time t, calculate EBIT_t as EBITDA_t minus Depreciation_t.
Interest
For each period of time t, the interest charge will be calculated in the Debt module (discussed in the next section).
Note that we will need to calculate interest for each debt instrument (term loan A and loan stock).
EBT (Earnings Before Tax)
For each period of time t, calculate EBT_t as EBIT_t minus Interest_t.
Tax
For each period of time t, calculate tax_t as tax rate times EBT_t if EBT_t is positive. Otherwise, tax_t = 0.
PAT (Profit After Tax)
For each period of time t, calculate PAT_t as EBT_t minus tax_t.
Retained Profit (Retained Earnings in U.S. Usage)
This will be calculated using a control account in which retained profit carried forward_t = retained profit brought forward_t plus PAT_t.
Retained profit brought forward_t = retained profit carried forward_t–1.
Retained profit brought forward_year 1 = retained profit carried forward in the opening balance sheet.
Debt Module
Since we have two debt instruments (term loan A and loan stock), we need to calculate the debt amounts and interest for each instrument.
The term loan A debt control account will be calculated as follows:
Term loan A debt opening balance_t = term loan A debt closing balance_t–1, with term loan A debt opening balance_year 1 = term loan A debt balance in postdeal balance sheet.
Term loan A debt drawdown_t = 0.
Term loan A debt repayment_t is given in the amortization schedule.
Term loan A debt closing balance_t = Term loan A debt opening balance_t plus term loan A debt drawdown_t plus term loan A debt repayment_t.
The term loan A interest control account will be calculated as follows:
Term loan A interest opening balance_t = term loan A interest closing balance_t–1, with term loan A interest opening balance_ year 1 = 0.
Term loan A interest charge_t = term loan A interest rate times (term loan A debt opening balance_t plus term loan A debt drawdown_t).
Term loan A interest paid_t = – term loan A interest charge_t.
Term loan A interest closing balance_t = term loan A interest opening balance_t plus term loan A interest charge_t plus term loan A interest paid_t.
The Loan stock debt control account will be calculated as follows:
Loan stock debt opening balance_t = loan stock debt closing balance_t–1, with loan stock debt opening balance_year 1 = loan stock debt balance in postdeal balance sheet.
Loan stock debt drawdown_t = 0.
Loan stock debt repayment_t = 0 if t is between 1 and 8; otherwise, loan stock debt repayment_t = – original loan stock amount drawn down.
Loan stock debt closing balance_t = loan stock debt opening balance_t plus loan stock debt drawdown_t plus loan stock debt repayment_t.
The loan stock interest control account will be calculated as follows:
Loan stock interest opening balance_t = loan stock interest closing balance_t–1, with loan stock interest opening balance_year 1 = 0.
Loan stock interest charge_t = loan stock interest rate times (loan stock debt opening balance_t + loan stock debt drawdown_t).
Loan stock interest paid_t = – loan stock interest charge_t.
Loan stock interest closing balance_t = loan stock interest opening balance_t plus loan stock interest charge_t plus loan stock interest paid_t.
Fixed Assets Module
Apart from tangible assets, all long-term assets in the balance sheet are assumed to be unchanged throughout the projection period.
The tangible assets control account will be calculated as follows:
Tangible assets opening balance_t = tangible asset closing balance_t–1, with tangible assets opening balance_year 1 = tangible assets balance in postdeal balance sheet.
Capex increases the book value of tangible assets. Capex_t = capex as % of sales_t times sales_t.
Depreciation decreases the book value of tangible assets. Depreciation_t is already given.
Tangible assets closing balance_t = tangible assets opening balance_t plus capex_t plus depreciation_t.
Tax Module
According to the tax assumptions given earlier, tax is paid a year after it has been incurred, thus creating a tax creditor on the balance sheet. We can calculate the tax creditor using the control account approach.
The tax creditor control account will be calculated as follows:
Tax creditor opening balance_t = tax creditor closing balance_t–1, with tax creditor opening balance_year 1 = tax creditor balance in postdeal balance sheet.
A tax charge in the Profit and Loss module increases the tax creditor balance. Tax charge_t = tax_t, taken from the Profit and Loss module.
Tax paid decreases the tax creditor balance. Tax paid_t = – tax charge_t–1.
Tax creditor closing balance_t = tax creditor opening balance _t plus tax charge_t plus tax paid_t.
Balance Sheet Module
Tangible assets closing balance_t is calculated in the Fixed Assets module.
Financial assets_t = financial assets in postdeal balance sheet.
Investment in joint ventures_t = investment in joint ventures in postdeal balance sheet.
Intangibles = intangibles in postdeal balance sheet.
Transaction goodwill_t = transaction goodwill in postdeal balance sheet.
Transaction costs_t = transaction costs in postdeal balance sheet.
Total fixed assets_t = the sum of tangible assets closing balance_t, financial assets_t, investment in joint ventures_t, intangibles_t, transaction goodwill_t, and transaction costs_t.
Stock_t = stock (inventory) as percent of sales_t times sales_t.
Trade debtors_t = trade debtors (accounts receivable) as percent of sales_t times sales_t.
Total current assets_t = stock_t plus debtors_t.
Closing cash balance_t is calculated in the Cash Flow module (discussed later).
Trade creditors_t = – trade creditors (accounts payable) as percent of sales_t times sales_t.
Other creditors_t = – other creditors as percent of sales_t times sales_t.
Tax creditor_t = – tax creditor closing balance_t.
Total current liabilities_t = creditors_t plus other creditors_t plus tax creditor_t.
Working capital_t = stock_t plus trade debtors_t plus trade creditors_t plus other creditors_t.
Total assets less current liabilities_t = total fixed assets_t plus total current assets_t plus current liabilities_t.
Net current assets_t = current assets_t plus current liabilities_t plus closing cash balance_t.
Closing term loan A debt balance_t is calculated in the Debt module (discussed earlier).
Closing loan stock debt balance_t is calculated in the Debt module (discussed earlier).
Net assets_t = fixed assets closing balance_t plus net current assets_t plus debt closing balance_t.
Ordinary shares_t = ordinary shares in postdeal balance sheet.
Retained profit carried forward_t (also referred to as P&L reserve) is calculated in the Profit and Loss module (discussed earlier).
Net worth_t = share capital_t plus retained profit carried forward_t.
Since net worth must equal net assets, let us add at the bottom of the balance sheet a balance sheet check that verifies that the balance sheet balances in each period. To this end, we include an additional line as follows:
Check balance_t = net assets_t minus net worth_t.
Cash Flow Module
EBITDA_t is calculated in the Profit and Loss module (discussed earlier).
Movement in stock_t = stock_t–1 minus stock_t, or stock at the start of the year minus stock at the end of the year.
Movement in trade debtors_t = trade debtors_t–1 minus trade debtors_t, or trade debtors at the start of the year minus trade debtors at the end of the year.
Movement in trade creditors_t = (trade creditors_t–1 plus other creditors_t–1) minus (trade creditors_t plus other creditors_t), or trade creditors and other creditors at the start of the year minus trade creditors and other creditors at the end of the year.
Movement in working capital_t = movement in stock_t plus movement in trade debtors_t plus movement in trade creditors_t.
Operating cash flow_t = EBITDA_t plus movement in working capital_t.
Capex_t is calculated in the Fixed Assets module (discussed earlier).
Cash flow before taxation and financing_t = operating cash flow_t minus capex_t.
Tax_t is calculated in the Tax module (discussed earlier).
Cash flow available for debt service_t = cash flow before taxation and financing_t minus tax_t.
Term loan A interest paid_t is calculated in the Debt module (discussed earlier).
Loan stock interest paid_t is calculated in the Debt module (discussed earlier).
Term loan A debt repayment_t is calculated in the Debt module (discussed earlier).
Loan stock debt repayment_t is calculated in the Debt module (discussed earlier).
Net cash flow_t = cash flow available for debt service_t minus interest paid_t (for term loan A and loan stock) minus debt repayment_t (for term loan A and loan stock).
We also need to include a control account calculating the closing cash balance_t.
Closing cash balance_t = opening cash balance_t + net cash flow_t.
Opening cash balance_t = closing cash balance_t–1, with opening cash balance_year 1 given in the postdeal balance sheet.
9.2.2 Building the Exit and Return Schedules
As explained earlier, we have essentially two parts to the calculations: the integrated financial model part (profit and loss statement, balance sheet, and cash flow statement, which was specified in Section 9.2.1) and the exit schedule. The main exit assumption has already been given, namely, the enterprise-value-to-EBITDA multiple. Based on the exit enterprise-value-to-EBITDA multiple, we can calculate the enterprise value. From the enterprise value, we can deduct net debt (that is, debt less cash), and the result is the value of equity. The equity value divided by the number of shares outstanding gives the value of one share. The steps from enterprise value down to equity value are referred to as the exit waterfall.
The exit waterfall can be calculated as follows:
Exit Waterfall
EBITDA_t is taken from the Profit and Loss module.
Enterprise-value-to-EBITDA multiple_t is taken from the exit assumptions.
Enterprise value_t = enterprise-value-to-EBITDA multiple_t times EBITDA_t.
Term loan A_t is taken from the Balance Sheet module.
Loan stock_t is taken from the Balance Sheet module.
Cash_t is taken from the Balance Sheet module.
Investor warrants proceeds_t = investor warrants strike cost.
Management options proceeds_t = management options strike cost.
Equity value_t = sum of (enterprise value_t, term loan A_t, loan stock_t, cash_t, investor warrants proceeds_t, and management options proceeds_t).
Number of fully diluted shares_t is taken from the exit assumptions.
Implied share price_t = equity value_t divided by number of fully diluted shares_t.
Notes:
There are essentially three components that are aggregated in the calculation of equity value. Equity value = enterprise value less debt plus cash.
The order in which proceeds from the sale of the company are allocated across the different classes of investments is very important, especially when there is not enough proceeds to allocate to all classes of investments. The exit waterfall just discussed results in the same share price whether cash is added to enterprise value before the deduction of debt or not. The reason for this is that the exit proceeds are sufficient to repay all the outstanding debt without the addition of the cash balance, warrants proceeds, and management options proceeds to the enterprise value.
Exit IRR and Money Multiple
The key to calculating the IRR and the money multiple is to make sure that all cash flows to and from the investor, together with the timing of these cash flows, have been captured. A simple table that can help ensure this is given in Table 9.3.
9.3 CREATING THE CALCULATIONS SHEET
Having gone through how the model calculations would work, we can now add the different calculation modules to the Calculations sheet.
9.3.1 Assumptions Module
Let’s bring together all the key operational assumptions, debt assumptions, and exit assumptions from the two input sheets.
Operational Assumptions
Historical sales. In cell I7, we can write the formula “=‘Operational Inputs’!I7”, which picks up the historical sales figure from the Operational Inputs sheet.
Copy cell I7 and paste special its formula into the following cells:
Historical EBITDA. Paste special the formula into cell I8.
Sales growth %. Paste special the formula into the block J9:S9.
EBITDA margin. Paste special the formula into the block J10:S10.
Stock (inventory) as % of sales. Paste special the formula into the block J11:S11.
Trade debtors (accounts receivable) as % of sales. Paste special the formula into the block J12:S12.
Trade creditors (accounts payable) as % of sales. Paste special the formula into the block J13:S13.
Other creditors as % of sales. Paste special the formula into the block J14:S14.
Capex as % of sales. Paste special the formula into the block J15:S15.
Corporation (corporate) tax. Paste special the formula into the block J16:S16.
Depreciation. Paste special the formula into the block J17:S17.
TLA repayment. Paste special the formula into the block J18:S18.
Note that a quicker way of completing the operational inputs would be to paste special the formula into the block I8:S18. This would involve only one step instead of eleven steps.
Debt Assumptions
Term loan A quantum. We can write in cell H21 the formula “=‘S&U Inputs’!D37”.
Copy cell H21 and paste special it into the block H21:J22. This picks up the entire debt assumptions table from the S&U Inputs sheet.
Exit Assumptions
Exit year. In cell I25, we can write the formula “=‘S&U Inputs’!D22”.
Copy cell I25 and paste special the formula into the following cells:
# warrant shares. Paste special the formula into cell I26.
Investors’ equity stake (FD basis). Paste special formula into cell I27.
9.3.2 Profit and Loss Module
Let’s create the Profit and Loss module right below the Assumptions module, say on row 31 (see Figure 9.11). We can now add the different lines of the Profit and Loss module following the specifications described earlier.
Sales
In cell I32, we can write the formula “=IF(I$1=0,I7,H32*(1+I9))”, which basically means that if we are in Year 0 (the historical year), then sales = historical sales (which is $57 million); otherwise, sales = sales from the previous year times (1 plus sales growth % for the current year).
EBITDA
In cell I33, we can write the formula “=IF(I$1=0,I8,I10*I32)”, which basically means that if we are in Year 0 (the historical year), then EBITDA = historical EBITDA (which is $9.5 million); otherwise, EBITDA = EBITDA margin times sales.
Copy cells I32:I33 and paste them into the block I32:S33.
Depreciation
In cell J34, we can write the formula “=J17”, which picks up the value of depreciation from row 17.
EBIT
In cell J35, we can write the formula “=J33+J34”, which calculates the profit (or earnings) before interest and tax as EBITDA less depreciation. Note that EBIT is defined as EBITDA less depreciation and amortization. In this case, since there is no amortization, EBIT is simply EBITDA less depreciation.
Term loan A interest
As indicated in the model specifications, the term loan A interest charge will be calculated in the Debt module. So we skip the interest line for now.
Loan stock interest
Likewise, the loan stock interest charge will be calculated in the Debt module. So we skip the interest line for now.
EBT
In cell J41, we can write the formula “=SUM(J39,J35)”, which calculates EBT as EBIT less term loan A interest and less loan stock interest (note that interest will be negative, as it counts toward reducing profit; that is, it is a cost to the business).
Taxes
In cell J42, we can write the formula “= –IF(J41>0,J16*J41,0))”, which calculates the taxes as tax rate times EBT if EBT is positive. In other words, taxes are incurred only if the project recorded a profit.
PAT
In cell J43, we can write the formula “=J42+J41”, which calculates PAT as EBT less taxes.
Retained profit b/f
In cell J45, we can write the formula “=IF(J$1=1,I132,I46)”, which means that if we are in Year 1, then the retained profit brought forward equals the retained profit from the postdeal balance sheet. Otherwise, the retained profit brought forward equals the retained profit carried forward at the end of the previous period.
Retained profit c/f
In cell J46, we can write the formula “=J45+J43”, which calculates the retained profit carried forward as equal to the retained profit brought forward plus PAT.
Copy cells J32:J46 and paste them into the block I32:S46. The Profit and Loss module is illustrated in Figure 9.11.
Note that the Profit and Loss module shown in Figure 9.11 is not yet final, as the term loan A interest and loan stock interest lines have not been completed.
9.3.3 Balance Sheet Module
We will do the balance sheet in two steps. First, we need to bring the postdeal balance sheet from the S&U Inputs sheet into the Calculations sheet. The postdeal balance sheet is the starting balance sheet for our projections and is therefore needed for our calculations.
Then we will return to the balance sheet and complete it once the different components (the Debt module, the Fixed Assets module, and so on) have been calculated.
To bring the postdeal balance sheet from the S&U Inputs sheet into the Calculations sheet, we can do the following:
Fixed Assets
Postdeal tangible assets
In cell I51, we can write the formula “=‘S&U Inputs’!J44”, which picks up the postdeal tangible assets from the S&U Inputs sheet.
Postdeal financial assets
In cell I52, we can write the formula “=‘S&U Inputs’!J45”, which picks up the postdeal financial assets from the S&U Inputs sheet.
Postdeal investment in joint ventures
In cell I53, we can write the formula “=‘S&U Inputs’!J46”, which picks up the postdeal investment in joint ventures from the S&U Inputs sheet.
Postdeal intangibles
In cell I54, we can write the formula “=‘S&U Inputs’!J47”, which picks up the postdeal intangibles from the S&U Inputs sheet.
Postdeal transaction goodwill
In cell I55, we can write the formula “=‘S&U Inputs’!J48”, which picks up the postdeal transaction goodwill from the S&U Inputs sheet.
Postdeal transaction costs
In cell I56, we can write the formula “=‘S&U Inputs’!J49”, which picks up the postdeal transaction costs from the S&U Inputs sheet.
Total fixed assets
In cell I57, we can write the formula “=SUM(I51:I56)”, which aggregates all the components of fixed assets. Copy cell I57 and paste special the formula into the block I57:S57.
Current Assets
Postdeal stock (inventory in U.S. usage)
In cell I60, we can write the formula “=‘S&U Inputs’!J53”, which picks up the postdeal stock from the S&U Inputs sheet.
Postdeal trade debtors (accounts receivable)
In cell I61, we can write the formula “=‘S&U Inputs’!J54”, which picks up the postdeal trade debtors from the S&U Inputs sheet.
Total current assets
In cell I62, we can write the formula “=SUM(I60:I61)”, which aggregates all the components of current assets.
Postdeal cash balance
In cell I64, we can write the formula “=‘S&U Inputs’!J57”, which picks up the postdeal cash balance from the S&U Inputs sheet.
Current Liabilities
Postdeal trade creditors (accounts payable in U.S. usage)
In cell I67, we can write the formula “=‘S&U Inputs’!J60”, which picks up the postdeal trade creditors from the S&U Inputs sheet.
Postdeal other creditors
In cell I68, we can write the formula “=‘S&U Inputs’!J61”, which picks up the postdeal other creditors from the S&U Inputs sheet.
Postdeal tax creditor
In cell I69, we can write the formula “=‘S&U Inputs’!J62”, which picks up the postdeal tax creditor from the S&U Inputs sheet.
Total current liabilities
In cell I70, we can write the formula “=SUM(I67:I69)”, which aggregates all the components of current liabilities. Copy cell I70 and paste special the formula into the block I70:S70.
Total assets less CL
In cell I72, we can write the formula “=I57+I62+I70”, which aggregates fixed assets, total current assets, and total current liabilities. Copy cell I72 and paste special the formula into the block I72:S72.
Net current assets
In cell I74, we can write the formula “=I62+I64+I70”, which aggregates total current assets, total current liabilities, and cash balance. Copy cell I74 and paste special the formula into the block I74:S74.
Long-Term Liabilities
Postdeal term loan A
In cell I77, we can write the formula “=‘S&U Inputs’!J70”, which picks up the postdeal term loan A from the S&U Inputs sheet.
Postdeal loan stock
In cell I78, we can write the formula “=‘S&U Inputs’!J71”, which picks up the postdeal loan stock from the S&U Inputs sheet.
Total long-term liabilities
In cell I79, we can write the formula “=SUM(I77:I78)”, which aggregates all the components of long-term liabilities. Copy cell I79 and paste special the formula into the block I79:S79.
Net assets
In cell I81, we can write the formula “=I57+I74+I79”, which aggregates total fixed assets, net current assets, and long-term liabilities. Copy cell I81 and paste special the formula into the block I81:S81.
Postdeal ordinary shares
In cell I83, we can write the formula “=‘S&U Inputs’!J76”, which picks up the postdeal ordinary shares from the S&U Inputs sheet.
Postdeal P&L reserve
In cell I84, we can write the formula “=‘S&U Inputs’!J77”, which picks up the postdeal P&L reserve from the S&U Inputs sheet.
Net worth
In cell I85, we can write the formula “=SUM(I83:I84)”, which aggregates all the components of net worth. Copy cell I85 and paste special the formula into the block I85:S85.
Balance sheet check
In cell I86, we can write the formula “=abs(I81–I85)”; this calculates the difference between net Assets and net worth, which should be 0. Copy cell I86 and paste special the formula into the block I86:S86.
Overall balance sheet check
In cell C3, we also write the formula “=IF(SUM($I$87:$S$87) <=0.1,”OK”,“Error”)”, which gives an overall balance sheet error check. A conditional formatting is applied to cell C3 such that if the value is OK, then the cell background color is green; and if the value is Error, then the cell background color is red.
The post deal balance sheet is illustrated in Figures 9.12 and 9.13.
Having completed the postdeal balance sheet, we will now focus on calculating the projected balance sheet. To this end, we need to calculate the individual components (fixed assets, debt, and so on), after which we will revisit the balance sheet to incorporate the projected figures.
9.3.4 Debt Module
Let’s create the Debt module right below the Balance Sheet module, say on row 90. We can now run a control account for the principal and a control account for the interest for each of term loan A and loan stock in line with the specifications described earlier.
Term Loan A Debt Control Account
Principal balance b/f
In cell J93, we can write the formula “=IF(J$1=1,–I77,I96)”, which basically means that if we are in Year 1, then the principal brought forward equals the principal amount in the postdeal balance sheet (which is in cell I77). Otherwise, the principal brought forward equals the principal carried forward from the previous year.
Principal drawdown
We can leave this row empty, as there is no planned drawdown after Year 0.
Principal Repayment
In cell J95, we can write the formula “=J18”, which basically picks up row 18 (debt repaid).
Principal balance c/f
In cell J96, we can write the formula “=SUM(J93:J95)”, which basically calculates the principal carried forward as equal to principal brought forward plus principal drawdown less principal repaid.
Copy cells J93:J96 and paste them into the block J93:S96.
Term Loan A Interest Control Account
Interest b/f
In cell J99, we can write the formula “=I102”, which basically means that the interest brought forward equals the interest carried forward from the previous year.
Interest charge
In cell I100, we can write the formula “=SUM(J93:J94)*$J$21”, which calculates the interest charge as being equal to the sum of the (debt balance brought forward plus debt drawdown) times the annual interest rate (which is in cell J21).
Interest paid
In cell J101, we can write the formula “= –J100”, which reflects our assumption that interest charged (or incurred) is paid the same year.
Interest c/f
In cell J102, we can write the formula “=SUM(J99:J101)”, which basically calculates interest carried forward as equal to interest brought forward plus interest charge less interest paid.
Copy cells J99:J102 and paste them into the block J99:S102.
Loan Stock Principal Control Account
Principal balance b/f
In cell J107, we can write the formula “=IF(J$1=1,–I78,I110)”, which basically means that if we are in Year 1, then the principal brought forward equals the principal amount in the postdeal balance sheet (which is in cell I78). Otherwise, the principal brought forward equals the principal carried forward from the previous year.
Principal drawdown
We can leave this row empty, as there is no planned drawdown after Year 0.
Principal Repayment
In cell J109, we can write the formula “=IF(J$1=$I$22,–J107,0)”, which means that debt repayment is 0 unless we are in Year 9 (which is the value in cell I22).
Principal balance c/f
In cell J110, we can write the formula “=SUM(J107:J109)”, which basically calculates the principal carried forward as equal to the principal brought forward plus principal drawdown less principal repaid.
Copy cells J107:J110 and paste them into the block J107:S110.
Loan Stock Interest Control Account
Interest b/f
In cell J113, we can write the formula “=I116”, which basically means that interest brought forward equals the interest carried forward from the previous year.
Interest charge
In cell J114, we can write the formula “=SUM(J107:J108)*$J$22”, which calculates the interest charge as being equal to the sum of the (principal balance brought forward plus principal drawdown) times the annual interest rate (which is in cell J22).
Interest paid
In cell J115, we can write the formula “= –J114”, which reflects our assumption that interest charged (or incurred) is paid in the same year.
Interest c/f
In cell J116, we can write the formula “=SUM(J113:J115)”, which basically calculates the interest carried forward as equal to the interest brought forward plus interest charge less interest paid.
Copy cells J113:J116 and paste them into the block J113:S116.
The Debt module is illustrated in Figures 9.14 and 9.15.
9.3.5 Fixed Assets Module
Let’s create the Fixed Assets module right below the Debt module, say on row 119. We can now run a control account for the tangible assets in line with the specifications described earlier.
Fixed Assets Control Account
Tangible fixed assets balance b/f
In cell J121, we can write the formula “=IF(J$1=1,I51,I124)”, which basically means that if we are in Year 1, then tangible fixed assets brought forward are equal to the tangible fixed assets amount in the postdeal balance sheet (which is in cell I51). Otherwise, tangible fixed assets brought forward are equal to tangible fixed assets carried forward from the previous year.
Capex
In cell J122, we can write the formula “=J15*J32”, which basically calculates capex as capex as % of sales times the sales figure.
Depreciation
In cell J123, we can write the formula “J17”, which basically picks up the depreciation amount from the Profit and Loss module.
Tangible fixed assets balance c/f
In cell J124, we can write the formula “=SUM(J121:J123)”, which basically calculates tangible fixed assets carried forward as equal to tangible fixed assets brought forward plus capex less depreciation.
Copy cells J121:J124 and paste them into the block J121:S124. The Fixed Assets module is illustrated in Figure 9.16.
9.3.6 Tax Module
Let’s create the Tax module right below the Fixed Assets module, say on row 127. We can run a control account for the tax creditor in line with the specifications described earlier.
Tax Creditor Control Account
Tax creditor balance b/f
In cell J129, we can write the formula “=IF(J$1=1,–I69,I132)”, which basically means that if we are in Year 1, then the tax creditor balance brought forward is equal to the tax creditor balance in the postdeal balance sheet (which is in cell I69). Otherwise, the tax creditor balance brought forward is equal to the tax creditor balance carried forward from the previous year.
Tax charge
In cell J130, we can write the formula “= –J42”, which basically picks up the tax charge from the Profit and Loss module.
Tax paid
In cell J131, we can write the formulae “= –I130”, which basically calculates the tax paid as being the tax charge from the previous period.
Tax creditor balance c/f
In cell J132, we can write the formula “=SUM(J129:J131)”, which basically calculates the tax creditor balance carried forward as equal to the tax creditor balance b/f plus tax charge less tax paid.
Copy cells J129:J132 and paste them into the block J129:S132. The tax creditor module is also illustrated in Figure 9.16.
Note: With the Debt module now completed, we can go back to the Profit and Loss module and complete the interest lines as follows:
Term loan A interest
In cell I37, we can write the formula “= – J100”, which picks up the interest charge for term loan A from the Debt module.
Loan stock interest
In cell I38, we can write the formula “= –J114”, which picks up the interest charge for the loan stock from the Debt module.
Total interest
In cell I39, we can write the formula “=SUM(J37:J38)”, which aggregates the term loan A and loan stock interest.
Copy cells J37:J39 and paste them into the block J37:S39.
The revised Profit and Loss module is illustrated in Figure 9.17.
9.3.7 Revisiting the Balance Sheet Module
We can now revisit the Balance Sheet module and complete it with the projected balance sheet (so far, only the postdeal balance sheet has been completed).
Fixed Assets
Tangible assets
In cell J51, we can write the formula “=J124”, which picks up tangible assets from the Fixed Asset module.
Financial assets
In cell J52, we can write the formula “=I52”, which picks up the financial assets from the previous period.
Investment in joint ventures
In cell J53, we can write the formula “=I53”, which picks up the investment in joint ventures from the previous period.
Intangibles
In cell J54, we can write the formula “I54”, which picks up the intangibles from the previous period.
Transaction goodwill
In cell J55, we can write the formula “I55”, which picks up the transaction goodwill from the previous period.
Transaction costs
In cell J56, we can write the formula “I56”, which picks up the transaction costs from the previous period.
Copy cells J51:J56 and paste special them into the block J51:S56.
Current Assets
Stock (inventory)
In cell J60, we can write the formula “=J$32*J11”, which calculates stock as sales times stock as % of sales.
Trade debtors (accounts receivable)
In cell J61, we can write the formula “=J$32*J12”, which calculates trade debtors as sales times trade debtors as % of sales.
Copy cells J60:J61 and paste special them into the block J60:S61.
Cash Balance
We skip cash for now, as we have yet to complete the Cash Flow module.
Current Liabilities
Trade creditors (accounts payable)
In cell J67, we can write the formula “=–J$32*J13”, which calculates trade creditors as sales times trade creditors as % of sales.
Other creditors
In cell J68, we can write the formula “=–J$32*J14”, which calculates other creditors as sales times other creditors as % of sales.
Tax creditor
In cell J69, we can write the formula “= –J132”, which picks up the tax creditor balance from the Tax module.
Copy cells J67:J69 and paste special the formula into the block J67:S69.
Long-Term Liabilities
Term loan A
In cell J77, we can write the formula “= –J96”, which picks up the term loan A balance from the Debt module.
Loan stock
In cell J78, we can write the formula “= –J110”, which picks up the loan stock balance from the Debt module.
Copy cells J77:J78 and paste special them into the block J77:S78. Ordinary shares
In cell J83, we can write the formula “=I83”, which picks up the ordinary shares balance from the previous period.
P&L reserve
In cell J84, we can write the formula “=J46”, which picks up the P&L reserve from the Profit and Loss module.
Copy cells J83:J84 and paste special them into block J83:S84. The Balance Sheet module is illustrated in Figures 9.18 and 9.19.
9.3.8 Cash Flow Module
Let’s create the Cash Flow module right below the Tax module, say on row 135. We can now add the different lines of the Cash Flow module following the specifications described earlier.
EBITDA
In cell J136, we can write the formula “=J33”, which picks up the EBITDA figure from the Profit and Loss module.
Note: The balance sheet figures are not yet final, as the cash balance line has yet to be completed.
Movement in stock
In cell J138, we can write the formula “=I60–J60”, which calculates the movement in stock (inventory) as stock at the end of the previous period (I60) minus stock at the end of the current period (J60).
Movement in debtors
In cell J139, we can write the formula “=I61–J61”, which calculates the movement in debtors (accounts receivable) as debtors at the end of the previous period less debtors at the end of the current period.
Movement in creditors
In cell J140, we can write the formula “=SUM(I67:I68)–SUM(J67:J68)”, which calculates the movement in creditors as the sum of trade and other creditors at the end of the previous period less the sum of trade and other creditors at the end of the current period.
Total movement in working capital
In cell J141, we can write the formula “=SUM(J138:J140)”, which calculates the total movement in working capital as the aggregate of movement in stock, movement in debtors, and movement in creditors.
Operating cash flow
In cell J143, we can write the formula “=J136+J141”, which calculates the operating cash flow as EBITDA (J136) plus movement in the sum of stock, debtors, and creditors (J141).
Cash conversion ratio %
In cell J144, we can write the formula “=J143/J136”, which calculates the cash conversion ratio as operating cash flow divided by EBITDA.
Capex
In cell J146, we can write the formula “= –J122”, which picks up capex from the Fixed Assets module.
Cash flow before tax and financing
In cell J147, we can write the formula “=SUM(J143,J146)”, which calculates cash flow before tax and financing as operating cash flow plus capex.
Tax
In cell J148, we can write the formula “=J131”, which picks up tax from the Tax module.
Debt drawdown
In cell J149, we can write the formula “=SUM(J94,J108)”, which calculates Debt drawdown as the sum of the amounts drawn down from the term loan A and the loan stock.
Cash flow available for debt service
In cell J150, we can write the formula “=SUM(J147:J149)”, which calculates cash flow available for debt service as the sum of cash flow before tax and financing, Tax, and Debt drawdown.
Term loan A interest
In cell J152, we can write the formulae “=J101”, which picks up interest for term loan A from the Debt module.
Loan stock interest
In cell J153, we can write the formula “=J115”, which picks up interest for the loan stock from the Debt module.
Total interest
In cell J154, we can write the formula “=SUM(J152:J153)”, which aggregates the term loan interest and the loan stock interest.
Term loan A debt repayment
In cell J157, we can write the formula “=J95”, which picks up the term loan A amount repaid from the Debt module.
Loan stock debt repayment
In cell J158, we can write the formula “=J109”, which picks up the loan stock amount repaid from the Debt module.
Total repayment
In cell J159, we can write the formula “=SUM(J157:J158)”, which aggregates the term loan A and loan stock debt amounts repaid.
Net cash flow
In cell J161, we can write the formula “=J150+J154+J159”, which calculates net cash flow as cash flow available for debt service less interest less repayments on the principal.
Cash balance b/f
In cell J162, we can write the formula “=IF(J$1=1,$I$64,I163)”, which calculates cash balance brought forward as the cash balance in the postdeal balance sheet (I64) if we are in Year 1. Otherwise, the cash balance brought forward is equal to the cash balance carried forward in the previous period.
Cash balance c/f
In cell J163, we can write the formula “=J162+J161”, which calculates the cash balance carried forward as the cash balance brought forward plus net cash flow.
Copy cells J136:J163 and paste them into the block J136:S163. The cash flow module is illustrated in Figures 9.20 and 9.21.
Note: With the Cash Flow module now completed, we can go back to the Balance Sheet module and complete the cash line as follows:
Cash
In cell J64, we can write the formula “=J163”, which picks up the cash balance carried forward from the Cash Flow module.
Copy cell J163 and paste it into the block J163:S163.
Overall balance sheet check
Note that after including the cash balance in the balance sheet, the balance sheet check shows a green OK remark.
The revised Balance Sheet module is shown in Figure 9.22.
9.3.9 Exit Schedule Module
Let’s create the Exit Schedule module right below the Cash Flow module, say on row 166.
Exit Waterfall
EBITDA
In cell J167, we can write the formula “=J136”, which picks up EBITDA from the Cash Flow module.
EV/EBITDA multiple
In cell J168, we can write the formula “=‘S&U Inputs’!$D$23”, which picks up the exit enterprise-value-to-EBITDA multiple from the S&U Inputs sheet.
Enterprise value
In cell J169, we can write the formula “=J168*J167”, which calculates enterprise value as the enterprise-value-to-EBITDA multiple times EBITDA.
Less
Term loan A
In cell J171, we can write the formula “= –MIN(J169,–J77)”, which calculates the proceeds from exit attributable to term loan A as the lower of enterprise value and the outstanding balance on term loan A as given in the balance sheet.
Loan stock
In cell J172, we can write the formula “= –MIN(J169+J171,–J78)”, which calculates the proceeds from exit attributable to loan stock as the lower of (1) enterprise value less term loan A and (2) the outstanding balance on loan stock as given in the balance sheet.
Plus
Cash
In cell J174, we can write the formula “=J64”, which picks up the cash balance from the balance sheet.
Investor warrants proceeds
In cell J175, we can write the formula “=‘S&U Inputs’!$D$28”, which picks up the proceeds from the investor warrants from the S&U Inputs sheet.
Management options proceeds
In cell J176, we can write the formula “=‘S&U Inputs’!$G$28”, which picks up the proceeds from the management options from the S&U Inputs sheet.
Equity value
In cell J178, we can write the formula “=SUM(J169:J176)”, which calculates the equity value as the enterprise value less term loan A and the loan stock, plus cash plus proceeds from investor warrants plus proceeds from management options.
Adjusted fully diluted share count (000s)
In cell J179, we can write the formula “=‘S&U Inputs’!$D$30/1000”, which picks up the adjusted number of shares on a fully diluted basis (000s) from the S&U Inputs sheet.
Implied share price ($ per share)
Copy cells J167:J180 and paste them into the block J167:S180. The Exit Schedule module is illustrated in Figure 9.23.
FIGURE 9.23
9.3.10 Investor Blended IRR and Money Multiple
As discussed earlier, the key to calculating the IRR and the money multiple is to ensure that all cash flows to and from the investor have been captured, along with the corresponding timing of these cash flows. The cash flows, together with the corresponding timing, were determined in Section 9.2.2. We can create the investor IRR and Money Multiple module, say on row 183.
Purchase of equity
In cell I184, we can write the formula “= –‘S&U Inputs’!D14”, which picks up the investor’s cash outlay for the purchase of equity in the textile project.
Purchase of loan stock
In cell I185, we can write the formula “= –‘S&U Inputs’!D15”, which picks up the investor’s cash outlay for the purchase of a loan stock in the textile project.
Transaction fee
In cell I186, we can write the formula “= –‘S&U Inputs’!D10”, which picks up the investor’s cash receipt in relation to the transaction fee.
Warrant exercise
In cell J187, we can write the formula “= –IF(J$1=$I$25,J175,0)”, which calculates warrant exercise cost as the opposite of the warrant proceeds to the company (in cell J175) if this is during the exit year; otherwise, warrant exercise cost is 0.
Loan stock repayment
In cell J188, we can write the formula “=IF(J$1=$I$25,–J78,0)”, which calculates loan stock repayment as the opposite of the loan stock balance in the balance sheet if this is during the exit year; otherwise, loan stock repayment is 0. Note that the formula works as long as the exit year is before the maturity of the loan stock repayment, which is the case here (the loan stock is a bullet loan payable at the end of nine years, whereas the exit year is Year 5).
Loan stock interest
In cell J189, we can write the formula “=IF(J$1<=$I$25,–J153,0)”, which calculates the loan stock interest as the opposite of the loan stock interest in the Cash Flow module if this is before the exit year; otherwise, loan stock interest is 0. Note that just as in the calculation of the loan stock repayment, the formula works as long as the exit year is before the maturity of the loan stock repayment, which is the case here (the loan stock is a bullet loan payable at the end of nine years, whereas the exit year is Year 5).
Sale of ordinary shares
In cell J190, we can write the formula “=IF(J$1=$I$25,J178*$I$27,0)”, which calculates the proceeds from the sale of ordinary shares as equal to the equity value (in cell J178) times the investor’s equity stake on a fully diluted basis (in cell I27); otherwise, the proceeds from the sale of ordinary shares is 0.
Sale of warrant shares
In cell J191, we can write the formula “=IF(J$1=$I$25,J180 *$I$26/1000000,0)”, which calculates the proceeds from the sale of warrant shares as equal to the implied share price (in cell J180) times the number of warrant shares (in cell I26); otherwise, proceeds from the sale of warrant shares is 0.
Net cash flows
In cell J192, we can write the formula “=SUM(I184:I191)”, which aggregates the net cash flows to and from the investor as the sum of rows 184 to 191.
IRR
In cell J194, we can write the formula “=IRR($I192:J192,0.1)”, which calculates the internal rate of return (IRR) of the cash flows in row 192. Note the presence of the $ symbol on column I, which ensures that as we copy the formula in cell J194 into future years, the starting point for the cash flows remains unchanged at Year 0.
Money multiple
In cell J195, we can write the formula “= –SUM($J188:J191)/$I$192”, which calculates the money multiple as equal to all cash inflows [in the block SUM($J188:J191)] divided by all cash outflows (in cell I192). Note here, too, the presence of the $ symbol on column J, which ensures that as we copy the formula in cell J195 into future years, the starting point for the cash flow remains unchanged at Year 1.
Copy cells J187:J191 and paste them into the block J187:S191. Copy cell J192 and paste it into the block J192:S192. Copy cells J194:J195 and paste them into the block J194:S195.
Note: In cells H194 and H195, we are showing an IRR and money multiple. These are for Year 7, and the content of cell H194 is “=P194” and that of cell H195 is “=P195”. The following section explains the purpose of these two cells.
9.3.11 Exit Sensitivity Analysis
When looking at investor returns (for example, in terms of the IRR or the money multiple), it is customary to conduct a sensitivity analysis that analyzes the extent to which a change in the value of the drivers of exit returns will have an impact on the magnitude of the return itself.
Here we have two measures of return: the IRR and the money multiple. We also have two key drivers of return: exit year and exit enterprise-value-to-EBITDA multiple. The year of exit is clearly a driver, as the financial performance of the company (in terms of EBITDA) will be different depending on the year in which we exit the company. Also, the exit enterprise-value-to-EBITDA multiple is another obvious driver of return, as it is the multiple that is applied to the EBITDA for the year of exit.
Our sensitivity analysis will focus on changing the value of these two drivers, exit year and exit multiple, and observing the impact of each on the IRR and the money multiple. The process of changing the values of the drivers can be done manually, but Microsoft Excel has a built-in tool, a data table, for running these sensitivities automatically.
A data table can be regarded in the same way as any other function in Microsoft Excel, and therefore it produces a result based on inputs that the user needs to specify. A data table’s inputs are (1) the drivers of the measure and (2) the measure itself. Once the user specifies the values for the drivers in (1) and the formula for the measure in (2), then the function will return a table of values for the measure corresponding to the values of the drivers.
We will create data tables for the IRR and for the money multiple.
IRR Data Table
Drivers: exit year and exit multiple
Measure: IRR
Data table inputs:
1. Values for exit year: Years 5, 6, and 7; values for exit enterprise-value-to-EBITDA multiple: 7, 8, and 9 times. In other words, we are looking to allow the exit year to vary among 5, 6, and 7. We are also looking to allow the exit enterprise-value-to-EBITDA multiple to vary among 7, 8, and 9 times.
2. Measure: IRR in Year 7. Based on the data table inputs just given, Year 7 is the latest year in which we are looking to exit the textile company. The cash flow to the investor after exiting the company is 0, and so if we exit in Year 5, the IRR in Year 7 will be the same as the IRR in Year 5. Likewise, if we exit in Year 6, the IRR in Year 7 will be the same as the IRR in Year 6. Finally, the IRR in Year 7 will apply if we exit in year 7. Cell H194 (shown in Figure 9.24) contains our measure for the purpose of the data table.
We will build the data table in the S&U Inputs sheet using the following steps (refer to Microsoft Excel’s Help for further information on creating data tables). The data table we will build is known as a two-variable data table (it has two drivers: exit year and exit multiple). We will have one variable along the row (say, exit Year 5, 6, and 7) and the other variable down the column (say exit multiple 7, 8, and 9 times).
Select the S&U Inputs sheet.
Enter the measure (Year 7 IRR) in any given cell, say I24. The formula in cell I24 reads “=Calculations!H194”.
Along row 24 (the same row as the measure, but to the right-hand side of the measure), enter the values for the exit year driver. Thus, in cells J24, K24, and L24, type 5, 6, and 7, respectively.
Down column I (the same column as the measure, but below the measure), enter the values for the exit enterprise-value-to-EBITDA multiple. And so in cells I25, I26, and I27, type 7x, 8x, and 9x, respectively.
Select the block of cells that contains the measure (I24) and the values of the two drivers. Thus, this selection will involve the block I24:L27.
On the Data tab, in the Data Tools group, click on What-If Analysis, and then click on Data Table. This is illustrated in Figure 9.25.
FIGURE 9.25
After clicking on Data Table, the Data Table dialogue box appears.
In the Row input cell box, enter the reference to the input cell for the input values in the row, which is the exit year driver and is specified in cell D22. And so we enter D22 in the Row input cell box. Alternatively, click on cell D22 (note that Microsoft Excel automatically writes cell $D$22 in the Row input cell box).
In the Column input cell box, enter the reference to the input cell for the input values in the column, which is exit enterprise-value-to-EBITDA multiple and is specified in cell D23. And so we enter D23 in the Column input cell box. Alternatively, click on cell D23 (note that Microsoft Excel automatically writes cell $D$23 in the Column input cell box). This is illustrated in Figure 9.26.
Click OK and press F9 to recalculate (recalculating ensures that Microsoft Excel will calculate the data table, in case the Microsoft Excel calculations options were not set to calculate data tables automatically).
The resulting IRR data table is illustrated in Figure 9.27.
Notes
For aesthetic reasons, we have formatted the font in cell I24 to a white color (that is, the same color as the cell’s background color) so that the cell containing the measure is not visible.
According to the best practice principles discussed in Chapter 2, the data table is a calculation and therefore should be included in the Calculations sheet. However, Microsoft Excel requires input drivers to be located in the same sheet as the data table itself. Thus, the only reason why we have created the data table in the S&U Inputs sheet instead of the Calculations sheet is Excel’s own limitations. Note that there are ways of creating off-sheet data tables in Microsoft Excel (that is, data tables where the input drivers are not necessarily located in the same sheet as the data table itself), but we will not go into these techniques in this book.
Money Multiple Data Table
Drivers: exit year and exit multiple
Measure: money multiple
Data table inputs:
1. Values for exit year: years 5, 6, and 7; values for exit enterprise-value-to-EBITDA multiple: 7, 8, and 9 times. In other words, we are looking to allow the exit year to vary among 5, 6, and 7. We are also looking to allow the exit enterprise-value-to-EBITDA multiple to vary between 7, 8, and 9 times.
2. Measure: money multiple in Year 7. Based on the data table inputs just given, Year 7 is the latest year in which we are looking to exit the textile company. The cash flow to the investor after exiting the company is 0, and so if we exit in Year 5, the money multiple in Year 7 is the same as the money multiple in Year 5. Likewise, if we exit in Year 6, the money multiple in Year 7 will be the same as the money multiple in Year 6. Finally, the money multiple in Year 7 will apply if we exit in Year 7. Cell H195 (shown in Figure 9.24) contains our measure for the purpose of the data table.
We will build the money multiple data table in the S&U Inputs sheet just below the IRR data table.
Select the S&U Inputs sheet.
Enter the measure (Year 7 Money Multiple), in, say, I31. The formula in cell I31 reads “=Calculations!H195”.
Along row 31 (the same row as the measure, but to the right-hand side of the measure), enter the values for the exit year driver. And so in cells J31, K31, and L31, type 5, 6, and 7, respectively.
Down column I (the same column as the measure, but below the measure), enter the values for the exit enterprise-value-to-EBITDA multiple. And so in cells I32, I33, and I34, type 7x, 8x, and 9x, respectively.
Select the block of cells that contains the measure (I31) and the values of the two drivers. Thus this selection will involve the block I31:L34.
On the Data tab, in the Data Tools group, click on What-If Analysis, and then click on Data Table.
After clicking on Data Table, the Data Table dialogue box appears.
As in the IRR data table, enter D22 in the Row input cell box.
As in the IRR data table, enter D23 in the Column input cell box.
Click OK and press F9 to recalculate.
The resulting money multiple data table is illustrated in Figure 9.28.
Note: As in the IRR data table, for aesthetic reasons, we have formatted the font in cell I31 to a white color (that is, the same color as the cell’s background color) so that the cell containing the measure is not visible.
Analysis of Data Table Sensitivity
The data table shows that as the exit enterprise-value-to-EBITDA multiple increases from 7 to 9 times, both the IRR and the money multiple increase, which is to be expected. Indeed, increasing the exit enterprise-value-to-EBITDA multiple will increase the enterprise value, which (given the fixed term loan A and loan stock) will result in an increased equity value.
As the exit year is delayed, the IRR decreases, which is to be expected. This result reflects the time value of money.
However, as the exit year is delayed, the money multiple increases. In our projections, EBITDA increases over time, and so delaying the exit means achieving a higher EBITDA figure at exit, which results in a higher enterprise value and consequently a higher equity value.
The previous point has important implications for the investor. The results of some investment funds are measured by IRR, in which case, all other things being equal, these funds would prefer realizing an earlier exit from their investments. On the other hand, the results of some funds are measured by money multiple as long as the exit is achieved within a certain time frame (for example, five to seven years). In this case, the fund would have a preference for delaying the exit by a couple of years in order to achieve a higher money multiple.
9.4 CREATING THE OUTPUTS SHEET
Now that we have completed all the necessary calculations, we are ready to bring together the key results from our calculations into the Output sheet.
The key outputs are the Profit and Loss, Balance Sheet, Cash Flow, Exit Schedule, Exit IRR & Money Multiple, and Exit Sensitivity Analysis modules.
Copy the Calculations sheet, rename it Outputs, and color it green. This way, the Outputs sheet initially has the same content as the Calculations sheet. We will then delete the elements of the Calculations sheet that are not going to be included in the Outputs sheet.
Since the main outputs are the Profit and Loss, Balance Sheet, Cash Flow, Exit Schedule, Exit IRR & Money Multiple, and Exit Sensitivity Analysis modules, we will:
1. Link the Profit and Loss, Balance Sheet, Cash Flow, Exit Schedule, and Exit IRR & Money Multiple modules in the Outputs sheet to the corresponding Profit and Loss, Balance Sheet, Cash Flow, Exit Schedule, and Exit IRR & Money Multiple modules in the Calculations sheet.
2. Link the Exit Sensitivity Analysis module in the Outputs sheet to the corresponding Exit Sensitivity Analysis module in the S&U Inputs sheet.
In Chapter 8, we learned how to link one sheet (the Outputs sheet) to another (the Inputs & Workings Sheet). We can use the same technique to complete steps 1 and 2 here. The result is an Outputs sheet that is exactly the same as the Calculations sheet contentwise except that the Outputs sheet takes its values from the Calculations sheet.
Select rows 4 to 28 (the Assumptions block) and delete the entire block. This brings the Profit and Loss module right to the top at row 4.
Delete the Debt, Fixed Assets and Tax modules. This brings the Cash Flow module to immediately below the balance sheet, from row 67.
Let us make a few minor adjustments to make the Outputs sheet more user-friendly.
We rename the “Workings” blue bar on row 4 as “Outputs.”
We also include a sales growth line and an EBITDA margin line in the Profit and Loss section of the Outputs sheet. Both the sales growth and EBITDA margin lines in the Outputs sheet can be linked to the corresponding line in the Calculations.
For sales growth, we write in cell J8 the formula “=Calculations !J9”.
Similarly, for EBITDA margin, we write in cell J10 the formula “=Calculations!J10”.
Copy cell J8 and paste it into the block J8:S8.
Copy cell J10 and paste it into the block J10:S10.
Remove the gray color from any cell shown as gray.
Also, to create a copy of the Exit Sensitivity Analysis in the Outputs sheet, do as follows:
Copy block I21:M35 in the S&U Inputs sheet and paste it into K133 in the Outputs sheet.
To link the IRR and money multiple data tables in the Outputs sheet to the S&U Inputs sheet:
Write in cell K136 the formula “=‘S&U Inputs’!I24”.
Copy cell K136 and paste special the formula into the block K136:N139.
Paste special the formula into the block K143:N146.
The resulting Outputs sheet is shown in the following figures. First we show the Profit and Loss module in Figure 9.29.
Next, we show the Balance Sheet module in Figures 9.30 and 9.31.
Then we show the Cash Flow module in Figure 9.32.
Then we show the exit waterfall in Figure 9.33.
