Spreadsheet Modeling and Analysis

As part of the companys continues commitment to product innovation, the Executive Committee of PLE is debating whether to replace its original tractor model, the PLE-Classic, with a new model, the PLE-Tough, which would appeal to a younger clientele. Whatever tractor chosen will be produced for the next 4 years, after which time a reevaluation will be necessary. The PLE-Tough has passed through the concept and initial design phases and is ready for final design and manufacturing. Final development costs are estimated to be $1,750,000, and the new fixed costs for tooling and manufacturing are estimated to be $6 million. The PLE-Tough is expected to sell for $4,200. The first year sales for the PLE-Tough is estimated to be 4,000, with a sales growth for the subsequent years of 6% per year. The variable cost per vehicle is uncertain until the design and supply-chain decisions are finalized, but is estimated to be $2,300. Next-year sales for the PLE-Classic are estimated to be 3,500, but the sales are expected to decrease at a rate of 10% for each of the next 3 years. The selling price is $3,800. Variable costs per vehicle are $2,140. Since the model has been in production, the fixed costs for development have already been recovered.

a. Using the spreadsheet , develop a 4-year financial model to recommend the best decision using a net present value discount rate of 8%. Include the discount rate as an input variable in your model. Use the following layout for your financial model. Paste a screenshot of your model here.

b. What is the best decision using a net present value discount rate of 8%?

c. Use Goal Seek to find how sensitive the best decision is to the estimated variable cost of the PLE-Tough. In other words, what is the maximum acceptable variable cost for the PLE-Tough such that the PLE-Tough is the product of choice?

d. Use Scenario Manager to evaluate the Net Present Value for the PLE-Tough under the following scenarios:

	Scenario 1	Scenario 2
Selling Price	$ 5,000.00	$ 4,000.00
Sales Growth for subsequent years	5%	8%
Variable Cost	$ 2,600.00	$ 2,350.00