Pro Forma Income Statement Template - PDF
Pro Forma Income Statement Template document sample
Shared by: mdv94274
APPENDIX Appendix 6A: Using the Pro Forma Spreadsheet • 1 6A Using the Pro Forma Spreadsheet The spreadsheet in this appendix can be downloaded (Interactive Financial Statement Tem- plate.xls in Templates folder) and used in either of two ways. First, it can be used to measure the cash shortfall for which financing is required or the surpluses that can be distributed to investors. Second, it can be used to reflect the effects of financing and disbursement decisions. General Steps for Completing the Pro Forma Statements A period length (month, quarter, year, etc.) should be selected that is appropriate for the purpose of the analysis. Start the projections with the Sales Forecast (lines 3 and 6) and beginning balances (first column) of accounts in the balance sheet. Cost of Goods Sold (line 7) often can be estimated as a percentage of sales, possibly based on industry norms for ventures of similar size or engineering studies of manufac- turing cost. Operating Expense items (lines 9 through 12) can include fixed and variable compo- nents, and often can be estimated in a manner similar to Cost of Goods Sold. Deprecia- tion Expense (line 11) is determined by previous asset acquisitions and accounting and tax policies. Interest Expense (line 15) can be estimated based on the balance of interest-bearing debt outstanding at the end of the prior period. If the period is long, it may be necessary to consider changes in the level of debt during the period. Interest Revenue (line 16) can be estimated in a manner similar to Interest Expense, but for interest-earning assets. Income Tax (line 18) can be estimated using the statutory tax rate and adjusting for any tax loss carry forwards. Operating Cash Flow (line 24) is determined by adding noncash expenses back to Net Income (line 19). Steps for Estimating the Cash Shortfall or Surplus Determine desired or expected levels of Current Assets except for Cash (line 38), and of Current Liabilities except for Current Portion of Long-term Debt (line 49, which is deter- mined by the contractual provisions of financing decisions made in previous periods), and Notes Payable (line 46, which may be affected by a new financing decision), and record in the Balance Sheet. Thus, for example, the ending balance of Accounts Payable (line 45) can sometimes be estimated based on industry norms of the ratio of Accounts Integrated Financial Statement Template Pro Forma Financial Statements 11 Beginning Period 1 Period 2 Period 3 Period 4 Period 5 ETC. 12 13 Sales Forecast 14 15 Income Statement 16 Sales Revenue 17 less: Cost of Goods Sold 18 GROSS PROFIT 0 0 0 0 0 19 less: Selling Expenses 10 less: General and Administrative Expenses 11 less: Depreciation Expense 12 less: Other Operating Expenses 13 Total Operating Expenses 0 0 0 0 0 14 OPERATING PROFIT 0 0 0 0 0 15 less: Interest and Other Expenses 16 plus: Interest and Other Revenues 17 PRE-TAX INCOME 0 0 0 0 0 18 Income Tax 19 NET INCOME 0 0 0 0 0 20 21 Cash Flow Statement 22 Net Income 23 Depreciation Expense 24 OPERATING CASH FLOW 0 0 0 0 0 25 plus: Increase in Accounts Payable 26 plus: Increase in Other Payables 27 less: Increases in Accounts Receivable 28 less: Increase in Inventory 29 OPERATING SOURCES (USES) OF CASH 0 0 0 0 0 30 plus: Net Cash from Financing Activities 31 less: Net Investment Outlays 32 CHANGE IN CASH 0 0 0 0 0 33 plus: Beginning Cash 34 ENDING CASH BALANCE 0 0 0 0 0 35 36 Balance Sheet 37 ASSETS 38 Cash and Equivalents 39 Accounts Receivable 40 Inventory 41 CURRENT ASSETS 0 0 0 0 0 42 Net Fixed Assets 43 TOTAL ASSETS 0 0 0 0 0 44 LIABILITIES 45 Accounts Payable 46 Notes Payable 47 Wages Payable 48 Taxes Payable 49 Current Portion of Long-Term Debt 50 CURRENT LIABILITIES 0 0 0 0 0 51 Long-Term Debt 52 TOTAL LIABILITIES 0 0 0 0 0 53 Common Stock 54 Retained Earnings 55 TOTAL EQUITY 0 0 0 0 0 56 TOTAL LIABILITIES AND EQUITY 0 0 0 0 0 Note: Cells with zeros are computed from other inputs and do not require direct entries. 2 Appendix 6A: Using the Pro Forma Spreadsheet • 3 Payable to Cost of Goods Sold, or a similar ratio. Ending balances of other current ac- counts except Notes Payable, Current Portion of Long-term Debt, and Cash can be esti- mated in similar ways. Record the new balance of Notes Payable, giving effect only to existing financing contracts. Using the Balance Sheet information for the period and for the prior period, determine the changes in each current accounts except for Cash and Current Portion of Long-term Debt, and record the changes in the Cash Flow Statement. Under Net Cash from Financing Activities (line 30), record only the contractually com- mitted repayments of long-term debt and any desired distributions to equity holders. Repayments of Notes Payable are captured by the change in Notes Payable. Under Net Investment Outlays (line 31), determine the desired level of new gross in- vestment in fixed assets and offset with any expected asset sales or other dispositions. This may be based on consideration of the level required to support sales for the period, or it may be based on longer-term considerations. Complete the Cash Flow Statement to determine the Ending Cash Balance (line 34) as- suming no new financing. Record Net Fixed Assets (line 42) in the Balance Sheet as the prior balance, less Depreci- ation Expense and the book value of asset dispositions, and plus the cost of fixed assets acquired. At this point, Notes Payable, Current Portion of Long-term Debt, and Long-term Debt are determined by the contractual provisions of previous financing decisions. The balance of Com- mon Stock (line 53) is unchanged from the prior period, and Retained Earnings (line 54) is computed as Net Income less any dividends desired to be distributed to investors. The cash shortfall or surplus of the venture can be determined by comparing the Ending Cash Balance (line 34) to the desired level of Cash and Equivalents (line 38). The difference is the amount of new financing required. Steps Reflecting Financing Decisions Revise the balance of Cash and Equivalents (line 38) to reflect the desired level of cash. In the event of a cash shortfall, determine the desired sources of additional financing (Notes Payable, Long-term Debt or Equity) and adjust the Balance Sheet accounts ac- cordingly. In the event of a surplus, decide whether to repay debt early or make addi- tional distributions to equity. Depending on the choice, it may be necessary to adjust other accounts related to dividend distributions and interest expense. Revise Net Cash from Financing Activities (line 30) to reflect the financing decisions. Re-compute the Ending Cash Balance (line 34), which should now equal the desired level of Cash and Equivalents in the Balance Sheet. APPENDIX 6B Sources of Information for Forecasting Refer to the Web site for additional information sources, additional information about these sources, and links to electronic databases. Macroeconomic variables: Bureau of the Census: www.census.gov/ Bureau of Economic Analysis: www.bea.doc.gov/ Economic Report of the President US Statistical Abstract Bureau of Labor Statistics: www.bls.gov/ Department of Commerce, Survey of Current Business Firm Data: Standard and Poor’s Stock Reports Standard and Poor’s Compustat Moody’s Industry Review Moody’s Manuals Securities and Exchange Commission: www.sec.gov/ SEC filings: 10-K, 10Q, Proxy statement, 13D, Insider trading Dun & Bradstreet Business Rankings Dun & Bradstreet Million Dollar Directories Dun & Bradstreet Corporate Affiliations Value Line Investment Survey Industry Data: US Industrial Outlook: description of two-digit and three-digit SIC industry groups; industry sales forecasts Census of Manufacturing RMA Annual Statement Studies 4 Appendix 6B: Sources of Information for Forecasting • 5 Almanac of Business and Industrial Financial Ratios Moody’s Industry Review: comparative statistics for major companies within an industry clas- sification Standard & Poor’s Industry Reports Predicast: summary of industry data, employment, capital expenditures, etc. General Management: Anbar Electronic Intelligence: contains reviewed article summaries; literature searches available www.anbar.com Regional Data: US Department of Commerce, Bureau of the Census: State and Metropolitan Area Data Book: data on social, economic, and political subjects USA Counties: includes over 4000 data times for all counties with State and US totals for com- parison. Covers population, housing, labor force, income, and more. International Data: Morgan Guaranty Trust Company, World Financial Markets: data on financial markets in many countries Central Intelligence Agency, CIA World Factbook (annual) International Monetary Fund (IMF): World Economic Outlook (biannual, general economic survey), International Financial Statistics (monthly), Labor Force Statistics (annual); Quarterly Labor Force Statistics; Monthly Statistics of Foreign Trade; Directory of Trade (monthly); Bal- ance of Payments Statistics (monthly) Many sources of data from foreign governments are available in English, such as the Monthly Re- port of the Deutsche Bundesbank (German Central Bank), Economic Statistics Monthly; Bank of Japan Quarterly Review and Economic Trends; and UK Economic Accounts and the Bank of Eng- land Quarterly Review World Bank, World Tables; World Development Report; OECD, National Ac- counts of OECD Countries (annual) United Nations, Statistical Yearbook.