Programming a Firm Value Calculator on Your PDA

PDAs (personal digital assistants) have capabilities way beyond scheduling, e-mail, Internet access, and phone calls. These essential pieces of business equipment can now perform scaled-down spreadsheet functions that can be greatly enhanced with templates to perform analysis quickly when a few inputs are entered. Although Microsoft Office Excel’s functions are limited in the PDA environment, the template described below only requires the PV() function.

The process explained here adapts the work of Arnold and James (2000) and Arnold, North, and Wiggins (2005), excluding the mathematical proofs, in order to program a pro forma analysis template for firm value into a PDA (we use a Hewlett-Packard iPAQ with Windows Mobile). The template technique assumes that the pro forma analysis is sales driven; in other words, that many of the income statement and balance sheet inputs grow at the same rate as sales or are proportionate to sales. It also assumes that sales will experience an initial period of abnormal growth followed by a period of steady growth, and that firm value is the sum of discounted FCFs (free cash flows). That is, FCF = (sales – operating expenses) × (1 – tax rate) + depreciation × tax rate – change in net working capital – change in fixed assets. Net working capital = current assets – current liabilities.

PDA Firm Value Calculator
| Download the Template |

 

Many of the inputs will grow at the same rate as sales or be considered a fixed proportion of sales: operating expenses (56% of sales), current assets (15% of sales), current liabilities (10% of sales), and fixed assets (135% of sales). Depreciation will be straight-line for 33 periods and will be based on the average of the fixed assets from the current period and the most recent previous period. The remaining inputs are the initial value for sales ($6,250); the initial growth rate for sales (49%); the length of the initial growth (5 periods); perpetual growth (i.e., the growth rate for sales after the initial growth period concludes, 7%); the tax rate (52.5%); and a discount rate (20%). Lines 1–11 of the table illustrate the inputs in a spreadsheet format.

The cash flows in the initial growth period will be discounted using a growing annuity equation, while cash flows in the second period will be discounted using a growing perpetuity. To simplify the calculations, adjust the discount rate by the associated growth rates. For the annuity portion, calculate the adjusted discount rate as {(1 + 20%) / (1 + 49%)} – 1 = -19.46%. For the perpetuity portion, calculate the adjusted discount rate as {(1 + 20%) / (1 + 7%)} – 1 = 12.15%.

Because depreciation is calculated using the average of fixed assets, an adjustment factor is necessary to change the annuity and perpetuity portions into discounted averages. For the annuity portion, calculate the average annuity factor as {1 + 1 / (1 + 49%)} / 2 = 0.8356. For the perpetuity portion, calculate the average perpetuity factor as {1 + 1 / (1 + 7%)} / 2 = 0.9673.

Because net working capital and fixed assets require the change between two periods, a second adjustment factor is needed to change the annuity and perpetuity portions into discounted one-period differences. For the annuity portion, calculate the difference annuity factor as {1 – 1 / (1 + 49%)} = 0.3289. For the perpetuity portion, calculate the difference perpetuity factor as {1 – 1 / (1 + 7%)} = 0.0654.

Lines 13–20 of the table illustrate these adjustments and provide the value for the discounted sales for the annuity and perpetuity portions of the cash flow using the PV() function.

All the discounting for the firm value calculator is now complete. The last set of programming simply calculates the sum of the discounted FCFs in a four-part process. Part 1 = (sales – operating expenses) × (1 – tax rate). Part 2 = depreciation × tax rate. Part 3 = change in net working capital. And part 4 = change in fixed assets. The discounted FCFs equal part 1 plus part 2 less part 3 less part 4 and are equivalent to the firm value (see lines 22–26 of the table).

The firm value calculator is equivalent to a full-scale pro forma analysis for finding the value of a firm. Once it is programmed into the PDA, changing any one of the eleven inputs provides a new analysis. On a PDA, the tiny template takes up little memory (12 KB); on a laptop or desktop computer, the template can be made more beneficial with the addition of data tables or Monte Carlo software for scenario analysis.

REFERENCES

Arnold, Tom, and Jerry James. March–April 2000. “Finding Firm Value without a Pro Forma Analysis.” Financial Analysts Journal, vol. 56, no. 2. 77–84.

Arnold, Tom, David North, and Roy A. Wiggins III. Fall 2005. “Improving Pro Forma Analysis through Better Terminal Value Estimates.” Journal of Financial Education, vol. 31. 77–95.

–Tom Arnold, PhD, CFA, and David S. North, PhD, are associate professors of finance at the Robins School of Business at the University of Richmond.