Hello, Please solve in excel with examples. Thank you!

A B E H L M N O P 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? 14 15 16 17 18 $ 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 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 21% 70% 15% 10% 2.750 10 1,000 10.000 5,000 2.000 10% $ S S $ a. Determine the incremental earings 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. 11 $ S 34 35 36 37 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 0 1 2 3 4 5 6 7 8 9 10 -5,000 S 10,000 S 10,000 s 10,000 S 10,000 s 10,000 S 10,000 S 10,000 S 10,000 $10,000 $ 10,000 3,500S -7,000S -7,000S -7,000 S -7,000 S -7,000 S -7,000 S -7,000 S -7,000 S -7,000 S -7,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 $ -2,000 S -275s -275s -275s -275s -275s -275s -275S -275s -275$ -275 -1,500 $ 725S 725S 725S 725S 725S 725S 725 S 725$ 725S 725 315s -152s -152 S -152s -152s -152s -152 $ -152 S -152 $ -152s -152 -1,185 573s 573s 573s 573 S 573S 573S 573 $ 573 $ 5738 573 $ 275s 275s 275s 275s 275s 275$ 275$ 275$ 275$ 275 -2,750 -600 S -1,200 S S S S s - s $ $ $ $ 1,000 $ 4,535s -352s 848s 848s 848s 848s 848s 848$ 848$ 848 $ 1,848$ $ $ S 38 39 40 41 42 $ $ $ 800 800 43 44 45 NPV (000) 46 47 48 Net Working Capital Increased receivables Increased payables Increased inventory NWC (000) 1,500$ -700 $ 49 50 51 52 $ $ $ s -750 S 1,500S 350 $ -700S 1,000 $ 1,000S 600s 1,800 s 1,500 $1,500S 1,500 $ 1,500S 1,500S -700 s -700 S -700 s -700 S -700 s 1,000 $ 1,000 1,000S 1,000S 1,000 $ 1,800 1,800 s 1,800S 1,800 s 1,800 S 1,500 $ -700S 1,000 $ 1,800 s 1,500 $ 1,500 $ -700 -700 S 1,000 $1,000 1.800 1,800 $ 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? 53 54 55 56 57 58 59 $ $ 11 $ $ 61 62 High revenue Low revenue 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 12,000 8,000 Free Cash Flows in the Best Case 0 1 2 3 4 5 6 7 8 9 10 5,000S 12,000S 12,000S 12,000S 12,000 S 12,000 S 12,000S 12,000S 12,000 $ 12,000 $ 12,000 3,500S -8,400 S 8,400 S 8,400S -8,400 S 8,400 S 8,400S -8,400$ 8,400 $ -8,400 $ -8,400 $ -2,000S -2,000 -2,000 2,000 2,000 $2,000 $2,000 $2,000 $ -2.000 $ -2,000 S -275 S -275$ -275S -275s -275s -275$ -2755 -2755 -2755 -275 -1,500S 1,325 1,325s 1,325 S 1,325s 1,3255 1,325$ 1,325$ 1,325 $ 1,325$ 1,325 315 -278s -278 S -278 S -278 S -278s -2788 -278$ -278$ -278$ 278 -1,185 S 1,047 S 1,047s 1,047 s 1,047 s 1,047S 1,047 S 1,047 $ 1,047$ 1,047 $ 1.047 Is 275 S 275 S 275 S 275S 275s 275 S 275$ 275$ 275$ 275 -2.750 -600S -1.360S $ S Is S S Is $ $ 1,000 $ 4,535s -38s 1,322S 1,322 s 1,322s 1,322 S 1,322s 1,322 S 1,322 $ 1,322 $ 2,322 $ $ $ $ 63 64 65 66 67 68 69 20 71 72 $ S $ 960 960 NPV (000) (