Use Excel in the due diligence process - Help and How-to

Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.

Printer-Friendly Version

Use Excel in the due diligence process

Applies to: Microsoft Office Excel 2003

When faced with a merger or acquisition, your company likely will be required to supply financial and operational information to its potential buyer during the due diligence process. Controllers and other financial officers of a company must identify, collect, and review a variety of current and historical company data, such as financial statements, assets, and customer contracts.

To help you conduct quality assurance on important due diligence items, use the statistical analysis features in Microsoft Office Excel 2003. These handy tools will help you streamline your collection and analysis tasks. You'll also be able to identify and solve issues before they become deal breakers with your potential buyer.

In the example used here, see how to use Excel 2003 to audit your company's customer contracts to make sure that they contain the appropriate assignment clause. Without the proper assignment clause, critical customer contracts may not be able to be transferred to the potential buyer.

Because most companies have hundreds of customer contracts, checking each one to see whether it contains the assignment clause would be extremely time-consuming, given the typical time constraints of the due diligence process. But with Excel, it's easy to evaluate a sample of the contracts.

Choose your sample

Copy and paste your customer list onto an Excel worksheet. In the example used here, there are 500 customer contracts. A partial customer list appears as follows.

Excel spreadsheet listing customer contract IDs

Note The statistical analysis features in Excel will not work with non-numeric data, so you will need to use customer or contract identification numbers instead of customer names on your worksheet.

Now you can use the Sampling analysis tool in Excel to select a random sample of customer IDs for review.

Show How?

Choose a number of customer contracts to search for the assignment clause. In this example, you have enough time and resources to review 100 of the 500 total contracts.
On the menu bar, click Data, and then select Sort. A random sampling of 100 contracts from your worksheet is generated.
In the Data Analysis dialog box, select Sampling, and then click OK.
In the Sampling dialog box, place the insertion point in the Input Range box, and then select the 500 customer contracts. In this example, the range of cells is A2 through A501. Make sure that the Labels check box remains unselected.
Under Sampling Method, select Random, place the insertion point in the Number of Samples box, and then type 125.
Note Excel calculates a list that contains some duplicate customer ID numbers. A sample number of 125 instead of 100 was used in this example to generate a list of a minimum of 100 unique random customer ID numbers.
Under Output options, select Output Range, place the insertion point in the Output Range box, and then enter the range D2:D126. The sample customer ID numbers are placed in column D on the contract worksheet.

Sort and review the sample

On the customer worksheet, select cells C1 through C126.
On the Data menu, click Sort.
In the Sort Warning dialog box, select Continue with Current Selection, and then click Sort.
In the Sort dialog box, in the list under Sort by, select Sample Customers, select Ascending, and then click OK. The customer ID numbers are sorted in numerical order.
If there are any redundant customer ID numbers to delete, right-click a duplicate ID number and then click Delete. In the Delete box, select Shift cells up, and then click OK. If the list of sample customer ID numbers extends beyond 100 after you delete the duplicate ID numbers, delete the extra customer ID numbers from the column.
Review each customer ID number in the sample list to identify which customer contracts contain the appropriate assignment clause. Customer ID numbers in column D that are next to a Yes indicate customer contracts that contain the assignment clause; the ID numbers that are next to a No indicate customer contracts that do not contain the assignment clause.

To calculate the percentage of sample customer contracts that contain the assignment clause, use the COUNTIF function.

Show How?

Place your insertion point in cell F104. On the Insert menu, click Function.
In the Insert Function dialog box, enter COUNTIF in the Search for a function box, and then click Go. In the Select a function box, select COUNTIF, and then click OK.
In the Function Arguments dialog box, in the Range box, type E2:E101. In the Criteria box, type Yes, and then click OK.

In the example used here, 92 out of 100 — or 92% of the sample contracts — contain the assignment clause.

Determine confidence level and interpret results

Choose a percentage of contracts that must contain the assignment clause. In this example, 90% was chosen. That is, it's assumed that the potential buyer will not cancel the deal if at least 90% of the company's contracts contain the appropriate assignment clause.

Now use the CONFIDENCE function to calculate the lower limit of the confidence interval for the percentage of contracts containing the assignment clause.

Show How?

Place your insertion point in cell E105. On the Insert menu, click Function.
In the Insert Function dialog box, enter CONFIDENCE in the Search for a function box, and then click Go. In the Select a function box, select CONFIDENCE, and then click OK.
In the Function Arguments dialog box, fill in the following parameters:
- Alpha is the significance level used to compute the confidence level. In this example, a significance level of 0.10 is used.
  How?
  For a two-sided confidence interval, the confidence level equals 100 × (1 – alpha)%. In other words, an alpha of 0.05 indicates a 95% confidence level. In this example, however, you are concerned only with the lower limit of the confidence level, so a significance level twice that of the two-sided confidence interval is used. So, for this example, an alpha of 0.10 is used.
- Standard_dev is the population standard deviation for the data range. The following sample standard deviation formula is used:
  where p equals the fraction of the sample contracts that contain the assignment clause. In this example, p = 0.92.
- Size is the sample size. In this example, the sample size is 100.
  Note This calculation assumes that the sample size is sufficiently large so that the normal distribution for calculating the confidence level may be used. This is generally acceptable for a sample size greater than 30.
Click OK.

In this example, the result from the CONFIDENCE function is 0.0446. To identify the lower limit of the confidence interval, subtract this number from the percentage of contracts (92%) containing the assignment clause:

0.92 – 0.0446 = 0.8754

This result can be stated as, "We are 95% confident, based on our sample, that at least 87.54% of our company contracts contain the assignment clause."

Earlier, it was decided that 90% of contracts must have the assignment clause — otherwise, the potential buyer may lose interest in the deal. Because 87.54% is less than 90%, the company's financial officers will need to review the company contracts and probably adjust any projected revenue stream that may go with the potential buyer. In addition, they should probably consider asking some of their customers to sign new contracts that contain an appropriate assignment clause.

Excel 2003 has powerful statistical functions that can aid you in the due diligence process by saving time and focusing your analyses on critical areas that are important to the potential buyer, as well as to your company's ongoing operations if the deal falls through.

Using Excel features such as the Sampling analysis tool and the CONFIDENCE function can generate detailed statistical analyses without requiring you to perform complex mathematical calculations.

Get Office 2007