« Book Review: The Fearful Rise of Markets | Main | Sustainability Focus Can Drive Profits for Food and Beverages Companies »

06/01/2010

Programming a Firm Value Calculator on Your PDA


Click to Print This Page

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 |

PDA Firm Value Calculator
 

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.

Related Posts Plugin for WordPress, Blogger...

Comments

The comments to this entry are closed.

UConn-ad

NYSSA Job Center Search Results

To sign up for the jobs feed, click here.


UPCOMING EVENT
MF16

NYSSA Market Forecast™: Investing In Turbulent Times
January 7, 2016

Join NYSSA to enjoy free member events and other benefits. You don't need to be a CFA charterholder to join!


CFA® EXAM PREP

CFA® Level I 4-Day Boot Camp
Midtown

Thursday November 12, 2015
Instructor: O. Nathan Ronen, CFA

CFA® Level II Weekly Review - Session A Monday
Midtown

Monday January 11, 2016
Instructor: O. Nathan Ronen, CFA

CFA® Level III Weekly Review - Session A Wednesday
Midtown

Wednesday January 13, 2016
Instructor: O. Nathan Ronen, CFA

CFA® Level III Weekly Review - Session B Thursday
Thursday January 21, 2016
Instructor: O. Nathan Ronen, CFA

CFA® Level II Weekly Review - Session B Tuesday
Thursday January 26, 2016
Instructor: O. Nathan Ronen, CFA