Need help with only the requirements (the 5 blue blanks) need the formulas for them too

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 (starting in year 0 and ending in year 10). Billingham's marginal corporate tax rate is 35% a. Determine the incremental eamings 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 35% Cost of goods as % of sales 70% Receivables as % of sales 15% Payables as % of COGS Machine price (000) Machine life (years) Increased inventory (000) 10% 2,750 10 1,000 First year sales (000) Disrupted sales (000) Personnel (000) Cost of capital 10,000 5,000 2,000 $ 10% 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. Year 1 2 3 5 6 7 8 10 11 $ 10,000 $ 10,000 10,000 10,000 10,000 10,000 -7,000 -7,000 -7,000 -7,000 S -7,000 -2,000 2,000 -5,000 10,000 3,500 7,000 S 10,000 $ 10,000 10,000 $ -7,000 $ -7,000 -7,000 -7,000 $ -2,000 2,000 $ -275 $ 725$ -254 $ Sales revenue Cost of goods sold Additional personnel Depreciation Equals net operating income -2,000$ -2,000$ -275 $ 725 $ $ 2,000 -2,000 -2,000$ -275 $ -2,000 -275 $ -275 $ -275 -275 $ -275 S 275 $ -275 -1,500 $ 525 $ -975 $ 725 $ -254 $ 725 725 725 725 $ 725 725 725 Minus income tax S -254 -254 -254 S -254 $ -254 S -254 S -254 $ -254 Equals unlevered net income Plus depreciation $ 471 471 471 $ 471 $ 471 $ 471 $ 471 $ 471 $ 471 471 275 S 275 $ 275 275 275 $ 275 $ 275 $ 275 275 275 Capital expenditures -2,750 $ -1,200 Add to NWC $ -600 S $ $ $ $ S $ $ 1,000 $ 800 746 $ 746 $ Free cash flow $ -4,325 -454 S 746 746 746 S 746 $ 746 746 $ 1,746 $ 800 NPV (000) Net Working Capital 1,500 S 1,500 $ 1,500 S 1,500 S 1,500 S Increased receivables 1,500 1,500 -700$ 1,000 $ 1,500 -750 $ 1,500 $ 1,500 $ Increased payables S 350 $ 700 $ -700 -700 $ -700 $ -700 $ -700 $ -700 $ -700 -700 $ 1,000 $ 1,800 S 1,000$ 1,800 $ 1,000 $ 1,000 $ 1,000 $ 1,800 $ Increased inventory 1,000 1,800 $ S 1,000 S 1,000 $ 1,000 NWC (000) 600 800 $ 1,800 1,800 $ 1,800 1,800 $ 1,800 $ 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? High revenue $ 12,000 Low revenue $ 8,000 Free Cash Flows in the Best Case Year 0 1 2 4 5 6 9 10 11 -5,000 12,000 12,000 12,000 12,000 12,000 12,000 12,000 $ 12,000 $ -8,400 $ 2,000 $ 12,000 $ 12,000 Sales revenue 3,500 -8,400 -8,400 -8,400 S -8,400$ -8,400 -8,400$ -8,400 $ -2,000 -275 $ 1,325 $ -464 $ 861 $ $ -8,400 $ 8,400 Cost of goods sold Additional personnel Depreciation Equals net operating income Minus income tax $ -2,000 -275 $ S 2,000 -2,000 -2,000 -2,000 2,000 -275 $ 1,325 $ $ -2,000 $ -2,000 -275 $ -275 $ -275 $ 1,325 $ $ -275 S 275 S -275 $ -275 1,325$ -1,500 $ 525 $ 1,325 S 1,325 S 1,325 $ 1,325 -464 1,325 -464 $ 1,325 $ -464 -464 -464 464 $ -464 $ -464 $ -464 $ Equals unlevered net income Plus depreciation Capital expenditures -975 $ 861 861 861 $ 861 $ 861 S 861 861 861 861 275 $ 275 275 $ 275 $ 275 275 275 $ 275 $ 275 275 S -2,750 -600 S -4,325 S 1,000 $ 2,136 $ -1,360 -224$ 960 Add to NWC $ $ $ 1,136 S 1,136$ 1,136 S 1,136 1,136 $ 1,136 S Free cash flow $ 1,136 $ 1,136 S 960 NPV (000) Net Working Capital -750 S Increased receivables 1,800$ 1,800 S 1,800$ 1,800 S 1,800 $ 1,800 $ 1,800 $ 1,800 1,800 $ 1,800 S Increased payables Increased inventory NWC (000) -840 $ $ 350 $ -840 $ -840 $ -840 $ -840 S -840 $ -840 $ -840 -840 S -840 $ 1,000 $ 1,960 $ 1,000S 1,960$ 1,000 $ 1,960 $ 1,000 1,960 1,000 1,000 1,960 $ 1,000 1,000 $ 1,000 S $ 1,000 $ $ $ S 1,960$ 600 $ 1,960 $ 1,960 $ 1,960 $ 960 Free Cash Flows in the Worst Case Year 0 2 3 4 5 6 7 8 9 10 11 8,000 $ 8,000 $ 8,000S 8,000 $ Sales revenue -5,000 8,000 8,000 8,000 -5,600 -5,600 -5,600 -5,600 $ 2,000$ $ 8,000 $ 8,000 8,000 3,500 -5,600 -5,600 5,600 S -5,600 S -5,600 Cost of goods sold Additional personnel Depreciation Equals net operating income $ -5,600 -2,000 $ -275 $ -2,000 2,000 -275 $ -2,000 -2,000$ -2,000 $ -275 $ S 2,000 -2,000 -275 $ 125 $ -2,000 S -275 $ -275 $ -275 S -275 -275 $ 275 -1,500 525 $ 975 $ 125 $ 125 $ 125 125 S 125 125 $ 125 S 125 125 -44 S -44$ Minus income tax -44 $ -44 $ -44 $ -44 $ -44 -44 $ -44 $ -44 Equals unlevered net income Plus depreciation Capital expenditures 81 81 $ 81 $ 81 $ 81 81 81 81 81 $ 81 275 $ 275 $ 275 $ 275 275 S 275 $ 275 $ 275 S 275 275 -2,750 1,000S Add to NWC $ -600 $ -1,040 $ $ $ $ S $ S S $ 640 356 $ -4,325 $ 356 S Free cash flow 356 356 $ -684 356 356 S 356 356 S 1,356 640 NPV (000) 240 7 Net Working Capital Increased receivables Increased payables Increased inventory NWC (000) 1,200 S -560 S 1,000 S 1,640 S 1,200 S -560 S 1,200 S -560 S 1.000 S -750 S 350 S 1,000 S 600 S 1,200 S 1,200 S -560 S 1,000 S 1,640 S 1,200 S -560 S 1,000 S 1,640 S 1,200 S -560 S 1,200 S -560 S 1,200 S -560 S 1,200 S -560 S 1,000 S 1,640 S S -560 S 1,000 S 1,640 S 1,000 S 1,640 S 1,000 1,640 S S 1,000 1,640 S 1,640 S 640 S 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) $ 10,143 69.55% 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? Machine price (000) 4,000 Year 0 1 2 3 4 5 6 7 8 9 10 11 -5,000 10,000 S 10,000 S 11,384 3,500 S -7,000 -7,000 s -7,969S -7,969 S S 11,384 S 11,384 S 11,384S -7,969 -7,969 S -7,969 S -7,969 s -7,969 S-7,969 S 11,384 S 11,384 S 11,384 S 11,384 Sales revenue Cost of goods sold Additional personnel Depreciation $ -2,000 -2,000 400 S -2,000 S -2,000 S -2,000 S -2,000S -2,000 S -2,000 S 400 S -2,000 S 2,000 400 S 1,015 S 400 S 600 1,015 400 S 1,015 S 400 S 400 S 400 S 1,015 S -355 S -400 400 1,500 S 525 S 1,015 S 355 S 1,015 S -355 S 1,015 S 355 S Equals net operating income Minus income tax 600 S -210 S S S 1,015 -210 S -355 S 355 S 355 S 355 Equals unlevercd net income Plus depreciation Capital expenditures Add to NWC -975 S 390 S 660 S 660 S 660 S 660 S 660 S 660 S 400 S S 390 S 660 S 660 400 S 400 400 400 400 400 400 S 400 S 400 -4,000 -600 -1,200 S -5,575 $ 1,000 S -111 S S S S 911 949 S 1,060 S 790 S 1,060 S 1,060 S 1,060 S 1,060 S 1,060 S Free cash flow -410 S 2,060 S 911 NPV (000) Net Working Capital 1,708 S -797 S 1,500 S 700 S 1,708 S -797 S 1,000 S 1,911 S 1,708 S 1,708 S -797 S 1,000 1,708 S -797 S -750 S 350 S 1,000 S 1,500 S -700 S 1,000 S 1,708 S 1,708 S 797 S 1,000 S 1,911 S Increased recivables S 1,708 797 S 1,000 S -797 S 1,000 S 1,911 S Increased payables Increased inventory NWC (000) -797 1,000 S 1,911 S 1,000 1,000 911 S 600 S 1,800 S 1,800 S 1,911 S 1,911 S 1,911 S S 11,384 Breakeven sales (more expensive machine) Additional sales needed to break even Requirements In cell D45, by using cell references, calculate the NPV of the project for this scenario (1 pt.) 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.) In cell E131, by using cell references, calculate the additional sales needed to break even (1 pt.) 1 2 3 4 5 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 (starting in year 0 and ending in year 10). Billingham's marginal corporate tax rate is 35% a. Determine the incremental eamings 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 35% Cost of goods as % of sales 70% Receivables as % of sales 15% Payables as % of COGS Machine price (000) Machine life (years) Increased inventory (000) 10% 2,750 10 1,000 First year sales (000) Disrupted sales (000) Personnel (000) Cost of capital 10,000 5,000 2,000 $ 10% 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. Year 1 2 3 5 6 7 8 10 11 $ 10,000 $ 10,000 10,000 10,000 10,000 10,000 -7,000 -7,000 -7,000 -7,000 S -7,000 -2,000 2,000 -5,000 10,000 3,500 7,000 S 10,000 $ 10,000 10,000 $ -7,000 $ -7,000 -7,000 -7,000 $ -2,000 2,000 $ -275 $ 725$ -254 $ Sales revenue Cost of goods sold Additional personnel Depreciation Equals net operating income -2,000$ -2,000$ -275 $ 725 $ $ 2,000 -2,000 -2,000$ -275 $ -2,000 -275 $ -275 $ -275 -275 $ -275 S 275 $ -275 -1,500 $ 525 $ -975 $ 725 $ -254 $ 725 725 725 725 $ 725 725 725 Minus income tax S -254 -254 -254 S -254 $ -254 S -254 S -254 $ -254 Equals unlevered net income Plus depreciation $ 471 471 471 $ 471 $ 471 $ 471 $ 471 $ 471 $ 471 471 275 S 275 $ 275 275 275 $ 275 $ 275 $ 275 275 275 Capital expenditures -2,750 $ -1,200 Add to NWC $ -600 S $ $ $ $ S $ $ 1,000 $ 800 746 $ 746 $ Free cash flow $ -4,325 -454 S 746 746 746 S 746 $ 746 746 $ 1,746 $ 800 NPV (000) Net Working Capital 1,500 S 1,500 $ 1,500 S 1,500 S 1,500 S Increased receivables 1,500 1,500 -700$ 1,000 $ 1,500 -750 $ 1,500 $ 1,500 $ Increased payables S 350 $ 700 $ -700 -700 $ -700 $ -700 $ -700 $ -700 $ -700 -700 $ 1,000 $ 1,800 S 1,000$ 1,800 $ 1,000 $ 1,000 $ 1,000 $ 1,800 $ Increased inventory 1,000 1,800 $ S 1,000 S 1,000 $ 1,000 NWC (000) 600 800 $ 1,800 1,800 $ 1,800 1,800 $ 1,800 $ 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? High revenue $ 12,000 Low revenue $ 8,000 Free Cash Flows in the Best Case Year 0 1 2 4 5 6 9 10 11 -5,000 12,000 12,000 12,000 12,000 12,000 12,000 12,000 $ 12,000 $ -8,400 $ 2,000 $ 12,000 $ 12,000 Sales revenue 3,500 -8,400 -8,400 -8,400 S -8,400$ -8,400 -8,400$ -8,400 $ -2,000 -275 $ 1,325 $ -464 $ 861 $ $ -8,400 $ 8,400 Cost of goods sold Additional personnel Depreciation Equals net operating income Minus income tax $ -2,000 -275 $ S 2,000 -2,000 -2,000 -2,000 2,000 -275 $ 1,325 $ $ -2,000 $ -2,000 -275 $ -275 $ -275 $ 1,325 $ $ -275 S 275 S -275 $ -275 1,325$ -1,500 $ 525 $ 1,325 S 1,325 S 1,325 $ 1,325 -464 1,325 -464 $ 1,325 $ -464 -464 -464 464 $ -464 $ -464 $ -464 $ Equals unlevered net income Plus depreciation Capital expenditures -975 $ 861 861 861 $ 861 $ 861 S 861 861 861 861 275 $ 275 275 $ 275 $ 275 275 275 $ 275 $ 275 275 S -2,750 -600 S -4,325 S 1,000 $ 2,136 $ -1,360 -224$ 960 Add to NWC $ $ $ 1,136 S 1,136$ 1,136 S 1,136 1,136 $ 1,136 S Free cash flow $ 1,136 $ 1,136 S 960 NPV (000) Net Working Capital -750 S Increased receivables 1,800$ 1,800 S 1,800$ 1,800 S 1,800 $ 1,800 $ 1,800 $ 1,800 1,800 $ 1,800 S Increased payables Increased inventory NWC (000) -840 $ $ 350 $ -840 $ -840 $ -840 $ -840 S -840 $ -840 $ -840 -840 S -840 $ 1,000 $ 1,960 $ 1,000S 1,960$ 1,000 $ 1,960 $ 1,000 1,960 1,000 1,000 1,960 $ 1,000 1,000 $ 1,000 S $ 1,000 $ $ $ S 1,960$ 600 $ 1,960 $ 1,960 $ 1,960 $ 960 Free Cash Flows in the Worst Case Year 0 2 3 4 5 6 7 8 9 10 11 8,000 $ 8,000 $ 8,000S 8,000 $ Sales revenue -5,000 8,000 8,000 8,000 -5,600 -5,600 -5,600 -5,600 $ 2,000$ $ 8,000 $ 8,000 8,000 3,500 -5,600 -5,600 5,600 S -5,600 S -5,600 Cost of goods sold Additional personnel Depreciation Equals net operating income $ -5,600 -2,000 $ -275 $ -2,000 2,000 -275 $ -2,000 -2,000$ -2,000 $ -275 $ S 2,000 -2,000 -275 $ 125 $ -2,000 S -275 $ -275 $ -275 S -275 -275 $ 275 -1,500 525 $ 975 $ 125 $ 125 $ 125 125 S 125 125 $ 125 S 125 125 -44 S -44$ Minus income tax -44 $ -44 $ -44 $ -44 $ -44 -44 $ -44 $ -44 Equals unlevered net income Plus depreciation Capital expenditures 81 81 $ 81 $ 81 $ 81 81 81 81 81 $ 81 275 $ 275 $ 275 $ 275 275 S 275 $ 275 $ 275 S 275 275 -2,750 1,000S Add to NWC $ -600 $ -1,040 $ $ $ $ S $ S S $ 640 356 $ -4,325 $ 356 S Free cash flow 356 356 $ -684 356 356 S 356 356 S 1,356 640 NPV (000) 240 7 Net Working Capital Increased receivables Increased payables Increased inventory NWC (000) 1,200 S -560 S 1,000 S 1,640 S 1,200 S -560 S 1,200 S -560 S 1.000 S -750 S 350 S 1,000 S 600 S 1,200 S 1,200 S -560 S 1,000 S 1,640 S 1,200 S -560 S 1,000 S 1,640 S 1,200 S -560 S 1,200 S -560 S 1,200 S -560 S 1,200 S -560 S 1,000 S 1,640 S S -560 S 1,000 S 1,640 S 1,000 S 1,640 S 1,000 1,640 S S 1,000 1,640 S 1,640 S 640 S 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) $ 10,143 69.55% 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? Machine price (000) 4,000 Year 0 1 2 3 4 5 6 7 8 9 10 11 -5,000 10,000 S 10,000 S 11,384 3,500 S -7,000 -7,000 s -7,969S -7,969 S S 11,384 S 11,384 S 11,384S -7,969 -7,969 S -7,969 S -7,969 s -7,969 S-7,969 S 11,384 S 11,384 S 11,384 S 11,384 Sales revenue Cost of goods sold Additional personnel Depreciation $ -2,000 -2,000 400 S -2,000 S -2,000 S -2,000 S -2,000S -2,000 S -2,000 S 400 S -2,000 S 2,000 400 S 1,015 S 400 S 600 1,015 400 S 1,015 S 400 S 400 S 400 S 1,015 S -355 S -400 400 1,500 S 525 S 1,015 S 355 S 1,015 S -355 S 1,015 S 355 S Equals net operating income Minus income tax 600 S -210 S S S 1,015 -210 S -355 S 355 S 355 S 355 Equals unlevercd net income Plus depreciation Capital expenditures Add to NWC -975 S 390 S 660 S 660 S 660 S 660 S 660 S 660 S 400 S S 390 S 660 S 660 400 S 400 400 400 400 400 400 S 400 S 400 -4,000 -600 -1,200 S -5,575 $ 1,000 S -111 S S S S 911 949 S 1,060 S 790 S 1,060 S 1,060 S 1,060 S 1,060 S 1,060 S Free cash flow -410 S 2,060 S 911 NPV (000) Net Working Capital 1,708 S -797 S 1,500 S 700 S 1,708 S -797 S 1,000 S 1,911 S 1,708 S 1,708 S -797 S 1,000 1,708 S -797 S -750 S 350 S 1,000 S 1,500 S -700 S 1,000 S 1,708 S 1,708 S 797 S 1,000 S 1,911 S Increased recivables S 1,708 797 S 1,000 S -797 S 1,000 S 1,911 S Increased payables Increased inventory NWC (000) -797 1,000 S 1,911 S 1,000 1,000 911 S 600 S 1,800 S 1,800 S 1,911 S 1,911 S 1,911 S S 11,384 Breakeven sales (more expensive machine) Additional sales needed to break even Requirements In cell D45, by using cell references, calculate the NPV of the project for this scenario (1 pt.) 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.) In cell E131, by using cell references, calculate the additional sales needed to break even (1 pt.) 1 2 3 4 5