For this assignment, you will construct pro forma cash flows to make a capital budgeting decision.

***I KNOW THIS IS A LONG QUESTION, BUT PLEASE HELP!!! AND PLEASE TEXT ME AT 214-952-5398 SO I CAN SEND YOU THE EXCEL TEMPLATE (or follow the one included) AND I WILL SEND YOU $75 IF YOU GET IT DONE TONIGHT!!!*** seriously, i want to pay whoever helps me out! please!

1. Considerations

After spending $300,000 for market research on the viability of making a low cost, portable espresso maker for RVs, 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 6% per year, and you plan to keep at the end of the 5 years. New equipment will cost $500,000 and can be liquidated at the end of the five years for $125,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 12% of sales thereafter. Assume a corporate tax rate of 21%, a cost of capital of 12%, and straight-line depreciation over three years.

2. 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.

Q1. What is the project NPV and IRR? Should you take it?

Q2. How does your answer change if the Biden administration raises the corporate tax rate back to 34%?

Turn in:

• An excel file with a single worksheet. Use the excel file that I give you as a template. There is no need to change any of the row or column headings, but you must properly fill and format all values and formulas. Yellow cells are for you input. Green cells are for your output (answers).
• Formulas should contain only references to other cells.
• Format each cell so that dollar values rounded to the nearest dollar and percentages have one decimal place.
• Do not include in your excel file any extraneous work or additional worksheets. There are no bonus points for extra work. To the contrary, anything that distracts from a clean and simple submission will result in a mark down.
• example template below

inputs label Input value 3 4 5 6 7 Year 0 100,000 2 125,000 3 150,000 140,000 5 120,000 Unit sales Revenue/Unit Revenue Variable cost Fixed cost less operating cost EBITDA less depreciation EBIT less taxes 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 Net Income add back deprecation OCF NWC Change in NWC Equipment Building less capital expenditures FCF NPY IRR inputs label Input value 3 4 5 6 7 Year 0 100,000 2 125,000 3 150,000 140,000 5 120,000 Unit sales Revenue/Unit Revenue Variable cost Fixed cost less operating cost EBITDA less depreciation EBIT less taxes 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 Net Income add back deprecation OCF NWC Change in NWC Equipment Building less capital expenditures FCF NPY IRR