Tough project that requires assistance. The analysis has been attached and the requirements have also been attached in a separate attachment. Thank you.

Investing in a Brewpub Two recent college graduates own a restaurant and want to decide whether to invest in a brewpub system, which would allow the pair to sell beer on tap to their customers. The business owners must complete a thorough analysis of their planned investment using the concepts of operating cash flows, working capital investment and capital expenditures. They need to have a keen understanding of relevant versus non-relevant cash flows. Further, they must use these estimates in order to come up with the net present value (NPV) and internal rate of return (IRR) of the investment under different realistic business scenarios. The pair also must use sensitivity analysis to see how their investment decision may or may not change as a result of varying costs of capital. In the end, the pair needs to decide whether to invest in the brewpub in light of their full analysis. Analysis Samantha Myers and Grant Patrick graduated from college seven years ago. Since then, they opened a casual, American-fare 80-seat restaurant, Explore Cafe, close to their college campus. Their clientele mainly consists of undergraduate and graduate students from the college (thus a lot of their business falls outside of the summer months), and an enthusiastic group of local residents who love to come to the restaurant on a regular basis year-round. Currently the restaurant is BYOB, meaning, the restaurant does not sell alcohol but allows customers to bring in their own bottles of wine and beer for a small \"corkage\"fee. After much consultation and market research (costing them roughly $2,000 and considerable time and effort), Samantha and Grant decided that the way to grow their small restaurant was to include a brewpub system, thus allowing therestaurant to offer beer on tap to their customers and do away with the BYOB label. Samantha did some research on the cost of a new brewpub system. She estimates that they will need about 1,000 square feet of space in the store to accommodate a 7 barrel (bbl) system. Currently they do not have the space available but it just so happens that the retail space next door to Explore Cafe is available for rent. The space costs $3,000 per month but Samantha thinks they can negotiate the rent down to $2,500 per month because of their good relationship with the landlord. However, the space is not equipped to handle the brewpub machinery. After talking with several contractors (with permission of the landlord) Samantha expects that initial construction costs could be as high as $250,000. In a barrel of beer, there are 31 gallons of beer. There are 8 pints in a gallon. Samantha estimates that each seat in the restaurant will require about 7 barrels of beer (best case scenario) per year. She is basing this on the expected number of patrons and on the number of beers each patron isexpected to order, on average, throughout the day. She uses some scenario analysis to also include an estimate of 5 barrels of beer per year per seat for a worst-case scenario outlook. They plan to sell 10 types of beer but all will have the same ingredient costs and sales price. The cost of a high-quality brewpub system is $300,000. This includes the heater, fermentation tanks, chiller, stainless steel beer faucets, hoses, valves, and carbonator gauges. Samantha looks into some options for ingredients and finds the best deal from an outside beer retailer. The ingredients will cost $4,000 to make 10 barrels of beer. These costs are expected to increase 5 percent per year based on projected agriculture prices. Based on discussions with the brewpub machinery manufacturer, Samantha estimates that it will cost about $15,000 per year (after the first year of operations) in maintenance expenses to keep the machinery running properly. Meanwhile, Grant looked into any additional costs (beyond ingredients and rent) that the Explore Cafe would encounter when they open the brewpub aspect to their business. He figures that he would need at least three additional servers per day at a cost to the restaurant of $80 per day per server (the servers earn most of their income through tips, and servers typically work about 320 days out of the year). They also plan to hire a person to run the brewery machinery on a full-time basis at a starting salary of $40,000 peryear. Generally, Grant and Samantha like to increase server and employee salaries by about 3 percent per year. Insurance costs would increase since the restaurant will now serve alcohol. Grant figures the insurance cost will be an additional $3,000 per year with the assumption that this will increase by 5 percent in five years (based on his discussions with the insurance agent) and hold steady at that new rate for the remainder of the time. The equipment itself will require additional utilities costs beyond what the restaurant operates at without the brewpub option. Grant estimates that utilities costs (water and electricity) will amount to an additional $24,000 per year over what the restaurant currently pays in utilities expenses. License fees and renewals were not something Grant initially thought about when opening the brewpub but after some research, he found that the Explore Caf would be required to pay a $65,000 initial license fee before they open the doors to the new brewpub. License renewal for the first year of sales and every year thereafter is expected to be $700 per year. This is the typical cost structure for licensing fees for this particular city. Grant and Samantha also decided that they would put a big effort into an advertisement campaign for the new brewpub. The pair does not do much advertising now other than flyers at the college and around the neighborhood and an occasional ad in the city newspaper. With the addition of the brewpub they plan to increase advertising expenses to around $80,000 per year to cover costs of outsourcing their Internet presence (website, Facebook, Twitter, etc.) and more substantial ads in local newspapers. They decide to pay a media company fee of $20,000 before the brewpub opens to immediately redesign their website and to begin advertising. As for sale price, the pair decides to set the price at $5 per pint during the first year of operation. They hope to increase this price by 3 percent each year thereafter. Samantha also realizes that they will need to store up on some inventory and receivables before they ever sell a single pint of beer. The increased inventory and receivable investment will be, she assumes, $10,000 just to get them started. Samantha figures that they will unwind the investment in inventory and receivables when the brewpub machinery's economic life is complete. It seems that brewpub systems have a 10-year economic life. They assume that they can sell the materials from the brewpub system once the useful life is complete. They estimate that they can get about $20,000 back from the scrapped material. After talking with their accountant, they decide to depreciate the brewpub system using the straightline method (down to zero) over the usable life of the machine. Right now, Explore Cafe pays a tax rate of 30 percent and this is expected to continue for the duration of the brewpub machinery's useful life. Grant estimates the cost of capital for the restaurant to be 8 percent based on current and long-term loan rates and they would need to borrow enough to cover the one time costs. To do: 1. Estimate the Operational Cash Flows for the brewpub project by creating a proforma income statement. Use the \"best case and \"worse\" case scenarios.\" To do this, you will need to calculate the annual revenues and annual expenses for the 10-year project, any changes in net working capital, and any changes to capital expenditures. Describe all assumptions and calculations you used to arrive at the final free cash flows. 2. Calculate the NPV and IRR of the project given the information presented using the \"best case scenario.\" Should Samantha and Grant go ahead with the brewpub investment? Why or why not? 3. What would be the impact on NPV and IRR if the \"worst case scenario\" occurs? Would this alter Grant and Samantha's decision whether to invest in the brewpub? Describe how you found this result (also show in the spreadsheet). 4. Suppose they are operating under the best case scenario and they decide that in year 5 they would like to do major renovations to the restaurant (a capital expense). They figure this will cost an additional $1,000,000 in year 5. Along with the renovations, they figure they could increase the price of the beer to $7 per pint and keep it at that price for the duration of the project. How do these changes impact NPV and IRR? Is it worth it for the pair to go forward with the renovations? Describe how you found this result (also show in the spreadsheet). 5. Would there be a significant impact to Samantha and Grant's brewpub decision if there were a change in the cost of capital? Describe how you found this result (also show in the spreadsheet). 6. Are there any other issues that you think might influence the pair's investment decision? What, if anything, have Samantha and Grant not considered in their capital budgeting analysis? NOTE: show your calculations