Capital Budgeting Analysis

Show all work in excel with formulas

November 2019: Martell Industries produces and exports wine in the region of Dorne, and is performing an important capital investment analysis. The firm will be deciding between keeping older, existing machinery in place (details for which will follow) versus investing in some new machines that would increase the firm's efficiency at producing and preparing for export the top-quality Dornish wine for which the region has become well-known. The purchase price of the new machinery is $900,000. The equipment would have a useful life of four years and, for tax purposes, depreciation charges would be according to the 7-year-asset MACRS schedule. The machinery cost should be capitalized (i.e., put onto the balance sheet) at t=0 and fully depreciated using the MACRS schedule. Management expects that this new machinery could be sold to wine aficionados for $210,790 four years from now. The MACRS schedule for a 7-year asset is for year 1: 14.29%; for year 2: 24.49%; for year 3: 17.49%; for year 4: 12.49%; for year 5: 8.93%; ... 6: 8.92%; 7: 8.93%; and 8: 4.46%. The old machinery was purchased three years ago for $420,000 and could easily remain deployed for four more years. The old equipment's depreciation method is straight-line depreciation over ten years. If the firm goes forward with the old machinery, the old machinery could be sold for scrap value of $68,000 at the end of the 4-year horizon. If Martell decides to sell the old machinery today, the selling price would be $260,000. Nymeria Sand, the firm's accountant, pointed out that the firm spent $105,200 fifteen months ago to send a team of engineers to a conference to see demonstrations of the machinery's effectiveness and to learn how to properly operate the new machinery. Because the new project, should it be implemented, would not be feasible without that training, Nymeria suggests that the costs of the engineers' conference attendance should be allocated to the project as one of its initial expenses. If Martell decides to buy the new machine and sell the old machine, it would take a loan of $640,000 at t = 0, make $32,000 interest payments for each year of the evaluation horizon, and then pay back the full $640,000 at termination. The sales (revenues) projections for the new machinery are shown in the paragraph after the next one. Operating expenses for the new machinery, excluding depreciation, are projected to be 40% of same-year sales for the new machines. The sales (revenues) projections for the old machinery are also shown in the next paragraph. Operating expenses for the old machinery, excluding depreciation, will be 60% of same-year sales for the old machines. The new machinery's sales projections are these for years 1-4: $700,000, $720,000, $750,000 & $730,000. The old machinery's sales projections are these: $400,000, $440,000, $380,000, & $270,000. If Martell purchases the new machinery, some of Martell's Net Working Capital (NWC) accounts will be incrementally affected. The schedule below shows incremental balances for Accounts Receivable, Inventory, and Accounts Payable across the evaluation horizon. You'll need to use this information to build one NWC Tracker, which in turn feeds into the ANWC line item in the main cash-flow worksheet. The Chief Financial Officer of Martell Industries, Ellaria Sand, requests your assistance in preparing an analysis of the net cash flow projections for the proposed investment. Ellaria believes that the systematic risk of this project is similar to the average systematic risk of other Martell projects. The firm-level required return (also called "hurdle rate" in business lingo) is 10%/year. Ellaria also indicates that 30% is the appropriate tax rate for this entire analysis. Next, here is the schedule for the various working-capital accounts that will be affected if the project is undertaken. These balances are incremental values if the firm goes forward with the new machinery in excess of the values if the firm goes forward with the old machinery. So, you need this information to, first, calculate (incremental) NWC balances and, in turn, construct the incremental) ANWC line item in the main sheet. Time Accts. Receivable 27000 39000 45000 Inventory 33000 45000 48000 60000 Accounts Payable 21000 25000 25000 33000 330 YOUR TASK: Ellaria has requested that you identify all of the incremental cash flows for this capital-budgeting analysis. After identifying all of the cash flows (24 points), you should then calculate the project's NPV (1.5 points) and IRR (1.5 points). Finally, provide your recommendation regarding whether the firm should (a) buy the new machinery and sell the old machinery or (b) go forward with the old machinery (1 point). November 2019: Martell Industries produces and exports wine in the region of Dorne, and is performing an important capital investment analysis. The firm will be deciding between keeping older, existing machinery in place (details for which will follow) versus investing in some new machines that would increase the firm's efficiency at producing and preparing for export the top-quality Dornish wine for which the region has become well-known. The purchase price of the new machinery is $900,000. The equipment would have a useful life of four years and, for tax purposes, depreciation charges would be according to the 7-year-asset MACRS schedule. The machinery cost should be capitalized (i.e., put onto the balance sheet) at t=0 and fully depreciated using the MACRS schedule. Management expects that this new machinery could be sold to wine aficionados for $210,790 four years from now. The MACRS schedule for a 7-year asset is for year 1: 14.29%; for year 2: 24.49%; for year 3: 17.49%; for year 4: 12.49%; for year 5: 8.93%; ... 6: 8.92%; 7: 8.93%; and 8: 4.46%. The old machinery was purchased three years ago for $420,000 and could easily remain deployed for four more years. The old equipment's depreciation method is straight-line depreciation over ten years. If the firm goes forward with the old machinery, the old machinery could be sold for scrap value of $68,000 at the end of the 4-year horizon. If Martell decides to sell the old machinery today, the selling price would be $260,000. Nymeria Sand, the firm's accountant, pointed out that the firm spent $105,200 fifteen months ago to send a team of engineers to a conference to see demonstrations of the machinery's effectiveness and to learn how to properly operate the new machinery. Because the new project, should it be implemented, would not be feasible without that training, Nymeria suggests that the costs of the engineers' conference attendance should be allocated to the project as one of its initial expenses. If Martell decides to buy the new machine and sell the old machine, it would take a loan of $640,000 at t = 0, make $32,000 interest payments for each year of the evaluation horizon, and then pay back the full $640,000 at termination. The sales (revenues) projections for the new machinery are shown in the paragraph after the next one. Operating expenses for the new machinery, excluding depreciation, are projected to be 40% of same-year sales for the new machines. The sales (revenues) projections for the old machinery are also shown in the next paragraph. Operating expenses for the old machinery, excluding depreciation, will be 60% of same-year sales for the old machines. The new machinery's sales projections are these for years 1-4: $700,000, $720,000, $750,000 & $730,000. The old machinery's sales projections are these: $400,000, $440,000, $380,000, & $270,000. If Martell purchases the new machinery, some of Martell's Net Working Capital (NWC) accounts will be incrementally affected. The schedule below shows incremental balances for Accounts Receivable, Inventory, and Accounts Payable across the evaluation horizon. You'll need to use this information to build one NWC Tracker, which in turn feeds into the ANWC line item in the main cash-flow worksheet. The Chief Financial Officer of Martell Industries, Ellaria Sand, requests your assistance in preparing an analysis of the net cash flow projections for the proposed investment. Ellaria believes that the systematic risk of this project is similar to the average systematic risk of other Martell projects. The firm-level required return (also called "hurdle rate" in business lingo) is 10%/year. Ellaria also indicates that 30% is the appropriate tax rate for this entire analysis. Next, here is the schedule for the various working-capital accounts that will be affected if the project is undertaken. These balances are incremental values if the firm goes forward with the new machinery in excess of the values if the firm goes forward with the old machinery. So, you need this information to, first, calculate (incremental) NWC balances and, in turn, construct the incremental) ANWC line item in the main sheet. Time Accts. Receivable 27000 39000 45000 Inventory 33000 45000 48000 60000 Accounts Payable 21000 25000 25000 33000 330 YOUR TASK: Ellaria has requested that you identify all of the incremental cash flows for this capital-budgeting analysis. After identifying all of the cash flows (24 points), you should then calculate the project's NPV (1.5 points) and IRR (1.5 points). Finally, provide your recommendation regarding whether the firm should (a) buy the new machinery and sell the old machinery or (b) go forward with the old machinery (1 point)