As a financial analyst for General Enterprises, you've been asked to evaluate the new Track-It! product. The
Question:
As a financial analyst for General Enterprises, you've been asked to evaluate the new "Track-It!" product. The developers of Track-It! propose that the company invest a total of $10 million to bring the product to market: $3 million in development expenses in year 1 and another $7 million in year 2. The first units would be manufactured and sold in year 3. They expect to sell 100,000 units in year 3 at a price of $90 per unit, resulting in $9 million in revenues in year 3. In order to make the product, they expect to incur variable costs of $30 per unit and fixed expenses of $3 million in year 3. Hence total costs would be $30 * 100,000 + $3,000,000 = $6 million in year 3 resulting in a profit of $3 million.
Thereafter they expect the number of units sold to increase by 10% per year for years 4, 5, and 6 and then decrease by 20% per year for years 7, 8, 9, and 10. They expect the sales price per unit and the fixed expenses of production to remain constant throughout the life of the product but they expect to be able to decrease the variable cost per unit by 2% each year through production efficiencies. The Track It! The product will be discontinued at the end of year 10. Other products may be introduced to complement or replace Track It! Over the next 10 years but that is outside the scope of this analysis. Create a worksheet that calculates the internal rate of return (IRR) for Track It! Look at the BioTech exercise from class for an example of how to do this. You should have a separate area at the top for Inputs with a cell for each assumption. Your Calculations area should have columns for each of the years from 1 to 10. You should use separate rows to calculate units sold, revenues, variable cost per unit, variable costs, fixed expenses, and total costs. The bottom row should be the annual cash flows (i.e., profits) equal to revenues minus total costs.
Use a separate, well-labelled cell at the very bottom to calculate the rate of return. Suppose General Enterprises has a hurdle rate of 20% for investments in new products. Should it undertake this project? Answer yes or no in your spreadsheet.
Financial Management for Public Health and Not for Profit Organizations
ISBN: 978-0132805667
4th edition
Authors: Steven A. Finkler, Thad Calabrese