Perform a discounted cash flow analysis only using Excel template.

Ron Christian and his wife, Kay established a carwash, Crossroads on 20 acres of worthless desert land 30 years ago on the outskirts of El Paso. They would have never imagined the urban sprawl that now engulfed their business. For years, the Christians worked 12 hours a day seven days a weck to support their family almost losing the business several times. All that changed when developers lured by inexpensive desert land prices began to build large subdivisions all aroundCrossroads. With each new subdivision, sales at Crossroads increased allowing the Christians to hire their adult children full time paying them each a not so modest $67,500 per year, even though neither was qualified when they graduated from high school. Joe, the oldest, was responsible for maintaining their aging equipment and Julic, his little sister, took charge of marketing. The Christians would never get rich, but Crossroads provided enough income to support their family and generated employment for the underserved in their community, Long ago, Ron and Kay decided to hire graduates from Hope Ministries, a local drug and alcohol rehab facility. While most stayed at Crossroads for only a year or two, a few with no other carcer options continued to work with the Christians for several years. Ron and Kay considered them like family. Ron and Kay, now in their late 50 's began to envision a retirement where they would slow down. Their plan was to transition day-to-day operation of Crossroads to their children while continuing to draw a combined salary of $75,000. With a meager $300,000 in retirement savings, Crossroads was the Christian's retirement. For years, commercial real estate developers were buying the desert land all around Crossroads to build fast food and quick service restaurants, convenience stores and even a dollar store to support all of the new homeowners, but no one expressed interest in Crossroads until today. While Kay was out of the office, a representative from big box retailer, Mega, approached Ron and offered him $1.75 million dollars for Crossroads including all 20 acres with the intention levelling (tearing down) the business in order to build a massive 180,000 square foot hyper-market to sell groceries and general merchandise. While Ron was excited to share the good news with Kay, he decided to wait and discuss the offer with her in person. Sharing the Good News After another long day, Ron was again stuck in traffic on his drive home. He couldn't help but contemplate what life would be like without Crossroads. When he finally got home, Ron anetad "I had an interesting meeting today. A representative from Mega stopped by the office." "I figured eventually somebody would want to buy us out, but I had no idea it would be Mega. What was their offer?" she asked. "He offered $87,500 per acre for all 20 acres." "One million seven hundred fifty thousand dollars seems high. I remember when Billy Joe sold his 5 acre tract, Burger Barn only paid him $250,000. Why are they offering so much?" Kay pondered. "Well, there is a bit of catch. The reason they offered us so much more to us is because they are including Crossroads in their offer." "What will they do with Crossroads?" Kay asked not really wanting to know his reply. "Their plan is to level Crossroads for parking the new Mega store." "So, if we sell to Mega, we won't have a carwash anymore. Right?" Kay questioned. "One million seven hundred fifty thousand dollars seems high. I remember when Billy Joe sold his 5 acre tract, Burger Barn only paid him $250,000. Why are they offering so much?" Kay pondered. "Well, there is a bit of catch. The reason they offered us so much more to us is because they are including Crossroads in their offer." "What will they do with Crossroads?" Kay asked not really wanting to know his reply. "Their plan is to level Crossroads for parking the new Mega store." "So, if we sell to Mega, we won't have a carwash anymore. Right?" Kay questioned. "That's right, but we will be $1.75 million dollars richer. Isn't that great?" "I don't know. What happens if we don't accept their offer?" "From what I understand, they approached several businesses with a comparable offer, but our location by far is the best. Kay, this is our opportunity to retire comfortably. I doubt we will get this opportunity again. What do you think?" "Ron, I agree with you, but what will happen to Joe and Julie and the others who have been with us for so many years?" "I am worried about them too. We only have a few days to decide before Mega moves onto other locations. Let's discuss with Sam, our accountant first thing Monday morning, Okay?" "I am worried about them too. We only have a few days to decide before Mega moves onto other locations. Let's discuss with Sam, our accountant first thing Monday morning. Okay?" "Sounds good. Let's get some sleep. Tomorrow will be another long day," Kay replied. Meeting with the Accountant "Hi Sam. Thanks for meeting with me on such short notice," Kay commented. "I am sorry Ron can't be here. The main pump went down again last night. He was at Crossroads all night to get it fixed, just so we could open this moming. We can't afford to lose all of those sales." "Kay, I completely understand. Tell me what is so urgent?" "On Friday, Mega offered us $1.75 million dollars for Crossroads and the surrounding land. While it sounds like a great deal, Ron and I are nervous. We are not sure if Mega's offer is what is best for us and we need your help to analyze our options." "That sounds like a great offer. Tell me more," said Sam. A confident Kay continued, "Crossroads is profitable now and we fully expect our profitability to increase in the future. We'd planned to run the business for another 10 years and expected to sell it for $2.5 million dollars. Unfortunately, to do that, we would deplete all of our expected to sell it for $2.5 million dollars. Unfortunately, to do that, we would deplete all of our retirement savings to upgrade the equipment, but it will saving us $0.25 in maintenance costs." "That would be a tough decision," Sam replied. "On the other hand, if we don't accept their offer, Mega will move onto another location and we could be passing up a golden opportunity to retire. Please, we really need your help." "I would be happy to help. Did you bring your most recent financials?" Sam asked. Kay replied handing Sam a paper with handwritten figures, "Yes. While Ron was working last night, I put together the following projections for next year." "Thanks. I will have something for you in the moming." * No net changes in working capital are expected ** New equipment useful life 10 years with no salvage/disposal value *** Rate of return (discount rate) 8% Case questions 1. What do you consider the Christians' key issue(s)? 2. Compute the breakeven carwashes (carwashes \& total sales dollars), margin of safety and degree of operating leverage. Assess Kay's comments about the current state Crossroads' profitability. 3. Assuming the Christians decide to upgrade their equipment, calculate Crossroads net operating income for each of the next ten years. Evaluate the long term viability of Crossroads. 4. Perform a discounted cash flow analysis and clearly recommend if the Christians should accept Mega's offer or continue to operate Crossroads. 5. Assess the impact of your recommendation on Ron and Kay's family, employees and the community with a focus on qualitative (non-financial) factors. Please use the attached Excel template to answer the questions if applicable, entering your formula in Excel to