Summary: Steps to Prepare a Quick Pro Forma (Projected) Set of Financial Statements

Pete Dukes

Since we are making a projection about the future, there is no single correct answer. You need to make what you feel are reasonable assumptions and proceed. In Analog Devices, some of those assumptions are given to you. Others you must develop based on projecting from past performance, and your knowledge of the situation. You can and should experiment and change the assumptions, per step 6 below, in order to sensitize your results to the assumptions and estimates.

Steps for preparing a pro forma income statement and balance sheet include:

1. Set up a spreadsheet with the income statement and balance sheet data for past relevant years - the more years the better. This past been partially done for you with the two Analog Devices Inc spreadsheets found at the Dukes Website. I think it is useful to setup a new combined Workbook type of linked spreadsheets, with the income statements as Sheet 1, the Annual balance sheets as Sheet 2, and then ratios, etc, in Sheet 3. Note that the original balance sheet information is quarterly data. In any case you will want to recast this into annual balance sheets.

2. Compute the income statement and balance sheet numbers as a percent of total revenue.

3. Forecast sales for 1999. You are given an expected growth rate in sales, and this is easily translated into a sales prediction for 1999. One important later activity will be to perform sensitivity analysis around this figure. What will happen if sales actually decline, say % percent? What will happen if sales should grow by 25 percent? These are sensitivity analysis activities.

4. Project next year's (1999) income statement numbers as a function of prior period's percentages, modified by your beliefs, trends, more recent information, etc. You should be able to do with within the income statement spreadsheet.

5. Project next year's balance sheet numbers as follows.A. Determine pro forma balance sheet numbers for those items likely to vary directly with

sales - generally items like accounts receivable, inventory, accounts payable, etc. Notes payable, Current portion of debt and capital lease obligation, investments, and other investing or financing activities may not be expected to vary directly with sales, hence more assumptions and estimates are needed.

B. Close your estimated income from the projected income statement to retained earnings. C. Estimate the effect of stock repurchase amounts on treasury stock, given information and

projections made by management. This will be a rough estimate.

1

D. Insert reasonable but admittedly rough estimates for remaining balance sheet items. For example, you are given some guidelines on expected changes in PP&E.

E. The "plug" account is likely to be cash, or if cash dips below some predetermined minimum balance, then the plug becomes "borrowings needed." Essentially you are solving for financing needs or excess cash.

6. Conduct sensitivity analysis as useful by changing key assumptions.

7. Revise pro forma as economic conditions change.

2