60.1K

Verified Solution

Link Copied!

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

This is the full case. Read as reference and answer on excel

Break-Even Analysis: Problem. 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? Solution Strategy. Start with the Project NPV - Basics Excel model. Move the Unit Sales line out of the Key Assumptions area, since that is what we are going to solve for. Restructure the Unit Sales forecast to depend on the Sales Growth Rate, which will be a key variable. Structure the Sales Grow Rate forecast over the entire period to depend on how fast the growth rate is initially. This will make it easy to use Solver and to create a Data Table later. Project the cash flows of the project and calculate the NPV. Use Solver to determine the amount of year 1 unit sales that will cause the NPV to equal zero, when the sales growth rate is at the base case level of 5% per year. Use Solver to determine the sales growth rate that will cause the NPV to equal zero, when the year 1 unit sales is at the base case level of 39,000. Create a two-variable data table using two input variables (year 1 unit sales and sales growth rate) and the output variable: NPV. Financial Modeling Project Projecting NPV Problem. Suppose a firm is considering the following project, where all of the dolar figures are in thousands of dollars. In year 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 51,400 in year 7. The projects 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 & The inflation rate is forecast to be 2.0% in year 1, ring to 4.0% in years, and then leveling off. The real cost of capital is forecast to be 11.0% in year 1, ring to 12.2% in year 7. The tax rate as 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 57.40 in year 1 and then grow with inflesion. Cash fixed costs are forecast to be $5.280 in year 1 and then grow with inflation. What is the project NPV? Solution Strategy. Forecast key assumptions, discounting, sales revenue per unit, variable costs per unit, and fixed costs over the seven-year horizon. Then, forecast the project income and expense items. Calculate the net cash flows Discount each cash flow back to the present and sum to get the NPV Modeling Issue. The inflation rate is forecast separately and explicitly enters the calculation of: (1) the discount rate (cost of capital) and (2) price or cost/ unit items. This guarantees that we are consistent in the way we are treating the inflation component of cash flows in the numerator of the NPV calculation and the ination component of the discount rate in the denominator of the NPV calculation This avoids a common error in practice that people often treat the cash flows and discount rates as they were unrelated to each other and thus they are inconsistent in way that they implicity treat the infution component of each Problem. What is the NPV Break Even Point in Year 1 Unt Sales, where NPV equals er? What is the MPV Break Even Point in the Year 2 Sales Growth Rate, where NPV equals zero! Solution Strategy. Start with the Project NPV-Baucs Decel model Move the Unit Sales Be out of the Key Assumptions area, since that is what we are going to solve for Restructure the Unit Sales forecast to depend on the Sales Growth Rate, which will be a key variable. Structure the Sales Grow Rate forecast over the entire period to depend on how fast the growth rate is initially. This will make it easy to use Solver and to create Data Table later Project the cash flows of the project and cakulate the NPV. Uve Solver to determine the amount of year 1 unt sales that will cause the NPV to equal era, when the sales growth rates at the base case level of 5% per year. Use Solver to determine the sales growth rate that will cause the NPV to equal zero, when the year 1 unit sales is at the base case level of 35,000 Create a two-variable data table using two input variables (year 1 unt sales and sales growth rate and the output variable: NPV 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, Material, Selling Expenses, and Other Variable costs are forecast to be $3.50, $2.00. $1.20, and 50.70, respectively, in year 1 and then grow with inflation. Leave Payment, Property Taxes, Administration, Advertising and Other cash fixed costs are forecast to be $2,800 $580, $450, 5930 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/unt and cash fixed costs in more detail. Then sum up all the items in each category to get the total Forecasting Working Capital Problem. Consider the same project as above. Suppose we add that the project will require working capital in the amount of $0.87 in year 0 for every unit of next year's forecasted sales and this amount will grow with inflation going forward. What is the project NPV? Sensitivity Analysi Problem. Consider the same project as above. Assume that the product life cycle of seven years viewed as a safe bet, but that the scale of demand for the product is highly uncertain. Analyze the sensitivity of the project NPV to the unit's sales scale factor and to the cost of capital Solution Strategy. Copy the pattern of unit sales in the base case to a new location and multiply this pattern by a scale factor to get the new unt sales scenario. Assume that the real cost of capitalis constant. Thus, forecast the future cost of capital by taking the year 1 cost of capital and adding the change in the inflation rate Create a two-way data table using a range of input values for unts sales scale factor and a range of mput values for t or the year 1 cost of st of capital. Using the data table results, create 3-0 surface chart All excel model should be created in a consistent format and follow the suggested best practice isted Structure your model as clearly and simply as posible Usable in your absence Assumptions should be clustered together Save often (Ctrl ) and keep saving new versions of your model, Excel does crash sometimes ' Take time to take a step back and the Break-Even Analysis: Problem. 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? Solution Strategy. Start with the Project NPV - Basics Excel model. Move the Unit Sales line out of the Key Assumptions area, since that is what we are going to solve for. Restructure the Unit Sales forecast to depend on the Sales Growth Rate, which will be a key variable. Structure the Sales Grow Rate forecast over the entire period to depend on how fast the growth rate is initially. This will make it easy to use Solver and to create a Data Table later. Project the cash flows of the project and calculate the NPV. Use Solver to determine the amount of year 1 unit sales that will cause the NPV to equal zero, when the sales growth rate is at the base case level of 5% per year. Use Solver to determine the sales growth rate that will cause the NPV to equal zero, when the year 1 unit sales is at the base case level of 39,000. Create a two-variable data table using two input variables (year 1 unit sales and sales growth rate) and the output variable: NPV. Financial Modeling Project Projecting NPV Problem. Suppose a firm is considering the following project, where all of the dolar figures are in thousands of dollars. In year 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 51,400 in year 7. The projects 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 & The inflation rate is forecast to be 2.0% in year 1, ring to 4.0% in years, and then leveling off. The real cost of capital is forecast to be 11.0% in year 1, ring to 12.2% in year 7. The tax rate as 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 57.40 in year 1 and then grow with inflesion. Cash fixed costs are forecast to be $5.280 in year 1 and then grow with inflation. What is the project NPV? Solution Strategy. Forecast key assumptions, discounting, sales revenue per unit, variable costs per unit, and fixed costs over the seven-year horizon. Then, forecast the project income and expense items. Calculate the net cash flows Discount each cash flow back to the present and sum to get the NPV Modeling Issue. The inflation rate is forecast separately and explicitly enters the calculation of: (1) the discount rate (cost of capital) and (2) price or cost/ unit items. This guarantees that we are consistent in the way we are treating the inflation component of cash flows in the numerator of the NPV calculation and the ination component of the discount rate in the denominator of the NPV calculation This avoids a common error in practice that people often treat the cash flows and discount rates as they were unrelated to each other and thus they are inconsistent in way that they implicity treat the infution component of each Problem. What is the NPV Break Even Point in Year 1 Unt Sales, where NPV equals er? What is the MPV Break Even Point in the Year 2 Sales Growth Rate, where NPV equals zero! Solution Strategy. Start with the Project NPV-Baucs Decel model Move the Unit Sales Be out of the Key Assumptions area, since that is what we are going to solve for Restructure the Unit Sales forecast to depend on the Sales Growth Rate, which will be a key variable. Structure the Sales Grow Rate forecast over the entire period to depend on how fast the growth rate is initially. This will make it easy to use Solver and to create Data Table later Project the cash flows of the project and cakulate the NPV. Uve Solver to determine the amount of year 1 unt sales that will cause the NPV to equal era, when the sales growth rates at the base case level of 5% per year. Use Solver to determine the sales growth rate that will cause the NPV to equal zero, when the year 1 unit sales is at the base case level of 35,000 Create a two-variable data table using two input variables (year 1 unt sales and sales growth rate and the output variable: NPV 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, Material, Selling Expenses, and Other Variable costs are forecast to be $3.50, $2.00. $1.20, and 50.70, respectively, in year 1 and then grow with inflation. Leave Payment, Property Taxes, Administration, Advertising and Other cash fixed costs are forecast to be $2,800 $580, $450, 5930 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/unt and cash fixed costs in more detail. Then sum up all the items in each category to get the total Forecasting Working Capital Problem. Consider the same project as above. Suppose we add that the project will require working capital in the amount of $0.87 in year 0 for every unit of next year's forecasted sales and this amount will grow with inflation going forward. What is the project NPV? Sensitivity Analysi Problem. Consider the same project as above. Assume that the product life cycle of seven years viewed as a safe bet, but that the scale of demand for the product is highly uncertain. Analyze the sensitivity of the project NPV to the unit's sales scale factor and to the cost of capital Solution Strategy. Copy the pattern of unit sales in the base case to a new location and multiply this pattern by a scale factor to get the new unt sales scenario. Assume that the real cost of capitalis constant. Thus, forecast the future cost of capital by taking the year 1 cost of capital and adding the change in the inflation rate Create a two-way data table using a range of input values for unts sales scale factor and a range of mput values for t or the year 1 cost of st of capital. Using the data table results, create 3-0 surface chart All excel model should be created in a consistent format and follow the suggested best practice isted Structure your model as clearly and simply as posible Usable in your absence Assumptions should be clustered together Save often (Ctrl ) and keep saving new versions of your model, Excel does crash sometimes ' Take time to take a step back and the

Answer & Explanation Solved by verified expert

See Answer