PART 1 (80 points)
The questions on this exam are adapted from the Case: Performance Lawn Equipment in Business Analytics by Evans. The accompanying data was modified to fit the goals and objectives of this exam.
Performance Lawn Equipment
Company Background Information
PLE, headquartered in St. Louis, Missouri, is a privately owned designer and producer of traditional lawn mowers used by homeowners. In the past 10 years, PLE has added another key product, a medium-size diesel power lawn tractor with front and rear power takeoffs, Class I three-point hitches, four-wheel drive, power steering, and full hydraulics. This equipment is built primarily for a niche market consisting of large estates, including golf and country clubs, resorts, private estates, city parks, large commercial complexes, lawn care service providers, private homeowners with five or more acres, and government (federal, state, and local) parks, building complexes, and military bases. PLE provides most of the products to dealerships, which, in turn, sell directly to end users. PLE employs 1,660 people worldwide. About half the workforce is based in St. Louis; the remainder is split among their manufacturing plants.
In the United States, the focus of sales is on the eastern seaboard, California, the Southeast, and the south central states, which have the greatest concentration of customers. Outside the United States, PLE’s sales include a European market, a growing South American market, and developing markets in the Pacific Rim and China. The market is cyclical, but the different products and regions balance some of this, with just less than 30% of total sales in the spring and summer (in the United States), about 25% in the fall, and about 20% in the winter. Annual sales are approximately $180 million.
Both end users and dealers have been established as important customers for PLE. Collection and analysis of end-user data showed that satisfaction with the products depends on high quality, easy attachment/dismount of implements, low maintenance, price value, and service. For dealers, key requirements are high quality, parts and feature availability, rapid restock, discounts, and time lines of support.
PLE has several key suppliers: Mitsitsiu, Inc., the sole source of all diesel engines; LANTO Axles, Inc., which provides tractor axles; Schorst Fabrication, which provides subassemblies; Cuberillo, Inc., supplier of transmissions; and Specialty Machining, Inc., a supplier of precision machine parts.
Spreadsheet Modeling and Analysis(20 points)
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 $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,800, 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. Develop a 4-year financial model to recommend the best decision using a net present value discount rate of 10%. 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.
Financial Model
Year 1 Year 2 Year 3 Year 4
First Year Sales
Sales Volume Growth Rate
Annual Sales
Revenue
Total Variable Cost
Net Profit
Net Present Value
b. What is the best decision using a net present value discount rate of 10%? What is the best decision using a net present value discount rate of 25%?
c. Use Goal Seek to find how sensitive the best decision (discount rate of 10%) is to the estimated variable cost of the PEL-Tough.In other words, what is the maximum acceptable variable cost for the PEL-Tough such that the PEL-Tough is the product of choice?
d. Construct a tornado chart and explain the sensitivity of each of the model’s parameters on the difference in net present value.
Simulation (20 points)
The Executive Committee of PLE indicated while considering to replace its original tractor model, the PLE-Classic, with a new model, the PLE-Tough, that several of the input parameters for the new model are uncertain. Consequently, they requested a Monte Carlo Simulation Model with the following assumptions: the selling price for the PLE-Tough is expected to follow a triangle distribution with a minimum value of $3,500, likely of $4,200 and maximum of $4.500. The first year sales for the PLE-Tough is estimated to be normally distributed with mean of 4,000 and standard deviation of 300. The variable cost per vehicle for the PLE-Tough is estimated to be normally distributed with mean of $2,300 and standard deviation of $500. Set the seed to 2 and run a 5,000-trial simulation.
a. Paste here a screenshot of the distribution of thedifference in NPVs.
b. What is the probability that the PLE-Tough yields a higher NPV than the PLE-Classic?
c. How much difference in NPVs can be expected with probability of at least 0.85?
d. Find a 95% confidence interval for mean difference in NPVs.
e. Interpret the sensitivity chart.
Data Mining (20 points)
PLE is committed to customer satisfaction so each completed order is immediately followed by a short customer survey.The seven PLE attributes rated by each respondent are
Delivery speed—- the amount of time it takes to deliver the product once an order is confirmed
Price level— the perceived level of price charged by PLE
Price flexibility— the perceived willingness of PLE representatives to negotiate price on all types of purchases
Manufacturing image— the overall image of the manufacturer
Overall service— the overall level of service necessary for maintaining a satisfactory relationship between PLE and the purchaser
Sales force image— the overall image of the PLE’s sales force
Product quality— perceived level of quality
Responses to these seven variables were obtained using a graphic rating scale, where a 10-centimeter line was drawn between endpoints labeled “poor” and “excellent”. Respondents indicated their perceptions using a mark on the line, which was measured from the left endpoint. The result was a scale from 0 to 10 rounded to one decimal place.
By cross matching databases, the data also include information on whether the customer returned part of its order within the 30-day satisfaction guarantee period.
Elizabeth Burke would like to understand these data. She would like to devise a method of early detection to identify customers that are likely to return any part of an order based on the survey responses. Create a classification method in order to predict whether a customer is likely to return a product within the 30-day satisfaction guarantee period.
a. Partition the data into a Training Set and a Validation Set using the default setting.
b. Apply data mining classification methods and recommend one. Explain your reasoning for the recommendation. Interpret the confusion tables to make your recommendation.
c. Using your recommended model, classify the new survey information. Include here the classification results.
Linear Programming (20 points)
One of PLE’s manufacturing facilities produces metal engine housings from sheet metal for both mowers and tractors. Production of each product consists of three steps: stamping, assembly, and painting to ship to its final assembly plant. The production rates in hours per unit and number of production hours available in each department are given in the following table:
Department Mower Housings Tractor Housings Production Hours Available
Stamping 0.03 0.07 200
Assembly 0.15 0.10 300
Painting 0.04 0.06 220
In addition, mower housing require 1.2 square feet of sheet metal per unit and tractor housings require 1.8 square feet per unit, and 2,500 square feet of sheet metal is available. The company would like to maximize the total number of housings they can produce during the planning period.
a. Identify the decision variables, objective function, and constraints in simple verbal expressions.
b. Mathematically formulate a linear optimization model.
c. Implement the linear optimization model that you developed in Excel and use Solver to find an optimal solution.
d. Interpret the Solver Answer report and identify the binding constraints. Include a screenshot of the model and answer report.
PART 2 (20 points)
Write a Business Memo to communicate to management your findings of one of the four initiatives. Keep in mind that you are writing a memo (not a report) so do not attempt to cover every question in Part 1 in the memo. Pickone issue and provide a thorough and concise explanation of your findings.