make a spreadsheet to show work.

Assignment This assignment is a capital budgeting case with an extra twist in which you also need to figure out the firm's cost of capital. This involves a step-by-step process in which you will calculate the cost of debt, the cost of equity, and the weights of debt and equity. (Note that the firm uses short- term debt in the form of a revolving line of credit, which has a particular interest rate, as well as long-term debt, which has a different yield.) Please show your work in an excel spreadsheet that uses cell references and formulas. In calculating the cost of equity, you can use CAPM. You will need to find beta, which can be done by regressing the returns for the company on the market returns over the same period. That is, the company's returns would be the dependent variable, and the market returns will be the independent variable. The slope of this regression is the beta for the company, similar to slides 19 & 20 in the Risk_Return slides. You can find the beta using a shortcut in excel, with the SLOPE function. You can look up the slope function (=slope) in excel for the exact inputs (which are the company returns and the market returns, which are given in the case). The slope calculated in excel will be the beta to be used in CAPM to find the cost of equity. Here is the case: International Research Journal of Applied Finance ISSN 2229-6891 Vol. VI Issue - 1 January, 2015 Case Study Series Project Evaluation: Tortuga Fishing Equipment Company Judson W. Russell Introduction Brooks Hamilton recently accepted a job with Tortuga Fishing Equipment Company (Tortuga) in the company's finance department. His first few assignments were fairly straightforward and Brooks relied on his background in both accounting and finance to get his career off to a great start. His manager, the company's Chief Financial Officer (CFO) was impressed with his work and decided to put Brooks on a new assignment. The firm was embarking on a new project which would define its future over the upcoming years. Given the importance of the project and high degree of visibility with the firm's senior management, Brooks was flattered to be asked to assist and eager to show that he was up to the task. The finance department was tasked with preparing an analysis to make a decision between two competing project plans which could very well decide the future of Tortuga in the competitive fishing equipment industry. The Chief Executive Officer (CEO) wants to have an answer from finance and expects a thorough analysis very quickly. The Company Tortuga is an Islamorada, Florida based company specializing in manufacturing high-end fishing rods and reels. Tortuga was founded by a retired university professor who fished all of his life and wanted to create the best equipment possible to handle a variety of fishing conditions and fish species. He partnered with an engineer who ran a machine shop to produce some prototype reels and supplied these to commercial fishing captains as test market research. The equipment produced by Tortuga was a significant improvement over the current line available and orders were strong. Through the years, the company made some modest improvements to their original prototype and had become an industry leader Tortuga's products are used by tournament fishing teams around the world. Over the past decade. tournament fishing has grown to become a big business with corporate endorsements and prize money. This growth has made what was once a recreational vocation into a full-time profession for some anglers. Judson W. Russell, Ph.D., CFA, Clinical Professor of Finance, Bclk College of Business, University of North Carolina Charlotte Fictitious company created to illustrate corporate finance principles Page 1 International Research Journal of Applied Finance ISSN 2229-6891 Vol. VI Issue - 1 January, 2015 Case Study Series The company recently launched an extensive research and development effort focused on a new flyrod and reel designed for one particular species of fish, the Atlantic Tarpon (Megalops atlanticus). Tarpon are long-lived fishes that migrate in the warmer climes of the Caribbean Sea, Gulf of Mexico, and along the Atlantic Ocean coastlines. Although the fish can reach lengths of eight feet (-2.4 meters) and weights of 280 pounds (127 kilograms), they inhabit the shallow flats and exhibit acrobatic leaps when booked. These traits make tarpon a popular game fish for anglers. Fishing gear needs to be sturdy to handle the power of these fish and Tortuga had developed products for this niche market which were allowing anglers to be successful in their angling pursuits. Recently, several sponsors had come together to launch competitive angling events called tournaments, where the best anglers vie to catch, and then release, the most and largest tarpon. Winners may receive up to $50,000 in a single weekend tournament and the difference between winning and losing could be a few pounds. With so much money at stake, tournament teams purchase the best gear available and are always looking for any competitive advantage with their equipment. Tortuga is looking to capitalize on this trend by offering a new line called the Tortuga Tarpon Classic. This new line incorporates the latest material and design improvements and is predicted to be the "gold standard" for all serious tournaments anglers. Tortuga plans to offer the Tortuga Tarpon Classic to recreational anglers as well to capture the growing demand by affluent anglers who want the same high-quality gear as the professionals. Financial Information Tortuga began with a modest amount of capital that the founder had managed to save during his years in academia. As the firm grew, its financing needs expanded as well . Through the years Tortuga had developed and maintained a strong relationship with a large bank which provided short-term working capital funds in the form of a revolving line of credit. When a funding need arose, Tortuga would draw from this line of credit and then repay the short-term draw as cash flowed back to Tortuga The $200 million revolving line of credit currently has $25 million drawn at an interest rate of 3-month Libor plus 350 basis points. The remaining S175 million credit line can be assumed to have no fees associated with it'. Brooks looks up the most recent 3. month U.S. dollar Libor rate here: https://www.wsj.com/market-data bonds Long-term financing was also in place in two forms. After several years of revenue and earnings growth, Tortuga issued ten million shares of common stock at an issue price of $10 per share. The firm used this $100 million in funding to increase production lines and build a global presence by opening an additional manufacturing facility in Panama. Brooks finds the current price per share for Tortuga to be $25. Two years ago, Tortuga issued a 10-year bond for S80 million face value. Each S1,000 par bond carries a coupon of 5%. The bond pays interest semi-annually and is currently trading in the market at 102.50 as a percent of par. The company has a 21% corporate tax rate. Libor is an acronym for London Interbank Offered Bank, which is a standard floating interest rate benchmark for credit facilities. A basis point is equal to 1/100 of 1%. One percent is 100 basis points Typically a bank will charge a facility fee for the entire credit facility, S200 million in this case and an interest rate based on utilization. We assume no facility fee for simplicity. Page 2 International Research Journal of Applied Finance ISSN 2229-6891 Vol. VI Issue - 1 January 2015 Case Study Series The firm calculates its required return on equity with the Capital Asset Pricing Model (CAPM) using the 10-year Treasury yield as the risk-free rate and 5.5% as the historical market risk premium. He looks up Treasury yields here: https://www.bloomberg.com/markets/rates-bonds government-bondos CAPM - Risk-free rate + B (Market Risk Premium) The annual stock returns versus the market are shown in Figure 1 below for the past 10 years Beta is calculated by regressing. While there are a variety of methods for calculating beta, Brooks decides to regress Tortuga's stock returns on the Standard & Poor's (S&P) 500 returns over the 10 years of annual data he has available at the time. He will check his result with more data points before submitting his final report to the CFO. Figure 1 Returns on Tortuga Stock versus the Standard & Poor 500 Year Tortuga Return S&P 500 Return a 1 2 3 4 22 -22 15 26 20 -6 5 24 -10 20 15 2 16 32 13 1.4 12 21.8 6 10 25 After Brooks calculates beta he employs formula (1) above along with the risk-free rate and market risk premium to determine the cost of cquity. The firm's weighted average cost of capital is a function of its equity market capitalization, cost of cquity, short-and long-term debt amounts and costs, and the tax rate. Using formula (2) below. Brooks can find the firm's weighted average cost of capital (WACC). WACC-Wal-T) + War where: WACC = weighted average cost of capital Wa - weight of debt to total capital (note: this includes short-and long-term) - cost of debt using yield to maturity and current values for short-term T = corporate tax rate = weight of market capitalization to total capital =cost of equity determined with CAPM ra We Page 3 International Research Journal of Applied Finance ISSN 2229-6891 Vol. VI Issue - 1 January, 2015 Case Study Series Tortuga Tarpon Classic The company has two separate research teams working on the project and they develop two distinctly different fishing combinations. The two rod and reel combinations are test marketed with guides and past tournament champions and demand forecasts are determined. Most fishing gear has a relatively short life due to continual product innovation. Manufacturing of the two combinations is estimated to require an upfront cost of $5 million to cool the machine shop The process for manufacturing the two combinations differ and ongoing variable costs are not the same. The net cash flows for the entire ten year expected life of the product is shown in Table 2 as Project A and Project B (all figures are Sthousands of net cash flow), Project A focuses on hand tooled fishing equipment which results in a more labor intensive process, but also allows for personalized features for customers. The price charged for customization offset the slower hand tooling process to generate substantial net cash flows. Part of the upfront S5 million includes the costs of training more machinists in the art of hand tooling, which is similar to watch making but with a few less moving parts. Project A is anticipated to generate lower cash flows in the early years due to the length of time required to get machinists who are adept at hand tooling to customer specifications. Over time the cash flows increase as more machinists gain proficiency. The project is expected to experience lower cash flows towards the end of its life due to market saturation. Due to the quality of the reels, they are built to last and seldom fail or wear out. Technological obsolescence is certain although Tortuga will be investing cash flows into research and development to launch the next generation at the conclusion of the Tortuga Tarpon Classic life cycle. Project B employs a mechanized approach to large scale production of standardized equipment Although the approach does not allow for personalization, it does allow Tortuga to build its inventory quickly and capture positive net cash flows immediately. The upfront expense is almost completely devoted to tooling equipment procurement and the number of units produced will be much higher and at lower price points than the approach of Project A. At the end of both projects life it is assumed that there will be zero salvage value as the pace of innovation will require a complete re-tooling for the next generation and the useful life of the equipment will have been fully realized Brooks realizes that he will need to calculate the firm's cost of capital discount rate and apply this to the cash flow projections of both projects. He recalls all of the assignments he completed at university and is thankful to have been so well-prepared for this task. He gets a cup of coffee, sits down at his desk, and gets to work Page 4 International Research Journal of Applied Finance ISSN 2229-6891 Vol. VI Issue - 1 January, 2015 Case Study Series Figure 2 Project Net Cash Flows for Tortuga Fishing Equipment (Sthousands) Year Project A Project B 1 200 860 600 800 900 2 3 4 S 6 7 860 860 860 860 860 860 860 860 860 1500 1000 1000 900 900 900 Source: Author 8 9 10 Since Brooks is new to his role, you have been asked to review his work and assess the financial viability of the projects. Given the importance of this decision you are helping to make sure the firm makes the right choice. Specific Questions 1. Using the Capital Asset Pricing Model, what is the required rate of retum on equity, te (cost of equity) for Tortuga? (Hint: Use the slope function in excel to find beta.) 2. Analyzing the company's bond, what is the yield to maturity on the bond issue, (cost of debt)? 3. Using the market weight of equity (i.c. based on the market value of equity), the original issue amount of debt, and the outstanding portion of the revolving line of credit, what are the weights of equity and debt in the capital structure (W & wa)? 4. Using the information provided, what is the firm's weighted average cost of capital (WACC)? 5. What are the net present value (NPV) and internal rate of return (IRR) for Projects A & B? (Hint: Use the WACC calculated above as the discount rate,1%), 6. What do you suggest to Tortuga? Page 5 Assignment This assignment is a capital budgeting case with an extra twist in which you also need to figure out the firm's cost of capital. This involves a step-by-step process in which you will calculate the cost of debt, the cost of equity, and the weights of debt and equity. (Note that the firm uses short- term debt in the form of a revolving line of credit, which has a particular interest rate, as well as long-term debt, which has a different yield.) Please show your work in an excel spreadsheet that uses cell references and formulas. In calculating the cost of equity, you can use CAPM. You will need to find beta, which can be done by regressing the returns for the company on the market returns over the same period. That is, the company's returns would be the dependent variable, and the market returns will be the independent variable. The slope of this regression is the beta for the company, similar to slides 19 & 20 in the Risk_Return slides. You can find the beta using a shortcut in excel, with the SLOPE function. You can look up the slope function (=slope) in excel for the exact inputs (which are the company returns and the market returns, which are given in the case). The slope calculated in excel will be the beta to be used in CAPM to find the cost of equity. Here is the case: International Research Journal of Applied Finance ISSN 2229-6891 Vol. VI Issue - 1 January, 2015 Case Study Series Project Evaluation: Tortuga Fishing Equipment Company Judson W. Russell Introduction Brooks Hamilton recently accepted a job with Tortuga Fishing Equipment Company (Tortuga) in the company's finance department. His first few assignments were fairly straightforward and Brooks relied on his background in both accounting and finance to get his career off to a great start. His manager, the company's Chief Financial Officer (CFO) was impressed with his work and decided to put Brooks on a new assignment. The firm was embarking on a new project which would define its future over the upcoming years. Given the importance of the project and high degree of visibility with the firm's senior management, Brooks was flattered to be asked to assist and eager to show that he was up to the task. The finance department was tasked with preparing an analysis to make a decision between two competing project plans which could very well decide the future of Tortuga in the competitive fishing equipment industry. The Chief Executive Officer (CEO) wants to have an answer from finance and expects a thorough analysis very quickly. The Company Tortuga is an Islamorada, Florida based company specializing in manufacturing high-end fishing rods and reels. Tortuga was founded by a retired university professor who fished all of his life and wanted to create the best equipment possible to handle a variety of fishing conditions and fish species. He partnered with an engineer who ran a machine shop to produce some prototype reels and supplied these to commercial fishing captains as test market research. The equipment produced by Tortuga was a significant improvement over the current line available and orders were strong. Through the years, the company made some modest improvements to their original prototype and had become an industry leader Tortuga's products are used by tournament fishing teams around the world. Over the past decade. tournament fishing has grown to become a big business with corporate endorsements and prize money. This growth has made what was once a recreational vocation into a full-time profession for some anglers. Judson W. Russell, Ph.D., CFA, Clinical Professor of Finance, Bclk College of Business, University of North Carolina Charlotte Fictitious company created to illustrate corporate finance principles Page 1 International Research Journal of Applied Finance ISSN 2229-6891 Vol. VI Issue - 1 January, 2015 Case Study Series The company recently launched an extensive research and development effort focused on a new flyrod and reel designed for one particular species of fish, the Atlantic Tarpon (Megalops atlanticus). Tarpon are long-lived fishes that migrate in the warmer climes of the Caribbean Sea, Gulf of Mexico, and along the Atlantic Ocean coastlines. Although the fish can reach lengths of eight feet (-2.4 meters) and weights of 280 pounds (127 kilograms), they inhabit the shallow flats and exhibit acrobatic leaps when booked. These traits make tarpon a popular game fish for anglers. Fishing gear needs to be sturdy to handle the power of these fish and Tortuga had developed products for this niche market which were allowing anglers to be successful in their angling pursuits. Recently, several sponsors had come together to launch competitive angling events called tournaments, where the best anglers vie to catch, and then release, the most and largest tarpon. Winners may receive up to $50,000 in a single weekend tournament and the difference between winning and losing could be a few pounds. With so much money at stake, tournament teams purchase the best gear available and are always looking for any competitive advantage with their equipment. Tortuga is looking to capitalize on this trend by offering a new line called the Tortuga Tarpon Classic. This new line incorporates the latest material and design improvements and is predicted to be the "gold standard" for all serious tournaments anglers. Tortuga plans to offer the Tortuga Tarpon Classic to recreational anglers as well to capture the growing demand by affluent anglers who want the same high-quality gear as the professionals. Financial Information Tortuga began with a modest amount of capital that the founder had managed to save during his years in academia. As the firm grew, its financing needs expanded as well . Through the years Tortuga had developed and maintained a strong relationship with a large bank which provided short-term working capital funds in the form of a revolving line of credit. When a funding need arose, Tortuga would draw from this line of credit and then repay the short-term draw as cash flowed back to Tortuga The $200 million revolving line of credit currently has $25 million drawn at an interest rate of 3-month Libor plus 350 basis points. The remaining S175 million credit line can be assumed to have no fees associated with it'. Brooks looks up the most recent 3. month U.S. dollar Libor rate here: https://www.wsj.com/market-data bonds Long-term financing was also in place in two forms. After several years of revenue and earnings growth, Tortuga issued ten million shares of common stock at an issue price of $10 per share. The firm used this $100 million in funding to increase production lines and build a global presence by opening an additional manufacturing facility in Panama. Brooks finds the current price per share for Tortuga to be $25. Two years ago, Tortuga issued a 10-year bond for S80 million face value. Each S1,000 par bond carries a coupon of 5%. The bond pays interest semi-annually and is currently trading in the market at 102.50 as a percent of par. The company has a 21% corporate tax rate. Libor is an acronym for London Interbank Offered Bank, which is a standard floating interest rate benchmark for credit facilities. A basis point is equal to 1/100 of 1%. One percent is 100 basis points Typically a bank will charge a facility fee for the entire credit facility, S200 million in this case and an interest rate based on utilization. We assume no facility fee for simplicity. Page 2 International Research Journal of Applied Finance ISSN 2229-6891 Vol. VI Issue - 1 January 2015 Case Study Series The firm calculates its required return on equity with the Capital Asset Pricing Model (CAPM) using the 10-year Treasury yield as the risk-free rate and 5.5% as the historical market risk premium. He looks up Treasury yields here: https://www.bloomberg.com/markets/rates-bonds government-bondos CAPM - Risk-free rate + B (Market Risk Premium) The annual stock returns versus the market are shown in Figure 1 below for the past 10 years Beta is calculated by regressing. While there are a variety of methods for calculating beta, Brooks decides to regress Tortuga's stock returns on the Standard & Poor's (S&P) 500 returns over the 10 years of annual data he has available at the time. He will check his result with more data points before submitting his final report to the CFO. Figure 1 Returns on Tortuga Stock versus the Standard & Poor 500 Year Tortuga Return S&P 500 Return a 1 2 3 4 22 -22 15 26 20 -6 5 24 -10 20 15 2 16 32 13 1.4 12 21.8 6 10 25 After Brooks calculates beta he employs formula (1) above along with the risk-free rate and market risk premium to determine the cost of cquity. The firm's weighted average cost of capital is a function of its equity market capitalization, cost of cquity, short-and long-term debt amounts and costs, and the tax rate. Using formula (2) below. Brooks can find the firm's weighted average cost of capital (WACC). WACC-Wal-T) + War where: WACC = weighted average cost of capital Wa - weight of debt to total capital (note: this includes short-and long-term) - cost of debt using yield to maturity and current values for short-term T = corporate tax rate = weight of market capitalization to total capital =cost of equity determined with CAPM ra We Page 3 International Research Journal of Applied Finance ISSN 2229-6891 Vol. VI Issue - 1 January, 2015 Case Study Series Tortuga Tarpon Classic The company has two separate research teams working on the project and they develop two distinctly different fishing combinations. The two rod and reel combinations are test marketed with guides and past tournament champions and demand forecasts are determined. Most fishing gear has a relatively short life due to continual product innovation. Manufacturing of the two combinations is estimated to require an upfront cost of $5 million to cool the machine shop The process for manufacturing the two combinations differ and ongoing variable costs are not the same. The net cash flows for the entire ten year expected life of the product is shown in Table 2 as Project A and Project B (all figures are Sthousands of net cash flow), Project A focuses on hand tooled fishing equipment which results in a more labor intensive process, but also allows for personalized features for customers. The price charged for customization offset the slower hand tooling process to generate substantial net cash flows. Part of the upfront S5 million includes the costs of training more machinists in the art of hand tooling, which is similar to watch making but with a few less moving parts. Project A is anticipated to generate lower cash flows in the early years due to the length of time required to get machinists who are adept at hand tooling to customer specifications. Over time the cash flows increase as more machinists gain proficiency. The project is expected to experience lower cash flows towards the end of its life due to market saturation. Due to the quality of the reels, they are built to last and seldom fail or wear out. Technological obsolescence is certain although Tortuga will be investing cash flows into research and development to launch the next generation at the conclusion of the Tortuga Tarpon Classic life cycle. Project B employs a mechanized approach to large scale production of standardized equipment Although the approach does not allow for personalization, it does allow Tortuga to build its inventory quickly and capture positive net cash flows immediately. The upfront expense is almost completely devoted to tooling equipment procurement and the number of units produced will be much higher and at lower price points than the approach of Project A. At the end of both projects life it is assumed that there will be zero salvage value as the pace of innovation will require a complete re-tooling for the next generation and the useful life of the equipment will have been fully realized Brooks realizes that he will need to calculate the firm's cost of capital discount rate and apply this to the cash flow projections of both projects. He recalls all of the assignments he completed at university and is thankful to have been so well-prepared for this task. He gets a cup of coffee, sits down at his desk, and gets to work Page 4 International Research Journal of Applied Finance ISSN 2229-6891 Vol. VI Issue - 1 January, 2015 Case Study Series Figure 2 Project Net Cash Flows for Tortuga Fishing Equipment (Sthousands) Year Project A Project B 1 200 860 600 800 900 2 3 4 S 6 7 860 860 860 860 860 860 860 860 860 1500 1000 1000 900 900 900 Source: Author 8 9 10 Since Brooks is new to his role, you have been asked to review his work and assess the financial viability of the projects. Given the importance of this decision you are helping to make sure the firm makes the right choice. Specific Questions 1. Using the Capital Asset Pricing Model, what is the required rate of retum on equity, te (cost of equity) for Tortuga? (Hint: Use the slope function in excel to find beta.) 2. Analyzing the company's bond, what is the yield to maturity on the bond issue, (cost of debt)? 3. Using the market weight of equity (i.c. based on the market value of equity), the original issue amount of debt, and the outstanding portion of the revolving line of credit, what are the weights of equity and debt in the capital structure (W & wa)? 4. Using the information provided, what is the firm's weighted average cost of capital (WACC)? 5. What are the net present value (NPV) and internal rate of return (IRR) for Projects A & B? (Hint: Use the WACC calculated above as the discount rate,1%), 6. What do you suggest to Tortuga? Page 5