Pro Forma Income Statement Template - PDF

							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.