This is for the course Capital Budgeting and Financial Modeling

Use Excel to answe as instructed below.

All excel model should be created in a consistent format and follow the suggested best practice listed below:

Structure your model as clearly and simply as possible

Usable in your absence

Assumptions should be clustered together

Save often (Ctrl s) and keep saving new versions of your model; Excel does crash sometimes

Take time to take a step back and think

Use the previous problems or questions as a reference for this current question.

1. Suppose a firm is considering the following project, where all of the dollar figures are in

thousands of dollars. In year 0, the project requires $11,350 investment in plant and equipment, is depreciated using the straight-line method over seven years, and there is a salvage value of $1,400 in year 7. The project is forecast to generate sales of 2,000 units in year 1, rising to 7,400 units in year 5, declining to 1,800 units in year 7, and dropping to zero in year 8. The inflation rate is forecast to be 2.0% in year 1, rising to 4.0% in year 5, and then leveling off. The real cost of capital is forecast to be 11.0% in

year 1, rising to 12.2% in year 7. The tax rate is forecast to be a constant 35.0%. Sales revenue per unit is forecast to be $9.70 in year 1 and then grow with inflation. Variable cost per unit is forecast to be $7.40 in year 1 and then grow with inflation. Cash fixed costs are forecast to be $5,280 in year 1 and then grow with inflation. What is the project NPV?

2. What is the NPV Break-Even Point in Year 1 Unit Sales, where NPV equals zero? What is the

NPV Break-Even Point in the Year 2 Sales Growth Rate, where NPV equals zero?

3. Consider the same project as Project NPV - Basics. Let's examine the details of how you

forecast the project cash flows. Suppose that Direct Labor, Materials, Selling Expenses, and Other Variable Costs are forecast to be $3.50, $2.00, $1.20, and $0.70, respectively, in year 1 and then grow with inflation. Lease Payment, Property Taxes, Administration, Advertising, and Other cash fixed costs are forecast to be $2,800, $580, $450, $930, and $520, respectively, in year 1 and then grow with inflation. What is the Total Variable Cost / Unit and the Total Cash Fixed Costs?

Forecasting Cash Flow: Problem. Consider the same project as Project NPV - Basics. Let's examine the details of how you forecast the project cash flows. Suppose that Direct Labor, Materials, Selling Expenses, and Other Variable Costs are forecast to be $3.50, $2.00, $1.20, and $0.70, respectively, in year 1 and then grow with inflation. Lease Payment, Property Taxes, Administration, Advertising, and Other cash fixed costs are forecast to be $2,800, $580, $450, $930, and $520, respectively, in year 1 and then grow with inflation. What is the Total Variable Cost / Unit and the Total Cash Fixed Costs? Solution Strategy. Forecast the variable cost/ unit and cash fixed costs in more detail. Then sum up all the items in each category to get the total. Forecasting Cash Flow: Problem. Consider the same project as Project NPV - Basics. Let's examine the details of how you forecast the project cash flows. Suppose that Direct Labor, Materials, Selling Expenses, and Other Variable Costs are forecast to be $3.50, $2.00, $1.20, and $0.70, respectively, in year 1 and then grow with inflation. Lease Payment, Property Taxes, Administration, Advertising, and Other cash fixed costs are forecast to be $2,800, $580, $450, $930, and $520, respectively, in year 1 and then grow with inflation. What is the Total Variable Cost / Unit and the Total Cash Fixed Costs? Solution Strategy. Forecast the variable cost/ unit and cash fixed costs in more detail. Then sum up all the items in each category to get the total