The FINANCIAL Model

For Use with the FINANCIAL Excel Spreadsheet

This basic financial worksheet is similar in form to the one accompanying the Bay City Electronics case in the New Products Management textbook.  It allows a wide range of flexibility in inputs – for example, you must calculate depreciation separately and enter your figures here – yet at the same time can calculate realistic five-year projections and useful diagnostics to assess the financial viability of a new product proposal.

INPUT

The Market or Category—The market for the new product is defined carefully, and the growth rate assumption is noted. Also, the current total market unit and dollar volumes are recorded.

Product Life—The number of years used in the economic analysis of new products is usually set by company policy, but any particular project may be an exception.

Pricing—Start with the end-user list price, work back through the various trade discounts to get a factory net, then deduct any planned special dis­counts and allowances. The average dollars per unit sold is the price used in worksheet calcu­lations.

Production Costs—Is ­anything unusual being done on this project? Actual anticipated cost goes directly onto the financial worksheet.

Future Special Expenditures—These typically include factory facilities, licens­ing rights, the one‑time introductory marketing cost, up-front payments to suppliers, further R&D on improvements and line extensions, and plant expansions as volume grows.­ These are all investment outflows.

Working Capital—This estimates cash, inventories, and receivables needed to support the sales volumes. How are they to be recovered?

Applicable Overheads—Some firms assign only direct overheads--those caused by the new product (such as an expanded sales force or a new quality function). Other firms believe overheads tend to grow as functions of volume and should be included.

Net Loss on Cannibalized Sales—These are dollar sales lost as the new product steals sales from current products. This is to be deducted from revenue. Some experts believe if we don't do this a competi­tor will, so they omit it.

Future Costs/Revenues of Project Abandonment—Along the way, the project may have accumulated facilities, people, patent rights, invento­ries, and so on. If abandoned now, disposal of these will produce revenue, money that is actually a cost of abandoning the project. In some cases (e.g., expensive disposal of radioac­tive chemicals), there may be a revenue of going ahead.

Tax Credits—Federal or state incentives for activity in the public interest.

Applicable Depreciation Rate—Policy question, set by management.

Federal and State Income Tax Rate—Company figure, provided.

Required Rate of Return—This tells us the cash flow discount rate to be used, and can be complex and political. Theoretically, the figure to use is the weighted average cost of capi­tal, including the three sources of capital--debt, preferred stock, and retained earnings. Often is simply the firm's current borrowing rate.  May be the rate of earnings from current opera­tions.­­

Basic Sales and Cost Forecasts—The primary data inputs: the number of units to be sold, the direct produc­tion cost per unit, and the total marketing expenditures.
           

OUTPUT

Net Present Value (NPV)—The sum of the discounted cash flows over the life of the product. 

FINANCIAL Exercise

Financial analysis of new products at Bay City Electronics had always been rather informal. Bill Roberts, who founded the firm in 1970, knew residential electronics because he had worked for almost seven years for another firm specializing in home security systems. But, he had never been trained in financial analysis. In fact, all he knew was what the bank had asked for every time he went to discuss his line of credit.

Bay City had about 45 full‑time employees (plus a seasonal factory work force) and did in the neighborhood of $18 million in sales. His products all related to home security and were sold by his sales manager, who worked with a group of manufacturers' reps, who in turn called on wholesalers, hardware and department store chains, and other large retailer. He did some consumer advertising, but not much.

Bill was inventive, however, and had built the business primarily by coming up with new techniques. His latest device was a remote­-controlled electronic closure for any door in the home. The closure was effected by a special ringing of the telephone: for example, if a user wanted to leave a back door open until 9:00 p.m. it was simple to call the house at 9:00 and wait for 10 rings, after which the electronic device would switch the door to a locked position. A similar call would reopen the door.

The bank liked the idea but wanted Bill to do a better job of financial analysis.  Based on his understanding of this market, Bill filled out the FINANCIAL worksheet as appears at the end of the exercise. To date, Bay City had spent $85,000 in expense money for supplies and labor developing the closure and had invested $15,000 in a machine (asset). If the company decided to go ahead, it would have to invest $50,000 more in a new facility, continue R&D to validate and improve the product, and--if things went according to expectations--invest another $45,000 in year 3 to expand production capability.

1. Use the given data to calculate the NPV for the electronic closure product.  Do the numbers look good?

1. How is NPV affected if the following contingencies occur? (Assess each of these separately.)

1. Direct manufacturing cost estimate may be overly optimistic, and may never get below the original $16.
2. Competition may force higher marketing costs – what if starting in year 2 the level that must be spent is exactly twice what was forecasted above?

Assumptions: