Gain Bottom Line Visibility With an Interactive Model

When a business is forced to deal with rapidly changing conditions or an uncertain future, an “Interactive Financial Model” is an invaluable tool.
​
Think of it as a projection for your business, driven by key assumptions that can be easily changed. Use the power of Excel to recalculate a projection of future results based not only on historical trends but your best estimate of what will happen.

What is the value of an Interactive Financial Model?

• Serves as an early warning system providing more time to react to changing conditions resulting in better decisions.
• The model becomes a vehicle to communicate both internally and externally where the company is headed, resulting in improved credibility and co-operation.
• When compared with actual results, this detail plan provides a way to laser in and make adjustments where needed resulting in improved bottom line results.
• Use as a tool for managing cash needs resulting in better relations with bankers and vendors.
• By modifying the assumptions, the plan can be used to perform if / than analysis allowing management to choose the best scenario.
• The tool can be used to predict personnel and hiring levels allowing a business to choose the best alternative for all involved.

Key components of a financial model:

• Summary of Assumptions
• Projected Balance Sheet
• Projected Income Statement
• Projected Cash-flow and Borrowing Needs
• Resulting Operating Statistics and Indicators

Establishing solid assumptions is a key to building a credible financial model.
The more effort you put into your assumptions the more valuable your model will be.

• Talk with key customers and include their expected purchases into your assumptions.
• Identify the “drivers” of your business and hone in on how they will perform moving forward. For example, if energy is a key cost, base your projected cost on the power company’s projection of future rates and your utilization metrics.
• Develop productivity metrics to show how labor costs will be impacted by changes in volume.

Other assumptions required will vary by business but should include:

• Gross Revenues – broken down by product line, customer type or other natural grouping
• Seasonal flow of revenue throughout the year
• Expected volume changes
• Price changes during the year
• Relationship between material, wages & revenues
• Anticipated wage rate changes
• Other costs that vary with revenues: commissions, shipping, shop overhead, etc.
• Cost that vary with wages: employee benefits, payroll taxes, training, and the like.
• Pay particular attention to changes in benefit coverage, rates, and employee contributions.
• Operating expenses: rent, utilities, depreciation, supplies, professional fees, insurance, etc.
• Interest Rates.
• Payroll tax rates.
• Income tax rates.
• Timing of receivable collections.
• Timing of payments on accounts payable.
• Increase / decrease in inventory levels.
• Debt payments.
• Fixed asset purchases.

Laying this all out in an excel worksheet so that assumptions can be changed to show what happens under different scenarios will make this a powerful tool for managing the business and reacting to changing conditions.  For an example of an Interactive Financial Model in Excel format click here.

A key part of the financial plan process is to report actual results against that plan and to investigate deviations to determine if corrective action is required. It is beneficial to periodically update the plan to account for real-life changes in assumptions and business conditions.

Consider saving a copy and updating it with actual results. Then reforecast the balance of the year so you always know what your year-end results will look like.
​
Creating a model from scratch may seem like an overwhelming task. To begin to gain the value, start with a few basic assumptions and use historic average information for the rest. Each month as you review, you can replace some of the average information with more refined assumptions.