Question

Billingham Packaging is considering expanding its production capacity by purchasing a new​ machine, the​ XC-750. The cost of the​ XC-750 is $2.68 million. Unfortunately, installing this machine will take several months and will partially disrupt production. The firm has just completed a $46,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.05 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 $4.93 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 71 % of their sale price. The increased production will also require an increased inventory on hand of $ 1.07 million during the life of the project. The increased production will require an additional inventory of $ 1.07 ​million, to be added in year 0 and depleted in year 10.

• 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 in years​ 1?10. Receivables are expected to be 15 % of revenues and payables to be

11% of the cost of goods sold.​ Billingham's marginal corporate tax rate is 15%.

a. Determine the incremental earning 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.3%, compute the NPV of the purchase                 

d. While the expected new sales will be $10.05 million per year from $7.95 to $12.15 million. What is the NPV in the worst case? In the best case?     

If all possible can you solve using excel showing formulas Thanks!

Answer 1

A									Amount in Million $
Year	1	2	3	4	5	6	7	8	9	10
Additional Sales	10.05	10.05	10.05	10.05	10.05	10.05	10.05	10.05	10.05	10.05
Disruptions	4.93	0	0	0	0	0	0	0	0	0
Cost Of Goods	7.1355	7.1355	7.1355	7.1355	7.1355	7.1355	7.1355	7.1355	7.1355	7.1355
Personnels	2	2	2	2	2	2	2	2	2	2
Incremental earnings	-4.0155	0.9145	0.9145	0.9145	0.9145	0.9145	0.9145	0.9145	0.9145	0.9145
Net Incremental earnings	4.215

B

We Know That,

Free Cash Flows = (Revenue-Expenses-Depreciation)*(1-Tax rate)+Depreciation-Capital expenditure-Changes in Net Working Capital

OR

Free Cash Flows = (Incremental Earnings-Depreciation)*(1-Tax rate)+Depreciation-Capital expenditure+/-Changes in Net Working Capital

Net Working Capital(NWC) = Cash+Inventory+Receivables-Payables

Therefore

Inventory = 1.07

Additional Inventory = 1.07

Receivables = 15% of Revenues

Payables = 11% of Cost of Goods Sold

Annual Depreciation = 2.68/10 = 0.268

NWC = 0+1.07+1.07+(0.15*100.5)-(0.11*71.355)

NWC = $9.366 Million. Since NWC is +ve it should be added back to FCF

Therefore FCF = (4.215-0.268)*(1-0.15)+0.268-2.68+9.366

FCF = $10.30895 Million

C

Net Present Value = Present value of Cash Inflows - Present value of Cash Outflows

Required Return - 10.03%

PV of Cash Flows = FCF = 10.30895

Required Return - 10.30% every year

PV of Cash outflow = Cost of the machine = 2.68

PV Interest factor of 10.30% for 10 years is (1/1.103)= M+ for 10 times and MRC in Calculator

PVIF for 10 years = 6.07

Therefore NPV = (10.30895*6.07)-2.68

NPV = $59.895 Million

D

BEST CASE NPV

WORST CASE NPV

Sales - 12.15

Sales - 7.95

COGS - 71% of 12.15 = 8.6265

COGS - 71% of 7.95 = 5.6445

Annual Depreciation = 2.68/10 = 0.268

Annual Depreciation = 2.68/10 = 0.268

Tax rate - 15%

Tax rate - 15%

Cost of Capital or Required Return - 10.3%

Cost of Capital or Required Return - 10.3%

Annual cash flow(ACF) = [(Sales-COGS)-Fixed Cost]*(1-tax Rate)+Depreciation*tax rate

Annual cash flow(ACF) = [(Sales-COGS)-Fixed Cost]*(1-tax Rate)+Depreciation*tax rate

ACF = [(12.15-8.6265)-0]*(1-0.15)+0.268*0.15

ACF = [(7.95-5.6445)-0]*(1-0.15)+0.268*0.15

ACF = $3.035 Million

ACF = $2.00 Million

Best Case NPV = Present Value of ACF - initial Investment

Worst Case NPV = Present Value of ACF - initial Investment

Best Case NPV = (3.035*6.07) - 2.68

Worst Case NPV = (2*6.07) - 2.68

Best case NPV = $15.742 Million

Worst case NPV = $9.46 Million