Problem 9-33 Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in mumerical data into a cell or fiunction. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted belov. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section Billingham Packaging is considering expanding its production capacity by purchasing a new machine, the XC-750 The cost of the XC-750 is $2.75 million. Unfortunately, installing this machine will take several months and will partially disrupt production. The firm has just completed a $50,000 feasibility study to analyze the decision to buy the XC-750, resulting in the following estimates 4 4 Marketing: Once the XC-750 is operating next year, the extra capacity is expected to generate S10 million per year in additional sales, which will continue for the ten-year life of the machine. Operatio: The disruption caused by the installation will decrease sales by S5 million this year. As with Billingham's existing products, the cost of goods for the products produced by the XC-750 is expected to be 70% of their sale price. The increased production will also require increased inventory on hand of $1 million during the life of the project. Human Resources: The expansion will require additional sales and administrative personnel at a cost of $2 million per year. Accounting: The XC-750 will be depreciated via the straight-line method over the ten-year life of the machine. The firm expects receivables from the new sales to be 15% of revenues and payables to be 10% of the cost of goods sold. Billingham's marginal corporate tax rate is 35%. 8 a. Determine the incremental earnings from the purchase of the XC-750. b. Determine the free cash flow from the purchase of the XC-750 c. If the appropriate cost of capital for the expansion is 10%, compute the NPV of the purchase. d. While the expected new sales will be S10 million per year from the expansion, estimates range from S8 million to S12 million. What is the 10 12 NPV in the worst case? In the best case? e. What is the break-even level of new sales from the expansion? What is the break-even level for the cost of goods sold? f. 13 Billingham could instead purchase the XC-900, which offers even greater capacity. The cost of the XC-900 is S4 million. The extra capacity would not be useful in the first two years of operation, but would allow for additional sales in years 3-10. What level of additional sales (above the $10 million expected for the XC-750) per year in those years would justify purchasing the larger machine? 14 Tax rate Cost of goods as % of sales Receivables as % of sales Payables as % of COGS Machine price (000) Machine life (years) Increased inventory (000) First year sales (000) Disrupted sales (000) Personnel (000) Cost of capital 3500 7000 15% 10% 16 17 19 20 21 $ 2,750 23 24 25 26 S 1,000 S 10,000 S 5,000 S 2,000 10% 28 29 30 a. Determine the incremental earnings from the purchase of the XC-750. b. Determine the free cash flow from the purchase of the XC-750 c. If the appropriate cost of capital for the expansion is 10%, compute the NPV of the purchase 1 $ -5,000 S10,000 S 10,000 S 10,000 S 10,000 S 10,00010,000 S 10,000 S10,000 10,000 S10,000 $ -7,000 S -7,000 S-7,000 S -2,000 S -2,000 S -2,000 S -2,000 S -2,000 -2,000 S -2,000 S -2,000 -2,000 S -2,000 S 275 S 275 S 275 S -275 S 275 275 S 275 S 275 S 275 S 275 S 1,500 S 725 725 S725 S 725S 725 725S 725 S 725 S 725 S 725 525 S 254 S 254 S 254S 254 S 254 $ -254 S 254 S -254 254 254 $ -975 |$ 471 |S 471 |S 471 S 471 |$ 471 |$ 471 |$ 471 $ 471 |$ 471 |S 471 275 S 275 S 275S 275 S 275 275 275 275 S275 S 275 Sales reventle Cost of goods sold $ 3.500 S -7,000S -7,000 S -7,000 S -7,000 S -7 Equals net operating income Minus income tax Equals unlevered net income Plus depreciation Capital expenditures Add to Nwc 3 $-600 S-1,200 S $4,325 S 454 S 746 S 746 S 746 S 746 S 1,000 S 800 7461,746S 800 746 S 746 746 Net Working Capital Increased receivables Increased payables Increased inventory NWC (000) $ .750 ls 1,500 sl.500 IS 1.500 |S1,500 S 1,500 |$1,500 si,500 ls i,500 $1,500 si,500 ls s 1,500 $ 1,500 s 48 49 50 350 S 700 S 700 S 700 S 700 700 S 700 S 700 S 700 S 700 S 700 S S 1,000 S1,000 S 1,000 S 1,000 S 1,000 S 1,000S1,000 S1,000 S 1,000$ 1,000 600s 1,800 S 1,800 $ 1,800 S1,800 1.800 S 1,800 S 1,800 1800s 1,800 S 800 S d. While the expected new sales will be S10 million per year from the expansion, estimates range from S8 million to S12 million. What is the 53 NPV in the worst case? In the best case? 54 High revenue Low revenue S 12,000 S8,000 56 Year Sales revenle Cost of goods sold Additional personnel Depreciation Equals net operating income Minus income tax 10 59 $5,000 $ 3,500 -8,400 S -8,400 S -8,400 S 8,400 -8,400S -8,400 S -8,400 S -8,400 S -8,400 S -8,400 S -2,000 S -2,000 S -2,000 S -2,000 S -2,000 S -2.000 S -2,000 S 2,000S -2,000 S -2,000 $ 275 $ -275 S 275 S 275 S 275 S 275 S 275 S 275 S 275 S 275 $ 1,500 S 1,325S 1,325S 1,325 S 1,325S 1,325S 1.325 S 1,325 S 1,325S 1,325 S 1,325 525 464 S 464 S 464 464 S 464S -464 S 464 S 464 S 464 464 60 Equals unlevered net income S-975 S861 S 861 S 861 S 861 S861 S 861 S 861 S 861 S 861 S 861 Plus depreciation Capital expenditures Add to NWC Free cash flow 275 S 275 S 275S 275 S 275 S 275 S 275 275 S 275 S 27 S 2,750 $ 600 S -1,360 S $ 4,325 S 224 S 1,136 S 1,1361,136 S 1,136 S 1,000 S 960 1,136 S 2,136 S 960 1,136 S 1,136 S 1,136 70 NPV (000) 73 74 rs T5 Net Working Capital Increased receivables Increased payables Increased inventory NWC (000) 350 S 840 S 840 S 840 S 840 S 840 S 840 S 840 S 840 S 840 $ -840 S S 1,000 S 1,000 S 1.000 S 1,000 S 1,000 1,000 S 1,000 1,000 S 1,000 S 1,000 s 600 | s 1,960 | $ 1,960 | 1.960 |$ 1.960 s 1,960 s 1,960 | $ 1,960 s 1,960 |$ 1,960 $ 960-s Year Sales revenue Cost of goods sold 10 S 5,000 S 8,000 S 8,000 S 8,000S 8,000 S 8,000 S 8,000 S 8,000 8,000 S 8,000 S 8,000 S 3,500 S -5,600 S -5,600S -5,600 S -5,600 -5,600 S -5,600 S -5,600 -5,600S -5.600 S -5,600 S-2,000 -2,000 S2,000 -2,000 -2,000 S 2,000-2,000 -2,000 S 2.000 2,000 S 275 S 275 S 275 S 275 275S 275 S 275 S 275 S 275S 275 S 1,500 S 125 125 S125 S 125 125 S 125 125 125 S 125 S 125 525 S 44 S-44 S 44S44 S$44 S 44 S S 44 -975 S 81S81 S81 S 81 $81 S 81 S 81 S 81 S 81 S 81 S 275S 275 S 275 275 275 S 275 275 275 S 275 S 275 Depreciation Equals net operating income Minus income tax Equals unlevered net income 3 Capital expenditures Add to NWC Free cash flow S -2,750 S 600 S 1,040 S S 4,325 S -684 S 356 S 356 S 356 S 356 S 356 S356 S 356 S 356 S 1356 S 640 S 1,000 S 640 NPV (000) 94 95 36 97 98 Net Working Capital Increased receivables Increased payables Increased inventory NWC (000) 1.200 S 1.200 750 | $ 1,200 S 1.200 | $ ,200 $ 1.200 | $ 1,200 |$ 1.200 | $ 1.200 $ ,200 | $ ,200 $ 1.200 | $ 350 S 560 S 560 S 560 $ 560 S 560 S 560 S 560 560 S 560 S 560 S S 1,000 S 1,000 S 1,000S 1.000 S 1,000 S1,000S1.000 S 1,000 S 1,000 S 1,000 600 | $1,640 | Sl.640 |S1.640 SL640 | $1,640 | Sl.640 |S1.640 | $1.640 |$640 is 640s e. What is the break-even level of new sales from the expansion? What is the break-even level for the cost of goods sold? 100 101 102 103 10,143 Breakeven sales (original assumptions) Breakeven COGS (original assumptions) 69.55% 104 f. Billingham could instead purchase the XC-900, which offers even greater capacity. The cost of the XC-900 is S4 million. The extra capacity would not be useful in the first two years of operation, but would allow for additional sales in years 3-10. What level of additional sales (above the S10 million expected for the XC-750) per year in those years would justify purchasing the larger machine!? 105 106 107 Machine prke (000 000 S 4,000 10 4 0 Year Sales reventie Cost of goods sold Additional personnel Depreciation Equals net operating income Minus income tax Equals unlevered net incomeS 975S 390 S 390 S 660 S 660 S 660 660 S 660S 660 S 660 S 660 Plus depreciation Capital expenditures Add to NWC Free cash flow S -5,000 S10,000 10,000 $ 11.384 S 11384 S 11,384 $ 11,384 S11,384$ 11.384 S 11,384 S 11,384 $ 3.500 $-7.000 | $-7000 | $-7.969 | S-7,969 | S-7.969 $-7.969 | $-7,969 | $-7.969 | S-7969 $-7.969 $-2.000 | $-2,000 | $-2,000 | $-2,000 |S-2000 | $-2.000 | $-2,000 | $-2,000 | S-2,000 $-2.000 $ -400 -400 400S 400 S 400 400 S 400 400 S 400 S -400 S 1,500 S 600 S 6001,015 S 1,015 S 1,015S 1,015 S 1,015 S 1,015 S 1,015 S 1,015 525 $ -210 |$ -210 |$ .355 S .355 S .355 | $ .355 |$ .355 |$ .355 IS .355 $ .355 109 110 112 113 115 116 $ 400 |$ 400 |$ 400 |S 400 S 400 |$ 400 |$ 400 |$ 400 |S 400 $ 400 S 4,000 S 1,000 S 911 S111S 790 S 949 1,060 2 600 S 1,200 S 911 119 120 121 122 1,060 1,060 S 1,0601,060 S 1,060 S2,060 S -5,575 S -410 NPV (000) Net Working Capital Increased receivables Increased payables Increased inventory NWC (000) 124 125 126 127 128 129 130 131 132 133 134 Requirements 135 136 131 138 139 750 |$1,500 |$1,500 s 1.708 |$ 1.708 |$ 1.708 $1,708 $ 1.708 |$1.708 $1.708 $1.708 350 S 700 S -700 S -797 S -797 S -797$ -797 S -797S 797 S -797S -797 S $ 1.000 IS 1,000 |$ 1,000 $ 1,000 |$ 1,000 |$ 1,000 $ 1,000 $ 1,000 | $ 1,000 $ 1,000 600 S 1,800S 1.800 S 1,911 S 1,911S 1,911 S 1,911 S 1,911S 1,911 S 1,911 S 911S S 11,384 Breakeven sales (more expensive machine) Additional sales needed to break even 1 In cell D45, by using cell references, calculate the NPV of the project for this scenario (1 pt.). 2 In cell D71, by using cell references, calculate the NPV of the project for this scenario (1 pt.) In cell D92, by using cell references, calculate the NPV of the project for this scenario (1 pt.). In cell D122, by using cell references, calculate the NPV of the project for this scenario (1 pt.). 3 4 5 In cell E131, by using cell references, calculate the breakeven cost of goods sold for the original assumptions (1 pt.). Problem 9-33 Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in mumerical data into a cell or fiunction. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted belov. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section Billingham Packaging is considering expanding its production capacity by purchasing a new machine, the XC-750 The cost of the XC-750 is $2.75 million. Unfortunately, installing this machine will take several months and will partially disrupt production. The firm has just completed a $50,000 feasibility study to analyze the decision to buy the XC-750, resulting in the following estimates 4 4 Marketing: Once the XC-750 is operating next year, the extra capacity is expected to generate S10 million per year in additional sales, which will continue for the ten-year life of the machine. Operatio: The disruption caused by the installation will decrease sales by S5 million this year. As with Billingham's existing products, the cost of goods for the products produced by the XC-750 is expected to be 70% of their sale price. The increased production will also require increased inventory on hand of $1 million during the life of the project. Human Resources: The expansion will require additional sales and administrative personnel at a cost of $2 million per year. Accounting: The XC-750 will be depreciated via the straight-line method over the ten-year life of the machine. The firm expects receivables from the new sales to be 15% of revenues and payables to be 10% of the cost of goods sold. Billingham's marginal corporate tax rate is 35%. 8 a. Determine the incremental earnings from the purchase of the XC-750. b. Determine the free cash flow from the purchase of the XC-750 c. If the appropriate cost of capital for the expansion is 10%, compute the NPV of the purchase. d. While the expected new sales will be S10 million per year from the expansion, estimates range from S8 million to S12 million. What is the 10 12 NPV in the worst case? In the best case? e. What is the break-even level of new sales from the expansion? What is the break-even level for the cost of goods sold? f. 13 Billingham could instead purchase the XC-900, which offers even greater capacity. The cost of the XC-900 is S4 million. The extra capacity would not be useful in the first two years of operation, but would allow for additional sales in years 3-10. What level of additional sales (above the $10 million expected for the XC-750) per year in those years would justify purchasing the larger machine? 14 Tax rate Cost of goods as % of sales Receivables as % of sales Payables as % of COGS Machine price (000) Machine life (years) Increased inventory (000) First year sales (000) Disrupted sales (000) Personnel (000) Cost of capital 3500 7000 15% 10% 16 17 19 20 21 $ 2,750 23 24 25 26 S 1,000 S 10,000 S 5,000 S 2,000 10% 28 29 30 a. Determine the incremental earnings from the purchase of the XC-750. b. Determine the free cash flow from the purchase of the XC-750 c. If the appropriate cost of capital for the expansion is 10%, compute the NPV of the purchase 1 $ -5,000 S10,000 S 10,000 S 10,000 S 10,000 S 10,00010,000 S 10,000 S10,000 10,000 S10,000 $ -7,000 S -7,000 S-7,000 S -2,000 S -2,000 S -2,000 S -2,000 S -2,000 -2,000 S -2,000 S -2,000 -2,000 S -2,000 S 275 S 275 S 275 S -275 S 275 275 S 275 S 275 S 275 S 275 S 1,500 S 725 725 S725 S 725S 725 725S 725 S 725 S 725 S 725 525 S 254 S 254 S 254S 254 S 254 $ -254 S 254 S -254 254 254 $ -975 |$ 471 |S 471 |S 471 S 471 |$ 471 |$ 471 |$ 471 $ 471 |$ 471 |S 471 275 S 275 S 275S 275 S 275 275 275 275 S275 S 275 Sales reventle Cost of goods sold $ 3.500 S -7,000S -7,000 S -7,000 S -7,000 S -7 Equals net operating income Minus income tax Equals unlevered net income Plus depreciation Capital expenditures Add to Nwc 3 $-600 S-1,200 S $4,325 S 454 S 746 S 746 S 746 S 746 S 1,000 S 800 7461,746S 800 746 S 746 746 Net Working Capital Increased receivables Increased payables Increased inventory NWC (000) $ .750 ls 1,500 sl.500 IS 1.500 |S1,500 S 1,500 |$1,500 si,500 ls i,500 $1,500 si,500 ls s 1,500 $ 1,500 s 48 49 50 350 S 700 S 700 S 700 S 700 700 S 700 S 700 S 700 S 700 S 700 S S 1,000 S1,000 S 1,000 S 1,000 S 1,000 S 1,000S1,000 S1,000 S 1,000$ 1,000 600s 1,800 S 1,800 $ 1,800 S1,800 1.800 S 1,800 S 1,800 1800s 1,800 S 800 S d. While the expected new sales will be S10 million per year from the expansion, estimates range from S8 million to S12 million. What is the 53 NPV in the worst case? In the best case? 54 High revenue Low revenue S 12,000 S8,000 56 Year Sales revenle Cost of goods sold Additional personnel Depreciation Equals net operating income Minus income tax 10 59 $5,000 $ 3,500 -8,400 S -8,400 S -8,400 S 8,400 -8,400S -8,400 S -8,400 S -8,400 S -8,400 S -8,400 S -2,000 S -2,000 S -2,000 S -2,000 S -2,000 S -2.000 S -2,000 S 2,000S -2,000 S -2,000 $ 275 $ -275 S 275 S 275 S 275 S 275 S 275 S 275 S 275 S 275 $ 1,500 S 1,325S 1,325S 1,325 S 1,325S 1,325S 1.325 S 1,325 S 1,325S 1,325 S 1,325 525 464 S 464 S 464 464 S 464S -464 S 464 S 464 S 464 464 60 Equals unlevered net income S-975 S861 S 861 S 861 S 861 S861 S 861 S 861 S 861 S 861 S 861 Plus depreciation Capital expenditures Add to NWC Free cash flow 275 S 275 S 275S 275 S 275 S 275 S 275 275 S 275 S 27 S 2,750 $ 600 S -1,360 S $ 4,325 S 224 S 1,136 S 1,1361,136 S 1,136 S 1,000 S 960 1,136 S 2,136 S 960 1,136 S 1,136 S 1,136 70 NPV (000) 73 74 rs T5 Net Working Capital Increased receivables Increased payables Increased inventory NWC (000) 350 S 840 S 840 S 840 S 840 S 840 S 840 S 840 S 840 S 840 $ -840 S S 1,000 S 1,000 S 1.000 S 1,000 S 1,000 1,000 S 1,000 1,000 S 1,000 S 1,000 s 600 | s 1,960 | $ 1,960 | 1.960 |$ 1.960 s 1,960 s 1,960 | $ 1,960 s 1,960 |$ 1,960 $ 960-s Year Sales revenue Cost of goods sold 10 S 5,000 S 8,000 S 8,000 S 8,000S 8,000 S 8,000 S 8,000 S 8,000 8,000 S 8,000 S 8,000 S 3,500 S -5,600 S -5,600S -5,600 S -5,600 -5,600 S -5,600 S -5,600 -5,600S -5.600 S -5,600 S-2,000 -2,000 S2,000 -2,000 -2,000 S 2,000-2,000 -2,000 S 2.000 2,000 S 275 S 275 S 275 S 275 275S 275 S 275 S 275 S 275S 275 S 1,500 S 125 125 S125 S 125 125 S 125 125 125 S 125 S 125 525 S 44 S-44 S 44S44 S$44 S 44 S S 44 -975 S 81S81 S81 S 81 $81 S 81 S 81 S 81 S 81 S 81 S 275S 275 S 275 275 275 S 275 275 275 S 275 S 275 Depreciation Equals net operating income Minus income tax Equals unlevered net income 3 Capital expenditures Add to NWC Free cash flow S -2,750 S 600 S 1,040 S S 4,325 S -684 S 356 S 356 S 356 S 356 S 356 S356 S 356 S 356 S 1356 S 640 S 1,000 S 640 NPV (000) 94 95 36 97 98 Net Working Capital Increased receivables Increased payables Increased inventory NWC (000) 1.200 S 1.200 750 | $ 1,200 S 1.200 | $ ,200 $ 1.200 | $ 1,200 |$ 1.200 | $ 1.200 $ ,200 | $ ,200 $ 1.200 | $ 350 S 560 S 560 S 560 $ 560 S 560 S 560 S 560 560 S 560 S 560 S S 1,000 S 1,000 S 1,000S 1.000 S 1,000 S1,000S1.000 S 1,000 S 1,000 S 1,000 600 | $1,640 | Sl.640 |S1.640 SL640 | $1,640 | Sl.640 |S1.640 | $1.640 |$640 is 640s e. What is the break-even level of new sales from the expansion? What is the break-even level for the cost of goods sold? 100 101 102 103 10,143 Breakeven sales (original assumptions) Breakeven COGS (original assumptions) 69.55% 104 f. Billingham could instead purchase the XC-900, which offers even greater capacity. The cost of the XC-900 is S4 million. The extra capacity would not be useful in the first two years of operation, but would allow for additional sales in years 3-10. What level of additional sales (above the S10 million expected for the XC-750) per year in those years would justify purchasing the larger machine!? 105 106 107 Machine prke (000 000 S 4,000 10 4 0 Year Sales reventie Cost of goods sold Additional personnel Depreciation Equals net operating income Minus income tax Equals unlevered net incomeS 975S 390 S 390 S 660 S 660 S 660 660 S 660S 660 S 660 S 660 Plus depreciation Capital expenditures Add to NWC Free cash flow S -5,000 S10,000 10,000 $ 11.384 S 11384 S 11,384 $ 11,384 S11,384$ 11.384 S 11,384 S 11,384 $ 3.500 $-7.000 | $-7000 | $-7.969 | S-7,969 | S-7.969 $-7.969 | $-7,969 | $-7.969 | S-7969 $-7.969 $-2.000 | $-2,000 | $-2,000 | $-2,000 |S-2000 | $-2.000 | $-2,000 | $-2,000 | S-2,000 $-2.000 $ -400 -400 400S 400 S 400 400 S 400 400 S 400 S -400 S 1,500 S 600 S 6001,015 S 1,015 S 1,015S 1,015 S 1,015 S 1,015 S 1,015 S 1,015 525 $ -210 |$ -210 |$ .355 S .355 S .355 | $ .355 |$ .355 |$ .355 IS .355 $ .355 109 110 112 113 115 116 $ 400 |$ 400 |$ 400 |S 400 S 400 |$ 400 |$ 400 |$ 400 |S 400 $ 400 S 4,000 S 1,000 S 911 S111S 790 S 949 1,060 2 600 S 1,200 S 911 119 120 121 122 1,060 1,060 S 1,0601,060 S 1,060 S2,060 S -5,575 S -410 NPV (000) Net Working Capital Increased receivables Increased payables Increased inventory NWC (000) 124 125 126 127 128 129 130 131 132 133 134 Requirements 135 136 131 138 139 750 |$1,500 |$1,500 s 1.708 |$ 1.708 |$ 1.708 $1,708 $ 1.708 |$1.708 $1.708 $1.708 350 S 700 S -700 S -797 S -797 S -797$ -797 S -797S 797 S -797S -797 S $ 1.000 IS 1,000 |$ 1,000 $ 1,000 |$ 1,000 |$ 1,000 $ 1,000 $ 1,000 | $ 1,000 $ 1,000 600 S 1,800S 1.800 S 1,911 S 1,911S 1,911 S 1,911 S 1,911S 1,911 S 1,911 S 911S S 11,384 Breakeven sales (more expensive machine) Additional sales needed to break even 1 In cell D45, by using cell references, calculate the NPV of the project for this scenario (1 pt.). 2 In cell D71, by using cell references, calculate the NPV of the project for this scenario (1 pt.) In cell D92, by using cell references, calculate the NPV of the project for this scenario (1 pt.). In cell D122, by using cell references, calculate the NPV of the project for this scenario (1 pt.). 3 4 5 In cell E131, by using cell references, calculate the breakeven cost of goods sold for the original assumptions (1 pt.)
