PlanWare: How to ...

Prepare Financial Projections

Computer-Based Modeling

A personal computer with appropriate software can help prepare financial projections. A computer-based model reduces the tedium of carrying out numerous repetitive calculations; simplifies the alteration of assumptions; and improves the presentation of results.

When using a computer, a manager or planner can utilize a spreadsheet to build a model from first principles. While the development of a spreadsheet model of a company's profit & loss account should be well within the capabilities of many managers, the development of an integrated and comprehensive financial planner is an infinitely more challenging, time consuming and difficult task. A manager must know when to draw the line between using a spreadsheet to plan and becoming a spreadsheet programmer.

As an alternative to a DIY model, a manager or entrepreneur can purchase a ready-made model which can be either (a) loaded as a template into a spreadsheet or (b) run as a stand-alone package.

The main advantage of building a bespoke model is that it can be fine- tuned to meet very specialized requirements. However, the drawback is that building a comprehensive, error-free and user-friendly financial model could require hundreds of hours of development and testing along with considerable programming and financial expertise. This work inevitably distracts from the real task of planning the business and begs the question as to why managers, entrepreneurs, advisers etc. should reinvent the wheel whenever a set of financial projections are required.

Modeling in Action

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 software 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, Invest-Tech'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, the table below shows the results of using one of Invest-Tech's models to undertake a "what-if" analysis where sales volumes and prices have been increased by a fixed percentage.

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.

Additional Help

Invest-Tech offers a range of financial planning packages - Exl-Plan. These incorporate extensive help facilities and are suitable for startup or established businesses ranging in turnover from $50,000 up to $99 million per annum. They can be used by planners, accountants, consultants etc. as well as by managers or business people with little or no previous experience of computers, finance or business planning.

You can also use our free On-Line Financial Planner to produce 'first-cut' five-year projections.

Also, have a look at Cashflow Plan, our range of fully-integrated cashflow planners which generate projections for 12 months ahead and incorporate a roll-forward facility to simplify the updating of projections.

Top of Page

Top of Page