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.
Background image of page 1
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
Background image of page 2
Show entire document
FIN625 Case Study: Hola-Kola HBS cases coursepack: http://cb.hbsp.harvard.edu/cbmp/access/49790625 Presentation: Your case analysis should: include a cover page of the report, with an executive summary (one to two paragraphs in length) along with your name. include numerical analysis (or printouts of your Excel worksheet) as listed in the following guideline. Provide justifications for the assumptions you have made in your analysis. include page numbers. Analytical guideline: In your review and analysis of the Hola-Kola case, in addition to your own questions and issues, you should also consider the following questions. In evaluating a potential investment project, the first thing is to examine the investment environment of the company? What do you think about the soda market in Mexico? What would be the potential risks and benefits of this investment to Hola-Kola? What are the key factors that could potentially affect the related cash flows and ultimately, the NPV? What are the relevant cash flows? Note: Define working capital requirement as: Receivables [(Sales/365)* average collection period] + Inventories [One month material costs] - Payables [(Material costs/365)*average payment period) Based on your projections of cash flows, compute a net present value (NPV) and internal rate of return (IRR). What is the breakeven point in sales that makes NPV equal to zero? Restore the value of sales after using the “Goal seek” function In addition to the above base case analysis, now conduct a scenario analysis based on the following two conditions. Scenario 1: the consultants advised that the energy costs, the labor costs, and the material costs are likely to rise by 5% a year, starting in Year 2. Assume that the company cannot pass the extra cost through to the customer, i.e. the company could not increase the price. Scenario 2: the consultants advised that the energy costs, the labor costs, and the material costs are likely to rise by 5% a year, starting in Year 2. Assume that the company can pass the extra cost through by increasing the price per unit by 5%. However, this would lead to a 2% drop in sales volume. What do you observe comparing these two scenarios to the base case? Based on your projections, would you recommend the company to invest in this project? Explain why.
Background image of page 1

Recently Asked Questions

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
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question