Develop effective financial models to evaluate new products

By John Seasholtz, Seasholtz Consulting

Financial models are designed to evaluate everything from investment performance to borrowing requirements. Models take into consideration the mathematical relationships between financial variables in order to forecast future performance and evaluate different operating scenarios. As such, financial models are valuable tools to support management decision-making.

When developing financial models to evaluate new products or services, financial teams are faced with a number of unique challenges. Some of these challenges include:

  • Management team members may have different ideas about the strategic role of a new product and how its success should be evaluated.
  • There may be little or no historical information, such as sales or cost trends, on which to base a financial model.
  • Many assumptions and calculations must be built into a financial model to account for different operating scenarios. This can make a model unwieldy.
  • Management may be skeptical of the model assumptions and therefore may not embrace related recommendations.

To overcome these challenges, incorporate the following tips for building effective financial models for new products or services.

Begin with the end in mind

First, make sure that you understand the key decisions that the model will be used to support, who will be using the financial model, and what criteria will be used to measure the model's success.

  • Identify the goals     Clearly identify the purpose of the financial model. Often, management will want to use a model to find out the impact of a new product on company profitability. Or management may want to evaluate a product's unique role within an overall product portfolio.
  • Identify and meet with stakeholders     Involve managers from product development, marketing, sales, production, customer service, and other relevant departments. Conduct introductory one-on-one meetings or hold a group kickoff meeting with these stakeholders to explain the modeling project goals and to ask for input about key sales, cost drivers, and risks.
  • Understand the company's lexicon     Every company has a unique set of terms used to communicate and evaluate financial performance. This includes evaluation metrics such as return on investment (ROI), internal rate of return (IRR), net present value (NPV), and market share. Even the definitions of simple terms (such as operating income and gross margin) can be perceived differently, depending on the department or organization. Make sure that key stakeholders are on board with the metric terms and methods that are used in the model.

Build the core structure of the financial model

Financial models for a new product or service are often custom-developed in spreadsheets and take into consideration the unique characteristics of that product's market, competitive situation, and cost structure. Avoid wasting precious time during this time-consuming process by following these guidelines:

  • Outline the model structure     Diagram the steps that the model will take to generate its output, including the interdependencies of model components, mathematical equations used for key components, and where user interaction occurs. Your model outline can be as simple as a sketch on a piece of paper. For more complex outlines, consider using a graphics program, such as Microsoft Office Visio Professional 2003.
  • Weigh costs and benefits of building a complicated model     Don't be tempted to build a model that considers every possible detail and scenario. Keep in mind that forecasting future performance is not an exact science, no matter how detailed the model. And the potential benefits of a financial model may become compromised if it is too onerous to use.
  • Understand product demand     Make sure that you understand customer appetite for your product, given different feature combinations and price points. Incorporate price elasticity of demand into your model to measure how price variations affect customer purchases. Understanding customer demand can be a challenging part of financial modeling, and you may need to work with marketing research or outside consultants to fully address this area.
  • Categorize costs     Break down costs associated with your product into different categories to aid data analysis and forecasting. Examples of cost categories include:
    • Variable costs, including direct materials and direct labor per unit of production.
    • Fixed costs, such as advertising and product manager salaries.
    • Mixed costs, such as communications.
    • Step costs, such as compensation for customer service representatives, each of whom can handle only a certain number of customer inquiries.

You also need to understand how overhead costs from the parent company will be allocated to the product.

Perform scenario analysis

Because financial models attempt to forecast future performance, they often involve many assumptions. Models should allow for testing a number of business situations and their potential impact on  — for example, profitability and market share.

  • Identify key drivers     Choose key variables that the user can change in the model to drive different operating scenarios. Examples of these key variables are unit prices, unit costs, price elasticity of demand, and market share. For example, perhaps your team feels strongly that unit costs and fixed costs will remain relatively stable, so you'll want to focus on the impact that different unit prices have on the product's demand and profitability.
  • Understand the risks     Gain an understanding of the risks involved in a potential product launch. For example, if a key distributor relationship is not established, what impact can that have on sales? If it is a significant impact, that risk should be turned into a model variable and evaluated in different scenarios.
  • Incorporate spreadsheet features     Users can alter inputs to run different scenarios, but they often need to hard-code changes in the model input section and save those scenarios as separate files. Learn how to use spreadsheet features, such as the Scenario Manager and Spinner features in Microsoft Office Excel 2003, to more easily develop and evaluate multiple scenarios on the fly.

Make the model intuitive

Ideally, a financial model will be distributed to stakeholders so that they can use it to test different scenarios and to help in their planning activities. Make sure that your model is easy to use so that those who weren't involved in building it will still be able to derive value from it.

  • Color-code for easy interpretation     Key variables, such as unit prices, unit costs, or required head count can be highlighted by using cell shading or font color. For example, red shading or font colors might be used for cells that need user input. Areas on the worksheet that contain formulas can also be color-coded — perhaps using gray for cells that contain formulas and therefore should not be altered.
  • Safeguard your model     Use the protection feature of the spreadsheet program to prevent user tampering that might have a ripple effect on the rest of the financial model.
  • Use multiple worksheets     Split key sections of the model into separate worksheets (all within the same spreadsheet file). For example, your model might have different worksheets for user input, output, a summary of data elements (for instance, outline of revenues, costs, income, expected returns, and market share), and supporting graphs. Be sure to use logical names for each worksheet tab.
  • Use comments and name cells     Use the comments function to add an explanation to a particular cell where you may have a formula that merits description. For instance, in Excel, you can insert a comment by clicking the Insert menu, clicking Comment, and then entering your text. Be sure to use clear, helpful names for column and row headers and titles for charts, tables, and graphs.
  • Provide helpful graphics     Because the output of the model will likely be used in management presentations, consider including graphs in the model. For instance, a breakeven point graph that illustrates fixed costs, variable costs, total costs, and total revenues over time can help management find out when the product might break even, and the graph can also outline short-term funding requirements.

Wrap it up

When the stakeholders have been engaged, the financial model created, and the scenarios developed, wrap up the project by making your recommendations and handing off the model to the people who will use it.

  • Document model framework and assumptions     Develop a brief report that summarizes how the model was constructed and that includes worksheet descriptions, key formulas, limitations, and a list of stakeholders who participated in developing requirements. This documentation should enable a person who was not involved in the model development to use the model effectively.
  • Test the model     As part of the quality control/testing process for your model, try to break the model by creating scenarios that stretch its capabilities. Sometimes it is best to get another person who wasn't involved in the model development to test the model.
  • Conduct a final presentation and hand off the model     Finally, present the model assumptions, findings, and your recommendations to the stakeholders. Try to resist pressure to make the new product forecast more positive than the most likely scenario indicates. If the numbers don't add up for developing and launching the proposed product, make recommendations, such as lowering unit costs or increasing advertising, that might improve the product's outlook. Finally, hand off the model to the stakeholders who will need to use it.

Financial modeling is critical to determine whether to build and launch a new product or service. To create an effective financial model, make sure that you outline the model structure, identify key drivers, allow for scenario analysis, and make the model intuitive and easy to use.

As you develop your financial model, communicate with stakeholders so that the final model meets all users' needs and expectations. With these basic building blocks in place, you'll ensure that your model will become a valuable tool for business decision-makers.

About the author     John Seasholtz is the founder and principal consultant of Seasholtz Consulting, Inc., in Seattle. Seasholtz Consulting provides strategic and operational planning services to clients in the consumer products, high-tech, financial services, and retail industries.

Applies to:
Excel 2003, Visio 2003