Need Excel Discounted Cash Flow Only. See Question Below!!!

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 week 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 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-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 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 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?" "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 morning. 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 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 morning." Revenue: Carwashes projected for the next year 37,500 Average revenue per car wash $13.00 Forecasted annual growth in carwashes 3 percent Expenses: Variable costs per carwash Utilities (Elec/gas/water) $0.78 Detergents/chemicals 0.64 Maintenance costs 0.47 Site labor 1.24 Total Variable Costs $3.13 Fixed costs per carwash (Based on 37,500 carwashes) Salaries $5.60 Insurance 0.44 Operating costs 0.60 Advertising 0.65 Depreciation (current) 0.00** Total Fixed Costs 7.29 Total $11.22 *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%

1. 1.Perform a discounted cash flow analysis using the excel template below

33 Discounted Cash Flow Analysis 2024 2025 2026 2020 2021 2022 2023 2027 2028 2029 2030 34 35 36 Cash Inflows (Reveme) 37 Sale of Business 38 Nel 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 43 Discount Factors Present Value of $1.00) 49 50 Present Value 51 52 53 54 Sell to Mega 55 Cash Today from Big Box 56 Retirement Savings 57 50 Cash Flow Sell to Mega 59 60 Contato Operate 6t Discounted Cash Flow 62 63 Ner Cash Flow 04 Breakin & Margin of Safety Operating & DCF +