View the step-by-step solution to:

# FIN625 Capital Budgeting (Excel Modeling) From Sakai, download the Excel model that illustrates the details of cash flow analysis for the following...

What is the executive summary in Hola Kola case study

FIN625 Capital Budgeting (Excel Modeling) From Sakai, download the Excel model that illustrates the details of cash flow analysis for the following capital budgeting. Webmasters has developed a powerful new server that would be used for corporations’ Internet activities. The related assumptions about manufacturing and selling the servers are: Buy an equipment which costs \$10 million at year 0, and use 5-year MACRS rates for depreciation NWC t =10% (Sales t+1 ), and will return after the project is completed Sell 1,000 units per year, each server sell for \$24,000 with variable costs \$17, 500 per unit Sales price and variable costs will increase at the inflation rate of 3% after year 1; Non variable costs would be \$1 million at year 1, and would increase with inflation Project lasts 4 years, the salvage value of the equipment at the end of 4-year life is \$500,000 Tax rate=40%, and WACC=10% for average risk-project Part I: Basic setup Complete the model to determine the relevant cash flows, NPV and IRR of the project. Note: NPV is defned as the present value oF the expected Future cash ±ows less the iniTal cost oF the investment. However, the NPV FuncTon in Excel doesn’t really calculate NPV. Instead, despite the word “net,” the NPV FuncTon is really just a present value oF a series oF uneven cash ±ows. ²he IRR FuncTon works consistently with the defniTon. Part II: Additional analysis 1. Using Excel’s “Goal Seek” (in Data Tab, click What-If analysis, and choose the Goal Seek tab) tool, conduct a breakeven analysis of NPV in sales price, that is, to find the value of sales price that makes NPV equal to zero.
After seeking the breakeven point in sales, restore your input of sales price to the original value. 2. Sensitivity analysis You may examine the sensitivity of NPV to up to two particular changing factors using Data Table tool (also in “What-if analysis”) in Excel. For example, if sales price is the changing variable and you assume it may vary from \$18 to \$30 at a \$4 interval. - Select the area from cell A61 to cell E62, - In Cell A62, first set up a link between Data Table and above NPV result by entering =D57 - In Data Tab, click What-If analysis, and choose the Data Table tab - in the pop-up window, enter as the following: -click on “OK”, the corresponding NPVs are shown in row 62 for above assumptions about various level of price. 3. Scenario analysis. In this scenario analysis, sales price, variable costs per unit, and number of units sold are changing variables , while NPV and IRR are resulting variables . Before conducting a scenario analysis, it would be helpful to first define names for all related variables. To name a variable, for example, “Sales Price”, first select cell D7 and then click the Formulas tab and choose Define name . Assign the name “SalesPrice” to this cell, and give a worksheet-level scope. You may enter a comment about its purpose if you like. Repeat the naming process and define the names for the other two changing variables. Assume that in addition to the based case as described above, there are two alternative conditions: best-case conditions, with each of the changing variables being 20% better than its base-case value, will occur. And a worst-case conditions, with the variables 20% worse than those of the base case. The values under different conditions are shown in the following table. Base Worst Best Sales Volume(in units) 1,000 800 1,200 Sales price per unit 24.0 19.2 28.8 Variable cost per unit 17.5 21.0 14.0
Show entire document

### Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

### -

Educational Resources
• ### -

Study Documents

Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

Browse Documents