Question

Badger Billing is considering opening a new production facility in Madison, Wisconsin. In deciding whether to proceed with the project, the company has gathered the follwing information:

The estimated up-front cost of constructing the new facility at t=0 is $12 million. For tax purposes, the facility will be depreciated on a straight-line basis of 5 years (eg: $2.4 million/year) Badger will operate the facility for four (4) years. The company estimates that the facility can be sold at the end of the project's life (t=4) for $3.0 million.

If the facility is opened, Badger will need to increase its inventory by $2.2 milllion at t= 0. In addition, its accounts payable will increase by $1.4 million at t=0. The company's net operating working capital will be recovered at t=4.

The new facility will increase Badger's annual sales by $8.6 million.

The operating costs (excluding depreciation) of the new facility are expected to equal 52% of annual revenues [$4,472,000].

The company's tax rate is 21 percent.

The project's cost of capital is 14 percent.

Create a spreadsheet, showing calculation for the project's Initial Cash Outlay, Ongoing AT Cash Inflows and Terminal AT Cash Flow

Answer 1

Costs	Amounts
construction cost of new facility	$12,000,000
increase in inventory	$2,200,000
Increase in accounts payable	-$1,400,000
Initial cash outlay	$12,800,000

increase in inventory is a cash outlay and increase in accounts payable is a cash inlay.

Calculations

Ongoing AT Cash Inflows

Years	1	2	3	4
Annual sales increase	$8,600,000	$8,600,000	$8,600,000	$8,600,000
Operating costs	$4,472,000	$4,472,000	$4,472,000	$4,472,000
Depreciation	$2,400,000	$2,400,000	$2,400,000	$2,400,000
Before-tax cash flows	$1,728,000	$1,728,000	$1,728,000	$1,728,000
Tax @ 21%	$362,880	$362,880	$362,880	$362,880
After-tax cash flows	$1,365,120	$1,365,120	$1,365,120	$1,365,120
Depreciation	$3,765,120	$3,765,120	$3,765,120	$3,765,120
recovery of working capital	$0	$0	$0	$800,000
Ongoing AT cash inflows	$5,130,240	$5,130,240	$5,130,240	$5,930,240

Depreciation will be added back because it's a non-cash expense. but it also gets tax shield. so, it will be first subtracted from sales.

Calculations

Terminal AT Cash Flow

Sale value of facility in year 4	$3,000,000
remaining book value	$2,400,000
Capital gain	$600,000
Tax on Capital gain @ 21%	$126,000
Terminal AT Cash Flow	$2,874,000

Annual depreciation is $2,400,000 and life of facility is 5-years. but depreciation will be applied for 4-years which is life of the project. so, year 5 depreciation is remaining book value of facility.

Calculations