Fairway Manufacturing is considering the introduction of a new food product. A market study was completed to
Question:
Fairway Manufacturing is considering the introduction of a new food product. A market study was completed to gather customer feedback. The cost of the study was $125,000. The new product is expected to last 5 years and then, because this is somewhat of a fad product, be terminated. The new product will compete against an existing product. This will result in the annual loss of operating cash flows from the existing product of $100,000. The company evaluates projects using a 20% required rate of return (cost of capital). The following information describes the new project.
The cost of the new equipment will be $15,850,000. Shipping and installation costs of the equipment will be $300,000. The equipment is considered to be 7-Year MACRS Property. The units to be sold in year 1 are expected to be 100,400 with an expected 4.5% growth in unit sales each year. The unit selling price in year 1 is expected to be $395. The manufacturing cost per unit in year 1 is expected to be $282. The unit selling price will decrease each year by 1.5% due to strong competition; however, the manufacturing cost per unit is expected to increase 3.2% each year due to inflation and demand for raw materials. Annual selling and administrative expenses are expected to be $2,850,000 per year. The company’s income tax rate is 21%.
Working Capital Requirements: There will be an initial net working capital requirement of $450,000 just to get production started. The total net working capital buildup will be liquidated by 85% at the termination of the project at the end of year 5.
Depreciation Expense: The VLOOKUP function must be used in the calculation of depreciation expense.
Sale of Equipment: The equipment is expected to be sold for $4,350,000 at the end of five years.
Formulas and Functions: All calculations must use formulas and/or functions. Cell references must be used in all formulas and functions, except for the number one in the growth formula, the column number in the VLOOKUP function, and the numbers zero and one in the IF function.
Instructions:
1. Calculate the incremental cash flows for years 0-5.
2. Calculate the NPV (display at 2 decimals).
3. Calculate the PI (display at 2 decimals).
4. Calculate the IRR (display at 2 decimals).
5. Calculate the MIRR (display at 2 decimals).
6. Calculate the Standard Payback (display at 2 decimals).
7. Using the IF function, indicate whether the project is acceptable under each of the five methods (the function should return either Accept or Reject). The standard payback cutoff period is three years.
8. Other requirements:
- Formulas must be used for all calculations. Formulas should use cell references, as appropriate.
- All numbers must be properly right-aligned. Do not use a dollar sign ($) for every dollar amount.
- An input area with the percentages and other assumptions must be provided so that what-if scenarios may be performed.
- Your spreadsheet must be prepared in a professional manner with an appropriate heading: use bolding, indenting and spacing, spell check, consistent number formats, etc.
