Modeling Project 1 FORECAST ANALYSIS PROJECT - East Metro Drive In In this exercise, you will be using the financial model forecast provided – which focuses on an investment in East Metro Drive In. The assignment is to use this file to conduct an analysis of this investment opportunity utilizing the forecast provided, as if you were the investor’s/buyer’s financial advisor advising him/her on this transaction This will involve analyzing the current forecast, making changes to various assumptions, and commenting on these new sensitivity cases. Finally, you will also be asked to prepare a new scenario as the recommended investment forecast. Each team will be asked to prepare a short (10-15) minute presentation analyzing a proposed business transaction. The presentation should be made by the financial advisory team - to the client – who is interested in purchasing this property. In order to accomplish this, the team will need to work with financial model, run sensitivities, and create a new scenario. Focus on the validity of the analysis, the financial health of the company, the appropriateness of the valuation, and the identification of risk issues that they would have to managed going forward. Students are encouraged to utilize the Excel based financial proforma as a tool to perform sensitivity analysis in order to better understand the inherent risk of the business. Key elements of this exercise include 1) utilizing a financial forecast to make perform financial analysis; 2) making a business decision and recommendation; 3) accessing and managing risk and 4) developing presentation skills. For each question below, please explore all possible ways to use the financial tool provided to enhance your answer. If you are asked to comment on a risk, measure the sensitivity of the results to that input, etc. This project has two components %uF0A7 A power point presentation that addresses the items outlined below, as well as summarizes the new scenario below. Presentations should be emailed to the instructor on October 20, 2004, along with the excel file containing the new scenario (which is the excel model provided, with inputs that you have changed) submitted at the same time.
%uF0A7 A presentation by each team of the material above. Please note – when submitting your files (ppt and excel, be sure to include your team member names in the name of the file.) A short verbal description for the East Metro Drive In financial model is provided below; more detail will be provided when this forecast is discussed in class. If, however, you have any additional questions about the assumptions or the business case, please email the instructor at any time. Questions Topic Question – respond to each question as if you are a financial advisor providing advice to the potential buyer of this drive in. For all but the last question, please use the existing business case, as presented in the financial model provided. Points Overview • What are the key business assumptions that drive the value of the investment? Focus on the top 3 items that drive the value of this investment. 5 Due Diligence and Analysis • Identify due diligence techniques that you would suggest to verify the assumptions in the financial forecast. What questions do you have for Mr. Berry? What documents would you ask for? • What is missing in this analysis? Where would you want more detail? What else would you add? 10 Equity Investment • As a sole proprietorship, how should an investor evaluate this investment (i.e. which ratios and performance measures would be most meaningful and why?) Include a short explanation/definition of the measures • Discuss this investment from the standpoint of the buyer (based on the measures you have just suggested) – is this a good investment? Does it meet their investment criteria? Why or why not? • Assume the investor can negotiate the purchase price – what would you recommend be paid in order to meet the requirements of both the lender and the investor? 10 Debt • What are the likely sources of debt funding for this project and why?
• Discuss this loan from the standpoint of the lender. How will the lender evaluate this loan? Is this a good lending opportunity? Does it meet their loan criteria? Should the loan be made? Why or why not? • Assume you have flexibility to change the financial plan or the lender’s terms– what would you change in order to determine a forecast that meets the requirements of both the lender and the owner? 10 Land Sale • Discuss the impact of the land sale on the project’s financial performance. Discuss the risk implications. How can these be mitigated? 5 Operations – Revenues • Discuss some of the risks of the drive inn’s revenue. From a practical point, which revenues would be most “at risk”? Discuss the sensitivity of the project’s financial performance to changes in these assumptions. Discuss possible ways that the investor could mitigate some of the risk associated with the revenue stream. • Assume you have flexibility to change the pricing structure (ticket prices for children, adults, etc.). What would you change in order to determine a forecast that meets the requirements of both the lender and the owner? 7.5 Operations – Expenses • Discuss some of the risks of the drive in’s expenses. From a practical point, which expenses would be most “at risk”? Discuss the sensitivity of the project’s financial performance to changes in these assumptions. Discuss possible ways that the investor could mitigate some of the risk associated with the project’s expenses. • Assume you have flexibility to alter the expenses of the drive in. What assumptions would you change in order to determine a forecast that meets the requirements of both the lender and the owner? 7.5 Cash Flow • Discuss why the investor may want to prepare a more detailed daily cash flow forecast for the Drive in Movie Theater. What information would this forecast provide? What issues or problems could you anticipate the forecast identifying? 5 New Scenario • As the financial advisor, you are asked to “bring it all together” in order to present back to your client a scenario that you believe is realistic, manages risk appropriately and also meets the criteria of both lenders and investors.
Determine a new scenario that includes changes to any assumptions that you prefer (revenues, expenses, purchase price, financing assumptions). Provide an explanation as to why you made these changes and why you believe this is an optimal investment case. 10 Sub Total 70 Financial Analysis Overall use of financial tool to support the questions above (use of financial analysis, output, sensitivities and graphs to support your positions above ) 15 Presentation Overall presentation, including quality of PowerPoint, speakers, etc. 15 Total 100 PROJECT DESCRIPTION – East Metro Drive In %uF0A7 Situation o Mr. Woody Berry is the current owner of East Metro Drive In and wants to sell the East Metro Drive In. o He is willing to sell the East Metro Drive In for $1,750,000. This price reflects a land value of $1,250,000 and a price of $500,000 for the remainder of the assets and business. Along with the business, you would get certain inventory ($20,000), accounts receivables ($5,000) and accounts payables ($25,000), resulting in a net working capital of ($0). o You would like to buy the East Metro Drive In, because it appears to be a profitable business, but also because you believe that the real estate is increasing in value substantially every year and you will be able to profit in the future by selling the land to a real estate developer for commercial development o Your plan is to buy East Metro Drive In on August 1, 2006. You will operate the business until December 31, 2017, at which time; you then expect to sell the land. You expect the value of the land to escalate at 10% per year between now and 2015. %uF0A7 Other Capital Assumptions o In your evaluation of the business, you are assuming that you will operate with various levels of working capital (see the model for inputs), including a minimum cash balance. Since Mr. Berry is not including any cash in the assets to be purchased, you will need to fund some cash when you acquire the business.
This is estimated to be 45 days of operating expenses. o Since you have a firm timetable for closing the business, you will depreciation the assets between the time of purchase and the scheduled closing date, which is approximately 11.4 years. (See the model for calculations). %uF0A7 Debt o You plan to finance this acquisition by borrowing 30% of the required funding, and expect to repay the loan using a mortgage style repayment over your expected years of ownership (11 years). You would like to borrow more, but do not know if you can meet the lender’s criteria. o The interest rate will be fixed at a rate equal to 6.50%. You expect the lender will want minimum debt service coverage (principle and interest) of 1.3 x. %uF0A7 Operations (Revenues and Expenses) o The East Metro Drive has various assumptions related to its operating revenues and expenses. (See the model for inputs). You are assuming an inflation rate of 2%, but do not anticipate any other increases in sales revenue due to growth. o In general, revenues include ticket sales, other revenue and revenue from the concession stands. o For the ticket sales, the revenues are driven by the number of parking spaces, the number of guests per car, the number of hours and days the drive in is open. (see the model for inputs) o For the concession revenue, there is a general assumption related to how much each type of customer spends, and what percentage of that concession revenue must be used to pay the food expense (Assumed to be 55%) o The costs associated with the drive in includes employee costs, the cost of movie rental (which is driven by whether 2 or 3 films are showing a night) and other fixed and variable cots (see the model for specific inputs) %uF0A7 Taxes and Distribution o You are assuming that your effective tax rate will be 40% paid on the company’s taxable income. o You are also assuming that all cash (beyond the minimum requirement) will be distributed to you o Your required rate of return on an equity investment is 10%