help with project

template included to fill out for project

6 2 3 4 Revenue per Carwash (Per carwash) 5 Variable Costs (per carwash): z Utilities (Elec/gas/water) 8 Detergents/Chemicals 9 Maintenance costs 10 Site Labor 11 Total Variable Costs 12 13 Contribution Margin 14 15 Contribution Margin% 16 17 Fixed Costs (dollars): 18 Owners' salary 19 Daughter's salary 20 Son's salary 21 Insurance 22 Operating costs (cc fees) 23 Advertising 24 Depreciation 25 Total Fixed Costs 26 27 28 Breakeven Carwashes 29 30 Fixed Costs 31 Contribution Margin per Carwash 22 Breakeven & Margin of Safety Ready Operating & DCF CE D 28 Breakeven Carwashes 29 30 Fixed Costs 31 Contribution Margin per Carwash 32 33 Breakeven Carwashes 34 35 Breakeven Dollars 36 Breakeven Carwashes 37 Revenue per Carwash 38 39 Breakeven Dollars 40 41 42 Margin of Safety Margin Forecasted Breakeven of Safety 43 44 45 46 47 Revenue per Carwash 48 Carwashes 49 50 Total Revenue 51 52 53 Degree of Operating Leverage 54 55 Forecasted Revenue 56 57 Forecasted Variable Costs 58 Variable Cost per Unit 59 Forecasted Carwashes Breakeven & Margin of Safety Ready Operating in B10 x B D E 49 50 Total Revenue 51 52 53 Degree of Operating Leverage 54 55 Forecasted Revenue 56 57 Forecasted Variable Costs 58 Variable Cost per Unit 59 Forecasted Carwashes 60 61 Forecasted Variable Costs 62 63 Forecasted Contribution Margin 64 65 Forecasted Fixed Costs 66 67 Forecasted Net Operating Income 68 69 70 Forecasted Contribution Margin 71 Forecasted Net Operating Income 72 73 Degree of Operating Leverage 74 75 76 77 78 79 80 $ Breakeven & Margin of Safety Ready Operating & DCF + Crossroads Forecasted Net Operating Income 2023 2024 2025 2026 2027 2021 2022 2028 2029 2030 5 Total Carwashes 3 Average Price per Car Wash 9 10 Sales 11 12 Variable Costs 13 14 Chemicals 15 Maintenance 16 Labor 17 Total Variable Costs 18 Contribution Margin 19 CM 20 21 Fixed Coats 22 Owners' Salaries 22 Daughter/Son Salary 24 Insurance Operating Costs Breakeven Margin of Safety Ready Operating & DCF D F G H K 16 Labor 17 Total Variable Costs 18 Contribution Margin 19 CMS 20 21 Fixed Costs 22 Owners' Sales 23 Daughter Son Salary 24 Insurance 25 Operating Costs 26 Advertising ) 27 Depreciation 28 Total Feed Costs 29 Total Expenses (Costa) 30 Net Operating Income 31 32 33 > 2020 2021 34 35 36 Cash flows Reveme) 37 Sale of Business 30 Net Cash flows 39 2022 Discounted Cash Flow Analysis 2021 2024 2025 2026 2027 2029 2029 2030 Breakeve & Marge of Safety Operating & DCF 1 K 34 E G H Discounted Cash Flow Analysis 2023 2024 2025 2026 2020 2021 2022 2027 2028 2029 2030 35 36 Cash Inflows (Revenue) 37 Sale of Business 38 Net Cash Inflows 19 40 Operating Expenses 41 Less Depreciation (Non-cash expense) 42 Equipment Uppade Net Cash Outflows 44 45 Net Cash Flows 46 48 Discount Factors (Present Value of $1.00) 49 50 Present Value 51 52 53 54 Sell to Mega Ss Cash Today from Big Box 56 Retirement Savings 57 Breakeven Margin of Safety Operating & DCF Bead D Discount Factors (Present Value of $1.00) Present Value Sell to Mega 5 Cash Today from Big Box Retirement Savings Cash Flow Sell to Mega 0 Continue to operate -1 Discounted Cash Flow 2 3 Nex Cash Flow 55 56 57 zn Breakeven & Margin of Safety Operating & DCF Crossroads: Which wash is the best wash? 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 hypermarket 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 Mera. What was their offer?" she asked "He offered $87,500 per acre for all 20 acres. "One million were hundred fifty thousand dollars seems high. I remember when Billy Joe sold his 3 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 so much more to us is because they are including Crossroads is the offer What will they do with Crowa Kay asked not really wanting to know his reply 25 *Their plan is to level Crossroads for parking the new Megastore." 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 morning. We can't afford to lose all of those sales "Kay, I completely understand. Tell me what is set t 315 ontent/798406/View "On Friday, Mega offered as $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 Rese: Carwashes projected for the next twelve months Average rerne per car wash Forecasted annual pronth is canales Expres 37.500 $13.00 3 percent Variable cose per creark Ulises Eesgar) Detergente chemicals Lastecat cu $0.18 0.64 9.21 50 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 0.80 Total Fixed Costs 8.09 Total SI22 *Based on 10 year useful life with no salvage value **No net changes in working capital are expected ***Rate 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) 3. 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. 6 2 3 4 Revenue per Carwash (Per carwash) 5 Variable Costs (per carwash): z Utilities (Elec/gas/water) 8 Detergents/Chemicals 9 Maintenance costs 10 Site Labor 11 Total Variable Costs 12 13 Contribution Margin 14 15 Contribution Margin% 16 17 Fixed Costs (dollars): 18 Owners' salary 19 Daughter's salary 20 Son's salary 21 Insurance 22 Operating costs (cc fees) 23 Advertising 24 Depreciation 25 Total Fixed Costs 26 27 28 Breakeven Carwashes 29 30 Fixed Costs 31 Contribution Margin per Carwash 22 Breakeven & Margin of Safety Ready Operating & DCF CE D 28 Breakeven Carwashes 29 30 Fixed Costs 31 Contribution Margin per Carwash 32 33 Breakeven Carwashes 34 35 Breakeven Dollars 36 Breakeven Carwashes 37 Revenue per Carwash 38 39 Breakeven Dollars 40 41 42 Margin of Safety Margin Forecasted Breakeven of Safety 43 44 45 46 47 Revenue per Carwash 48 Carwashes 49 50 Total Revenue 51 52 53 Degree of Operating Leverage 54 55 Forecasted Revenue 56 57 Forecasted Variable Costs 58 Variable Cost per Unit 59 Forecasted Carwashes Breakeven & Margin of Safety Ready Operating in B10 x B D E 49 50 Total Revenue 51 52 53 Degree of Operating Leverage 54 55 Forecasted Revenue 56 57 Forecasted Variable Costs 58 Variable Cost per Unit 59 Forecasted Carwashes 60 61 Forecasted Variable Costs 62 63 Forecasted Contribution Margin 64 65 Forecasted Fixed Costs 66 67 Forecasted Net Operating Income 68 69 70 Forecasted Contribution Margin 71 Forecasted Net Operating Income 72 73 Degree of Operating Leverage 74 75 76 77 78 79 80 $ Breakeven & Margin of Safety Ready Operating & DCF + Crossroads Forecasted Net Operating Income 2023 2024 2025 2026 2027 2021 2022 2028 2029 2030 5 Total Carwashes 3 Average Price per Car Wash 9 10 Sales 11 12 Variable Costs 13 14 Chemicals 15 Maintenance 16 Labor 17 Total Variable Costs 18 Contribution Margin 19 CM 20 21 Fixed Coats 22 Owners' Salaries 22 Daughter/Son Salary 24 Insurance Operating Costs Breakeven Margin of Safety Ready Operating & DCF D F G H K 16 Labor 17 Total Variable Costs 18 Contribution Margin 19 CMS 20 21 Fixed Costs 22 Owners' Sales 23 Daughter Son Salary 24 Insurance 25 Operating Costs 26 Advertising ) 27 Depreciation 28 Total Feed Costs 29 Total Expenses (Costa) 30 Net Operating Income 31 32 33 > 2020 2021 34 35 36 Cash flows Reveme) 37 Sale of Business 30 Net Cash flows 39 2022 Discounted Cash Flow Analysis 2021 2024 2025 2026 2027 2029 2029 2030 Breakeve & Marge of Safety Operating & DCF 1 K 34 E G H Discounted Cash Flow Analysis 2023 2024 2025 2026 2020 2021 2022 2027 2028 2029 2030 35 36 Cash Inflows (Revenue) 37 Sale of Business 38 Net Cash Inflows 19 40 Operating Expenses 41 Less Depreciation (Non-cash expense) 42 Equipment Uppade Net Cash Outflows 44 45 Net Cash Flows 46 48 Discount Factors (Present Value of $1.00) 49 50 Present Value 51 52 53 54 Sell to Mega Ss Cash Today from Big Box 56 Retirement Savings 57 Breakeven Margin of Safety Operating & DCF Bead D Discount Factors (Present Value of $1.00) Present Value Sell to Mega 5 Cash Today from Big Box Retirement Savings Cash Flow Sell to Mega 0 Continue to operate -1 Discounted Cash Flow 2 3 Nex Cash Flow 55 56 57 zn Breakeven & Margin of Safety Operating & DCF Crossroads: Which wash is the best wash? 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 hypermarket 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 Mera. What was their offer?" she asked "He offered $87,500 per acre for all 20 acres. "One million were hundred fifty thousand dollars seems high. I remember when Billy Joe sold his 3 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 so much more to us is because they are including Crossroads is the offer What will they do with Crowa Kay asked not really wanting to know his reply 25 *Their plan is to level Crossroads for parking the new Megastore." 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 morning. We can't afford to lose all of those sales "Kay, I completely understand. Tell me what is set t 315 ontent/798406/View "On Friday, Mega offered as $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 Rese: Carwashes projected for the next twelve months Average rerne per car wash Forecasted annual pronth is canales Expres 37.500 $13.00 3 percent Variable cose per creark Ulises Eesgar) Detergente chemicals Lastecat cu $0.18 0.64 9.21 50 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 0.80 Total Fixed Costs 8.09 Total SI22 *Based on 10 year useful life with no salvage value **No net changes in working capital are expected ***Rate 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) 3. 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