--------------------------------------------------------------------------------
3. How Financial Planning Models Work
A computer-based financial model is the electronic equivalent of a very large sheet of ruled paper which, depending on circumstances, could be 10 to 20 square feet in area. The computer's screen serves as a small window on this electronic sheet which usually displays variables and values along rows and time periods in columns.
A model utilizes assumptions for sales volumes, prices, operating costs, funding etc., to produce projected balance sheets, profit & loss accounts and cashflow statements. Typically, it makes monthly projections for the first year and less detailed projections for the following years. For example, PlanWare's models will produce projections (P&Ls, cashflows, balance sheets) for the first year on a monthly basis and for the following two years on a quarterly basis. As all the components of a model are linked by formulae, a change to any assumption in any period results in appropriate adjustments to profits, cashflows etc. throughout the model for the remaining months, quarters and years.
Once initial assumptions have been entered, they can be readily altered to evaluate alternative scenarios. For example, a model could be used to explore the extent to which future sales can be increased while holding borrowings within predetermined limits; to assess the effects of varying selling prices and/or volumes on net profits; or to determine the optimum level and mix of future funding for a business.
As a practical example, Figure 2 below shows the results of using one of PlanWare's models to undertake a "what-if" analysis where sales volumes and prices have been increased by a fixed percentage.
Figure 2 - Results of Sensitivity Analysis
Scenario: Base Case covering 12 months Sales Volumes increased by 20% Selling Prices increased by 10%
Sales ($000) 553 664 608
Sales (% change) n/a +20 +10
Pre-tax Profits ($000) 28 65 85
Pre-tax Profits (% Change) n/a +132 +204
Net Cashflow ($000) 43 68 93
Net Cashflow (% Change n/a +58 +116
Peak Monthly Loans ($000) 72 65 57
Peak Monthly Loan (% Change) n/a -10 -21
Full-Year Return on Capital (%) 16 30 35
Given a choice between a 20% increase in sales volumes or a 10% increase in selling prices, the model shows that the latter would be a far more attractive option. The results also offer an insight into the underlying cashflows and funding requirements. Our software planners, Exl-Plan and Cashflow Plan, offer extensive facilities and utilities for doing "what-if" sensitivity analyses.
Top of Page
--------------------------------------------------------------------------------
4. Planning to Make Business Plan Projections
For managers of an existing business, or promoters planning a substantial new venture, financial modeling can be an invaluable tool to assist the preparation of a business plan. However, business planning should not be confused with the preparation of financial projections. The former must provide the foundation for the financial projections which can be derived arithmetically by a model. The model and its forecasts should contribute to but never dictate the contents of a written business plan.
Once basic issues relating to markets, sales and operations have been fully researched and considered, a model can be used to produce the financial projections. However, the veracity and usefulness of these projections will be completely determined by the quality and reliability of the underlying assumptions determined outside the model. For example, if sales or cost forecasts are unrealistic or inadequately researched, then the value of a model's output is greatly diminished. An impressive set of financial projections is of little benefit if unsupported by research or only based on speculation or wishful thinking.
Before using a financial model to help plan the future of a business, a manager or entrepreneur should:
Decide at the very outset on the central purpose of the planning exercise (raise funds etc.); the target audience (co-directors, financial institutions etc.); and the time horizon (one year etc.).
Identify and think through all the critical assumptions. Prepare outline projections to confirm their overall direction, examine the critical elements in detail and consider strategic issues relating to sales, profitability, funding etc.
Check that all key assumptions (e.g. sales forecasts) and data (e.g. opening balance sheet and any prior-year financial results) are to hand, and have been adequately researched.
There is a special facility, called Quik-Plan, within PlanWare's Exl-Plan and Cashflow Plan financial planning packages which is ideal for preparing 'first-cut' projections which can be progressively refined and expanded.
Recognize the danger of presenting too much detail or too many reports. Most senior managers, investors and financiers seek simple financial statements which have been based on detailed analysis and realistic assumptions.