With this information- Submit: Build a spreadsheet model that has a table for inputs (Sales, growth...

Transcribed Image Text:

With this information- Submit: Build a spreadsheet model that has a table for inputs (Sales, growth rate, depreciation rates, WACC) and a table for the output (NPV) Compute the operating cash flows from the inputs above (DO THIS BY REFERENCING CELLS-NOT ENTERING #'s) Compute the NPV of the project o Run a scenario of what level of sales would create a NPV = 0 (1) Printout of the spreadsheet model for the base scenario (inputs you were given) (5 pts) (2) A printout of the spreadsheet for the scenario where the NPV = 0 (10 pts) (3) A printout of the spreadsheet showing all formulas entered for the calculations Under the main menu- select Formulas, then a window allows you to select Formula Auditing and then click on show formulas and then print the spreadsheet again. (This allows the imbedded formulas to be displayed and printed.) (5 pts) Staple all items Deere Inc is considering bringing out a new product: A golf Cart! The company is planning on marketing the cart as the toughest golf cart ever built. One that can be used for golfing in the summer and hunting in the winter! The cost of building the plant and equipment as well as the modification and shipping costs for this facility are outlined below: Cost of Plant and equipment is estimated at $456,500,000 The amount of net working capital that will need to be invested is $18,750,000 Plant and equipment modifications add up to $8,300,000 Shipping and installation +$3,750,000 The marketing study that was done a year ago to determine demand for the Deer Golf Cart cost $220,000 The cost of capital of Deere for an "average" project is 9.78%. However, the company uses a +2% or -2% adjustment to account for additional risk or safety. The project is considered to be an "above" average risk project. The project is expected to generate initial sales in year 1 of 12,000 units at a price of $12,500. The company then estimates a growth rate on sales of 4.5%. This will cover the project's expected life of 7 years when new technology will render this Deere Cart obsolete. Variable costs of production are estimated to be 55% of sales. There is a fixed cost estimate of $1,500,000. The company's tax rate is 25%. And the depreciation schedule that Deere will use MACRS 10 year schedule (10%, 18%, 14.40%, 11.52%, 9.22%, 7.37%, 6.55%, 6.55%, 6.56%, 6.55%, 3.28%) (i.e. however note that the project only has 7 years life) Finally, the salvage value of the plant and equipment is estimated to be $102,000,000 and the company will recover the full NOWC investment at the end. Please make a recommendation of whether Deere should undertake or not this project Excel Format Build your excel model so that the 1st box/area contains your inputs: Price Sales Growth rate of sales VC percentage WACC Salvage Value Build a t-0 section (Initial Outlay) Build an estimation of Net Cash flows given your inputs for the 7 years of the project Build a section for the end of the project cash flows Make sure that these calculations are all sensitive to the input variables you have above Compute NPV and IRR With this information- Submit: Build a spreadsheet model that has a table for inputs (Sales, growth rate, depreciation rates, WACC) and a table for the output (NPV) Compute the operating cash flows from the inputs above (DO THIS BY REFERENCING CELLS-NOT ENTERING #'s) Compute the NPV of the project o Run a scenario of what level of sales would create a NPV = 0 (1) Printout of the spreadsheet model for the base scenario (inputs you were given) (5 pts) (2) A printout of the spreadsheet for the scenario where the NPV = 0 (10 pts) (3) A printout of the spreadsheet showing all formulas entered for the calculations Under the main menu- select Formulas, then a window allows you to select Formula Auditing and then click on show formulas and then print the spreadsheet again. (This allows the imbedded formulas to be displayed and printed.) (5 pts) Staple all items Deere Inc is considering bringing out a new product: A golf Cart! The company is planning on marketing the cart as the toughest golf cart ever built. One that can be used for golfing in the summer and hunting in the winter! The cost of building the plant and equipment as well as the modification and shipping costs for this facility are outlined below: Cost of Plant and equipment is estimated at $456,500,000 The amount of net working capital that will need to be invested is $18,750,000 Plant and equipment modifications add up to $8,300,000 Shipping and installation +$3,750,000 The marketing study that was done a year ago to determine demand for the Deer Golf Cart cost $220,000 The cost of capital of Deere for an "average" project is 9.78%. However, the company uses a +2% or -2% adjustment to account for additional risk or safety. The project is considered to be an "above" average risk project. The project is expected to generate initial sales in year 1 of 12,000 units at a price of $12,500. The company then estimates a growth rate on sales of 4.5%. This will cover the project's expected life of 7 years when new technology will render this Deere Cart obsolete. Variable costs of production are estimated to be 55% of sales. There is a fixed cost estimate of $1,500,000. The company's tax rate is 25%. And the depreciation schedule that Deere will use MACRS 10 year schedule (10%, 18%, 14.40%, 11.52%, 9.22%, 7.37%, 6.55%, 6.55%, 6.56%, 6.55%, 3.28%) (i.e. however note that the project only has 7 years life) Finally, the salvage value of the plant and equipment is estimated to be $102,000,000 and the company will recover the full NOWC investment at the end. Please make a recommendation of whether Deere should undertake or not this project Excel Format Build your excel model so that the 1st box/area contains your inputs: Price Sales Growth rate of sales VC percentage WACC Salvage Value Build a t-0 section (Initial Outlay) Build an estimation of Net Cash flows given your inputs for the 7 years of the project Build a section for the end of the project cash flows Make sure that these calculations are all sensitive to the input variables you have above Compute NPV and IRR