Math is my worst subject, and I don't know how to do formulas in Excel. Assignment instructions:

I need help with the information below. Math is my worst subject, and I don't know how to do formulas in Excel. Any help is appreciated!

Assignment instructions:

Answer the 6 questions associated with Case 8.1 The Local Café: New Project Analysis on page 284-286 of the Entrepreneurial Finance text. Use spreadsheet software to answer the questions and copy and paste your results into your submitted document - APA guidelines suggest that tables and or exhibits be placed at the end of your paper (after your references) and referenced within your paper.

Case 8.1 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 project—which 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 (Table 8.10).

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).

Table 8.10 Modified Accelerated Cost Recovery System 5-Year Schedule

Year Percentage

1 0.2

2 0.32

3 0.192

4 0.1152

5 0.1152

6 0.0576

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:

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.

The security market line equation from the capital asset pricing model, used to calculate the cost of equity, is:

r E = rRF + (MRP)β

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.

Case Questions

Create ten years of forecast cash flows. It will be convenient to use spreadsheet software, such as Excel.
What is Ramsay's cost of capital to use in the analysis?
Calculate the expected sale price of the restaurant at the end of year 10 (that is, at t = 10).
Using all these answers, calculate the project's NPV, IRR, and payback period. Based on these calculations, should Ramsay go ahead with the project?
If you have created a spreadsheet to analyze the project, you should be able to make additional scenarios. What if revenues are not as good as Ramsay has forecast? What if costs are higher?
Are there any other issues you think are important? Considering the scenarios you have created, and any other issues you feel are important, what would you recommend Ramsay do?