FIN357 Excel Exercise 2.docx Download Info X Close Page 1 of 2 o ZOOM + Excel Exercise #2 Constructing pro forma cash flows to make a capital budgeting decision Due: Monday, October 26, 10 pm For this assignment, you will make a capital budgeting decision based on the following information. 1. Considerations After spending $300,000 for market research on the viability of making a low cost, portable scorpion tracker, your firm projects potential sales of 100,000, 125,000, 150,000, 140,000, and 120,000 over the next 5 years at an average price of $25 per unit. An Austin-area manufacturing site can be bought for $1 million. It is expected to appreciate at 7% per year, and you plan to keep it at the end of the 5 years. Manufacturing equipment will cost $550,000 and can be sold at the end of the five years for $155,000. Expect a fixed production cost of $100,000 per year and a variable cost of $20 per unit. Net working capital starts at $300,000 in year zero and is required to be 10% of sales thereafter. Assume a corporate tax rate of 21%, a cost of capital of 9%, and straight-line depreciation over three vears. FIN357 Excel Exercise 2.docx Download Info X Close Page 1 > of 2 o ZOOM + II. Build pro forma cash flows In the excel spreadsheet given to you, follow the step-by-step method of calculating free cash flow showing: . Revenues and costs EBIDTDA and depreciation EBIT and taxes Net income Operating cash flows Free cash flows Formulas should contain only references to other cells. FIN357 Excel Exercise 2.docx Download Info X Close Page of 2 o ZOOM + II. Build pro forma cash flows In the excel spreadsheet given to you, follow the step-by-step method of calculating free cash flow showing: . Revenues and costs EBIDTDA and depreciation EBIT and taxes Net income Operating cash flows Free cash flows Formulas should contain only references to other cells. FIN357 Excel Exercise 2.docx Download Info X Close Page