I want to know the solution method in Excel only without the actual solution and the use of written numbers

Problem 9-33 Complete the steps below using cell references to giver data or previous calculations. In some cases, a simple cell reference is all you need. To copypaste 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 thar function. Do not type in numerical data into a cell or function. Instead, make a reference to the cel in which the data is found. Make your computations only in the blue cdis highlighted below. In all cases, unless otherwise directed, so the earliest appearance of the data in your formulas, usually the Giver 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: Markering: 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 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 increas investory 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 fim expects receivables from the new sales to be 15% of revenues and payables to be 10% of the cost of goods sold (starting in year and ending in year 10). Billingham's marginal corporate tax rate is 35% a. Determine the incremental camnings from the purchase of the XC-750. b. Determine the free cash flow from the purchase of the XC-750. 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? Billingham could instead purchase the XC-X, which offers even greater capacity. The cost of the XC-900 is 54 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 70% Receivables as % of sales Payables as % of COGS 10% Machine price (000) Machine life years) Increased Inventory (000) First year sales (000) 10,000 Disrupted sales (000) S 5,000 Personnel (000) $ 2,000 Cost of capital 35% 15% $ 2.750 10 1,000 lululul 10% a. Determine the incremental camnings 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. b. Determine the free cash flow from the purchase of the XC-750 If the appropriate cost of capital for the expansion is 10%, compute the NPV of the purchase. 11 S s 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 s S 0 2 10 -5.000 10,000 S 10.000 S 10,000 S 10.000 S 10,000 S 10,000 $10,000 S 10,000 $10,000 S 10,000 3.500S -7.000 S -7,000 -7.000 S -7,000 5 -7,000S -7,000S -7.0005 -7,000 -7.0005 -7.000 5 -2,000 -2,000 $ -2,000 -2,000 $2,000 -2,000 -2,000 -2,000 $ -2,000 2,000 $ -2755 -2755 315$ -275$ -275S -275 -275$ -275 $ -2755 -275 -1.500S 725S 725S 725$ 725$ 725$ 725$ 725$ 7255 7255 725 525$ -2545 -2545 -2545 -2545 -1945 -2545 -2545 -2545 -2545 -254 -975s 4715 4715 4715 4715 471 $ 47115 471 $ 47115 471 S 2755 2755 2755 275 IS 2755 275 $ 2755 2755 2755 275 -2.750 -600 S .1,200 S 5 $ $ - s $ 5 5 5 1.000S -4.025s -4415 77465 7461 7465 7465 72465 7465 746 7461 1.7465 411 15 s $ s 300 NPV (000) Net Working Capital Increased receivables Incremad payables Increwed inventory NWC000) 1,500S -700S s S 5 3 -750S 3505 1,000 600 1.500 1.500S 1.500 1.500 1.500 5 1.500 5 -700 S -7005 -T00 $ -700S -700S -700 S 1,000 $ 1.000 1.000 1,000S 1.000 1.000 1.800 1.NOOL.300 1.100S 1.800 1.NOOS 1.500 5 -700S 1.000 1,005 1,500S 1.500 5 -7005 -70015 1,000S 1.000 1.NOOS 1,100 NOOS $ d. While the expected new sales will be $10 million per year from the expansion, autimates range from $8 million to 512 million. What is the NPV in the worst case in the best use? Iligh one 5 12.000 Low revenue Free Cash Flows in the Best Case Year 0 1 4 5 10 Sales revenue -5,000 S 12.000 12.000 5 12.000 12.000 12.000 S 12.000 S 12.000 S 12,000S 12.000 S 12.000 Cost of goods sold S J.500S -8.400 3,400 S 8.400S -8,400 S 8.400 S 8.400 S 8.400S -8,400 8,400 -3,400 Additional personnel S-2,000 5 2,000 5 -2,000 -2,000 $ 2,000,000 $ 2,000 2,000 2,000 -2,000 Depreciation $ 215 -2755 -2755 -2755 -275 -2755 2751 -275 -2155 -275 Equals net operating income S -1,500S 1,325 1,325 $ 1,325 S 1.325S 1,325 1.325$ 1,335 1,325 1,325 1.325 Minus income tax $ 525S 1645 1645 164 $ 4645 464 S 464 S 4645 464 S 4645 464 Equals unlevered at income S -975s 361 5 161 S 8615 161 $ M6115 161 $ 36115 16s 6115 161 Plus depreciation S 2755 2755 2755 275 $ 275 275 $ 2755 2755 275 5 275 Capital expenditures S 2.250 Add to NWC $ 600 $1,360S S S $ $ $ $ $ 1,000 Free cash flow ES 4.025s -224 1.136 S 1.1.765 1.1.11 1.1365 1.1365 1.175 1.1365 1125 2.1965 NPV (000) 960 960 Net Working Capital Increased receivables Increased payables Increased inventory NWC (000) $ 5 5 $ Year 11 $ $ Sales reve 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 NPV (000) -750S 1.800S 1,800S 1.800 S 1.800S 1.800 5 1.800S 1.800S 1.800 $ 1.800S 1.8005 350 S -8405 -8405 -8405 840S -840S -840 -8405 -840S 840 S -$405 1,000 $ 1,000 $ 1.000 5 1.000 S 1,000S 1.0005 1.000 1.000 1.000 1.000 600S 1.9605 1.9605 1.9605 1.960S 1.960 1,960 S 1.960S 1.9605 1.960S 960s Free Cash Flows in the Worst Case 0 2 3 4 5 6 7 9 10 -5,000 $ 8,000S 8,000 $ 8,000 $ 8,000S 8,000S 8,000 $ 8.000 S 8,000 $ 8,000S 8,000 3.500S -5,600S -5.600S -5,600 -5,600S -5,600S -3,600 S 5,600S -5,600 -5,600S -5,600 $ -2,000$ -2,000 5 -2,000 -2,000 S -2,000 -2,000 5 -2,000 S -2,000 5 -2,000 -2,000 $ -2755 -2755 -2755 -2755 -275 s -2755 -275$ -2755 -2755 -275 -1,500 $ 125$ 1255 1255 125S 1255 125s 1255 125s 125S 125 525 14 $ 445 -445 44 S 445 -44 $ 44 S -145 445 -14 -975S 81 $ LIS 81 $ R15 8115 81 1 8115 815 811S 81 $ 275$ 275$ 2755 275 $ 2755 2755 2755 2755 275S 275 -2.750 -600 $ -1,040 $ $ S $ $ $ $ $1,000 $ 43255 3565 3565 3565 356 s 3565 3565 3565 1,356 15 $ $ $ $ $ . 640 1.2005 -5605 1,000S 1,6405 1.200 $ 1.2005 1,200S 1.200 S -560 $ 56015 560S -5605 1.000 1.000 1.000 1.640 S 1,6405 1,640$ 6.10 S Net Working Capital Increased receivables s -750 S 1,200 S 1.2005 1,200 1,200 S 1,200 S Increwed payables $ 350 S -560 $ -560 5 -560 860S 560 S Increwed inventory $ 1,000 1,000 1,000 5 1,000 1,000 1,000 S NWC 000) s 600 1,640 5 1,610S 1,6405 1,640 1,640 What is the beak-even level of new sales from the expansion? What is the beak-even level for the cost of goods sold? Broskevan sales original assumptions) $ 10,143 Broskevan COGS (original sumptions) 0 s Billingham could instead purchase the XC-900, which offen even groter capacity. The cost of the XC-200 is S4 million. The extn capacity would not be useful in the first two years of operation, but would allow for additional sales in yours.3.10. What level of additional salon (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 Year 10 Sales revue S -5,000 10,000 $10,000 3 11,143 11,3 11,384 S 11,384 S 11.384 11,384 11,384 S 11,384 Cost of goods sold 3,500-70003 -7000 S 7969S -2.8695-7,95 -2.9693-7.969 -7.9693-749693-7,909 Additional per S 2.000 -2.000 2,000 2,000 2,000 2,000 2.000 2.000 2,000 2,000 Depreciation S 400 S -400S 400S 400 S 400S 400 -400 400S 400 Equals not operating income 1,500 600 1,015 1,015 1,015 1,015 15 1,015$ 1,015 S 1,015 1,015 Minus income tax $ 525$ 210S -210 -355 $ 355S 355 S 3555 355 355 355 Equals unlevered net income $ -975s 3901 3905 66013 660ls 660s 660S 666 $ 660s 15013 660 Plus depreciation $ 400 400s 400s 400S Capital expenditures -4.000 Add to NWC $ -600S -1,200 -1115 $ $ $ S $1,000 Free cash flow 5 -5,575 -110 7905 9495 1.060.5 1.06015 1.069 1.000 1.000 1.000 2.0405 NPV (000) -400 $ $ $ 600 S $ $ 355 $ 400 IS 400 S 400 $ 400 5 400 S 400 $ $ 5 911 911 NPV (000) S S Net Working Capital Increased receivables Increased payables Increased inventory NWC (0) S S s s -7505 350 $ 1,000 $ 600S 1,500 5 7005 1.000 1,800S 1,500 S 1,70 S -700 -797 1,000S 1,000 $ 1,800S 1,911S 1,703 -797 1.000 1,911 1.70 1.708S 1.70NS 1,708S -7975 -7975 -7975 -797 1.000 S 1,000S 1.000 1,000S 1.9115 1,911S 1,9115 1,911S 1,700 -791 1.70 -797 $ 1.000 1,911$ 911S . S 11,384 Broskevenales (more expensive machine) Additional salended to break even ruments 1 In cell 045, by using cell references, calculate the NPV of the project for this comario (pt.) In cell 071, by using cell references, calculate the NPV of the project for this scenario (dpt.) 3 In cell 092, by using cell references, calculate the NPV of the project for this scenario pt.) In cell D122, by using cell references, calculate the NPV of the project for this scenario (PL) In oell E131, by using cell reformos, calculate the additional sales needed to break even (pt) 4 5 Problem 9-33 Complete the steps below using cell references to giver data or previous calculations. In some cases, a simple cell reference is all you need. To copypaste 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 thar function. Do not type in numerical data into a cell or function. Instead, make a reference to the cel in which the data is found. Make your computations only in the blue cdis highlighted below. In all cases, unless otherwise directed, so the earliest appearance of the data in your formulas, usually the Giver 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: Markering: 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 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 increas investory 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 fim expects receivables from the new sales to be 15% of revenues and payables to be 10% of the cost of goods sold (starting in year and ending in year 10). Billingham's marginal corporate tax rate is 35% a. Determine the incremental camnings from the purchase of the XC-750. b. Determine the free cash flow from the purchase of the XC-750. 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? Billingham could instead purchase the XC-X, which offers even greater capacity. The cost of the XC-900 is 54 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 70% Receivables as % of sales Payables as % of COGS 10% Machine price (000) Machine life years) Increased Inventory (000) First year sales (000) 10,000 Disrupted sales (000) S 5,000 Personnel (000) $ 2,000 Cost of capital 35% 15% $ 2.750 10 1,000 lululul 10% a. Determine the incremental camnings 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. b. Determine the free cash flow from the purchase of the XC-750 If the appropriate cost of capital for the expansion is 10%, compute the NPV of the purchase. 11 S s 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 s S 0 2 10 -5.000 10,000 S 10.000 S 10,000 S 10.000 S 10,000 S 10,000 $10,000 S 10,000 $10,000 S 10,000 3.500S -7.000 S -7,000 -7.000 S -7,000 5 -7,000S -7,000S -7.0005 -7,000 -7.0005 -7.000 5 -2,000 -2,000 $ -2,000 -2,000 $2,000 -2,000 -2,000 -2,000 $ -2,000 2,000 $ -2755 -2755 315$ -275$ -275S -275 -275$ -275 $ -2755 -275 -1.500S 725S 725S 725$ 725$ 725$ 725$ 725$ 7255 7255 725 525$ -2545 -2545 -2545 -2545 -1945 -2545 -2545 -2545 -2545 -254 -975s 4715 4715 4715 4715 471 $ 47115 471 $ 47115 471 S 2755 2755 2755 275 IS 2755 275 $ 2755 2755 2755 275 -2.750 -600 S .1,200 S 5 $ $ - s $ 5 5 5 1.000S -4.025s -4415 77465 7461 7465 7465 72465 7465 746 7461 1.7465 411 15 s $ s 300 NPV (000) Net Working Capital Increased receivables Incremad payables Increwed inventory NWC000) 1,500S -700S s S 5 3 -750S 3505 1,000 600 1.500 1.500S 1.500 1.500 1.500 5 1.500 5 -700 S -7005 -T00 $ -700S -700S -700 S 1,000 $ 1.000 1.000 1,000S 1.000 1.000 1.800 1.NOOL.300 1.100S 1.800 1.NOOS 1.500 5 -700S 1.000 1,005 1,500S 1.500 5 -7005 -70015 1,000S 1.000 1.NOOS 1,100 NOOS $ d. While the expected new sales will be $10 million per year from the expansion, autimates range from $8 million to 512 million. What is the NPV in the worst case in the best use? Iligh one 5 12.000 Low revenue Free Cash Flows in the Best Case Year 0 1 4 5 10 Sales revenue -5,000 S 12.000 12.000 5 12.000 12.000 12.000 S 12.000 S 12.000 S 12,000S 12.000 S 12.000 Cost of goods sold S J.500S -8.400 3,400 S 8.400S -8,400 S 8.400 S 8.400 S 8.400S -8,400 8,400 -3,400 Additional personnel S-2,000 5 2,000 5 -2,000 -2,000 $ 2,000,000 $ 2,000 2,000 2,000 -2,000 Depreciation $ 215 -2755 -2755 -2755 -275 -2755 2751 -275 -2155 -275 Equals net operating income S -1,500S 1,325 1,325 $ 1,325 S 1.325S 1,325 1.325$ 1,335 1,325 1,325 1.325 Minus income tax $ 525S 1645 1645 164 $ 4645 464 S 464 S 4645 464 S 4645 464 Equals unlevered at income S -975s 361 5 161 S 8615 161 $ M6115 161 $ 36115 16s 6115 161 Plus depreciation S 2755 2755 2755 275 $ 275 275 $ 2755 2755 275 5 275 Capital expenditures S 2.250 Add to NWC $ 600 $1,360S S S $ $ $ $ $ 1,000 Free cash flow ES 4.025s -224 1.136 S 1.1.765 1.1.11 1.1365 1.1365 1.175 1.1365 1125 2.1965 NPV (000) 960 960 Net Working Capital Increased receivables Increased payables Increased inventory NWC (000) $ 5 5 $ Year 11 $ $ Sales reve 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 NPV (000) -750S 1.800S 1,800S 1.800 S 1.800S 1.800 5 1.800S 1.800S 1.800 $ 1.800S 1.8005 350 S -8405 -8405 -8405 840S -840S -840 -8405 -840S 840 S -$405 1,000 $ 1,000 $ 1.000 5 1.000 S 1,000S 1.0005 1.000 1.000 1.000 1.000 600S 1.9605 1.9605 1.9605 1.960S 1.960 1,960 S 1.960S 1.9605 1.960S 960s Free Cash Flows in the Worst Case 0 2 3 4 5 6 7 9 10 -5,000 $ 8,000S 8,000 $ 8,000 $ 8,000S 8,000S 8,000 $ 8.000 S 8,000 $ 8,000S 8,000 3.500S -5,600S -5.600S -5,600 -5,600S -5,600S -3,600 S 5,600S -5,600 -5,600S -5,600 $ -2,000$ -2,000 5 -2,000 -2,000 S -2,000 -2,000 5 -2,000 S -2,000 5 -2,000 -2,000 $ -2755 -2755 -2755 -2755 -275 s -2755 -275$ -2755 -2755 -275 -1,500 $ 125$ 1255 1255 125S 1255 125s 1255 125s 125S 125 525 14 $ 445 -445 44 S 445 -44 $ 44 S -145 445 -14 -975S 81 $ LIS 81 $ R15 8115 81 1 8115 815 811S 81 $ 275$ 275$ 2755 275 $ 2755 2755 2755 2755 275S 275 -2.750 -600 $ -1,040 $ $ S $ $ $ $ $1,000 $ 43255 3565 3565 3565 356 s 3565 3565 3565 1,356 15 $ $ $ $ $ . 640 1.2005 -5605 1,000S 1,6405 1.200 $ 1.2005 1,200S 1.200 S -560 $ 56015 560S -5605 1.000 1.000 1.000 1.640 S 1,6405 1,640$ 6.10 S Net Working Capital Increased receivables s -750 S 1,200 S 1.2005 1,200 1,200 S 1,200 S Increwed payables $ 350 S -560 $ -560 5 -560 860S 560 S Increwed inventory $ 1,000 1,000 1,000 5 1,000 1,000 1,000 S NWC 000) s 600 1,640 5 1,610S 1,6405 1,640 1,640 What is the beak-even level of new sales from the expansion? What is the beak-even level for the cost of goods sold? Broskevan sales original assumptions) $ 10,143 Broskevan COGS (original sumptions) 0 s Billingham could instead purchase the XC-900, which offen even groter capacity. The cost of the XC-200 is S4 million. The extn capacity would not be useful in the first two years of operation, but would allow for additional sales in yours.3.10. What level of additional salon (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 Year 10 Sales revue S -5,000 10,000 $10,000 3 11,143 11,3 11,384 S 11,384 S 11.384 11,384 11,384 S 11,384 Cost of goods sold 3,500-70003 -7000 S 7969S -2.8695-7,95 -2.9693-7.969 -7.9693-749693-7,909 Additional per S 2.000 -2.000 2,000 2,000 2,000 2,000 2.000 2.000 2,000 2,000 Depreciation S 400 S -400S 400S 400 S 400S 400 -400 400S 400 Equals not operating income 1,500 600 1,015 1,015 1,015 1,015 15 1,015$ 1,015 S 1,015 1,015 Minus income tax $ 525$ 210S -210 -355 $ 355S 355 S 3555 355 355 355 Equals unlevered net income $ -975s 3901 3905 66013 660ls 660s 660S 666 $ 660s 15013 660 Plus depreciation $ 400 400s 400s 400S Capital expenditures -4.000 Add to NWC $ -600S -1,200 -1115 $ $ $ S $1,000 Free cash flow 5 -5,575 -110 7905 9495 1.060.5 1.06015 1.069 1.000 1.000 1.000 2.0405 NPV (000) -400 $ $ $ 600 S $ $ 355 $ 400 IS 400 S 400 $ 400 5 400 S 400 $ $ 5 911 911 NPV (000) S S Net Working Capital Increased receivables Increased payables Increased inventory NWC (0) S S s s -7505 350 $ 1,000 $ 600S 1,500 5 7005 1.000 1,800S 1,500 S 1,70 S -700 -797 1,000S 1,000 $ 1,800S 1,911S 1,703 -797 1.000 1,911 1.70 1.708S 1.70NS 1,708S -7975 -7975 -7975 -797 1.000 S 1,000S 1.000 1,000S 1.9115 1,911S 1,9115 1,911S 1,700 -791 1.70 -797 $ 1.000 1,911$ 911S . S 11,384 Broskevenales (more expensive machine) Additional salended to break even ruments 1 In cell 045, by using cell references, calculate the NPV of the project for this comario (pt.) In cell 071, by using cell references, calculate the NPV of the project for this scenario (dpt.) 3 In cell 092, by using cell references, calculate the NPV of the project for this scenario pt.) In cell D122, by using cell references, calculate the NPV of the project for this scenario (PL) In oell E131, by using cell reformos, calculate the additional sales needed to break even (pt) 4 5