Case Study #1 - Business Finance

xlsx

School

Edison State Community College *

*We aren’t endorsed by this school

Course

229

Subject

Finance

Date

Jan 9, 2024

Type

xlsx

Pages

Uploaded by EarlUniverseZebra32

Welcome to Case Study #1 Chapter 7, Case Study Table of Contents: Red Tab: Narrative of the Case you will work (Problem 20 a-c, page 188-189) Blue Tabs: How To Examples for formulas and set up tables to calcuate IRR and NPV Green Tabs:Student Tabs to Insert Your Work. Instructions: Review the Case Review and practice the examples for IRR and NPV Work problems within the green tabs in this workbooks Save this workbook using your last name in the title of the file Submit your work via the Blackboard Assignment folder

Case Study #1 - Chapter 7, The Techniques of Capital Budgeting Textbook, Page 188 and 189, Problem 20.a-c As chief financial officer you must approve or reject projects based upon the company’s traditional capital produced by two projects suggested by the marketing department. Half of the marketing team favored one project and half favored the other project. The cash flow analysis indicated the following cash flows for each project, one called Peanut Butter and the other Chocolate: Cash Flows in: This is shown as a negative Year 0 Year 1 Year 2 Year 3 in the textbook - that is an error ($3,000) $0 $1,000 $0 Chocolate $0 $9,053 $0 ($9,053) Both projects look pathetic. The Peanut Butter project actually has more dollar outflows than inflows. The Chocolate project does not begin for another year and has future inflows equal to outflows. Nevertheless, in order to evaluate the projects in terms of company policy, you compute the internal rate of return of each project. Sure enough, the internal rate of return of Peanut Butter is –42.265%. The internal rate of return of Chocolate is 0%. Because your company requires a rate of return of 15%, you send out the bad news that both projects are rejected. Several days later the whole marketing department runs into your office with some startling news: In a seminar on working together, they learned the value of teamwork, and they suggest that the two projects be put together to form one great project. None of the revenues or expenses will change, so the combined project looks like this: Cash Flows in: Year 0 Year 1 Year 2 Year 3 ($3,000) $9,053 $1,000 ($9,053) When plugged into the computer, the project produces an internal rate of return of 20%. Because this internal rate of return exceeds the company-required rate of return, it looks like both projects can go ahead. a. Verify that the internal rates of return have been computed correctly. b. Find the net present value of each project and the combination using a discount rate of 15%. c. Discuss what you would do, and why internal rate of return did or did not work. budgeting method: the IRR . Your financial analysts recently calculated the cash flows that would be Peanut Butter Combined Project

Calculating IRR in Excel This worksheet demonstrates examples of using an Excel function to find the internal rate of return (IRR). Example 1) Yr 0 -37776 Yr 1 12000 2) Yr 2 15000 Yr 3 18000 IRR 8.663% 3) 4) Leave the "Guess" field blank. 5) IRR is the internal rate of return of a cash flow stream associated with an investment. The IRR formula [=IRR(values,guess)] consists of two fields. Values is an array or a reference to cells that contain numbers for which you want to calculate the internal rate of return. Guess is a number that you guess is close to the result of IRR. IRR is the internal rate of return for a series of cash flows represented by the numbers in values. These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur at regular intervals, such as monthly or annually. IRR is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods. The "Values" field must contain at least one positive value and one negative value in order to calculate IRR. If there is no cash flow for an individual period within the investment duration, it must be represented as zero. IRR will return a periodic interest rate which may need to be annualized in order to compare to the cost of capital in making investment decisions. This example assumes an estimated cost of $37,776 for beginning a business, and income of $12,000, $15,000, and $18,000 in the three years that follow. Follow these steps to use Excel's IRR function to find the internal rate of return for this investment: Select the output cell for the solution. (For this example, use cell J34 .) Click the function button ( fx ), select All in the left pane to display all Excel functions, and double-click IRR in the right pane. Note: When you click on IRR, the formula is shown at the bottom of the Paste Function dialog box: =IRR(values,guess) . The cursor automatically appears in the "Values" field, prompting you for the required data. To select the data range for this calculation, select the fields (click and drag) that constitute your data range. This data range appears in the "values" field. (For this example, the range is J28:J33 .) The formula bar should now contain the formula: =IRR(J28:J33) . After you have entered the required data, click OK. Excel displays the result in the output cell. The result for this example is 8.663%, which indicates the expected return on the $37,776 initial investment after five years.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Calculating Net Present Value in Excel This worksheet demonstrates examples of using an Excel function to find the net present value of an investment. Example 1) % 10% Yr 0 -10000 2) Yr 1 3000 Yr 2 4200 Yr 3 6800 NPV $1,188 3) 4) 5) Net present value is calculated using a discount rate (which may represent an interest rate or the rate of inflation) and a series of future payments (negative values) and income (positive values). The NPV formula [NPV(rate,value1, value2,...)] consists of three fields. Rate is the periodic discount rate over the length of the project. Value1 and Value2 are 1 to 29 arguments representing the payments and income. These values must be equally spaced in time and occur at the end of each period. NPV uses the order of Value1, Value2,… to interpret the order of cash flows. Be sure to enter payment and income values in the correct sequence. Arguments that are numbers, empty cells, logical values, or text representations are counted. Empty cells, logical values, text, or error values are ignored. Excel’s method of calculating NPV assumes that the first value occurs one period from the present. Many investments projects call for an immediate initial investment, followed by a stream of cash flows in subsequent periods. If this is the case, you must exclude the initial investment from the NPV calculation in Excel by calculating the NPV of the cash flow stream and subtracting the initial investment from the NPV calculation to get the actual NPV for the project. This example assumes an investment of $10,000 one year from today; an annual income of $3,000, $4,200, and $6,800 in the three years that follow; an annual discount rate of 10%. Follow these steps to use Excel's NPV function to find the net present value for an investment: Select the output cell for the solution. (For this example, use cell B57 .) Click the function button ( fx ), select All in the left pane to display all Excel functions, and double-click NPV in the right pane. Note: When you click on NPV, the formula is shown at the bottom of the Paste Function dialog box: =NPV(rate,value1,value2,...) . The cursor automatically appears in the "Rate" field, prompting you for the required data. Enter the rate. (For this example, the rate is 10%, or .10 .) Enter data for the remaining data fields. For "value1," enter -10000 . For "value2," enter 3000 . (When you click in the value2 box, a box appears for "value3," and so on) For "value3," enter 4200 . For "value4," enter 6800 . Note: As the cursor moves from field to field, the definition of each respective field is shown at the bottom of the NPV function box. The formula bar should now contain the formula: =NPV(10%,-10000,3000,4200,6800) . After you have entered the required data, click OK . Excel displays the result in the output cell. The result for this example is $1,188. In this example, you include the initial $10,000 cost as one of the values because the payment occurs at the end of the first period.

Problem 20.a. Verify IRR 20.a. Verify that the internal rates of return have been computed correctly. Use the cells below to show your work. Calculate the IRR Calculate the IRR Verify IRR of 42.26% Chocolate Verify IRR of 20% Combined Yr 0 -3000 0 Yr 0 -3000 Yr 1 0 9053 Yr 1 9053 Yr 2 1000 0 Yr 2 1000 Yr 3 0 -9053 Yr 3 -9053 IRR -42.26% 0% IRR 20% Then Prove Your Calculated IRR Then Prove Your Calculated IRR Results in a NPV of Zero Validate the NPV = 0 Results in a NPV of Zero Validate the NPV = 0 Chocolate Combined % 42.260% 0.000% % 20% Yr 0 -3000 0 Yr 0 -3000 Yr 1 0 9053 Yr 1 9053 Yr 2 1000 0 Yr 2 1000 Yr 3 0 -9053 Yr 3 -9053 NPV ($1,864.66) $0.00 NPV ($0) Peanut Butter Peanut Butter

Problem 20.b & c. Find the NPV 20.b. Find the net present value of each project and the combination using a discount rate of 15%. Use the cells below to show your work. Calculate NPV Calculate NPV Calculate NPV Peanut Butter Chocolate Peanut Butter Chocolate Combined Combined Rate of Return 15% 15% 20% 20% 15% 20% Yr 0 -3000 0 -3000 0 -3000 -3000 Yr 1 0 9053 0 9053 9053 9053 Yr 2 1000 0 1000 0 1000 1000 Yr 3 0 -9053 0 -9053 -9053 -9053 NPV ($1,951) $1,669 ($1,921) $1,921 ($282) ($0) 20.c. Discuss what you would do, and why internal rate of return did or did not work. Insert in space belo The IRR of 15%, and 20% doesn’t work because the Peanut Butter project still has more dollar outflows than the Chocolate project does. But when you combine them The 15% still has more outflow but the 20% is perfect zero. Combined both the Peanut Butter and Chocolate projects balaonce out the revenues and expenses. I would say they are both are a go and make the whole marketing department happy!

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version