Investment Project Analysis for Buzz Motors
1) What are the steps to determine the free cash flows for this investment project?
2) What is the purchase price of the new equipment that makes this a break-even project?
3) How can you build a one-dimensional data table to analyze the impact of Initial Sales on NPV?
4) How do you create a two-dimensional data table to perform a sensitivity analysis on NPV with respect to Year 2 and Year 3 sales growth?
5) How can you compare different scenarios for Cost of Capital, Initial Sales, Year 2, and Year 3 sales growth using the Scenario Manager?
1) Determine the free cash flows for this investment project.
To determine the free cash flows for this investment project, you need to calculate the cash flows for each year. Start with the initial costs, including CAPEX, employee training, and miscellaneous expenses. Then, calculate sales revenue and unit costs based on the given information for each year (Year 1, Year 2, Year 3). Subtract all operating expenses, taxes, and the initial inventory cost. Finally, consider the salvage value of the equipment at the end of Year 3. The resulting cash flows for each year will give you the project's free cash flows.
2) What is the most you should pay for the new equipment and machinery?
The purchase price of the new equipment that makes this a break-even project is the initial investment cost (CAPEX) of $2,000,000. At this purchase price, the project's Net Present Value (NPV) will be zero.
3) Build a one-dimensional data table that varies Initial Sales (in units) of the new bike from 25 to 175 in steps of 25 to find how that affects the NPV.
Create a one-dimensional data table that varies Initial Sales (in units) from 25 to 175 in steps of 25. For each level of initial sales, calculate the NPV. Graph and format the results to visualize how changes in initial sales affect the project's NPV.
4) Build a two-dimensional data table that varies the year 2 sales growth from 0-45% and year 3 sales growth from 0 - 30% to analyze NPV sensitivity.
Build a two-dimensional data table that varies Year 2 sales growth from 0% to 45% in steps of 5% and Year 3 sales growth from 0% to 30% in steps of 5%. This will allow you to perform a sensitivity analysis on NPV. Format the data using Conditional Formatting, with positive NPV outcomes in green and negative NPV outcomes in red.
5) Compare different scenarios for Cost of Capital, Initial Sales, Year 2, and Year 3 sales growth using the Scenario Manager.
Create a base scenario with the base-case parameter values for Cost of Capital, Initial Sales, Year 2 sales growth, and Year 3 sales growth. Then, add two additional scenarios named Pat and Chris, each with the parameter values suggested by Pat and Chris. Use the Scenario Manager to show the impact on NPV and IRR for each scenario in a single output table. This will help you compare the different scenarios and their effects on project profitability.
Investment projects require thorough analysis to determine their viability and potential returns. Buzz Motors' investment project involves various expenses and revenue streams that need to be evaluated to calculate free cash flows, break-even points, and sensitivity to key variables.
Calculating Free Cash Flows
In order to determine the free cash flows for this investment project, you need to consider all relevant cash inflows and outflows over the project's lifespan. Start by calculating the initial costs such as CAPEX, training expenses, and miscellaneous costs. Then, estimate the sales revenue based on the sales forecast provided. Deduct the unit costs, operating expenses, taxes, and initial inventory costs. Finally, factor in the salvage value of the equipment at the end of the project to derive the free cash flows for each year.
Determining Break-Even Purchase Price
The purchase price of the new equipment that results in a break-even project is equal to the initial investment cost of $2,000,000. At this price point, the project's Net Present Value (NPV) will be zero, indicating no profit or loss from the investment.
Analyzing NPV Sensitivity
To assess the impact of Initial Sales on NPV, you can create a one-dimensional data table that varies the sales volume of the new product. By analyzing how different levels of initial sales affect the NPV, you can understand the project's profitability under various scenarios and make informed decisions.
A two-dimensional data table can be utilized to study the sensitivity of NPV to Year 2 and Year 3 sales growth rates. By exploring a range of growth scenarios for these variables, you can identify the factors that have the most significant impact on the project's financial performance.
Scenario Analysis
Use the Scenario Manager to compare different scenarios based on Cost of Capital, Initial Sales, and sales growth rates. By creating a base scenario and additional scenarios reflecting varying parameter values, you can evaluate the project's NPV and IRR under different conditions. This analysis helps to assess the project's robustness to changes in key variables and supports decision-making processes.