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. Ifa specific Excel function is to be used, the directions will specify the use of that function. Dio not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. 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 Marketing: Once the XC-750 is operating next year, the extra capacity is expected to generate $10 million per year in additional sales, which will continue for the ten-year life of the machine - Operations: The disruption caused by the installation will decrease sales by $5 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% 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 $10 million per year from the expansion, estimates range from $8 million to $12 million. What is the 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. Billingham could instead purchase the XC-900, which offers even greater capacity. The cost of the XC-900 is $4 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? 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 35% 70% 15% 10% ,750 10 1,000 10,000 5,000 2,000 10% a. Determine the incremental carnings 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. 7 5,000 S 10,000$10,000 S 10,00010,000 10,000S 10,000 $ 10,000 S 10,000 S 10,000 $ 10,000 3,500 S -7,000 -7,000 S7,000 -7,000 -7,000S7,000 $ -7,000 S 7,000 S -7,000 $ -7,000 S 2,000 -2,000 S 2,0002,000-2,000 S2,000 -,000 S 2,0002,000 $ 2,000 s revenuc Cost of goods sold 2 Equals net operating income Minus income tax Equals unlevered net income 1,500 S 725$725S 725S725 S 725S725 $ 725S725 S 725S725 -975 S 471471 471471 471 S471471S 471S471 S471 275 S 275S 275 S275 S 1 5 275 275 S tures -600 -1,200 S 4,325 1,000 S 800 746 S746$ 746S746S 746 $ 1,746 S 800 454 746 746 S746 Net Working Capital Increased receivables -750 1,500 1,500S1,500 $ 1,500 1,500 S 1,500 1,500S1,500 S 1,500 1,500 S 350 700$ 700 S 700 700S700S 700-700 700-700700 S 1 Increased inventory NWC (000) 1,000 S 1,000$ 1,000 S 1,000 1,000 S1,000S 1,000S1,000 S 1,000 S 1,000 600 S1.800 S 1,800 S1,800 1.800 S1 S1,800 1.800 S1,800 S1,800 S800S d. While the expected new sales will be S10 million per year from the expansion, estimates range from $8 million to $12 million. What is the NPV in the worst case? In the best case? High revenue Low revenue 12,000 Free Cash Flows in the Best Case Year Sales revenue Cost of goods sold Additional personnel Depreciation Equals net operating income Minus income tax Equals unlevered net income Plus depreciation Capital expenditures Add to NWC Free cash flow 5,000 12,000S12,000 S 12,000 12,000 12,00012,000 S 12,000S 12,000 12,00012,000 3,500 S -8,400S -8,400 S -8,400-8,400-8,400 $ -8,400S -8,400 S -8,400S -8,400-8,400 275 1,500 |$ 1,325 |$ 1.325 | S 1.325 |$ 1.325 |$ 1,325 | $ 1,325 |$ 1.325 | S 1.325 | 1.325 |$ 1.325 525 S 464S-464 S464$-464 464 $ 464 S 464 S -464 464 $464 975S 861S 861 S 861 861$ 86861 S861 861 S 861 861 275 S -275S 275 S 275 $ 275 $ 275S 275 S 275 S 275 S 1 1 5 5 5 750 -600 S-1,360SS $ 1,000960 1,136 | $ 2,136 | $ 960 4,325 | $ -224 | S 1,136 | S 1,136 | $ 1,136 | $ 1,136 | $ 1,136 | $ 1,136 | S 1,136 |$ NPV (000) Net Working Capital Increased receivables Increased payables Increased inventory NWC (000) 750 |$ 350 S 1,800 |$ 1.800 |$ 840S 84S-840 S840-840 $ 1,800 IS 1,800 |$ 1,800 |$ 1,800 |$ 840S 1.800 |S 1.800 |S 840S 840S-840 $ 1.800 |$ 2 1,800 |$ 840 S 1,000 1,000 1,000S1,000 1,000$ 1,000 S 1,000 1,000 S 1,000S 1,000 600 1,960 S 1,960 1,960 1,960 1,9601,960 S 1,960 1,960 1,960$ 960 $ Free Cash Flows in the Worst Case Year Sales revenue Cost of goods sold Additional personnel 10 8 0 000 S 8,0008,000 8,000 8,000 8,000$ 8,000 8,000 8,000S 8,000 8,000 3,500S -5,6005,600 S-5,600 -5,600$-5,600 -5,600-5,600 -5,600 -5,600 S-5,600 S-2,000S-2,000 S -2,000 S-2,000 $-2,000 S-2,000 -2,000 S-2,000 S-2,000 S-2,000 S -275 S-275 -275 275 -275 $275 275 -275S 275 S275 1,500 S 125S125S 125S125 125S125 S 125S125 S 125S 125 Equals net operating income Minus income tax Equals unlevered net income 525 S-44 S -975 S 81 S 275 S 275 275$275$ 275$275 275S275S 275 S275 81 S 81 S 81 S 81 S 81 S 81 S 81 S 81 S 81 S Capital expenditures Add to NWC Free cash flow 750 -600 S-1,040 S S 1,000 S 4,325 S 684 S356S 356$356 356 356 356S 356356 S 1,356 S640 NPV (000) Net Working Capital Incrcased reccivables Increased payables Increased inventory NWC (000) 750 |$ 1,200 |$ 1,200 |$ 1,200 | $ 1,200 | $ 1.200 |$ 1.200 | $ 1.200 |$ 1.200 S 1.200 IS 1.200 Is- 350 S560S 560 560560-560 560560 560-560 560 S 1,000 S 1,000 1,000 S1,000 1,000$ 1,000 1,000 1,000 1,000S 1,000 600 S1,640 S1,6401,640 1,6401,640 1,6401,640 1,640 1,640 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? Breakeven sales (original assumptions) Breakeven COGS (original assumptions) S 10,143 69.55% 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? Machine price (000) 4,000 10 car Sales revenue Cost of goods sold Additional personnel 5,000 S 10,000S 10,00011,384$ 11,384 S 11,384 S 11 $ 11,384 S 11,384 S 11,384 3,500 S -7,0007,000-7,969 $ -7,969 -7,969 -7,969 -7,969-7,969 -7,969-7,969 S 2,000 S 2,000 2,000S 2,000 S -2,000S 2,000$-2,000 $ 2,000S2,000 S 2,000 S 400S400 $400 S 40 400 S 400 -400 400-400 S 400 1,500 S 600S6001,015S1,015 S1,015 S1,015 $ 1,015 1,015S 1,015 S 1,015 355 975S 390 390 660 660S660660 660660 S 660 660 S 400 S400 400S400 S 400S400S 400400S 400S 400 S 11 Equals net operating income Minus income tax Equals unlevered net income Plus depreciation Capital expenditures Add to NWC Free cash flow 525 S 210 S 210 S 355 $ 355 S 355 S 355 S 355 $ 355 S 355 S 4,000 -600 S -1,200SS111 S -5,575 S 410790 S 1,000 911 9491,060 S1,060S1,0601,060 1,060 1,060S 2,060S911 NPV (000) Net Working Capital 750 |$ 1,500 | $ 1,500 |$ 1,708 |$ 1,708 |$ 1,708 |$ 1,708 |$ 1,708 |$ 1,708 |$ 1,708 |s 1,708 |$ 350 S 70700797 797 S797 S797 $-797 S 797S-797S -797 S Increased payables Increased inventory NWC (000) 1,000 1,000S 1,000 1,000S1,0001,000 S1,000S1,000 1,000 1,000 600 |S 1.800 | $ 1.800 | $ 1,911|$ 1,911 S 1,911|$ 1,911|$ 1,911 | $ 1,911|$ 1,91 1 |s 911|$ Breakeven sales (more expensive machine) Additional sales needed to break even S 11,384 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. Ifa specific Excel function is to be used, the directions will specify the use of that function. Dio not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. 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 Marketing: Once the XC-750 is operating next year, the extra capacity is expected to generate $10 million per year in additional sales, which will continue for the ten-year life of the machine - Operations: The disruption caused by the installation will decrease sales by $5 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% 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 $10 million per year from the expansion, estimates range from $8 million to $12 million. What is the 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. Billingham could instead purchase the XC-900, which offers even greater capacity. The cost of the XC-900 is $4 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? 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 35% 70% 15% 10% ,750 10 1,000 10,000 5,000 2,000 10% a. Determine the incremental carnings 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. 7 5,000 S 10,000$10,000 S 10,00010,000 10,000S 10,000 $ 10,000 S 10,000 S 10,000 $ 10,000 3,500 S -7,000 -7,000 S7,000 -7,000 -7,000S7,000 $ -7,000 S 7,000 S -7,000 $ -7,000 S 2,000 -2,000 S 2,0002,000-2,000 S2,000 -,000 S 2,0002,000 $ 2,000 s revenuc Cost of goods sold 2 Equals net operating income Minus income tax Equals unlevered net income 1,500 S 725$725S 725S725 S 725S725 $ 725S725 S 725S725 -975 S 471471 471471 471 S471471S 471S471 S471 275 S 275S 275 S275 S 1 5 275 275 S tures -600 -1,200 S 4,325 1,000 S 800 746 S746$ 746S746S 746 $ 1,746 S 800 454 746 746 S746 Net Working Capital Increased receivables -750 1,500 1,500S1,500 $ 1,500 1,500 S 1,500 1,500S1,500 S 1,500 1,500 S 350 700$ 700 S 700 700S700S 700-700 700-700700 S 1 Increased inventory NWC (000) 1,000 S 1,000$ 1,000 S 1,000 1,000 S1,000S 1,000S1,000 S 1,000 S 1,000 600 S1.800 S 1,800 S1,800 1.800 S1 S1,800 1.800 S1,800 S1,800 S800S d. While the expected new sales will be S10 million per year from the expansion, estimates range from $8 million to $12 million. What is the NPV in the worst case? In the best case? High revenue Low revenue 12,000 Free Cash Flows in the Best Case Year Sales revenue Cost of goods sold Additional personnel Depreciation Equals net operating income Minus income tax Equals unlevered net income Plus depreciation Capital expenditures Add to NWC Free cash flow 5,000 12,000S12,000 S 12,000 12,000 12,00012,000 S 12,000S 12,000 12,00012,000 3,500 S -8,400S -8,400 S -8,400-8,400-8,400 $ -8,400S -8,400 S -8,400S -8,400-8,400 275 1,500 |$ 1,325 |$ 1.325 | S 1.325 |$ 1.325 |$ 1,325 | $ 1,325 |$ 1.325 | S 1.325 | 1.325 |$ 1.325 525 S 464S-464 S464$-464 464 $ 464 S 464 S -464 464 $464 975S 861S 861 S 861 861$ 86861 S861 861 S 861 861 275 S -275S 275 S 275 $ 275 $ 275S 275 S 275 S 275 S 1 1 5 5 5 750 -600 S-1,360SS $ 1,000960 1,136 | $ 2,136 | $ 960 4,325 | $ -224 | S 1,136 | S 1,136 | $ 1,136 | $ 1,136 | $ 1,136 | $ 1,136 | S 1,136 |$ NPV (000) Net Working Capital Increased receivables Increased payables Increased inventory NWC (000) 750 |$ 350 S 1,800 |$ 1.800 |$ 840S 84S-840 S840-840 $ 1,800 IS 1,800 |$ 1,800 |$ 1,800 |$ 840S 1.800 |S 1.800 |S 840S 840S-840 $ 1.800 |$ 2 1,800 |$ 840 S 1,000 1,000 1,000S1,000 1,000$ 1,000 S 1,000 1,000 S 1,000S 1,000 600 1,960 S 1,960 1,960 1,960 1,9601,960 S 1,960 1,960 1,960$ 960 $ Free Cash Flows in the Worst Case Year Sales revenue Cost of goods sold Additional personnel 10 8 0 000 S 8,0008,000 8,000 8,000 8,000$ 8,000 8,000 8,000S 8,000 8,000 3,500S -5,6005,600 S-5,600 -5,600$-5,600 -5,600-5,600 -5,600 -5,600 S-5,600 S-2,000S-2,000 S -2,000 S-2,000 $-2,000 S-2,000 -2,000 S-2,000 S-2,000 S-2,000 S -275 S-275 -275 275 -275 $275 275 -275S 275 S275 1,500 S 125S125S 125S125 125S125 S 125S125 S 125S 125 Equals net operating income Minus income tax Equals unlevered net income 525 S-44 S -975 S 81 S 275 S 275 275$275$ 275$275 275S275S 275 S275 81 S 81 S 81 S 81 S 81 S 81 S 81 S 81 S 81 S Capital expenditures Add to NWC Free cash flow 750 -600 S-1,040 S S 1,000 S 4,325 S 684 S356S 356$356 356 356 356S 356356 S 1,356 S640 NPV (000) Net Working Capital Incrcased reccivables Increased payables Increased inventory NWC (000) 750 |$ 1,200 |$ 1,200 |$ 1,200 | $ 1,200 | $ 1.200 |$ 1.200 | $ 1.200 |$ 1.200 S 1.200 IS 1.200 Is- 350 S560S 560 560560-560 560560 560-560 560 S 1,000 S 1,000 1,000 S1,000 1,000$ 1,000 1,000 1,000 1,000S 1,000 600 S1,640 S1,6401,640 1,6401,640 1,6401,640 1,640 1,640 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? Breakeven sales (original assumptions) Breakeven COGS (original assumptions) S 10,143 69.55% 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? Machine price (000) 4,000 10 car Sales revenue Cost of goods sold Additional personnel 5,000 S 10,000S 10,00011,384$ 11,384 S 11,384 S 11 $ 11,384 S 11,384 S 11,384 3,500 S -7,0007,000-7,969 $ -7,969 -7,969 -7,969 -7,969-7,969 -7,969-7,969 S 2,000 S 2,000 2,000S 2,000 S -2,000S 2,000$-2,000 $ 2,000S2,000 S 2,000 S 400S400 $400 S 40 400 S 400 -400 400-400 S 400 1,500 S 600S6001,015S1,015 S1,015 S1,015 $ 1,015 1,015S 1,015 S 1,015 355 975S 390 390 660 660S660660 660660 S 660 660 S 400 S400 400S400 S 400S400S 400400S 400S 400 S 11 Equals net operating income Minus income tax Equals unlevered net income Plus depreciation Capital expenditures Add to NWC Free cash flow 525 S 210 S 210 S 355 $ 355 S 355 S 355 S 355 $ 355 S 355 S 4,000 -600 S -1,200SS111 S -5,575 S 410790 S 1,000 911 9491,060 S1,060S1,0601,060 1,060 1,060S 2,060S911 NPV (000) Net Working Capital 750 |$ 1,500 | $ 1,500 |$ 1,708 |$ 1,708 |$ 1,708 |$ 1,708 |$ 1,708 |$ 1,708 |$ 1,708 |s 1,708 |$ 350 S 70700797 797 S797 S797 $-797 S 797S-797S -797 S Increased payables Increased inventory NWC (000) 1,000 1,000S 1,000 1,000S1,0001,000 S1,000S1,000 1,000 1,000 600 |S 1.800 | $ 1.800 | $ 1,911|$ 1,911 S 1,911|$ 1,911|$ 1,911 | $ 1,911|$ 1,91 1 |s 911|$ Breakeven sales (more expensive machine) Additional sales needed to break even S 11,384