Typical variable sets

As explained at the Business model analysis - quantitative page, comprehensive, well-designed and well-constructed financial models should be able to easily and repeatedly test for the effects of changes in all variables likely to impact upon the financial performance of the business, project or investee entity.  While variable sets will vary in size and complexity from project to project, and from entity to entity, they will usually include some variation on the following:

• Project period to be modelled - e.g. three years, five years, 10 years (Wingarra BMI models typically cover five years)
• Period segmentation - e.g. monthly, quarterly
• Range of products/services - e.g. 5, 10, 15 separately identifiable product categories
• Target geographic regions - e.g. Australia/NZ, North America, UK, Continental Europe, rest of the world (ROW), etc
• Target market penetration assumptions - unit rollout schedules by product by region by month by year
• Seasonal sales patterns - month-to-month or season-to-season fluctuations if relevant
• Customer churn rates (for subscription- or licence-based products and services)
• Revenue calculation parameters - e.g. sale prices by product by region by year, flat fees Vs user-based fees, etc (There will be huge variations within the revenue calculation module, depending on type of business, product characteristics, etc.)
• Revenue share %ages where distribution is to be via alliance partner relationships
• %age-based maintenance fees - e.g. second and later year fees for software products/services
• Minimum fees (where per-user model applied) by product by region by year
• Complementary revenue streams - e.g. after-sales service offerings, product upgrade options, etc
• Direct costs - %age of turnover or fixed amount by product by region by year
• Other %age-based expenses
• One-off expenses by month incurred
• Salary on-cost rates
• Position descriptions, salaries, months of commencement and yearly uplift factors (where real salary increases are to be budgeted)
• Other expenses, months of commencement and yearly uplift factors (where real expense increases are to be budgeted)
• Sales collection profile (relative to month of sale)
• Production and distribution cost payment profile (relative to month of incurrence)
• Other expense payment profile (relative to month of incurrence)
• Plant and  equipment acquisition schedule by month installed
• Depreciation rates
• Income tax rates by region by year
• Dividend payout ratio - e.g. based on %age of prior year Net Operating Cashflow where positive
• Opening Balance Sheet.

Wingarra BMI's customised financial models generate integrated P&Ls, Balance Sheets, Cashflow Statements and ratio analysis schedules for each period modelled, and are flexible and easy to use. In particular, they are designed so as to make it easy to test changes in any single variable or any combination of variables.

Valuations, equity allocations, etc

In many cases, it is also necessary or useful to calculate notional starting valuations and/or to project notional terminal valuations. Notional starting valuations are necessary, for example, to assess the level of equity interest to be offered to an investor in exchange for a certain level of investment. Notional terminal valuations are required in order to properly assess return on investment over a particular holding period. To accommodate these requirements, the models allow for estimates of the following valuation-specific or investment return-specific variables:

• Notional starting valuation timeframe - e.g. valuation based on projected performance in Year 3, 4 or 5
• PE ratio to be applied to Net Operating Profit after Tax (NOPAT) of relevant year
• NPV discount rate to be applied to this [PE X NOPAT]
• Probability assessment valuation %age (applied to net company cashflows to test primary valuation methodology)
• Selected starting valuation (after taking note of calculated notional valuations under primary and secondary methodologies)
• Equity injection amounts by round
• Month of each equity injection tranche
• Share issue price for each equity injection round
• PE ratio to be applied to NOPAT of final year of investment period for terminal valuation
• NPV discount rate to be applied to investor cashflows
• IRR estimate for investor return.

With these variables in place, the model can be run to perform Discounted Cash Flow (DCF) calculations to provide estimates of project and/or entity and investor returns in the form of Net Present Value (NPV) (at nominated hurdle rates) and Internal Rate of Return (IRR).

Cashflow implications

By approaching the financial modelling task in this way, project managers, business founders or investors work with Wingarra BMI to identify the key drivers and variables likely to affect the relevant business or project. It is only through a thorough understanding of these issues that it is possible to realistically project financial performance. This, in turn, is crucial in gaining a proper understanding of the likely magnitude and timing of project or entity cashflows, including maximum anticipated cash requirements, a critical factor in any business planning process. It is also a crucial step in identifying viable commercialisation strategies.

Examples

Wingarra BMI has assisted many companies with sophisticated financial projection modelling assignments across a range of industries and projects. Please go to the Financial models - customisation examples page which outlines features specific to five actual client models. Further examples of specific-purpose models are provided at the Financial models - renewable energy projects page, the Financial models - life cycle comparative cost (L3C) models page and the Financial models - budgeting models page. The Financial models - general examples page outlines several features common to most models. All of these pages contain numerous screenshots.

Like us to prepare a proposal for a financial modelling assignment for your company or project? Please go to our Request for proposal page for details.