Financial Analysis and Forecasting

1. Select a government, not-for-profit, or for-profit organization that you are interested in and that is relevant to your career goals. You must be able to collect at least three years of historical financial statement data for the organization.

2. Research the financial environment for the entity. This might include:

            a. Industry/region-specific economic conditions and outlook

b. Fund-raising environment (nonprofit)

c. Environment for raising capital (for-profit)

d. Public appetite for increased taxes and/or increased spending (gov)

3. Forecast a 3-year pro forma Income Statement, Cash Flow Statement, and Balance Sheet for the entity (Spreadsheets with supporting assumptions). Your forecasts will require at least 3 years of historical data, which you should include in your spreadsheets. Depending on the size and complexity of the organization you study, it may be appropriate to combine certain line items so as to simplify the statements, especially on the cash flow statement.

4. Calculate 3-5 relevant financial ratios for the entity (current and forecast years). The ratios you calculate will depend on the nature of the organization. Explain how the ratios you have chosen are relevant to understanding the financial condition of the organization. If you are uncertain about what ratios to choose, I would suggest using at least one liquidity ratio (current ratio, quick ratio), one solvency ratio (leverage ratio), and one profitability ratio.

5. Identify one possible investment (or policy change) and evaluate its impact. You should evaluate the impact by discounted cash flow analysis (net present value) AND by projecting the short-term impact of the investment on your pro forma financial statements. The NPV analysis should cover the entire lifecycle of the investment. Make sure that your discounted cash flow analysis mirrors the alternative pro formas that include the investment (eg. in the investments section of the cash flow statement).

6. Identify one major risk that the organization faces and assess its implications on financial performance using your pro forma financial statements. Thus, in addition to your baseline pro formas, you will also produce two sets of alternative pro formas: one that reflects an investment, and another that reflects downside risk.

Write up your findings in a paper of no more than 10 pages, double-spaced.  Along with the paper, you should include an Excel file that includes your assumptions, the pro forma statements, the financial ratios, the discounted cash flow analysis, and the alternative statements under the a) investment, and b) risk scenarios.

Forecast Assumptions

To prepare your baseline forecasts, you should rely on the growth rates in the historical data, particularly as it pertains to sales and/or revenue. For example, if revenue was growing by 3 percent per year, then it may be reasonable to conclude that it will continue to grow by 3 percent. To the extent that linear growth trends are not appropriate, you should describe the assumptions you are using. There is no right way to forecast. I will evaluate you on the clarity of your thinking; I am less interested in the exact numbers that you come up with.

As noted in point 3 above, for larger companies with complicated financial statements, it may be appropriate to simply the statements by collapsing and condensing some of the line items. You should use your judgement; if you have any questions, please reach out to me.  If you find that it is necessary to combine large numbers of line items, then you may want to consider choosing a different organization.

As with the baseline forecasts in your pro formas, you will need to make some assumptions to arrive at the cash flows that you will use in your discounted cash flow analysis. Where possible, try to make reference to other investments the organization or its peer organizations have made. But if no such information exists, then just describe your reasoning and state your assumptions clearly. Once again, I am less interested in what numbers you use; I am simply looking for you to demonstrate that you understand how to evaluate an investment.

Structure of Paper

Your paper should proceed in the following fashion.  1) First, you should provide an introduction and/or background section in which you describe the organization and provide relevant background.  You should also elaborate on the financial environment the organization faces and its strategy. 2) Next, you should provide the pro-forma financial statements.  You should concisely summarize the assumptions you used to prepare the statements. Which assumptions were most important? You should also concisely summarize your baseline forecasts.  What are the important take-aways?  What do the pro forma statements reveal about the organization’s financial condition? 3) As part of your discussion of the pro forma financial statement, you should also discuss what financial ratios you have selected, why they are particularly relevant for assessing this organization, and what conclusions you have drawn from the calculations. 4) Next, you should discuss your investment analysis. Why might this investment make sense for the organization? The analysis should include both the discounted cash flow analysis and the effect on the modeled financial statements. You should show these two analyses on separate tabs of your Excel spreadsheet and label them so that it is clear to the reader what information is presented on each tab. What does your analysis reveal about the return on the investment? 5) The analysis of the risk scenario should also be presented on a separate tab on your spreadsheet and labeled appropriately. How large is the downside risk from this particular scenario? 6) In your conclusion, you should briefly re-state the financial condition of the organization and summarize the conclusions from your analyses.    

Your paper should focus on how to interpret the information in the financial statements; it should focus primarily on what the numbers mean, not just where they came from.  Try to avoid just re-stating numbers from the spreadsheets.  The best papers will make it easy for the reader to draw conclusions about the future path of the organization as well as the risk/reward that it faces under alternative scenarios. This paper is not intended to be a research assignment, however to the extent that it is helpful, it is ok to draw on outside research, so long as you properly cite all of the sources you consult.