please help me fill this
C D E F 6 H J L M N 0 P a R Input label Sales per unit innut value $25 BEST CASE Year 1 2 2 Unit sales Raverun/unit Revenue Variable cost/unit Variable cost/y Fixed past/yr less operating cost EBITDA less depreciation EBIT less taxes (21%) Net Income add back deprecation OCH NWC Charge in NWC Equipment Building less capital expenditures FOF Case 2 + 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 years. 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 C D E F 6 H J L M N 0 P a R Input label Sales per unit innut value $25 BEST CASE Year 1 2 2 Unit sales Raverun/unit Revenue Variable cost/unit Variable cost/y Fixed past/yr less operating cost EBITDA less depreciation EBIT less taxes (21%) Net Income add back deprecation OCH NWC Charge in NWC Equipment Building less capital expenditures FOF Case 2 + 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 years. 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
