Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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
image text in transcribed

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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Commodity Market Trading And Investment

Authors: Tom James

1st Edition

1137432802, 978-1137432803

More Books

Students also viewed these Finance questions

Question

Enhance the basic quality of your voice.

Answered: 1 week ago

Question

Describe the features of and process used by a writing team.

Answered: 1 week ago