Robot Smart Ltd is a leading high-tech company which is incorporated in Surrey, BC. The company wants to add an additional production line in August 2022. They hired you, a UCW graduate, to prepare a capital budgeting for the project.

Below is the information that your manager provided:

1. The facility is made up of one piece of land in North Vancouver value at 10%, one non residential building value at 20% of the total cost 70% of manufacturing equipment. At the end of projects life, the equipment will be sold for an estimated $0.2 million; assume the buildings value will be $0.1 million. Land residual value is unclear.

2. Start-up costs include $1.5 million to build the production facilities, including land, building and equipment. The project will last for 8 years.

3. The company estimated that it is able to make 2300 of its new products robots, could be sold annually over the next eight years at a price of $800 each. Variable costs per robot are $300 and each years fixed costs is 55,000.

4. To handle the new product line, Robot Smarts net operating working capital would have to increase by an amount equal to 10% of sales revenues and will be half recovered at the end of project.

5. However, if Robot introduces its new products, sales of its existing products will fall $300,000 per year. There will also be $80,000 to hire new workers who are familiar with the new equipment operation. The market research on the new robots was $350,000. The company will retool one of its existing manufacturing facilities to produce the new model. The one-time retooling cost is $170,000. BC government also granted the company an innovation funding of $10,000 in Jan 2022

6. The manager is complaining the inflation will affect fixed cost, variable cost and the sales price in current years. The financial division has estimated the companys WACC is 12%. The company also assume the sales will increase 8% per year.

Requirements

1. Using an Excel spreadsheet:

Find the NPV of the project by using the pro forma financial statement method to determine cash flows.

Set up the necessary equations by referencing to the input variable cells. The spreadsheet must be formula driven; do not put any numbers in equations, must use cell references.

• Use Excels built-in functions wherever possible
• Must list all the reference sources.

2. Assume that you are confident of your estimates of all variables that affect the projects cash flows except unit sales and sales price.

Worst Case:

If product acceptance is poor, unit sales will be only 1500 units a year and the unit price will be only $700

Best Case:

A strong demand will produce sales of 3000 units and a unit price of $850.

Probability:

The marketing department told you that there is a 25% chance of poor acceptance and 25% chance of excellent acceptance, and a 50% chance of average acceptance (the base case).

1. (a) What is the worst case NPV and the best case NPV?
2. (b) What is the expected NPV for this project considering all possibilities?

3. Recommendation

Use the results you obtained in the NPV and scenario analysis above to write a report (ALSO IN EXCEL) on your findings and recommend whether or not the company should proceed with the project with reasonings and references. The evaluation must be written in a professional way and at least 200 words.

4. Present this assignment in a professional way. It is your responsibility to communicate clearly to the marker.

5. Hand in by Excel Spread Sheets (One Excel File) (FOLLOW THESE INSTRUCTIONS STRICTLY):

Spread Sheet 1, cover page with your name and student ID.

Spread Sheet 2, Base Case: Data source, Income Statement, NPV. Must state the reasoning of the cash inflows/outflows.

Spread Sheet 3, Best Case: Income Statement, NPV

Spread Sheet 4, Worst Case: Income Statement, NPV

Spread Sheet 5, Scenario Analysis, Project Evaluation/Summary

Spread Sheet 6, References