Spreadsheet Modeling and Analysis
As part of the company’s 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 provided, 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%?