1. Robot Smart Ltd wants to add an additional production line. They hired you, a UCW graduate, to prepare a capital budgeting for the project. Below is the information that your manager provided: 1. Start-up costs include $1.065 million to build production facilities The project will last for 5 years. 2. The facility is made up of building value at 20% of the total cost that will belong to CCA Class 3 and 80% of manufacturing equipment (belonging to CCA Class 8). Class 3 has a CCA rate of 5% while Class 8 has a rate of 20%. At the end of projects life, the facilities will be sold for an estimated $0.2 million; assume the buildings value will be $0.1 million. 3. The company estimated it is able to make 2500 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 50,000. 4. To handle the new product line, Robot Smarts net operating working capital would have to increase by an amount equal to 15% of sales revenues and will fully recover 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 in retraining costs incurred for workers who lost their jobs manufacturing the existing product. 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. 6. The taxation rate is 20% and Robot Smart estimate the inflation rate is 3% per year wich will affect fixed cost, variable cost and the sales price. The companys WACC is 12%. The company also assume the sales will increase 10% 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, only cell references. Use Excels built-in functions wherever possible 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). (a) What is the worst-case NPV and the best case NPV (b) What is the expected NPV for this project considering all possibilities?