Pro-forma Financial Statements

MainMenu MAIN MENU Proforma Page 1 Copyright 2000, Irwin/McGraw-Hill. This computer program is protected by copyright law.  Instructions for Using PROFORMA PROFORMA is intended as a companion to ANALYSIS FOR FINANCIAL MANAGEMENT, 6th edition, by Robert C. Higgins. Some basic familiarity with Microsoft Excel will be helpful, but not necessary, to use this program. PROFORMA converts user-supplied information and assumptions about a company into pro forma financial forecasts for as many as five years into the future. It also provides a ratio analysis and a sustainable growth analysis of the results. Additional "what-if" analysis can be easily performed. The program HISTORY, also in this workbook, offers a convenient way to develop the user-supplied information and assumptions required by PROFORMA. PROFORMA output is in the form of four, one-page tables: Table Contents A Pro forma assumptions B Pro forma balance sheets 1-5 years C Pro forma income statements 1-5 years D Pro forma ratio and sustainable growth analysis INSTRUCTIONS FOR USE All of the worksheets in PROFORMA are tied to the Main Menu, from which you selected the Instructions button. To launch the other worksheets, simply choose the appropriate button from 3/1/2008 Analysis for Financial Management, 5th Ed. by Robert C. Higgins Published by Irwin McGraw-Hill the Main Menu. You can return to the Main Menu at any time by selecting the Main Menu button from any worksheet. You may want to begin by printing a copy of these instructions. To do so, use the regular Print command in your software. You can print any of the Output Tables this way. Enter Information and Forecasts 1. Leave the instructions and return to the Main Menu by clicking on the Main Menu button at the top of this page. At the Main Menu, select Input Data and fill in the fields in the dialog box. When you have finished, you will see Table A. 2. Table A is where you need to enter your assumptions and forecasts. Fill in the fields in light blue. You are not required to enter information in every field if the field is not relevant. You may also customize any label in blue simply by typing over the field name. 3. When you have completed the information in Table A, click on the Analyze button. This will return you to the Main Menu. 4. To view the output, click on the View Tables button and select the Table you wish to view. In order to view another table, simply return to the Main Menu and repeat this process. What If Analysis If you are interested in doing a "what if" analysis of your initial pro forma forecast, you can do so by changing your assumptions in Table A. Use the View Tables button to return to Table A. In Table A, simply change whichever assumptions you wish and click on the Analyze button. For example, if you originally entered a sales growth of 10 percent and wonder what the forecast would look like at a 15 percent growth rate, change the 10 to 15 and click on the Analyze button. Clear The Clear button on the Main Menu clears all the tables so you can analyze another company. If you want to save your work, use the Save As command in your software and rename this program. Discussion of Output 1. The red field labeled "Required External Financing" at the bottom of the Balance Sheets is the amount of external financing calculated by the computer that is implied by your forecasts. A positive number in the field implies a need for cash. The ensuing calculations assume the financing requirements are funded with short-term debt and assess interest expense accordingly. A negative financing requirement is assumed to be invested in securities and earns interest. Interest income and expense, and the ratios in Table D, are consistent with these assumptions. Once you know the sign and magnitude of the external financing requirement, you can fine-tune your forecast by assuming all or a portion of the financing will be funded with long-term debt or equity; or that a negative financing requirement will be invested in securities. To enter these adjustments, return to Table A using the View Output button at the Main Menu. Make your changes at the bottom of Table A in the fields labeled for fine tuning. Remember that these adjustments are INCREMENTAL to the forecast numbers. Click the Analyze button when you are finished. 2. The forecast assumes that dividends will be paid only if income available for common is positive. 3. The ratios in Table D are the same as those in Table F of HISTORY. See the instructions to History for detailed definitions. 3/1/2008 Analysis for Financial Management, 5th Ed. by Robert C. Higgins Published by Irwin McGraw-Hill Table A. Pro Forma Assumptions Actuals: Net sales Net plant Long-term debt Owner's equity Growth in sales (%) Gross margin (%) Op. expenses/sales (%) Non-op income (expense) Int. rate on excess cash (%) Interest rate on debt (%) Income tax rate (%) Ex. gains (loss) net tax Other gains (losses) Preferred dividends Dividend payout ratio (%) Minimum cash balance Collection period (days) Inventory turnover (x) Other current assets Growth in fixed assets (%) Other investments Other long-term assets Payables period (days) Other short-term debt Accrued taxes Other accruals Current portion l-t debt Deferred taxes Other deferrals Minority interests Other long-term liabilities Preferred stock Once FINANCING has been estimated, use items below for fine-tuning… Incr. (decr.) in securities Incr. (decr.) in long-term debt Incr. (decr.) in equity 3/1/2008 Analysis for Financial Management, 6th Ed. by Robert C. Higgins Published by Irwin/McGraw-Hill Table B. Pro Forma Balance Sheets Dell $ in Millions December 31 ASSETS Cash -$ -$ -$ -$ -$ Securities -----Accounts receivable -----Inventory #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Other current assets -----Total current assets #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Net property, plant, etc. -----Other investments -----Other long-term assets -----Total Assets #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! LIABILITIES AND SHAREHOLDERS' EQUITY Short term debt Ext'l Fin Ext'l Fin Ext'l Fin Ext'l Fin Ext'l Fin 3/1/2008 Analysis for Financial Management, 6th Ed. by Robert C. Higgins Published by Irwin/McGraw-Hill Other short-term debt -----Accounts payable -----Accrued taxes -----Other accruals -----Current portion l-t. debt -----Total current liabilities -----L-t. debt and capital leases -----Deferred taxes -----Other deferrals -----Minority interests -----Other long-term liabilities -----Total liabilities -----Preferred stock -----Owners' equity #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Total equity #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Total liabilities & equity #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! External Financing Req'd #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Table C. Pro Forma Income Statements Dell $ in Millions December 31 Net sales -$ -$ -$ -$ -$ Cost of goods sold -----Gross profit -----Operating expenses -----Operating income -----Non-op. income (expense) -----Interest income #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Interest expense #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Income before tax #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Provision for tax #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 3/1/2008 Analysis for Financial Management, 6th Ed. by Robert C. Higgins Published by Irwin/McGraw-Hill Net income #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Ex. gains (loss) net tax -----Other gains (losses) -----Preferred dividends -----Available for common #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Common dividends #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Added to retained earnings #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Table D. Pro Forma Ratio and Sustainable Growth Analysis Dell PROFITABILITY RATIOS: Return on equity (%) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Return on assets (%) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Return on inv. captial (%) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Profit margin (%) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Gross margin (%) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! LEVERAGE & LIQUIDITY RATIOS: Assets to equity (%) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Total liabilities to assets (%) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Total liabilities to equity (%) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Long term debt to equity (%) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Times interest earned (x) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Times burden covered (x) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Current ratio (x) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Acid test (x) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! TURNOVER-CONTROL RATIOS: 3/1/2008 Analysis for Financial Management, 6th Ed. by Robert C. Higgins Published by Irwin/McGraw-Hill Asset turnover (x) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Inventory turnover (x) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Collection period (days) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Days sales in cash (days) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Payables period (days) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Fixed asset turnover (x) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Pro Forma Sustainable Growth Analysis 1995 1996 1997 REQUIRED RATIOS: Profit margin (P) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Retention ratio (R) 100.0% 100.0% 100.0% 100.0% 100.0% Asset turnover (A) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Financial leverage# (T) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Sustainable growth rate (g*) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Actual growth rate (g) 0.0% 0.0% 0.0% 0.0% 0.0% #Defined as assets divided by beginning-of-period equity DialogData Month Date Analysis Years January 1 1 Februrary 2 2 March 3 3 April 4 4 May 5 5 June 6 July 7 August 8 September 9 October 10 November 11 December 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Page 8ProformaDialog Input Proforma Data OK Cancel Dell Company Name $ in Millions Currency, Scale (e.g. $ in Millions) Fiscal Year End January Februrary 12 1995 First Analysis Year 1 Number of Years to Analyze 12 Page 9TableViewer PROFORMA Tables OK Cancel Table A: Assumptions Table B: Balance Sheets Table C: Income Statements Table D: Ratio and Sustainable Growth Analysis Select The Table You Wish to View Page 10