answer the question on the word document using the attachments

The Local Caf: New Project Analysis Richard Borgman and Kirk Ramsay Kirk Ramsay was a serial entrepreneur. Another way to characterize him was as a small businessman who enjoyed new challenges and new adventures. After earning his business degree at the state university, Kirk built several successful businesses. He designed and built homes, owned several rental properties, and ran a property management business, and continued to follow his musical passion by playing lead guitar in a successful local \"cover\" band. In the small city in New England where he lived, he saw a need for a local no-frills, good food, breakfast and lunch eatery located downtown, where the national chains did not locate. He had found his next potential projectwhich he planned to call \"The Local Caf.\" And he had found the empty storefront in which to put the restaurant. The landlord had urged Kirk to make his decision in a few days, because he claimed to have another interested party. To make the decision, Kirk would calculate NPV, IRR, and payback period. The restaurant was part of a clearly defined life plan. His children were now grown; Kirk and his wife hoped to work about ten years more to solidify their retirement, and in ten years move to a warmer climate, hopefully fully retired or, if necessary, semi-retired. So if opened, Kirk expected to own and operate the restaurant for about 10 years. After establishing it as a successful business, he planned to sell the restaurant as part of his plan to move at least part of the year to a warmer climate. The space Kirk had found was about 3,000 square feet, enough for a kitchen and ten tables. The monthly rent was $600, leased \"triple net,\" meaning that the renter was responsible for all other costs, including utilities. He anticipated other building-related costs of $300/month for insurance and $500/month for utilities (water, sewer, gas, trash removal). He believed these costs would increase at least with inflation, which he assumed to be about 4 percent per year. To prepare the space, which had not previously been a restaurant, Kirk anticipated the following costs: stove, $1,600; double fryer, $800; sinks, $700; preparation tables, $1,500; pots and pans, utensils, glasses, and dishes, $1,500; 10 tables and 40 chairs, $2,000 in total. The costs to renovate the space (plumbing, electric, carpentry, and signage) would be $10,000. All these costs were subject to depreciation. Kirk assumed a 5-year depreciable life and prepared a modified accelerated cost recovery system (MACRS) schedule. For the first year, Kirk assumed the following revenues. The average breakfast patron would spend about $7.50. He assumed that with a 40-person capacity, there would be about 30 people per sitting, and about two \"turns\" per day. The average lunch patron would spend about $11.50. There would be 30 people per sitting, and about three \"turns\" per day. The restaurant would close in the early afternoon and would not serve dinner. The plan would be for the restaurant to be open 5 days a week (260 days per year). To control costs, the restaurant would not have an extensive menu, serving standard breakfast fare (eggs and bacon and the like) and having rotating specials for lunch plus a standard menu. Kirk anticipated \"consumables and perishables\" costs to be about 60 percent of revenues. Other non-labor costs would be about 3 percent of revenues. There would be three cooks working three overlapping 8 hour shifts, making $11 an hour. There would be two servers working two overlapping 8 hour shifts, making $4 per hour (plus tips). Finally, there would be a manager who normally operated a register as well, making $12 per hour for a daily 8 hour shift. Over the following years, Kirk expected revenues to increase at least with inflation (as before, assumed to be about 4 percent per year). Costs would remain at the current percentage of revenues. Net working capital (NWC) was assumed to be 5 percent of revenues in the upcoming year (for example, NWC at t = 1 equals 5 percent of t = 2 revenues). The change in net working capital was calculated as NWC in the current year minus NWC in the previous year (thus change in NWC for t = 1 is NWC (t = 1) minus NWC (t = 0). Kirk faced a 35 percent tax rate. He could borrow from a local bank at 9 percent. The loan would have five-year amortizing, monthly payments, and no prepayment penalty. The loan was secured by the equipment and, as was usual, required a personal guarantee by Ramsay. He estimated his cost of equity and financing weights using data from a sample of 84 publicly traded restaurant firms compiled by Answath Damodaran (http://pages.stern.nyu.edu/~adamodar/). These firms had an average unlevered beta (unlevered means the beta if there was no debt in their capital structure) of 0.69, an average debt-to-equity (D/E) ratio of 0.2757, and an average correlation with the market of 0.3053. He knew that he could calculate a levered beta using the following simplified Hamada equation: BetaLevered = BetaUn-levered * (1+(1-T) * (D/E)) Ramsay assumed that his mixture of debt and equity financing (D/E) would be close to the industry average. Ramsay also realized that he was relatively undiversified in comparison with the stockholders of a large publicly traded firm. Damodaran suggested that an undiversified beta, called total beta, could be approximated by dividing the \"market beta\" (the firm's levered beta) by the correlation between the market and the individual restaurant firms. Radd = Adjustment factor * BetaUn-levered * (Rm - Rf) The security market line equation from the capital asset pricing model, used to calculate the cost of equity, is: RL = Rf + BetaLevered * (Rm - Rf) Cost of Equity = Re = RL + Radd where MRP is the market risk premium, gRF is the risk-free rate, and beta in this case is the total beta. He planned to calculate his project weighted average cost of capital (WACC) as, WACC = weRe + wdRd(1 T) where \"w\" are the weights of equity and debt, respectively, and \"g\" refers to the costs of equity and debt, respectively. T is the tax rate. In ten years, when Kirk expected to sell the restaurant to fund his retirement, he knew the value would depend on cash flow. To be conservative, he valued the sale in ten years as a no-growth perpetuity; that is, the firm would be worth the present value of a perpetual series of constant cash flows (based on t = 10 cash flow). Also, to be conservative, he assumed that the sale would be fully taxable. Kirk wanted a payback within three years, assuming a risk-free rate of 3.04 percent and an equity risk premium of 5 percent. Questions: I have worked out an excel template for your analysis. However, all the results are hard-coded numbers. You need to figure out the excel formula to replicate my analysis. Such an exercise also can server as a template for your other work in project analysis. Once you have created a spreadsheet to analyze the project, you should be able to create additional scenarios. Here are some of the changes you need to make a. b. c. d. e. Reduce the lunch price from 11.5 to 11. Inflation is scaled down from 4% to 3%. Tax rate is scaled down from 35% to 30%. Average S&P500 risk premium is adjusted from 5% to 5.5%. Cost of debt adjusted from 9% to 8%. Please incorporate the above changes into your Excel worksheet and update the calculations. With the updated calculations, please answer the following questions. 1. What is Ramsay's cost of capital to use in the analysis? 2. Calculate the expected sale price of the restaurant at the end of year 10 (that is, at t = 10). 3. Using all these answers, calculate the project's NPV, IRR, and payback period. Based on these calculations, should Ramsay go ahead with the project? 4. If the payback criteria a good indicator for the project? Appendix 8.1Industry-Specific Values for Unlevered Beta and Unsystematic Risk Adjustment Factor Note All the numbers are either from the question or from calcuations. you need to workout the formular. With the completed excel fomula, you can work out the results from different scenarios. Initial cost stove double fryer sinks preparation tables pots and pans, utensils, etc 10 tables and 40 chairs renovation total Initial 1,600 800 700 1,500 1,500 2,000 10,000 18,100 breakfast lunch total yearly 117,000 269,100 386,100 spending 7.5 11.5 rent other building related costs utilities sum total yearly 7,200 3,600 6,000 16,800 18,100 monthly 600 300 500 3 cooks 2 servers 1 manager total yearly 68,640 16,640 24,960 110,240 # people 3 2 1 consumables and perishables other cost total 231,660 11,583 243,243 Revenue operation expense cost of labor 39,231 non labor cost