Please create an excel spreadsheet with formulas for requirement 5 (pages 24-27)and requirement 6 (pages 28-31)only. Please submit in excel format. Both questions and answers are provided for requirement 5 and requirement 6 in pdf but I need them in excel with formulas.

0|Page Capital Investment Decision Case Analysis Cape Chemical co. & Elizabeth Webb Cooper 0 CAPITAL INVESTMENT DECISION CASE ANALYSIS CAPE CHEMICAL CO. & ELIZABETH WEBB COOPER COURSE CODE: F-303 SUBMITTED TO: TAHER JAMIL LECTURER DEPARTMENT OF FINANCE UNIVERSITY OF DHAKA SUBMITTED BY: GROUP: SECTION: A DEPARTMENT OF FINANCE UNIVERSITY OF DHAKA Date of submission: 18 August, 2015 1|Page 2|Page Group Members: Serial 1 Name Maruf Hossain ID 19-013 2 Manjurul Ahsan 19-099 3 Md. Abdul Quyum 19-121 4 Md. Ripon Molla 19-123 5 Raqib Hossain 19-157 BBA 19TH BATCH, SECTION: A DEPARTMENT OF FINANCE UNIVERSITY OF DHAKA 2 Letter of Transmittal August 18, 2015, Taher Jamil Lecturer Department of Finance University of Dhaka Subject: A report on \"Capital Investment Decision- cases and scenerio\" Honorable Sir, This is a great pleasure for us to submit the report on \"Economic Intergration in the prespective of Bangladesh\" as a partial requirement of the BBA program in University of Dhaka. Preparation of this report has been a great pleasure & an interesting experience. It enabled us to know about various financial knowledge more broadly than before. This report helped us tremendously to understand and to know the various term of finance and the use of different financial tools followed by different financial organizations. We have undertaken our sincere effort for successful completion of the BBA program. If we have any unintentional errors and omissions that may have entered into this report will be considered with sympathy. Therefore, we beg your kind consideration in this regard, we will be very grateful if you accept our report and oblige there by. Sincerely, On the behalf of members of group 3|Page Acknowledgement We would like to pay our gratitude to all of the related books, articles, journals, authors and related Web Sites that helped us a lot for the completion of this report before, during, and after the working period. At first we would like to acknowledge the Almighty, who helped us every time and was with us and gave us moral support and strength every moment. We are especially grateful to our honorable course instructor Taher Jamil for giving us valuable suggestions and support to prepare this report. Without his advice and support, it would not be possible for us to prepare this report. 4|Page Executive Summary From ancient to present world finance is playing the most important role to take any business related decision. By applying financial methods we can take decision about any project that we should go ahead or not. Following this convention, we make solution of two cases. The first case is about CAPE CHEMICAL: CAPITAL BUDGETING ISSUES, in this case we have solved some requirements. These are Cape Chemical's weighted average cost of capital (WACC), should they proceed with internal cost of capital and the new equipment with bank loan, evaluation of the strengths and weaknesses of the Cash Payback Period, Discounted Cash Payback Period, NPV, IRR and MIRR. Here we have calculated the Cash Payback Period, Discounted Cash Payback Period, NPV, IRR and MIRR for each alternative. In the second case we are concerned with Investing in a Brewpub: A Capital Budgeting Analysis, in this case we have solved the requirement given. Such as we have estimated the annual cash flows in best case scenario and the worst case scenario, then we calculated the NPV and IRR, in the both scenario. We have showed the impact if there would any change in cost of capital. In the both case we have taken the help of MS excel to solve the problems. 5|Page Table of Contents Chapter 1 ................................................................................................................................... 8 Introduction ............................................................................................................................... 8 1.1 Introduction ......................................................................................................................... 9 1.2 Objective of the study:......................................................................................................... 9 1.3 Scope of the report: ............................................................................................................. 9 1.4 Methodology of the report: ................................................................................................ 10 1.5 Limitation of the study: ..................................................................................................... 10 Chapter 2 ................................................................................................................................. 11 Theoretical Background .......................................................................................................... 11 2.1 Capital Budgeting: ............................................................................................................. 12 2.2 Different methods used in capital budgeting: .................................................................... 12 Net Present Value:............................................................................................................... 12 Internal Rate of Return:....................................................................................................... 12 Real Options Valuation: ....................................................................................................... 13 Equivalent Annuity Method: ............................................................................................... 13 2.3 Necessity of capital budgeting: ......................................................................................... 14 Chapter 3 ................................................................................................................................. 15 Case 1 ...................................................................................................................................... 15 CASE SYNOPSIS .................................................................................................................... 16 BACKGROUND ..................................................................................................................... 16 CHEMICAL DISTRIBUTION.................................................................................................... 17 THE SITUATION .................................................................................................................... 17 PROJECT EVALUATION PROCESS ......................................................................................... 18 Weighted Average Cost of Capital (WACC) ......................................................................... 18 Used Equipment .................................................................................................................. 19 New Equipment ................................................................................................................... 19 REQUIREMENTS .................................................................................................................. 19 SOLUTION OF THE CASE: ......................................................................................................... 20 Requirement - 1 .................................................................................................................. 20 Requirement 2 ..................................................................................................................... 21 Requirement 3 ..................................................................................................................... 21 Requirement 4 ..................................................................................................................... 21 6|Page Requirement - 5 .................................................................................................................. 24 REQUIREMENT 6.................................................................................................................. 28 Requirement 7 ..................................................................................................................... 32 Requirement 8 ..................................................................................................................... 32 Chapter 4 ................................................................................................................................. 33 Case 4 Analysis ....................................................................................................................... 33 4. CASE SCENARIO: .................................................................................................................. 34 4.1: case scenario: ................................................................................................................... 34 Requirements: ......................................................................................................................... 37 3.2: Solution: ........................................................................................................................... 38 Requirement 1 ..................................................................................................................... 38 (Best Case Scenario): Annual cash flow calculation .............................................................. 38 Requirement - 2 .................................................................................................................. 39 Requirement - 3 .................................................................................................................. 40 Requirement - 4 .................................................................................................................. 41 Requirement - 5................................................................................................................... 42 Requirement - 6................................................................................................................... 42 Conclusion ............................................................................................................................... 44 7|Page Chapter 1 Introduction 8|Page 1.1 Introduction Capital budgeting, or investment appraisal, is the planning process used to determine whether an organization's long term investments such as new machinery, replacement machinery, new plants, new products, and research development projects are worth the funding of cash through the firm's capitalization structure (debt, equity or retained earnings). It is the process of allocating resources for major capital, or investment, expenditures. One of the primary goals of capital budgeting investments is to increase the value of the firm to the shareholders. The report discusses about different capital budgeting techniques used in businesses and organizations. 1.2 Objective of the study: The main objectives of this report are: To gather knowledge broadly about Capital Budgeting. To gather knowledge about different capital budgeting tools. To decide the best way to valuate any business. 1.3 Scope of the report: The report has been prepared by collecting information from study materials and internet. The sources were relevant to the main problem of the report. Much data and sources made the report simple. The scopes of the report have very good influence to make it the perfect one. 9|Page 1.4 Methodology of the report: We took help from the study material to understand the conceptual matters. We also followed guidance of our course teacher. We also make analysis with the help of internet browsing. Though the analysis in Microsoft word and Microsoft Excel we have tried to draw some valid conclusions and findings. 1.5 Limitation of the study: I. II. III. Time limitation. Lack of information. Lack of previous experience on study on using financial calculation tools. 10 | P a g e Chapter 2 Theoretical Background 11 | P a g e 2.1 Capital Budgeting: Capital budgeting is the process in which a business determines whether projects such as building a new plant or investing in a long-term venture are worth pursuing. Oftentimes, a prospective project's lifetime cash inflows and outflows are assessed in order to determine whether the returns generated meet a sufficient target benchmark. It is also known as "investment appraisal." Many formal methods are used in capital budgeting, including the techniques such as Accounting rate of return Payback period Net present value Profitability index Internal rate of return Modified internal rate of return Equivalent annual cost Real options valuation 2.2 Different methods used in capital budgeting: NET PRESENT VALUE: Capital Budgeting projects are classified as either Independent Projects or Mutually Exclusive Projects. An Independent Project is a project whose cash flows are not affected by the accept/reject decision for other projects. Thus, all Independent Projects which meet the Capital Budgeting criterion should be accepted. Mutually Exclusive Projects are a set of projects from which at most one will be accepted. For example, a set of projects which are to accomplish the same task. Thus, when choosing between "Mutually Exclusive Projects" more than one project may satisfy the Capital Budgeting criterion. However, only one, i.e., the best project can be accepted. INTERNAL RATE OF RETURN: The internal rate of return (IRR) is defined as the discount rate that gives a net present value (NPV) of zero. It is a commonly used measure of investment efficiency. The IRR method will result in the same decision as the NPV method for (non-mutually exclusive) projects in an unconstrained environment, in the usual cases where a negative cash flow occurs at the start of the project, followed by all positive cash flows. In most realistic cases, all independent projects that have an IRR higher than the hurdle rate should be 12 | P a g e accepted. Nevertheless, for mutually exclusive projects, the decision rule of taking the project with the highest IRR - which is often used - may select a project with a lower NPV. In some cases, several zero NPV discount rates may exist, so there is no unique IRR. The IRR exists and is unique if one or more years of net investment (negative cash flow) are followed by years of net revenues. But if the signs of the cash flows change more than once, there may be several IRRs. The IRR equation generally cannot be solved analytically but only via iterations. REAL OPTIONS VALUATION: Real options analysis has become important since the 1970s as option pricing models have gotten more sophisticated. The discounted cash flow methods essentially value projects as if they were risky bonds, with the promised cash flows known. But managers will have many choices of how to increase future cash inflows, or to decrease future cash outflows. In other words, managers get to manage the projects - not simply accept or reject them. Real options analysis try to value the choices - the option value - that the managers will have in the future and adds these values to the NPV. EQUIVALENT ANNUITY METHOD: The equivalent annuity method expresses the NPV as an annualized cash flow by dividing it by the present value of the annuity factor. It is often used when assessing only the costs of specific projects that have the same cash inflows. In this form it is known as the equivalent annual cost (EAC) method and is the cost per year of owning and operating an asset over its entire lifespan. It is often used when comparing investment projects of unequal lifespans. For example if project A has an expected lifetime of 7 years, and project B has an expected lifetime of 11 years it would be improper to simply compare the net present values (NPVs) of the two projects, unless the projects could not be repeated. The use of the EAC method implies that the project will be replaced by an identical project. 13 | P a g e 2.3 Necessity of capital budgeting: 1. As large sum of money is involved which influences the profitability of the firm making capital budgeting an important task. 2. Long term investment once made cannot be reversed without significance loss of invested capital. The investment becomes sunk, and mistakes, rather than being readily rectified, must often be borne until the firm can be withdrawn through depreciation charges or liquidation. It influences the whole conduct of the business for the years to come. 3. Investment decision are the base on which the profit will be earned and probably measured through the return on the capital. A proper mix of capital investment is quite important to ensure adequate rate of return on investment, calling for the need of capital budgeting. 4. The implication of long term investment decisions are more extensive than those of short run decisions because of time factor involved, capital budgeting decisions are subject to the higher degree of risk and uncertainty than short run decision. 14 | P a g e Chapter 3 Case 1 15 | P a g e 2.1: CASE SCENARIO: CASE SYNOPSIS The case tells the story of Ann Stewart, President and primary owner of Cape Chemical. By most measures, the performance of Cape Chemical has been very good over the last three years. Double-digit sales growth has been achieved, new product lines have been added and profits have more than tripled. The growth has required the acquisition of equipment, expansion of storage capacity and increasing the size of the work force. The unexpected withdrawal of one of Cape Chemical's competitors from the region has provided the opportunity to increase its blended packaged goods sales. However, Cape Chemical's blending equipment is already operating at capacity. To take advantage of this opportunity, additional equipment must be obtained, requiring a major capital investment. It is estimated that Cape Chemical must increase its annual blending capacity by 800,000 gallons to meet expected demand for the next three years Annual capacity of 1,400,000 gallons is necessary to meet projected demand beyond the next three years. The firm has no systematic capital expenditure evaluation process. BACKGROUND Cape Chemical is a relatively new regional distributor of liquid and dry chemicals, headquartered in Cape Girardeau, Missouri. The company, founded by Ann Stewart, has been serving southeast Missouri, southern Illinois, northeast Arkansas, western Kentucky and northwest Tennessee for five years and has developed a reputation as a reliable supplier of industrial chemicals. Stewart's previous business experience provided her with a solid understanding of the chemical industry and the distribution process. As a general manager for a chemical manuf Stewart had profit and loss (P&L) responsibility, but until beginning Cape Chemical, she had limited exposure to company accounting and finance decisions. The company reported small losses during its early years of operation, but performance in recent years has been very good. Sales have grown at double-digit rates, new product lines have 16 | P a g e been added and profits have more than tripled. The growth has required the acquisition of additional land, equipment, expansion of storage capacity and more than tripling the size of the work force. Stewart has proven to be an expert marketer, and Cape Chemical has developed a reputation with its customers of providing quality products and superior service at competitive prices. Despite its business success, Cape Chemical is still a \"large\" small business with Stewart making all important decisions. She recognized the need to develop a professional managerial staff, particularly in the area of finance. Recently, she hired Kate Clarkson as the company's first finance professional and placed her in charge of the company's accounting and finance activities. Cape Chemical's board of directors is composed of Stewart, her brother and the company's attorney. The board's existence satisfies state regulatory requirements for corporations but provides no input to business operations. CHEMICAL DISTRIBUTION A chemical distributor is a wholesaler. Operations may vary but a typical distributor purchases chemicals in large quantities (bulk - barge, rail or truckloads) from a number of manufacturers. They store bulk chemicals in "tank farms", a number of tanks surrounded by dikes to prevent pollution in the event of a tank failure. Tanks can receive and ship materials from all modes of transportation. Packaged chemicals are stored in a warehouse. Other distributor activities include blending, repackaging, and shipping in smaller quantities (less than truckload, tote tanks, 55-gallon drums, and other smaller package sizes) to meet the needs of a variety of industrial users. THE SITUATION The unexpected withdrawal of one of Cape Chemical's competitors from the region has provided the opportunity to increase its blended packaged goods sales. That's the good news. The bad news is Cape Chemical's blending equipment is operating at capacity, thus to take advantage of this opportunity, additional equipment must be obtained, requiring a major capital investment. It is estimated that Cape Chemical must increase its annual blending capacity by 800,000 gallons to meet expected demand for the next three years Annual capacity must increase by 1,400,000 gallons to meet projected demand beyond the next three years. Stewart is considering two alternatives proposed by the company's engineer. The first is the acquisition and installation of used equipment that will provide the capacity to blend an additional 800,000 gallons annually. The used equipment will cost $105,000 to 17 | P a g e acquire and $15,000 to install. The equipment is projected to have an estimated life of three years. The second option is the acquisition and installation of new equipment with the capacity to blend 1,600,000 gallons annually. The new equipment would have a substantially higher cost of $360,000 to acquire and $60,000 to install, but have a higher capacity and an economic life of seven years. The newacturer, equipment is also more efficient thus the cost of blending is less than the blending cost of the used equipment. Stewart asked Clarkson to lead the evaluation process. Stewart thinks the used equipment could be obtained without a new bank loan. The acquisition of the new equipment would require new bank borrowing. The evaluation of each alternative will require an estimate of the financial benefits associated with each. The marketing and sales staff estimated incremental sales of blended package material will be 600,000 gallons the first year and increase by 15% each year thereafter. During the last year, the average selling price for blended material has been near $4.05 per gallon and material cost (not including a cost for blending the material) has been approximately $3.53. The marketing staff anticipates no significant change in either future selling prices or product costs; however they do estimate variable selling and administrative expenses associated with the increased blended material sales to be $.20 per gallon. PROJECT EVALUATION PROCESS The company has no formal process for evaluating capital expenditure projects. In the past Stewart had reviewed investment alternatives and made the decision based on her \"informal\" evaluation. Clarkson plans to develop a formal capital budgeting process using the Cash Payback Period, Discounted Cash Payback Period, Net Present Value (NPV), Internal Rate of Return (IRR) and Modified Internal Rate of Return (MIRR) evaluation methods. WEIGHTED AVERAGE COST OF CAPITAL (WACC) Using input from an investment banking firm, Clarkson estimates the company's cost of equity to be 18%. Their bank has indicated a long-term bank loan can be arranged to finance the new equipment at an annual interest rate of 12% (before tax cost of debt). The bank would require the loan to be secured with the new equipment. The loan agreement would also include a number of restrictive covenants, including a limitation of dividends while the loans are outstanding. While long-term debt is not included in the firm's current capital structure, Clarkson believes a 30% debt, 70% equity capital mix would be appropriate for Cape Chemical. Last year, the company's federalplus-state income tax rate was 30%. Clarkson does not expect the income tax rate to change in the foreseeable future. 18 | P a g e USED EQUIPMENT The used equipment will cost $105,000 with another $15,000 required to install the equipment. The equipment is projected to have an economic life of three years with a salvage value of $9,000. The equipment will provide the capacity to blend an additional 800,000 gallons annually. The variable cost to blending cost is estimated to be $.20 per gallon. The equipment will be depreciated under the Modified Accelerate Cost Recovery System (MACRS) 3-year class. Under the current tax law, the depreciation allowances are 0.33, 0.45, 0.15, and 0.07 in years 1 through 4, respectively. The increased sales volume will require an additional investment in working capital of 2% of sales (to be on hand at the beginning of the year). NEW EQUIPMENT The acquisition of new equipment with the capacity to blend 1,600,000 gallons annually is the second alternative. The new equipment would cost $360,000 to acquire with an installation cost of $60,000 and have an economic life of seven years and a salvage value of $60,000. The new equipment can be operated more efficiently than the used equipment. The cost to blend a gallon of material is estimated to be $.17. The equipment will be depreciated under the MACRS 7-year class. Under the current tax law, the depreciation allowances are 0.14, 0.25, 0.17, 0.13, 0.09, 0.09, 0.09 and 0.04 in years 1 through 8, respectively. The increased sales volume will require an additional investment in working capital of 2% of sales (to be on hand at the beginning of the year). REQUIREMENTS Assume the role of a consultant, and assist Clarkson to answer the following questions. 1) Calculate Cape Chemical's weighted average cost of capital (WACC). Note: round to the nearest whole number. Discuss the theory used by Clarkson to determine Cape Chemical's optimum target capital structure (30% debt and 70% equity). 2) Since the used equipment will be financed with internal capital and the new equipment with a bank loan, should the same discount rate be used to evaluate each alternative? Explain. 3) Explain why an accurate WACC is important to a firm's long-term success. 4) Evaluate the strengths and weaknesses of the Cash Payback Period, Discounted Cash Payback Period, NPV, IRR and MIRR capital expenditure budgeting methods. Prepare a 19 | P a g e recommendation for Stewart regarding the capital budgeting method or methods to use in evaluating the expansion alternatives. Support your answer. 5) Calculate the Cash Payback Period, Discounted Cash Payback Period, NPV, IRR and MIRR for each alternative. For these calculations, assume a WACC of 15%. Based strictly on the results of these methods, should either option be selected? Why? Solution requires preparation of a spreadsheet. 6) Stewart is concerned that the projected annual sales growth rate of 15% for incremental blended material may be optimistic. Recalculate the Cash Payback Period, Discounted Cash Payback Period, NPV, IRR and MIRR for each alternative assuming the annual sales growth rates of 10% and 5%. Assume a WACC of 15%. Does the change in growth rate alter the recommendation made in question 5? Solution requires preparation of spreadsheets. Explain. 7) The projected cash flow benefits of both projects did not include the effects of inflation. Future cash flows were determined using a constant selling price and operating costs (real cash flows). The cash flows were then discounted using a WACC that included the impact of inflation (nominal WACC). Discuss the problem with using real cash flows and a nominal WACC when calculating a project's Discounted Payback Period, NPV, IRR and MIRR. 8) What other issues should Stewart and Clarkson considered before a final decision regarding the expansion alternatives is made? SOLUTION OF THE CASE: REQUIREMENT - 1 WACC Calculation: 20 | P a g e A firm's WACC is minimized when its capital structure (mix of debt and equity) is optimum. Simply stated, a firm's value is maximized when its WACC is minimized; therefore, firm's try to determine the capital structure which will minimize its WACC. The use of debt lowers Cape Chemical's cost of capital because low-cost debt capital is substituted for high-cost equity capital. Debt has a lower cost than equity because to the holder of debt there is less risk. Debt has less risk because the certainty of payments associated with debt (interest and principal) is greater than the payments associated with equity (dividends and stock appreciation). Debt payments are legal obligations thus are paid before any payment to equity shareholders. Because there is less risk associated with debt, the providers of debt are satisfied with a lower but more certain return. The downside of debt is the fixed nature of the payments, thus the use of debt by a firm increases its financial risk. The more debt a firm has, the greater the financial risk or financial leverage. The introduction of debt into a firm's capital structure will at first cause the WACC to decline, but eventually the use of large amounts of debt will cause the WACC to increase. What businesses attempt to achieve is a capital structure which provides the lowest cost of capital because it is there the value of the firm is maximized. REQUIREMENT 2 Different discount rate should be used in these two alternative. The reason is that when a project is financed by equity, the cost is more than f that project would be financed by debt. As the cost of debt is less than that og equity, so the discount rate mustn't be the same. That's why different discounting rate should be used in these two alternative. REQUIREMENT 3 Importance of Accurate WACC: WACC is generally used as the discounting rate to evaluate all the projects of a firm. So it is closely related to all the decision making. In a word, accepting or rejecting a project is totally dependent on WACC. If the WACC is not accurate of a firm, all the decisions will be inaccurate. Because an inaccurate WACC means an inaccurate discounting rate. Suppose the correct WACC is 15% but it is inaccurately calculated as 14%. When a project is evaluated using this wrongly calculated WACC, it will give a higher NPV. Let the NPV at 15% is -1000 and the NPV is 2000 if the discount rate is 14%. So the firm may accept a project which should not be accepted if the correct WACC is used. This must have a negative effect on the long term goal (wealth maximization) of a firm. That's why an accurate WACC is important in the long run for a firm. REQUIREMENT 4 Strength and Weakness of Different Capital Budgeting Techniques: Discounted Cash Payback Period was developed to correct one of the weaknesses of the Cash Payback Period (ignoring the time value of money). The Discounted Cash Payback Period is the number of years required to recover the original investment, but in this case the present value of future cash flows are determined (using the firm's WACC as the discount rate) before the payback period is calculated. The advantages of the Discounted Payback Period include: 1) focuses on future cash flows, 2) incorporates time value of money and 3) places a premium on liquidity (i.e. a quick return of the investment). 21 | P a g e Disadvantages: 1) ignores cash flows beyond the payback period, and 2) does not include an accept/reject feature. Net Present Value (NPV) method is determined by 1) calculating the present value of the future cash flows (using the WACC as the discount rate) and 2) deducting the project's cost from the present value of the future cash flows. If the present value of the future cash flows exceeds the project's cost, the project is said to have a positive NPV. Stated another way, if the project's value (the present value of its future cash flows) exceeds its cost, the project is a good investment and should be accepted. Advantages of this method include: 1) focuses on future cash flows, 2) takes into account time value of money, 3) considers all cash flows associated with the project, 4) assumes cash flows associated with the project are reinvested at the firm's WACC (the WACC reflects the current risk level of the firm) and 5) includes an accept/reject feature. Disadvantages: 1) relatively difficult to explain and calculate, and 2) requires knowledge of a firm's WACC. Internal Rate of Return (IRR) method is calculated by determining the discount rate that will cause the present value of the future cash flows to equal the project's cost. The discount rate is the project's internal rate of return (IRR). If the IRR exceeds the firm's WACC, the project should be accepted. Advantages of this method include: 1) focuses on future cash flows, 2) takes into account time value of money, 3) considers all cash flows associated with the project, and 4) does not require knowledge of a firm's WACC. Disadvantages: 1) relatively difficult to explain and calculate, 2) if the project's future cash flows include some years with cash outflows rather than cash inflows, multiple IRRs may result and 3) assumes the project's cash flows are reinvested at the project's rate of return rather than the firm's WACC. A more reasonable assumption is that cash flows are reinvested at the firm's WACC. 22 | P a g e Modified Internal Rate of Return (MIRR) method was developed to correct the reinvestment rate assumption associated with the IRR. The IRR is calculated by determining the discount rate that will cause the present value of the project's terminal value (the future values of the project's cash flows, compounded at the firm's WACC), to equal the project's cost. Like the IRR, if the MIRR exceeds the firm's WACC, the project should be accepted. Advantages of this method include: 1) focuses on future cash flows, 2) takes into account time value of money, 3) considers all cash flows associated with the project, and 4) does not require knowledge of a firm's WACC. Disadvantages: 1) relatively difficult to explain and calculate, and 2) if the project's future cash flows include some years with cash outflows rather than cash inflows, multiple IRRs may result. Recommendation should include the use of all evaluation methods because each provides valuable information regarding a potential project. Priority should be given to the results of the NPV method because it compares the projects value (the present value of future cash flows, determined by using the firm's WACC as the discount rate) to the projects cost. If a project's value exceeds its cost, it is a good investment. For a more complete discussion of the superiority of the NPV method over the other techniques, see Eugene Brigham and Joel Houston's "Fundamentals of Financial Management". 23 | P a g e REQUIREMENT - 5 Used Equipment: OCF Calculation: PCF Calculation: 24 | P a g e Calculation of Pay Back Period: Discounted Pay Back Period: NPV and IRR: Decision: As the NPV is positive and the IRR is more than the cost of capital, so the project is acceptable. 25 | P a g e The New equipment: OCF Calculation: PCF Calculation: NPV and IRR: 26 | P a g e Pay Back Period: Discounted Pay Back Period: Decision: Like the used asset, the new equipment also has a positive NPV and the IRR is more than the cost of capital. So this alternative is also acceptable like the used equipment. 27 | P a g e REQUIREMENT 6 NPV and IRR with 5% & 10% Growth of Sales: Used Equipment: 10% sales Growth: If the sales growth is 10%, the NPV is positive and the IRR is more than the cost of capital. So the recommendation given in question 5 is unchanged. That means, the alternative is still acceptable. 28 | P a g e At 5% Sales Growth: Decision: The NPV is still positive if the sales growth decreased to 5%. The IRR is 20% which is more than the cost of capital. So the alternative is acceptable and there is no change of the recommendation in question 5. 29 | P a g e New Equipment: At 10% Sales Growth: OCF and PCF: NPV and IRR: Pay Back Period: 5% sales Growth 30 | P a g e OCF & PCF: NPV & IRR: Pay Back Period: 31 | P a g e REQUIREMENT 7 Adjustment of Inflation: In general, using "real" future cash flows and a "nominal" WACC will result in an understated NPV and IRR or both will have a downward bias. If inflation is neutral, impacting revenues and costs equally, the NPV and IRR will be underestimated. Because revenues are usually greater than costs, revenues will increase by a greater dollar amount than costs. The exact impact of combining "real" cash flows and a "nominal" discount rate can only be determined by removing the impact of inflation from the discount rate or adding the impact of inflation to the future cash flows. REQUIREMENT 8 As discussed in the answer to question six, the growth rate assumption is key to the analysis. Before a decision is made, the growth rate assumption needs to be revisited. Stewart appears to understand this and requested additional analysis based on an annual growth lower than the 15% provided by the marketing department. Incorporating an assumption regarding future inflation rates would also improve the analysis. The point of this question is to illustrate to the students that the financial analysis is only part of the decision-making process. 32 | P a g e Chapter 4 Case 4 Analysis 33 | P a g e 4. CASE SCENARIO: 4.1: case scenario: 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 cash flow 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 cash flows 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. The Case Samantha Myers and Grant Patrick graduated from college seven years ago. Since then, they opened a casual, American-fare 80-seat restaurant, Explore Caf, 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 the restaurant 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 Caf 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 is expected 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 worstcase 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. 34 | P a g e 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 Caf 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 per year. 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 straight-line method 35 | P a g e (down to zero) over the usable life of the machine. Right now, Explore Caf 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. To do 1. Estimate the annual cash flows for the brewpub project. Use the \"best case scenario.\" To do this, you will need to calculate the annual revenues and annual expenses for the 10year 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 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? 36 | P a g e Requirements: 1. \"Best case scenario.\" Calculation of annual revenues and annual expenses for the 10year 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 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? 37 | P a g e 3.2: Solution: REQUIREMENT 1 (Best Case Scenario): Annual cash flow calculation Calculating OCF: Calculating PCF: 38 | P a g e REQUIREMENT - 2 NPV and IRR (Best Case Scenario): Decision: The project of Samantha Myers and Grant Patrick to convert their cafe from BYOB to brewpub system gives a positive NPV. So total cash outflow of this project is less than the present value of total cash inflow from the project. The required rate of return of this project is 8%. Internal Rate of Return (IRR) is 27% which ish more than their required rate of return. So the project is acceptable for Samantha Myers and Grant Patric. 39 | P a g e REQUIREMENT - 3 Worst Case OCF: PCF Calculation: NPV is 682694 and the IRR is 27% in the best case scenario. But NPV decreased to 755 and the IRR decreased to 8%in worst case. NPV and IRR decreased but these are still favorable. NPV is not negative or zero under worst case scenario and the IRR is not less than the cost of capital. This indicates that the project is much safe to invest. They can invest in the project even in the worst possible situation. 40 | P a g e REQUIREMENT - 4 Best Case with Renovation: PCF, NPV, IRR Calculation: If the partners go for the renovation under the best case scenario, the NPV goes down $493,478.51 from 682694. So the NPV is decreased by 189216 if renovation will be taken. The IRR will be decreased by 11% (27% in the best case scenario and 16% in the best case scenario with renovation). So the renovation worsen both the NPV and IRR of the project. So the pair should continue their business with best case scenario without any renovation. 41 | P a g e REQUIREMENT - 5 Impact of Change in Cost of Capital: Discounting rate is used to discount the cash flow and to calculate the NPV. A change in cost of capital may have significant effect in the decision of a project. If we consider the cost of capital as 8%, the project is acceptable in both the best case and the worst case scenarios. But if the cost of capital is increased to 10% from 8%, then the best case scenario will still be acceptable. But the project will not be acceptable in the worst case situation because the NPV will be negative (-55816) and the IRR (8%) will be less than the cost of capital. REQUIREMENT - 6 Some consideration in Capital Investment Decision: Samantha and Grant used the popular techniques of capital budgeting. So their estimation about the success in this project is not realistic. But their estimation will be more successful if they would take help of sensitivity analysis. Sensitivity analysis measure the significance of a single variable on the project's cash flow. This analysis would help them to know which variable is most important for NPV or Sales Revenue etc. For tax benefit they can also consider the option of taking loan instead of all equity financing in the project. If the project would be financed with debt. As a result there would be possible that the worst case NPV will be more than that of without any debt. So the IRR would be increased. The decision would be more materialistic. 42 | P a g e Chapter 5 Conclusion 43 | P a g e Conclusion By solving the two cases we learned how to apply the capital budgeting techniques to make real life financial decisions .Capital budgeting is the most important mechanism to make financial decisions. We have used different capital budgeting methods to solve the cases and made decision .Cash Payback Period, Discounted Cash Payback Period, NPV, IRR and MIRR, WACC, ARR are used to solve the cases and make investment decisions. In our report we completed all the requirement .The capital budgeting techniques helps to determine the best way to decide the most prudent decision in organization. The financial managers take decisions using the different capital budgeting techniques. After all the managers have to find out the proper ways to solve different decisional activities. 44 | P a g e