Please prepare a spreadsheet as in Chapter 9 to show the cash flow of a business for 10 years, internal rate of return, and the net present value of the proposed project. Based on that determine weather you should open up a business or not. The business that is chosen is a Coffee Shop.

investment is primarily in industrial equipment and thus qualities as ever MACRS 286 PARTS Capital Budgeting CHAT Man Catch 287 Yes MACHS Percentage I! BV Un Price Heves 1 3126.000 76 20.00 32.00 19 20 1152 11.52 5.76 100.00 3 4 5 6 Depreciation 2000 $160.000$ 33.000 3200 x 160.000 - 51.200 1920 160,000 30720 1152 160,000 18.432 1152160,000 0576 x 160,000 5,216 $160.000 TABLES Projected revenues power mulicher project 15.09 2164 9,216 $120 120 120 110 110 110 110 110 2.000 5.000 6.000 3.500 G.000 5.000 4.00u 1,000 $10.00 00 DO 720.000 71500 60 000 550.000 410.000 330.000 3 Netice at we could the book of the item as at the end of each yma bookvalet the end of year 48 $27040. Wwe set the system for $10000 the time of $17648 the area for to us. The of course, it is can be isnt a capense Wentyappers? Things. First Wow $10.000 from the Son Weso 4 $12648 - 56000 Sa tot atertax cash row from the sales a cash Year MACROPE Ending to Vale TABLE 9.10 Annual depreciation power mulcher project 2469 2 1 1 Depreciation 1420 1100.000 - 314320 2440 x 800 DOO 195920 1749 400.000 - 139920 124900 000 59,00 CJ 800,000 71440 0892 Boucon T130 cena 100.000 - 11,440 0446 900.000 - 500 SROOD 8.33 sa 680 189.760 349,840 249,000 178,40 HOT 120 35 680 0 8 8.83 4.46 10000 An Example: The Majestic Mulch and Compost Company (MMCC) At this point, we want to go through a somewhat more involveu capital hudgeting analysis Keep in mind as you read that the basic approach here is exactly the same as that incat earlier shark tractant example. We have only added some more real world" detail and lot more numbers) MMCC is investigating the feasibility of a new line at power muling tools the growing number of home composters. Based on exploratory conversations with buyers for large garden shops, it projects unit sales as follows: Pro fomia income statements, power mulcher project TABLE 9.11 Year Unit Saks 1 2 3 4 5 5 7 2000 5.000 5.000 6500 6.000 5,000 4,000 3.000 Un price Unit Herencs Valecos The costs Deprecan Teses 34 Net income 2 4 5 5 $ 120$ 120 5920 $ 1103 1105 110 3,000 5,000 6.000 5.500 5,000 5.000 $360,000 $600,000 $720,000 5715 000 $660.000 $550,000 *80.000 300,000 300,000 390,000 160000 300,000 25,000 25 000 25.000 20.000 25,000 25 000 105.920 914.320 538 920 99.920 71,440 71.160 540680 $ 75,000 $195.000 5200.000 5203 5203 $153.640 13.831 26.07 66.327 68037 69210 52.238 $26.849 $ 52,193 $120.753 $132.051 $134105101402 7 5 1105 110 4000 000 $440.000 $330,000 240,000 180,000 25,000 25.000 71.440 35680 $103,560 $ 89,320 35,210 30.369 $350 $ 5951 The new power mukher will be priced to sell at 5120) per unit to start When the competitie catches up after three years, bowever, MMCC anticipates that the price will cimp to 10 The power mukher project will require 820.000 in net working capital at the stat Sub sequently, total net wwwking capital at the end of each year will be about 15 percent of sales for that year. The variable cost per unit is so, and italfined costs are $25.000 per year It will cost about $800.000 to buy the equipment necessary to begin production. This property. The equipment will actually be worth about 20 percent of its cost in vichar op 20 $800.000 = $160.000. The relevant tax rate is 34 percent, and the required nelift Operating Cash Flows There is a lot of information here that we need to organize The first thing we can do is calculate projected sales. Sales in the first year are projected at 5.1 ants at $121) apiere, or $360 tal. The remaining figures are shown Table 9.9 Next , we compute the depreciation on the S800/ investment Table 9.10. With the information, we can prepare the peu fortal income statements , as shown in Table 9.1 here, computing the operating cash flows straightforward. The results are illes trated in the first and Table is 15 menit on this informatie TABLE 9.13 Projected cash flows, power mulcher project Year 1 2 3 5 8 EBIT Depreciation Taxes Operating cash flow $ 40,680 114,320 13,831 $141,169 $ 79,080 195.920 26,887 $248,113 1. Operating Cash Flow $195.080 $200,080 $203.560 139.920 99,920 71 440 66,327 68,027 59.210 $268,673 $231,973 $205.790 II. Net Working Capital $153,640 71,360 52 238 5172752 $103.560 71,440 35.210 5139.790 $ 89,320 35,680 30.369 594,63: -$ 20,000 $ 34,000 -$ 36,000 -$ 18,000 $ 750 $ 8,250 516 500 $16.500 Initial NWC Increases in NWC NWC recovery Changes in NWC $16.500 49.500 $ 20,000 $ 34.000 -$ 36,000 $ 8.250 $16.500 $16.500 $ 56.000 $ 18,000 s 750 HII. Capital Spending RE MSA -$800,000 Initial outlay Aftertax salvage Capital spending $800,000 $105.600 $105,600 TABLE 9.14 Projected total cash flows, power mulcher project Year o 1 2 $141,169 34,000 $248,113 36,000 $268,673 18,000 $231,973 750 $205,790 8.250 5172,762 16.500 $139,790 16,500 $ 194,631 66,000 105.600 $266,231 SBOB 500 $107.169 $212,113 $250,673 $232.723 $214.040 Operating cash flow Changes in NWC -$ 20,000 Capital spending 800.000 Total project cash flow $820,000 Cumulative cash flow $820,000 Net present value (15%) = $65,485 Internal rate of return 17.24% Payback 4.08 years $156,290 $189,252 $385,979 $712,831 $500.718 $250,046 $ 17,323 $196,717 $542 269 dari Capital Budgeting PARTS Net Working Capital Year $ 20,000 54,000 0 1 90,000 2. 3 4 $360,000 600,000 720,000 715,000 660,000 550,000 440,000 330,000 5 6 7 8 Total Cash Flow and Value We now have all the cash flow pieces, and we put them together in Table 9.14. In addition to the total project cash flows, we have calculated the cumulative cash flows. At this point, it's essentially plug-and-chug to calculate the ne (at 15 percent) works out to be $65.485. This is positive; so, based on these preliminary If we sum the discounted cash flows and the initial investment, the net present value projections, the power mulcher project is acceptable. The internal, or DCF, rate of returns greater than 15 percent since the NPV is positive. It works out to be 17.24 percent, after four years since the cumulative cash flow is almost zero at that time. As indicated the Looking at the cumulative cash flows, we see that the project has almost paid back fractional year works out to be $17,322/214.040 = .08, so the payback is 4.08 years. We t have a benchmark for MMCC This is Cash Flow Revenues LE 9.12 -520,000 34.000 in net capital 36,000 ulcher 108,000 18,000 107.250 750 99.000 8,250 82,500 16.500 66,000 16,500 49,500 16,500 Changes in NWC Now that we have the operating cash flows, we need to determine the changes in NWC. By assumption, net working capital requirements change as sales change. In each year, we will generally either add to or recover some of our project ne working capital. Recalling that NWC starts out at $20,000 and then rises to 15 percent of sales, we can calculate the amount of NWC for each year as illustrated in Table 9.12 As illustrated during the first year, net working capital grows from $20,000 to 15 x $360,000 = $54,000. The increase in net working capital for the year is thus $54.000 20,000 = $34.000. The remaining figures are calculated the same way. Remember that an increase in net working capital is a cash outflow, so we use a negative sign in this table to indicate an additional investment that the firm makes in net working capt tal. A positive sign represents net working capital returning to the firm. Thus, for example, $16,500 in NWC flows back to the firm in Year 6. Over the project's life, net working capital. builds to a peak of $108,000 and declines from there as sales begin to drop off. We show the result for changes in net working capital in the second part of Table 9.13 Notice that at the end of the project's life there is $49,500 in net working capital still to be recovered. Therefore, in the last year, the project returns $16,500 of NWC during the year and then returns the remaining $49,500 at the end of the year for a total of $66,000. Capital Spending Finally, we have to account for the long-term capital invested in the project. In this case, we invest $800.000 at Year 0. By assumption, this equipment will be worn $160,000 at the end of the project. It will have a book value of zero at that time. As we discussee above, this $160.000 excess of market value over book value is taxable, so the aftertax proceed will be $160.000 x (1 34) = $105,600. These figures are shown in the third part of Table 9.13 present value, internal rate of return, and payback. indicating that the project is acceptable. can't say whether or not this is good since we do the usual problem with payback periods. investment is primarily in industrial equipment and thus qualities as ever MACRS 286 PARTS Capital Budgeting CHAT Man Catch 287 Yes MACHS Percentage I! BV Un Price Heves 1 3126.000 76 20.00 32.00 19 20 1152 11.52 5.76 100.00 3 4 5 6 Depreciation 2000 $160.000$ 33.000 3200 x 160.000 - 51.200 1920 160,000 30720 1152 160,000 18.432 1152160,000 0576 x 160,000 5,216 $160.000 TABLES Projected revenues power mulicher project 15.09 2164 9,216 $120 120 120 110 110 110 110 110 2.000 5.000 6.000 3.500 G.000 5.000 4.00u 1,000 $10.00 00 DO 720.000 71500 60 000 550.000 410.000 330.000 3 Netice at we could the book of the item as at the end of each yma bookvalet the end of year 48 $27040. Wwe set the system for $10000 the time of $17648 the area for to us. The of course, it is can be isnt a capense Wentyappers? Things. First Wow $10.000 from the Son Weso 4 $12648 - 56000 Sa tot atertax cash row from the sales a cash Year MACROPE Ending to Vale TABLE 9.10 Annual depreciation power mulcher project 2469 2 1 1 Depreciation 1420 1100.000 - 314320 2440 x 800 DOO 195920 1749 400.000 - 139920 124900 000 59,00 CJ 800,000 71440 0892 Boucon T130 cena 100.000 - 11,440 0446 900.000 - 500 SROOD 8.33 sa 680 189.760 349,840 249,000 178,40 HOT 120 35 680 0 8 8.83 4.46 10000 An Example: The Majestic Mulch and Compost Company (MMCC) At this point, we want to go through a somewhat more involveu capital hudgeting analysis Keep in mind as you read that the basic approach here is exactly the same as that incat earlier shark tractant example. We have only added some more real world" detail and lot more numbers) MMCC is investigating the feasibility of a new line at power muling tools the growing number of home composters. Based on exploratory conversations with buyers for large garden shops, it projects unit sales as follows: Pro fomia income statements, power mulcher project TABLE 9.11 Year Unit Saks 1 2 3 4 5 5 7 2000 5.000 5.000 6500 6.000 5,000 4,000 3.000 Un price Unit Herencs Valecos The costs Deprecan Teses 34 Net income 2 4 5 5 $ 120$ 120 5920 $ 1103 1105 110 3,000 5,000 6.000 5.500 5,000 5.000 $360,000 $600,000 $720,000 5715 000 $660.000 $550,000 *80.000 300,000 300,000 390,000 160000 300,000 25,000 25 000 25.000 20.000 25,000 25 000 105.920 914.320 538 920 99.920 71,440 71.160 540680 $ 75,000 $195.000 5200.000 5203 5203 $153.640 13.831 26.07 66.327 68037 69210 52.238 $26.849 $ 52,193 $120.753 $132.051 $134105101402 7 5 1105 110 4000 000 $440.000 $330,000 240,000 180,000 25,000 25.000 71.440 35680 $103,560 $ 89,320 35,210 30.369 $350 $ 5951 The new power mukher will be priced to sell at 5120) per unit to start When the competitie catches up after three years, bowever, MMCC anticipates that the price will cimp to 10 The power mukher project will require 820.000 in net working capital at the stat Sub sequently, total net wwwking capital at the end of each year will be about 15 percent of sales for that year. The variable cost per unit is so, and italfined costs are $25.000 per year It will cost about $800.000 to buy the equipment necessary to begin production. This property. The equipment will actually be worth about 20 percent of its cost in vichar op 20 $800.000 = $160.000. The relevant tax rate is 34 percent, and the required nelift Operating Cash Flows There is a lot of information here that we need to organize The first thing we can do is calculate projected sales. Sales in the first year are projected at 5.1 ants at $121) apiere, or $360 tal. The remaining figures are shown Table 9.9 Next , we compute the depreciation on the S800/ investment Table 9.10. With the information, we can prepare the peu fortal income statements , as shown in Table 9.1 here, computing the operating cash flows straightforward. The results are illes trated in the first and Table is 15 menit on this informatie TABLE 9.13 Projected cash flows, power mulcher project Year 1 2 3 5 8 EBIT Depreciation Taxes Operating cash flow $ 40,680 114,320 13,831 $141,169 $ 79,080 195.920 26,887 $248,113 1. Operating Cash Flow $195.080 $200,080 $203.560 139.920 99,920 71 440 66,327 68,027 59.210 $268,673 $231,973 $205.790 II. Net Working Capital $153,640 71,360 52 238 5172752 $103.560 71,440 35.210 5139.790 $ 89,320 35,680 30.369 594,63: -$ 20,000 $ 34,000 -$ 36,000 -$ 18,000 $ 750 $ 8,250 516 500 $16.500 Initial NWC Increases in NWC NWC recovery Changes in NWC $16.500 49.500 $ 20,000 $ 34.000 -$ 36,000 $ 8.250 $16.500 $16.500 $ 56.000 $ 18,000 s 750 HII. Capital Spending RE MSA -$800,000 Initial outlay Aftertax salvage Capital spending $800,000 $105.600 $105,600 TABLE 9.14 Projected total cash flows, power mulcher project Year o 1 2 $141,169 34,000 $248,113 36,000 $268,673 18,000 $231,973 750 $205,790 8.250 5172,762 16.500 $139,790 16,500 $ 194,631 66,000 105.600 $266,231 SBOB 500 $107.169 $212,113 $250,673 $232.723 $214.040 Operating cash flow Changes in NWC -$ 20,000 Capital spending 800.000 Total project cash flow $820,000 Cumulative cash flow $820,000 Net present value (15%) = $65,485 Internal rate of return 17.24% Payback 4.08 years $156,290 $189,252 $385,979 $712,831 $500.718 $250,046 $ 17,323 $196,717 $542 269 dari Capital Budgeting PARTS Net Working Capital Year $ 20,000 54,000 0 1 90,000 2. 3 4 $360,000 600,000 720,000 715,000 660,000 550,000 440,000 330,000 5 6 7 8 Total Cash Flow and Value We now have all the cash flow pieces, and we put them together in Table 9.14. In addition to the total project cash flows, we have calculated the cumulative cash flows. At this point, it's essentially plug-and-chug to calculate the ne (at 15 percent) works out to be $65.485. This is positive; so, based on these preliminary If we sum the discounted cash flows and the initial investment, the net present value projections, the power mulcher project is acceptable. The internal, or DCF, rate of returns greater than 15 percent since the NPV is positive. It works out to be 17.24 percent, after four years since the cumulative cash flow is almost zero at that time. As indicated the Looking at the cumulative cash flows, we see that the project has almost paid back fractional year works out to be $17,322/214.040 = .08, so the payback is 4.08 years. We t have a benchmark for MMCC This is Cash Flow Revenues LE 9.12 -520,000 34.000 in net capital 36,000 ulcher 108,000 18,000 107.250 750 99.000 8,250 82,500 16.500 66,000 16,500 49,500 16,500 Changes in NWC Now that we have the operating cash flows, we need to determine the changes in NWC. By assumption, net working capital requirements change as sales change. In each year, we will generally either add to or recover some of our project ne working capital. Recalling that NWC starts out at $20,000 and then rises to 15 percent of sales, we can calculate the amount of NWC for each year as illustrated in Table 9.12 As illustrated during the first year, net working capital grows from $20,000 to 15 x $360,000 = $54,000. The increase in net working capital for the year is thus $54.000 20,000 = $34.000. The remaining figures are calculated the same way. Remember that an increase in net working capital is a cash outflow, so we use a negative sign in this table to indicate an additional investment that the firm makes in net working capt tal. A positive sign represents net working capital returning to the firm. Thus, for example, $16,500 in NWC flows back to the firm in Year 6. Over the project's life, net working capital. builds to a peak of $108,000 and declines from there as sales begin to drop off. We show the result for changes in net working capital in the second part of Table 9.13 Notice that at the end of the project's life there is $49,500 in net working capital still to be recovered. Therefore, in the last year, the project returns $16,500 of NWC during the year and then returns the remaining $49,500 at the end of the year for a total of $66,000. Capital Spending Finally, we have to account for the long-term capital invested in the project. In this case, we invest $800.000 at Year 0. By assumption, this equipment will be worn $160,000 at the end of the project. It will have a book value of zero at that time. As we discussee above, this $160.000 excess of market value over book value is taxable, so the aftertax proceed will be $160.000 x (1 34) = $105,600. These figures are shown in the third part of Table 9.13 present value, internal rate of return, and payback. indicating that the project is acceptable. can't say whether or not this is good since we do the usual problem with payback periods