Solve at excel

a CASE STUDY HOW A NEW ENGINEERING GRADUATE CAN HELP HIS FATHER Background the current trend of negative net profit will probably "I don't know whether to sell it, expand it, lease it or what. But continue. Projections are $1.25 million per year for ex- I don't think we can keep doing the same thing for many more penses and $1.15 million per year in rovenue. Elmer had an appraisal last year, and the report indicated Gulf years. What I really want to do is to keep it for 5 more years, Coast Wholesale Auto Parts is worth a net $2 million ther sell it for a bundle," Elmer Kettler said to his wife, Jarise, Elmer's wish is to soll out completely after 5 more their son, John Kettler, and new daughter-in-lasy, Suzanne Gestory, as they were gathered around the dinner table. Eliner years at this price, and to make a deal that the new owner pay $500,000 per year at the end of year 5 (sale was sharing thoughts on Gulf Coast Wholesale Auto Parts, a time) and the same amount for the next 3 years, company he has owned and operated for 25 years on the south em outskirts of Houston, Texas. The business has excellent con- Option 4: Trade-out. Elmer has a close friend in the tracts for parts supply with several national retailers operating antique auto parts business who is making a "killing." in the area NAPA, AulZone, O'Reilly, and Advance. Addi so he says, with e-commerce. Although the possibility tionally. Gulf Coast operates a rebuild shop serving these sarn is risky, it is enticing to Elmer to consider a whole new retailers for major automobile components, such as carburetors, line of parts, but still in the basic business that he al- transmissions, and air conditioning compressors ready understands. The trade-out would cost an esti- At his home after dinner, John decided to help his father mated $1 million for Elmer immediately. The 10 year with an important and difficult decision: What to do with his horizon of annual expenses and revenues is consider- irusiness? John graduated just last year with an engineering ably higher than for his current business. Expenses are degree from a major state university in Texas, where he com- estimated at $3 million per year and revenues at pleted a course in engineering economy. Part of his job at $3.5 million each year, Energoon Industries is to perform basic rate of return and Option 5: Lease arrangement Gulf Coast could be present worth analyses on energy management proposals. leased to sorne turnkey company with Elmer remaining Information The owner and bearing part of the expenses for building, delivery trucks, insurance, etc. The first-cut estimates Over the next few weeks, John outlined five options, includ- for this option are $1.5 million to get the business ready ing his dad's favorite of selling in 5 years. John Summarized now, with arnual expenses at $500,000 per year and all the estimates over a 10-year horizon. The options and revenues at $1 million per year for a 10 year contract. estimates were given to Elmer, and he agreed with them. Case Study Exercises Option 1: Renove rebuild. Stop operating the re- build shop and concentrate on selling wholesale Help John with the analysis by dotng the following parts. The removal of the rebuild operations and the 1. Develop the actual cash low series and incremental switch to an "all-parts house" are expected to cost $750,000 in the first year. Overall revenues will cash flow scries (in $1000 units) for all five options in drop to $1 million the first year with an expected 4% preparation for an incremental ROR analysis. 2. Discuss the possibility of multiple rate of return values increase per year thereafter. Expenses are projected at $0.8 million the first year, increasing 6% per year for all the actual and increnental cash flow series. Find thereafter. any multiple rates in the range of 0% to 100%. 3. IF John's father insists that he make 25% per year or more Option 2: Contract rebuild operations. To get the on the selected option over the next 10 years, what should rebuild shop ready for an operations contractor to he do? Use all the methods of economic analysis you take over will cost $400.000 immediately, If ex- have learned so far (PW, AW, ROR) so John's father can penises stay the same for 5 years, they will average understand the recommendation in one way or another. $1.4 million per year, but they can be expected to 4. Prepare plots of the PW versus i for cach of the five options, rise to $2 million per year in year 6 and thereafter. Estimate the breakeven rate of return between options. Elmer thinks revenues under a contract arrangement 5. What is the minimum amount that must be received in can be $1.4 million the first year and can riso 5% per each of years 5 through 8 for option 3 (the one Elmer year for the duration of a 10-year contract. wants) to be best economically? Given this amount, Option 3: Maintain status quo and sell out after 5 years what does the sale price have to be, assuming the same (Elmer's personal favorite). There is no cost now, but payment arrangement as presented above