discounted cash flow analysis

Crestronda: Wiwil is the best wax? Roa Christian and his wife, Kay established a carwash, Crossroads en 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 yns, the Christians worked 12 hours a day seven days a week to support their family almost losing the business several times. All that changed when developers hured by inexpensive desert land prices began to build large subdivisions all around Crossroads. 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 Julie, 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 career 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-markort 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 started, "I had an interesting meeting today. A representative from Mega stopped by the office With surprise in her voice Kay replied, "What did he want?" "Mega is interested in purchasing Crossroads," Ron replied matter-of-factly. "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 Bam 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 Crossroadei 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 similar deal, 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?" "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 13 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 retirement savings to upgrade the equipment." 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." *Thanks. I will have something for you in the morning." Revenue: Carwashes projected for the next twelve months Average revenue per car wash Forecasted annual growth in carwashes 37,500 $13.00 3 percent Expenses Variable coste per carwash Utilities (Elec gas water) Detergents chemicals Naintenance costs $0.78 0.64 0.42 Site labor 1.24 Total Variable Costs 33.13 Fixed coses per carwash (Based on 37,500 carwashes) Salaries $5.60 Insurance 0.44 Operating costs 0.60 Advertising 0.65 Depreciation 0.80* Total Fixed Costs 8.09 Total $11.22 *Based on 10 year useful life with no salvage value **No nst changes in working capital are expected ***Rats of return (discount rate) 8% Case questions 1. What do you consider the key issue(s) facing the Christians? (15 points) 2. For 2020, compute the breakeven carwashes (carwashes & total sales dollars) and margin of safety. Assess Kay's comments about the current state of Crossroads' profitability. (15 points) 3. Based on Kay's projections forecast net operating income for Crossroads for the next ten years? Evaluate the long term viability of Crossroads' profitability. (25 points) 4. Perform a discounted cash flow analysis and clearly recommend if the Christians should accept Mega's offer or continue to operate Crossroads. (25 points) 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. (20 points) NB: Include formulas used into your excel document. D G Discounted Cash Flow Analysis 2023 2024 2025 2026 2020 2021 2022 2027 2028 2029 2030 34 35 36 Cash Inflows (Revenue) 37 Sale of Business Net Cash Inflows 39 40 Operating Expenses 41 Less Depreciation (Non-cash expense) 42 Equipment Upgrade 43 Net Cash Outflows 44 45 Net Cash Flows 46 47 48 Discount Factors (Present Value of $1.00) 49 50 Present Value 51 52 53 54 Sollto VR 55 Cash Today from Big Box 56 Retirement Savings 57 50 Cash Flow Sell to Mega 59 50 Continue to Operate 61 Discounted Cash Flow 62 63 Net Cash Flow Breakeven & Margin of Safety Operating & DCF